“让技术被看见 | OceanBase 布道师计划” 由OceanBase主办,墨天轮社区协办,面向广大开发者的年度征文活动。全年 4 轮,以季度为周期进行优秀文章评比,每年 1 届,以年为单位进行最佳布道师评选。目前,首轮技术征文获奖文章已评选出炉,本篇内容为「OceanBase 布道师计划」优秀文章之一,作者 凡尘dba!
活动仍在进行中,欢迎感兴趣的小伙伴「点击此处」进入活动官网,了解活动详情或进一步投稿。🥳
评委有话说:
老鱼(资深 IT 媒体人、《老鱼笔记》主理人):文章介绍了 OceanBase v4.2.1 表级恢复的实操过程,包括原理、流程、限制及具体操作步骤,对 DBA 有较大指导意义。
作为一名 DBA,数据库的备份与恢复都是异常重要的,日常我们也许更关注备份,但在真实的故障场景下,数据恢复反而更为重要,过长的恢复时间可能满足不了 RTO 的要求。本文基于官方文档 OceanBase v4.2.1按表恢复 ,探讨当OB的表delete部分数据之后,应该如何恢复。
一、原理
OceanBase 数据库的表级恢复功能是通过从备份数据中将用户指定的表恢复到一个已存在的租户中来实现的,并且该已存在的租户与原表所在的租户可以是同一个租户,也可以是同一集群中的不同租户,还可以是不同集群中的租户。
二、恢复流程
首先,在辅助租户中将数据恢复到指定时间点;
再将指定的表从辅助租户跨租户导入到目标租户中;
最后清理辅助租户。
注意:表级恢复过程中需要使用辅助租户(辅助租户只是在表级恢复中过度的隐藏租户,是表恢复过程中需要消耗的额外的计算存储资源,恢复完之后就会自动清理,即后文中的AUX_TENANT_NAME: AUX_RECOVER$1732603529353483),因此,在进行表恢复前,您需要在目标租户所在的集群内为辅助租户创建所需的资源池。
三、限制

