OceanBase4.0版本PITR恢复-初试
本文尝试一下OceanBase中物理备份，做一次PITR恢复
环境准备
OceanBase当前物理备份恢复存储介质支持NFS和阿里云OSS，因为是分布式数据库需要将所有节点数据统一备份到一个地方，这里就使用NFS
NFS服务端部署
- 服务器端配置
- 安装NFS
sudo yum install nfs-utils
- 设置共享目录
mkdir -p /nfs_ob_backup echo "/nfs_ob_backup *(rw,sync,all_squash)" > /etc/exports
- 修改文件权限
chown -R nobody:nobody /nfs_ob_backup
- 配置NFS参数
echo "RPCNFSDCOUNT=8 RPCNFSDARGS="-N 2 -N 3 -U" NFSD_V4_GRACE=90 NFSD_V4_LEASE=90" > /etc/sysconfig/nfs
- 重启NFS服务器
sudo systemctl restart nfs-server
- 设置Slot Table
echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.conf sysctl -w sunrpc.tcp_max_slot_table_entries=128
NFS客户端部署
需要再所有OBServer服务器上执行
- 安装NFS
sudo yum install nfs-utils
- 设置Slot Table
echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.conf sysctl -w sunrpc.tcp_max_slot_table_entries=128
- 挂载目录
mkdir -p /data/nfs sudo mount -tnfs4 -o rw,nfsvers=4.1,sync,lookupcache=positive,hard,timeo=600,wsize=1048576,rsize=1048576,namlen=255 139.198.168.235:/nfs_ob_backup/ /data/nfs
模拟PITR恢复
OceanBase中物理备份恢复操作与大多数数据库一样，全备+归档日志进行恢复，OceanBase中也可以单独对某个租户设置归档和备份，做的比较灵活。
这里模拟一次drop table 操作，通过全备+归档恢复到指定时间点，大致步骤:
- 开启归档
- 创建测试表，插入部分数据
- 执行全量备份
- 插入部分数据
- 执行误操作 drop table
- 执行恢复
开启归档
需要再开启归档后才能进行数据库备份
设置归档和备份参数，这里设置下NFS挂载的路径，用户租户root用户执行:
用户租户root用户执行，如果有多个租户需要都都执行:
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive';
LOG_ARCHIVE_DEST设置备份路径，也有可选参数BINDING、PIECE_SWITCH_INTERVAL
BINDING用于设置归档和业务的优先模式。目前支持 Optional 模式和 Mandatory 模式
-
Optional 模式表示以用户业务优先。在该模式下，当归档（日志归档）速度跟上不日志生成的速度时，日志有可能来不及归档就被回收了，然后发生断流。
-
Mandatory 模式表示以归档优先。在该模式下如果归档跟不上用户数据的写入，可能会导致用户无法写入。
PIECE_SWITCH_INTERVAL 设置归档日志Piece切换周期，每一个Piece代表一个租户连续时间段内完成的归档日志，可设置为 ‘1d’ 或者 ‘2d’
开启归档,sys租户root用户开启归档:
ALTER SYSTEM ARCHIVELOG TENANT=ALL;
插入测试数据
使用用户租户tenant_2创建测试表:
obclient -u root@tenant_2 -h 127.0.0.1 -P 2883
创建测试表:
CREATE table ob_restore (id int , create_gtm datetime);
插入数据:
insert into ob_restore values (1, now());
insert into ob_restore values (2, now());
insert into ob_restore values (3, now());
obclient [ob_test]> select * from ob_restore;
+------+---------------------+
| id | create_gtm |
+------+---------------------+
| 1 | 2023-02-27 18:00:16 |
| 2 | 2023-02-27 18:00:16 |
| 3 | 2023-02-27 18:00:16 |
+------+---------------------+
3 rows in set (0.023 sec)
查看归档情况，STATUS状态为’DOING’代表正在进行中，在后面执行全备时需要保证为此状态 ：
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_ARCHIVELOG;
+-----------+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+---------------------------------+
| TENANT_ID | DEST_ID | ROUND_ID | INCARNATION | DEST_NO | STATUS | START_SCN | START_SCN_DISPLAY | CHECKPOINT_SCN | CHECKPOINT_SCN_DISPLAY | COMPATIBLE | BASE_PIECE_ID | USED_PIECE_ID | PIECE_SWITCH_INTERVAL | UNIT_SIZE | COMPRESSION | INPUT_BYTES | INPUT_BYTES_DISPLAY | OUTPUT_BYTES | OUTPUT_BYTES_DISPLAY | COMPRESSION_RATIO | DELETED_INPUT_BYTES | DELETED_INPUT_BYTES_DISPLAY | DELETED_OUTPUT_BYTES | DELETED_OUTPUT_BYTES_DISPLAY | COMMENT | PATH |
+-----------+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+---------------------------------+
| 1014 | 1001 | 1 | 1 | 0 | DOING | 1677491633506757143 | 2023-02-27 17:53:53.506757 | 1677491633506757143 | 2023-02-27 17:53:53.506757 | 1 | 1 | 1 | 86400000000 | 1 | none | 161692628 | 154.20MB | 161692628 | 154.20MB | 1.00 | 0 | 0.00MB | 0 | 0.00MB | | file:///data/nfs/backup/archive |
+-----------+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+---------------------------------+
1 row in set (0.013 sec)
执行全量备份
设置全量备份参数，用户租户root用户执行
ALTER SYSTEM SET data_backup_dest='file:///data/nfs/backup/data';
执行全量备份, sys租户执行
ALTER SYSTEM BACKUP DATABASE;
备份结果通过CDB_OB_BACKUP_TASK_HISTORY查看，这里能看到备份集结束的时间点是"2023-02-27 18:12:15.127795":
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_TASK_HISTORY;
+-----------+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+------------------------------+
| TENANT_ID | TASK_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | START_TIMESTAMP | END_TIMESTAMP | STATUS | START_SCN | END_SCN | USER_LS_START_SCN | ENCRYPTION_MODE | PASSWD | INPUT_BYTES | OUTPUT_BYTES | OUTPUT_RATE_BYTES | EXTRA_META_BYTES | TABLET_COUNT | FINISH_TABLET_COUNT | MACRO_BLOCK_COUNT | FINISH_MACRO_BLOCK_COUNT | FILE_COUNT | META_TURN_ID | DATA_TURN_ID | RESULT | COMMENT | PATH |
+-----------+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+------------------------------+
| 1014 | 1 | 1 | 1 | 1 | 2023-02-27 18:03:33.956865 | 2023-02-27 18:12:15.127795 | COMPLETED | 1677492204364518000 | 1677492724953601000 | 1677492220369223584 | NONE | | 342590340 | 342590340 | 657347.3697 | 0 | 1054 | 1054 | 163 | 163 | 0 | 1 | 1 | 0 | | file:///data/nfs/backup/data |
+-----------+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+------------------------------+
1 row in set (0.003 sec)
再次插入测试数据
再次插入一些数据，这部分数据没有在全备中，但是在归档中
insert into ob_restore values (4, now());
insert into ob_restore values (5, now());
insert into ob_restore values (6, now());
obclient [ob_test]> select * from ob_restore;
+------+---------------------+
| id | create_gtm |
+------+---------------------+
| 1 | 2023-02-27 18:00:16 |
| 2 | 2023-02-27 18:00:16 |
| 3 | 2023-02-27 18:00:16 |
| 4 | 2023-02-27 18:18:00 |
| 5 | 2023-02-27 18:18:00 |
| 6 | 2023-02-27 18:18:00 |
+------+---------------------+
6 rows in set (0.001 sec)
obclient [ob_test]> drop table ob_restore;
Query OK, 0 rows affected (0.412 sec)
执行恢复
执行恢复前需要确认误操作对应的时间点，OceanBase中恢复可以执行SCN/时间点，这里需要找到对应操作的时间点(SCN还没有找的方法)
obclient [oceanbase]> select * from GV$OB_SQL_AUDIT where tenant_name = 'tenant_2' and query_sql like '%drop table%';
+---------------+----------+------------+-------------+-----------------------------------+------------+---------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+-----------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------+----------------+--------+--------+----+--------+----+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+------------------+--------------+-----------------------+---------------+-----------+--------------------+--------------+
| SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE |
+---------------+----------+------------+-------------+-----------------------------------+------------+---------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+-----------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------+----------------+--------+--------+----+--------+----+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+------------------+--------------+-----------------------+---------------+-----------+--------------------+--------------+
| 10.140.114.12 | 2882 | 176 | 114054550 | YB420A8C720C-0005F52222306564-0-0 | 3221735236 | 10.140.114.12 | 35186 | 1014 | tenant_2 | 1014 | 200001 | root | 0 | 127.0.0.1 | 500002 | ob_test | 088AEA236B3799E0C5D0ABA45FEB8A23 | drop table ob_restore | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | sleep wait | sleep_interval | 100000 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED KNOWN TIME | 100063 | 408310 | 5 | 1 | 0 | 0 | 0 | 0 | 1677493163217505 | 411829 | 0 | 2 | 23 | 1 | 87 | 411700 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 245200 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 2 | 0 | NULL | 0 | 0 | |
+---------------+----------+------------+-------------+-----------------------------------+------------+---------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+---------+----------------------------------+-----------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------+----------------+--------+--------+----+--------+----+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+------------------+--------------+-----------------------+---------------+-----------+--------------------+--------------+
1 rows in set (0.083 sec)
这里通过GV$OB_SQL_AUDIT视图查询到drop table 操作的REQUEST_TIME时间戳， 转换成时间，这个时间要大于全备完成的时间"2023-02-27 18:12:15.127795"，所以在恢复时有部分数据需要从归档中进行恢复
obclient [oceanbase]> select from_unixtime(1677493163.217505);
+----------------------------------+
| from_unixtime(1677493163.217505) |
+----------------------------------+
| 2023-02-27 18:19:23.217505 |
+----------------------------------+
1 row in set (0.001 sec)
恢复前创建资源，这个资源作为租户恢复后，所使用的资源
CREATE RESOURCE UNIT S2 MAX_CPU 4, MEMORY_SIZE = '4G', MAX_IOPS 10240, MIN_IOPS=10240;
CREATE RESOURCE POOL pool_2 unit = 'S2', unit_num = 1, zone_list = ('zone1','zone2','zone3');
执行恢复
ALTER SYSTEM RESTORE tenant_2 FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' until TIME='2023-02-27 18:19:23.217505' WITH 'pool_list=pool_2';
上面步骤是从备份中恢复tenant_2这个租户，使用pool_2这个资源，如果之前系统中存在相同的租户名，恢复时会报错，这里可以做法就是恢复前删除租户，直接利用租户之前的资源进行恢复。
恢复时进度查看,STATUS有多个状态，可以查看:
查看恢复进度 这里介绍更详细
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_PROGRESS;
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+------------------------------------------------+-------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST | RESTORE_OPTION | RESTORE_SCN | RESTORE_SCN_DISPLAY | STATUS | START_TIMESTAMP | BACKUP_SET_LIST | BACKUP_PIECE_LIST | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+------------------------------------------------+-------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
| 1 | 9 | tenant_2 | 1018 | tenant_2 | 1014 | ob_cluster | file:///data/nfs/backup/data,file:///data/nfs/backup/archive | pool_list=pool_2 | 1677493163217505000 | 2023-02-27 18:19:23.217505 | WAIT_TENANT_RESTORE_FINISH | 2023-02-27 18:31:51.210085 | file:///data/nfs/backup/data/backup_set_1_full | file:///data/nfs/backup/archive/piece_d1001r1p1 | NULL | NULL | NULL | NULL | |
| 1018 | 9 | tenant_2 | 1018 | tenant_2 | 1014 | ob_cluster | file:///data/nfs/backup/data,file:///data/nfs/backup/archive | pool_list=pool_2 | 1677493163217505000 | 2023-02-27 18:19:23.217505 | RESTORE_WAIT_LS | 2023-02-27 18:31:51.210085 | file:///data/nfs/backup/data/backup_set_1_full | file:///data/nfs/backup/archive/piece_d1001r1p1 | 342590340 | 326.72MB | 0 | 0.00MB | |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+------------------+---------------------+----------------------------+----------------------------+----------------------------+------------------------------------------------+-------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
2 rows in set (0.010 sec)
恢复完成后，结果查看:
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_HISTORY;
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+---------------------+----------------------------+------------------------+----------------------------+----------------------------+---------+-------------------------------------------------+------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST | RESTORE_SCN | RESTORE_SCN_DISPLAY | RESTORE_OPTION | START_TIMESTAMP | FINISH_TIMESTAMP | STATUS | BACKUP_PIECE_LIST | BACKUP_SET_LIST | BACKUP_CLUSTER_VERSION | LS_COUNT | FINISH_LS_COUNT | TABLET_COUNT | FINISH_TABLET_COUNT | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION | COMMENT |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+---------------------+----------------------------+------------------------+----------------------------+----------------------------+---------+-------------------------------------------------+------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| 1 | 9 | tenant_2 | 1018 | tenant_2 | 1014 | ob_cluster | file:///data/nfs/backup/data,file:///data/nfs/backup/archive | 1677493163217505000 | 2023-02-27 18:19:23.217505 | pool_list=pool_2 | 2023-02-27 18:31:51.210085 | 2023-02-27 18:51:37.373109 | SUCCESS | file:///data/nfs/backup/archive/piece_d1001r1p1 | file:///data/nfs/backup/data/backup_set_1_full | 17179869184 | 4 | 0 | 521 | 0 | 342590340 | 326.72MB | 0 | 0.00MB | NULL | NULL |
| 1018 | 9 | tenant_2 | 1018 | tenant_2 | 1014 | ob_cluster | file:///data/nfs/backup/data,file:///data/nfs/backup/archive | 1677493163217505000 | 2023-02-27 18:19:23.217505 | pool_list=pool_2 | 2023-02-27 18:31:51.210085 | 2023-02-27 18:51:23.510916 | SUCCESS | file:///data/nfs/backup/archive/piece_d1001r1p1 | file:///data/nfs/backup/data/backup_set_1_full | 17179869184 | 4 | 0 | 521 | 0 | 342590340 | 326.72MB | 0 | 0.00MB | NULL | NULL |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+--------------------------------------------------------------+---------------------+----------------------------+------------------------+----------------------------+----------------------------+---------+-------------------------------------------------+------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
5 rows in set (0.022 sec)
将恢复后租户转为主租户，登录sys租户执行:
ALTER SYSTEM ACTIVATE STANDBY TENANT tenant_2;
这时恢复全部完成，可登录tenant_2租户查看数据恢复情况:
obclient [ob_test]> select * from ob_restore;
+------+---------------------+
| id | create_gtm |
+------+---------------------+
| 1 | 2023-02-27 18:00:16 |
| 2 | 2023-02-27 18:00:16 |
| 3 | 2023-02-27 18:00:16 |
| 4 | 2023-02-27 18:18:00 |
| 5 | 2023-02-27 18:18:00 |
| 6 | 2023-02-27 18:18:00 |
+------+---------------------+
6 rows in set (0.002 sec)
备份清理
通常需要设置备份集保留个数，防止归档和备份集过大，可以设置备份自动清理策略，设置后每1个小时会做一次检查
在sys租户下执行，当前仅支持设置租户级清理策略，不支持设置集群级清理策略，每条命令只能为一个租户设置一个清理策略
ALTER SYSTEM ADD DELETE BACKUP POLICY 'default' RECOVERY_WINDOW '1d' TENANT tenant_2;
设置后超过一天且不会再用到备份和归档会被自动清理:
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BACKUP_JOB_HISTORY;
+-----------+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+------------------------------+
| TENANT_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | PLUS_ARCHIVELOG | BACKUP_TYPE | JOB_LEVEL | ENCRYPTION_MODE | PASSWD | START_TIMESTAMP | END_TIMESTAMP | STATUS | RESULT | COMMENT | DESCRIPTION | PATH |
+-----------+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+------------------------------+
| 1 | 8 | 1 | 0 | 1 | 8 | 1018 | OFF | FULL | CLUSTER | NONE | | 2023-02-28 13:46:23.748677 | 2023-02-28 14:15:33.360413 | COMPLETED | 0 | | | |
| 1 | 9 | 1 | 0 | 1 | 9 | 1018 | OFF | FULL | CLUSTER | NONE | | 2023-02-28 14:17:44.693446 | 2023-02-28 14:44:43.421884 | COMPLETED | 0 | | | |
| 1 | 10 | 1 | 0 | 1 | 10 | 1018 | OFF | FULL | CLUSTER | NONE | | 2023-03-01 10:31:03.230361 | 2023-03-01 11:22:57.676013 | COMPLETED | 0 | | | |
| 1018 | 3 | 1 | 1 | 1 | 8 | 1018 | OFF | FULL | USER_TENANT | NONE | | 2023-02-28 13:46:23.767527 | 2023-02-28 14:15:33.278656 | COMPLETED | 0 | | | file:///data/nfs/backup/data |
| 1018 | 5 | 1 | 2 | 1 | 9 | 1018 | OFF | FULL | USER_TENANT | NONE | | 2023-02-28 14:17:44.713059 | 2023-02-28 14:44:43.383928 | COMPLETED | 0 | | | file:///data/nfs/backup/data |
| 1018 | 26 | 1 | 3 | 1 | 10 | 1018 | OFF | FULL | USER_TENANT | NONE | | 2023-03-01 10:31:03.248605 | 2023-03-01 11:22:57.609095 | COMPLETED | 0 | | | file:///data/nfs/backup/data |
+-----------+--------+-------------+---------------+---------------------+------------------+--------------------+-----------------+-------------+-------------+-----------------+--------+----------------------------+----------------------------+-----------+--------+---------+-------------+------------------------------+
13 rows in set (0.004 sec)
最早的备份是一天前"2023-02-28 14:15:33.278656"完成，到达1天后会清理这个备份
obclient [oceanbase]> SELECT * FROM CDB_OB_BACKUP_DELETE_JOBS;
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+---------------+--------+------------+--------------------+--------+---------+
| TENANT_ID | JOB_ID | INCARNATION | INITIATOR_TENANT_ID | INITIATOR_JOB_ID | EXECUTOR_TENANT_ID | TYPE | PARAMETER | JOB_LEVEL | START_TIMESTAMP | END_TIMESTAMP | STATUS | TASK_COUNT | SUCCESS_TASK_COUNT | RESULT | COMMENT |
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+---------------+--------+------------+--------------------+--------+---------+
| 1018 | 31 | 1 | 1018 | 31 | 1018 | DELETE OBSOLETE BACKUP | 2023-02-28 15:17:56.682094 | USER_TENANT | 2023-03-01 15:17:56.683163 | NULL | DOING | 1 | 0 | 0 | |
+-----------+--------+-------------+---------------------+------------------+--------------------+------------------------+----------------------------+-------------+----------------------------+---------------+--------+------------+--------------------+--------+---------+
1 row in set (0.005 sec)
通过CDB_OB_BACKUP_DELETE_JOBS可以查看到"2023-03-01 15:17:56.683163"开始启动清理备份任务，清理条件是"2023-02-28 15:17:56.682094"时间点之前的备份
历史的清理任务可以查看CDB_OB_BACKUP_DELETE_JOB_HISTORY试图
相关视图
备份恢复中视图大致分为两类， 一类是正在进行中的任务，另外是执行完后会归档到HISTORY中，其中又回分sys租户下查看的以CDB_*开头，是可以查看集群内所有租户的备份恢复信息，DBA_*开头查看的是当前租户下的信息
日志相关视图:https://www.oceanbase.com/docs/community-observer-cn-10000000000901789
数据备份相关视图:https://www.oceanbase.com/docs/community-observer-cn-10000000000901797
清理备份相关视图:https://www.oceanbase.com/docs/community-observer-cn-10000000000901780
恢复数据相关视图:https://www.oceanbase.com/docs/community-observer-cn-10000000000901805
最后修改时间：2023-03-10 17:28:10
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
