运维首页 > 数据运维 > OceanBase物理备份恢复初试

OceanBase物理备份恢复初试

2024年 5月 7日 68.4k 0

OceanBase物理备份恢复-初试

本文尝试一下OceanBase中物理备份，做一次PITR恢复

环境准备

OceanBase当前物理备份恢复存储介质支持NFS和阿里云OSS，因为是分布式数据库需要将所有节点数据统一备份到一个地方，这里就使用NFS

NFS服务端部署

  • 服务器端配置

1. 安装NFS

sudo yum install nfs-utils

2. 设置共享目录

mkdir -p /nfs_ob_backupecho "/nfs_ob_backup *(rw,sync,all_squash)" > /etc/exports

3. 修改文件权限

chown -R nobody:nobody /nfs_ob_backup

4. 配置NFS参数

echo "RPCNFSDCOUNT=8RPCNFSDARGS="-N 2 -N 3 -U"NFSD_V4_GRACE=90NFSD_V4_LEASE=90" > /etc/sysconfig/nfs

5. 重启NFS服务器

sudo systemctl restart nfs-server

6. 设置Slot Table

echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries=128

NFS客户端部署

需要再所有OBServer服务器上执行

1. 安装NFS

sudo yum install nfs-utils

2. 设置Slot Table

echo "sunrpc.tcp_max_slot_table_entries=128" >> /etc/sysctl.confsysctl -w sunrpc.tcp_max_slot_table_entries=128

3. 挂载目录

mkdir -p /data/nfssudo 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 操作，通过全备+归档恢复到指定时间点，大致步骤:

  1. 开启归档
  2. 创建测试表，插入部分数据
  3. 执行全量备份
  4. 插入部分数据
  5. 执行误操作 drop table
  6. 执行恢复

开启归档

需要再开启归档后才能进行数据库备份

设置归档和备份参数，这里设置下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

大树
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

大树

这个人很懒，什么都没有留下～

文章8568 评论0