1、OB集群版本4.x及以上,OCP上暂不支持表级恢复,只支持租户级恢复。
2、对于 V4.2.1 BP2(不含该版本)之前版本,不支持恢复表上的外键、触发器及统计信息等。
3、与租户级恢复一样,表级恢复当前也是仅支持将低版本的备份数据中的表恢复到同版本或高版本中,同版本下的小版本之间也不支持逆向恢复。
四、操作步骤
1、登录sys租户,创建一个与待恢复表租户配置一样的辅助租户资源池
① 查看源租户资源单元配置
select * from oceanbase.DBA_OB_UNIT_CONFIGS where UNIT_CONFIG_ID in (select UNIT_CONFIG_ID from dba_ob_resource_pools where tenant_id in (select tenant_id from dba_ob_tenants where tenant_name='fanchen' limit 1));② 创建 Unit(资源单元)
CREATE RESOURCE UNIT restore_3c4g MAX_CPU 3,MIN_CPU 1, MEMORY_SIZE = '4G',IOPS_WEIGHT=1;③ 查看源租户资源池配置
select * from dba_ob_resource_pools where tenant_id in (select tenant_id from dba_ob_tenants where tenant_name='fanchen' limit 1);④ 创建 Resource Pool(资源池)。
为辅助租户创建资源池时,建议尽量与源租户保持同构,即建议辅助租户的资源池中 unit_num 的个数与源租户相同。
CREATE RESOURCE POOL restore_pool unit = 'restore_3c4g', unit_num = 1, zone_list = ('zone1','zone2','zone3');2、查看备份路径和归档路径
登录用户租户
--备份路径
select * from oceanbase.dba_ob_backup_job_history order by start_timestamp desc limit 3\G--归档路径
select * from oceanbase.dba_ob_archive_dest;或者登录sys租户
--备份路径
select * from oceanbase.cdb_ob_backup_job_history where tenant_id in (select tenant_id from oceanbase.dba_ob_tenants where tenant_name='fanchen' limit 1) order by start_timestamp desc limit 3\G
--归档路径
select * from oceanbase.cdb_ob_archive_dest where tenant_id in (select tenant_id from oceanbase.dba_ob_tenants where tenant_name='fanchen' limit 1);3、sys租户开始恢复
ALTER SYSTEM
RECOVER TABLE fanchendb.t1
TO TENANT fanchen
FROM 's3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***,s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***'
UNTIL TIME='2024-11-26 10:37:00'
WITH 'pool_list=restore_pool'
REMAP TABLE fanchendb.t1:t1_new;① RECOVER TABLE:如需恢复多个表,database_name.table_name1,database_name.table_name2,...,多个表之间使用英文逗号(,)分隔。
其中指定 database_name 和 table_name 时:
table_name需要与系统实际存储的表名一致。例如,Oracle 模式租户下创建表test,而系统内部实际存储的表名为TEST,故在恢复表时需要指定表名为TEST,否则系统会报错,提示表不存在。- 对于
database_name或table_name中含有特殊字符的场景,含特殊字符的database_name或table_name需要放在反引号(``)内。 - 如果需要恢复一个 Database 下的所有表,则可以表示为
database_name.*。 - 如果需要恢复租户下的所有用户表,则可以表示为
*.*。
② FROM:根据步骤2查看的备份路径和归档路径,按照以下官方文档给出的S3对象存储路径格式填写,注意:需要知道对象存储的host、access_id和access_key,不需要encrypt_key
's3://oceanbase-test-bucket/backup/data/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***,s3://oceanbase-test-bucket/backup/archive/?host=obs.***.myhuaweicloud.com&access_id=***&access_key=***'③ REMAP TABLE:重命名恢复后的表名。支持仅重命名表名,表所属的 Database 不变;也支持表名不变,仅重命名到其他 Database;还支持重命名表名,同时所属的 Database 重命名为其他 Database。源对象与重命名对象之间使用英文冒号(:)连接,具体格式的示例如下:
- 表名从
student重命名为student2,表所属的 Database 不变:REMAP TABLE school.student:student2。
当所属的 Database 不变时,在恢复到目标租户创建表时,系统默认会将表恢复到目标租户同名的 Database 中,如果同名的 Database 不存在,则表恢复就会失败。
- 表名不变,表所属的 Database 从
school改为college:REMAP TABLE school.student:college.student。 - 表名从
student重命名为student2,表所属的 Database 从school改为college:REMAP TABLE school.student:college.student2。 - 将
school下的所有表恢复到college中:REMAP TABLE school.*:college.*
4、sys租户查看恢复进度
SELECT * FROM oceanbase.CDB_OB_RECOVER_TABLE_JOBS\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 1310
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 0
START_TIMESTAMP: 2024-11-26 14:45:31.673222
FINISH_TIMESTAMP: NULL
STATUS: RECOVERING
AUX_TENANT_NAME: AUX_RECOVER$1732603529353483
TARGET_TENANT_NAME: fanchen
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT:
COMMENT:
DESCRIPTION: NULL
*************************** 2. row ***************************
TENANT_ID: 1040
JOB_ID: 2574
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1310
START_TIMESTAMP: 2024-11-26 14:45:31.673222
FINISH_TIMESTAMP: NULL
STATUS: IMPORTING
AUX_TENANT_NAME: AUX_RECOVER$1732603529353483
TARGET_TENANT_NAME: fanchen
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT:
COMMENT:
DESCRIPTION: NULL
2 rows in set (0.10 sec)视图 CDB_OB_RECOVER_TABLE_JOBS 中对应两条任务记录:一条 sys 租户的任务记录和一条目标用户租户自身的任务记录。AUX_RECOVER$1732603529353483 为辅助租户,backup_tenant 为目标用户租户。
STATUS 表示按表恢复当前的阶段:
PREPARE:任务初始化阶段RECOVERING:等待目标租户完成恢复中,仅sys租户会显示该状态。RESTORE_AUX_TENANT:恢复辅助租户PRECHECK_IMPORT:导入前检查GEN_IMPORT_JOB:导入任务初始化IMPORTING:表导入中CANCELING:任务取消中COMPLETED:任务完成FAILED:任务失败
查看辅助租户恢复任务的进度
SELECT * FROM oceanbase.CDB_OB_RESTORE_PROGRESS\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 10
RESTORE_TENANT_NAME: AUX_RECOVER$1732603529353483
RESTORE_TENANT_ID: 1066
BACKUP_TENANT_NAME: fanchen
BACKUP_TENANT_ID: 1040
BACKUP_CLUSTER_NAME: test
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
RESTORE_OPTION: pool_list=restore_pool
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
STATUS: WAIT_TENANT_RESTORE_FINISH
START_TIMESTAMP: 2024-11-26 14:45:31.682045
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
RECOVER_SCN: NULL
RECOVER_SCN_DISPLAY: NULL
RECOVER_PROGRESS: NULL
TABLET_COUNT: NULL
FINISH_TABLET_COUNT: NULL
RESTORE_PROGRESS: NULL
TOTAL_BYTES: NULL
TOTAL_BYTES_DISPLAY: NULL
FINISH_BYTES: NULL
FINISH_BYTES_DISPLAY: NULL
DESCRIPTION:
*************************** 2. row ***************************
TENANT_ID: 1066
JOB_ID: 10
RESTORE_TENANT_NAME: AUX_RECOVER$1732603529353483
RESTORE_TENANT_ID: 1066
BACKUP_TENANT_NAME: fanchen
BACKUP_TENANT_ID: 1040
BACKUP_CLUSTER_NAME: test
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
RESTORE_OPTION: pool_list=restore_pool
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
STATUS: RESTORING
START_TIMESTAMP: 2024-11-26 14:45:31.682045
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
RECOVER_SCN: 1732554064400603357
RECOVER_SCN_DISPLAY: 2024-11-26 01:01:04.400603
RECOVER_PROGRESS: 0.00
TABLET_COUNT: 0
FINISH_TABLET_COUNT: 0
RESTORE_PROGRESS: NULL
TOTAL_BYTES: 60645195
TOTAL_BYTES_DISPLAY: 57.84MB
FINISH_BYTES: 0
FINISH_BYTES_DISPLAY: 0.00MB
DESCRIPTION:
2 rows in set (0.05 sec)查看目标租户导入任务的进度
SELECT * FROM oceanbase.CDB_OB_IMPORT_TABLE_JOBS\G
*************************** 1. row ***************************
TENANT_ID: 1040
JOB_ID: 2575
INITIATOR_TENANT_ID: 1040
INITIATOR_JOB_ID: 2574
START_TIMESTAMP: 2024-11-26 14:50:32.456207
FINISH_TIMESTAMP: NULL
SRC_TENANT_NAME: AUX_RECOVER$1732603529353483
SRC_TENANT_ID: 1066
STATUS: IMPORT_TABLE
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
TOTAL_TABLE_COUNT: 1
FINISHED_TABLE_COUNT: 0
FAILED_TABLE_COUNT: 0
RESULT:
COMMENT:
DESCRIPTION: NULL
1 row in set (0.04 sec)查看每张表导入的详细信息
SELECT * FROM oceanbase.CDB_OB_IMPORT_TABLE_TASKS\G5、查看按表恢复结果
SELECT * FROM oceanbase.CDB_OB_RECOVER_TABLE_JOB_HISTORY\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 1310
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 0
START_TIMESTAMP: 2024-11-26 14:45:31.673222
FINISH_TIMESTAMP: 2024-11-26 14:53:26.602250
STATUS: COMPLETED
AUX_TENANT_NAME: AUX_RECOVER$1732603529353483
TARGET_TENANT_NAME: fanchen
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT: SUCCESS
COMMENT: import succeed table count: 1, failed table count: 0
DESCRIPTION: NULL
*************************** 2. row ***************************
TENANT_ID: 1040
JOB_ID: 2574
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 1310
START_TIMESTAMP: 2024-11-26 14:45:31.673222
FINISH_TIMESTAMP: 2024-11-26 14:52:33.281053
STATUS: COMPLETED
AUX_TENANT_NAME: AUX_RECOVER$1732603529353483
TARGET_TENANT_NAME: fanchen
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT: SUCCESS
COMMENT: import succeed table count: 1, failed table count: 0
DESCRIPTION: NULL
2 rows in set (0.06 sec)COMMENT 用于记录任务失败时的相关错误信息。
查看辅助租户恢复任务的结果
SELECT * FROM oceanbase.CDB_OB_RESTORE_HISTORY\G
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 10
RESTORE_TENANT_NAME: AUX_RECOVER$1732603529353483
RESTORE_TENANT_ID: 1066
BACKUP_TENANT_NAME: fanchen
BACKUP_TENANT_ID: 1040
BACKUP_CLUSTER_NAME: test
BACKUP_DEST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog?***
RESTORE_SCN: 1732588620000000000
RESTORE_SCN_DISPLAY: 2024-11-26 10:37:00.000000
RESTORE_OPTION: pool_list=restore_pool
START_TIMESTAMP: 2024-11-26 14:45:31.682045
FINISH_TIMESTAMP: 2024-11-26 14:50:26.502451
STATUS: SUCCESS
BACKUP_PIECE_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/clog/piece_d1005r3p166?***
BACKUP_SET_LIST: s3://db-ob-backup/test/1691672001/tenant_incarnation_1/1040/data/backup_set_82_full?***
BACKUP_CLUSTER_VERSION: 17180000520
LS_COUNT: 4
FINISH_LS_COUNT: 0
TABLET_COUNT: 1004
FINISH_TABLET_COUNT: 1004
TOTAL_BYTES: 60645195
TOTAL_BYTES_DISPLAY: 57.84MB
FINISH_BYTES: 0
FINISH_BYTES_DISPLAY: 0.00MB
DESCRIPTION: NULL
COMMENT:
1 row in set (0.13 sec)查看目标租户跨租户导入任务的结果
SELECT * FROM oceanbase.CDB_OB_IMPORT_TABLE_JOB_HISTORY\G
*************************** 1. row ***************************
TENANT_ID: 1040
JOB_ID: 2575
INITIATOR_TENANT_ID: 1040
INITIATOR_JOB_ID: 2574
START_TIMESTAMP: 2024-11-26 14:50:32.456207
FINISH_TIMESTAMP: 2024-11-26 14:51:33.252306
SRC_TENANT_NAME: AUX_RECOVER$1732603529353483
SRC_TENANT_ID: 1066
STATUS: IMPORT_FINISH
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `fanchendb`.`t1`
REMAP_DB_LIST:
REMAP_TABLE_LIST: `fanchendb`.`t1`:`fanchendb`.`t1_new`
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
TOTAL_TABLE_COUNT: 1
FINISHED_TABLE_COUNT: 1
FAILED_TABLE_COUNT: 0
RESULT: SUCCESS
COMMENT: import succeed table count: 1, failed table count: 0
DESCRIPTION: NULL
1 row in set (0.18 sec)查看每张表导入的结果
SELECT * FROM oceanbase.CDB_OB_IMPORT_TABLE_TASK_HISTORY\G
*************************** 1. row ***************************
TENANT_ID: 1040
TASK_ID: 76821979
JOB_ID: 2575
SRC_TENANT_ID: 1066
SRC_TABLESPACE: NULL
SRC_TABLEGROUP: NULL
SRC_DATABASE: fanchendb
SRC_TABLE: t1
SRC_PARTITION: NULL
TARGET_TABLESPACE: NULL
TARGET_TABLEGROUP: NULL
TARGET_DATABASE: fanchendb
TARGET_TABLE: t1_new
TABLE_COLUMN: 21
STATUS: FINISH
START_TIMESTAMP: 2024-11-26 14:50:32.550967
COMPLETION_TIMESTAMP: 2024-11-26 14:51:33.231243
CUMULATIVE_TS: -1
TOTAL_INDEX_COUNT: 0
IMPORTED_INDEX_COUNT: 0
FAILED_INDEX_COUNT: 0
TOTAL_CONSTRAINT_COUNT: 0
IMPORTED_CONSTRAINT_COUNT: 0
FAILED_CONSTRAINT_COUNT: 0
TOTAL_REF_CONSTRAINT_COUNT: 0
IMPORTED_REF_CONSTRAINT_COUNT: 0
FAILED_REF_CONSTRAINT_COUNT: 0
RESULT: SUCCESS
COMMENT:
1 row in set (0.07 sec)6、取消恢复
ALTER SYSTEM CANCEL RECOVER TABLE dest_tenant_name;dest_tenant_name 需要填写表待恢复的目标租户名
7、手动释放为辅助租户创建的资源池和资源单元
drop resource pool restore_pool;
drop resource unit restore_3c4g;





