前言
有一天正在查看社区的问题帖子，突然电话响起，开发人员反馈有一张业务表误操作被drop掉导致业务中断，询问单表是否可以恢复，当时安慰开发人员生产租户都存在备份，不要慌。我立刻登录OCP查看租户是否 开启回收站 以及 租户的备份归档状态是否正常，此时手机不断弹出消息，开发人员已经炸锅了，已经来不及安慰他，立马准备应急方案。
OceanBase版本信息
企业版OceanBase-4.2.1.8 MySQL租户
回收站方式
OB存在类似于Oracle的回收站功能，被drop掉的对象均可以进入回收站(表，租户等)，单独删除索引不会进入回收站，4.2.1版本回收站使用限制引用官网说明如下:
- 直接删除索引，该索引不会进入回收站。删除表时，表上的索引会随主表一起进入回收站。
- FLASHBACK 数据库对象的顺序需要符合从属关系，即：Database > Table。
- 不支持直接恢复索引，恢复表会连同索引一并恢复。
- 不能对回收站对象做任何查询和 DML 操作，DDL 操作中也仅支持 Purge 和 Flashback 操作。
- 进入回收站的对象并不是直接删除，而是会继续占用存储空间。
开启回收站
1.使用sys租户 或者 用户租户的租户管理员(root) 登录到数据库，建议使用后者方式
2.回收站功能默认是不开启的，需要手动将其开启，与MySQL一样，全局变量修改后，对当前已打开的 Session 不生效，需要重新建立 Session 才能生效，如果此时在当前会话drop对象是不会进入回收站的
[root@observer062 ~]# obclient -h10.0.0.62 -P2883 -uroot@obmysql#obtest -p'aaAA11__' -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 280537 Server version: OceanBase 4.2.1.8 (r108000052024072217-77f9516419866bc291fc79b950b0865f2f4194e0) (Built Jul 22 2024 17:41:00) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [oceanbase]> obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | recyclebin | OFF | +---------------+-------+ 1 row in set (0.026 sec) obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> obclient [oceanbase]> SET GLOBAL recyclebin = on; ---开启回收站 Query OK, 0 rows affected (0.510 sec) obclient [oceanbase]> show databases; +--------------------+ | Database | +--------------------+ | auto_db | | auto_test | | information_schema | | mysql | | oceanbase | | scb_test | | test | | tpcc | | ysc | +--------------------+ 9 rows in set (0.069 sec) obclient [oceanbase]> obclient [oceanbase]> use tpcc; Database changed obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_new_order | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 10 rows in set (0.004 sec) obclient [tpcc]> drop table bmsql_new_order; ---删除表对象 Query OK, 0 rows affected (0.474 sec) obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; ---查看回收站为空 Empty set (0.011 sec) obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; Empty set (0.008 sec) obclient [tpcc]> exit Bye
查看回收站状态
重新登录会话，查看回收站功能已开启
[root@observer062 ~]# obclient -h10.0.0.62 -P2883 -uroot@obmysql#obtest -p'aaAA11__' -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 280539 Server version: OceanBase 4.2.1.8 (r108000052024072217-77f9516419866bc291fc79b950b0865f2f4194e0) (Built Jul 22 2024 17:41:00) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [oceanbase]> SHOW RECYCLEBIN; Empty set (0.004 sec) obclient [oceanbase]> SHOW VARIABLES LIKE 'recyclebin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | recyclebin | ON | +---------------+-------+ 1 row in set (0.003 sec) obclient [oceanbase]> obclient [oceanbase]> use tpcc; Database changed obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> SHOW RECYCLEBIN; Empty set (0.004 sec) obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> SHOW VARIABLES LIKE 'recyclebin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | recyclebin | ON | +---------------+-------+ 1 row in set (0.003 sec)
查看回收站内容
模拟误操作drop table，可以看到此时被drop的表对象已经进入回收站中
obclient [tpcc]> obclient [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 9 rows in set (0.042 sec) obclient [tpcc]> obclient [tpcc]> obclient [tpcc]> drop table bmsql_order_line; Query OK, 0 rows affected (0.161 sec) obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+------------------+-------+----------------------------+ | __recycle_$_1729607639_1731592705008384 | bmsql_order_line | TABLE | 2024-11-14 21:58:25.009139 | +-----------------------------------------+------------------+-------+----------------------------+ 1 row in set (0.003 sec) obclient [tpcc]>
还原表对象
从回收站中恢复表并重命名，等待业务确认无误后重命名为原表名
obclient [tpcc]> drop table bmsql_order_line; Query OK, 0 rows affected (0.161 sec) obclient [tpcc]> SHOW RECYCLEBIN; +-----------------------------------------+------------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-----------------------------------------+------------------+-------+----------------------------+ | __recycle_$_1729607639_1731592705008384 | bmsql_order_line | TABLE | 2024-11-14 21:58:25.009139 | +-----------------------------------------+------------------+-------+----------------------------+ 1 row in set (0.003 sec) obclient [tpcc]> --- 使用object_name进行还原 --- obclient [tpcc]> FLASHBACK TABLE __recycle_$_1729607639_1731592705008384 TO BEFORE DROP RENAME To res_bmsql_order_line; Query OK, 0 rows affected (0.091 sec) obclient [tpcc]> obclient [tpcc]> show tables; +----------------------+ | Tables_in_tpcc | +----------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_oorder | | bmsql_stock | | bmsql_warehouse | | res_bmsql_order_line | +----------------------+ 9 rows in set (0.004 sec) obclient [tpcc]> --- 业务确认无误后将表名重命名为原表名 obclient [tpcc]> alter table res_bmsql_order_line rename to bmsql_order_line; Query OK, 0 rows affected (0.130 sec) obclient [tpcc]> obclient [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 9 rows in set (0.004 sec) obclient [tpcc]>
备份集方式还原
如果租户的回收站没有开启，此时也不要慌，利用以往的物理备份集方式进行还原单表也是OK的。生产租户备份一定要满足最低要求： 一次全量备份 + 日志持续归档，如果生产租户备份连这种标准都没有达到，只能说明这个OB集群处于裸奔状态(不接受任何反驳)
拥有物理全备
确保有数据备份支撑数据还原(执行物理备份的前提是开启归档)
删除单表
在obmysql租户下执行drop table 操作，模拟误操作，时间点为 2024-10-31 22:13:08
单表恢复
恢复前准备(创建资源池)
由于表恢复过程中需要使用辅助租户，因此，在进行表恢复前，需要在目标租户所在的集群内为辅助租户创建所需的资源池，仅需创建资源池作为辅助租户即可，不需要创建新租户，资源池相当于辅助租户
OceanBase 数据库的表级恢复功能是通过从备份数据中将用户指定的表恢复到一个已存在的租户中来实现的，并且该已存在的租户与原表所在的租户可以是同一个租户，也可以是同一集群中的不同租户，还可以是不同集群中的租户
obclient [(none)]> CREATE RESOURCE UNIT unit_res MAX_CPU 3, MEMORY_SIZE = '5G', MAX_IOPS 10240, MIN_IOPS=10240;
Query OK, 0 rows affected (0.012 sec)
obclient [(none)]>
obclient [(none)]>
obclient [(none)]>
obclient [(none)]> CREATE RESOURCE POOL restore_pool unit = 'unit_res', unit_num = 1, zone_list = ('zone2','zone3');
Query OK, 0 rows affected (0.018 sec)
obclient [(none)]>
obclient [(none)]>
obclient [(none)]>
查看备份路径
数据备份路径
本次查询在sys租户下，在用户租户下应查询视图 DBA_OB_BACKUP_PARAMETER
obclient [(none)]> select * from oceanbase.CDB_OB_BACKUP_PARAMETER where TENANT_ID=1006; +-----------+------------------+--------------------------------------------------------------------------+ | TENANT_ID | NAME | VALUE | +-----------+------------------+--------------------------------------------------------------------------+ | 1006 | data_backup_dest | file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data | +-----------+------------------+--------------------------------------------------------------------------+ 1 row in set (0.006 sec)
归档日志路径
本次查询在sys租户下，在用户租户下应查询视图 CDB_OB_ARCHIVE_DEST 的 LOG_ARCHIVE_DEST 列
obclient [(none)]> obclient [(none)]> obclient [(none)]> select * from oceanbase.CDB_OB_ARCHIVE_DEST where TENANT_ID=1006; +-----------+---------+-----------------------+--------------------------------------------------------------------------+ | TENANT_ID | DEST_NO | NAME | VALUE | +-----------+---------+-----------------------+--------------------------------------------------------------------------+ | 1006 | 0 | binding | OPTIONAL | | 1006 | 0 | dest_id | 1001 | | 1006 | 0 | path | file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog | | 1006 | 0 | piece_switch_interval | 1d | | 1006 | 0 | state | ENABLE | +-----------+---------+-----------------------+--------------------------------------------------------------------------+ 5 rows in set (0.014 sec) obclient [(none)]>
执行恢复
官方使用限制：
- 仅支持恢复用户表，不支持单独恢复临时表、视图、索引等。
- 对于 V4.2.1 BP2（不含该版本）之前版本，不支持恢复表上的外键、触发器及统计信息等。
- 表恢复的源租户与目标租户的兼容性必须一致，例如，均为 Oracle 兼容性租户，或者均为 MySQL 兼容性租户。
- 恢复表时，指定的表名需要与系统实际存储的表名一致。例如，Oracle 模式租户下创建表
test，而系统内部实际存储的表名为TEST，故在恢复表时需要指定表名为TEST，否则系统会报错，提示表不存在 - 表级恢复当前仅支持将低版本的备份数据中的表恢复到同版本或高版本中
恢复命令
恢复删除表至原租户obmysql中，恢复至资源池restore_pool中，由于删除时间点在2024-10-31 22:13:08，恢复时间点选择为 2024-10-31 22:12:00
obclient [(none)]> ALTER SYSTEM RECOVER TABLE tpcc.bmsql_order_line TO TENANT obmysql FROM 'file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data,file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog' UNTIL TIME='2024-10-31 22:12:00' WITH 'pool_list=restore_pool'; Query OK, 0 rows affected (0.062 sec)
查看表恢复进度
使用sys租户登录查看
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_RECOVER_TABLE_JOBS\G;
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 5
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 0
START_TIMESTAMP: 2024-10-31 22:26:52.083689
FINISH_TIMESTAMP: NULL
STATUS: RECOVERING
AUX_TENANT_NAME: AUX_RECOVER$1730384812047075
TARGET_TENANT_NAME: obmysql
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `tpcc`.`bmsql_order_line`
RESTORE_SCN: 1730383920000000000
RESTORE_SCN_DISPLAY: 2024-10-31 22:12:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data,file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog
BACKUP_SET_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data/backup_set_2_full
BACKUP_PIECE_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog/piece_d1001r1p1
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST:
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT:
COMMENT:
DESCRIPTION: NULL
*************************** 2. row ***************************
TENANT_ID: 1006
JOB_ID: 3
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 5
START_TIMESTAMP: 2024-10-31 22:26:52.083689
FINISH_TIMESTAMP: NULL
STATUS: RESTORE_AUX_TENANT
AUX_TENANT_NAME: AUX_RECOVER$1730384812047075
TARGET_TENANT_NAME: obmysql
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `tpcc`.`bmsql_order_line`
RESTORE_SCN: 1730383920000000000
RESTORE_SCN_DISPLAY: 2024-10-31 22:12:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data,file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog
BACKUP_SET_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data/backup_set_2_full
BACKUP_PIECE_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog/piece_d1001r1p1
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST:
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT:
COMMENT:
DESCRIPTION: NULL
2 rows in set (0.019 sec)
查看表恢复结果
使用sys租户登录查看，RESULT字段值为SUCCESS，表示恢复成功
obclient [(none)]> SELECT * FROM oceanbase.CDB_OB_RECOVER_TABLE_JOB_HISTORY\G;
*************************** 1. row ***************************
TENANT_ID: 1
JOB_ID: 5
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 0
START_TIMESTAMP: 2024-10-31 22:26:52.083689
FINISH_TIMESTAMP: 2024-10-31 22:33:15.274994
STATUS: COMPLETED
AUX_TENANT_NAME: AUX_RECOVER$1730384812047075
TARGET_TENANT_NAME: obmysql
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `tpcc`.`bmsql_order_line`
RESTORE_SCN: 1730383920000000000
RESTORE_SCN_DISPLAY: 2024-10-31 22:12:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data,file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog
BACKUP_SET_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data/backup_set_2_full
BACKUP_PIECE_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog/piece_d1001r1p1
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST:
REMAP_TABLEGROUP_LIST:
REMAP_TABLESPACE_LIST:
RESULT: SUCCESS
COMMENT: import succeed table count: 1, failed table count: 0
DESCRIPTION: NULL
*************************** 2. row ***************************
TENANT_ID: 1006
JOB_ID: 3
INITIATOR_TENANT_ID: 1
INITIATOR_JOB_ID: 5
START_TIMESTAMP: 2024-10-31 22:26:52.083689
FINISH_TIMESTAMP: 2024-10-31 22:32:51.332691
STATUS: COMPLETED
AUX_TENANT_NAME: AUX_RECOVER$1730384812047075
TARGET_TENANT_NAME: obmysql
IMPORT_ALL: 0
DB_LIST:
TABLE_LIST: `tpcc`.`bmsql_order_line`
RESTORE_SCN: 1730383920000000000
RESTORE_SCN_DISPLAY: 2024-10-31 22:12:00.000000
RESTORE_OPTION: pool_list=restore_pool
BACKUP_DEST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data,file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog
BACKUP_SET_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/data/backup_set_2_full
BACKUP_PIECE_LIST: file:///backup/obbackup/obtest/1729607639/tenant_incarnation_1/1006/clog/piece_d1001r1p1
BACKUP_PASSWD: NULL
EXTERNAL_KMS_INFO: NULL
REMAP_DB_LIST:
REMAP_TABLE_LIST:
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.056 sec)
使用业务租户查看表已还原
[root@observer062 ~]# obclient -h10.0.0.62 -P2883 -uroot@obmysql#obtest -p'aaAA11__' Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 274228 Server version: OceanBase 4.2.1.8 (r108000052024072217-77f9516419866bc291fc79b950b0865f2f4194e0) (Built Jul 22 2024 17:41:00) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [(none)]> obclient [(none)]> obclient [(none)]> obclient [(none)]> obclient [(none)]> obclient [(none)]> use tpcc; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed obclient [tpcc]> obclient [tpcc]> select count(*) from bmsql_order_line; +----------+ | count(*) | +----------+ | 220113 | +----------+ 1 row in set (0.004 sec) obclient [tpcc]> obclient [tpcc]>
释放资源池(可选)
定表的恢复结束后，可以在集群中执行以下命令，手动释放为单表恢复创建的资源池(辅助租户)
obclient [(none)]> DROP RESOURCE POOL restore_pool; Query OK, 0 rows affected (0.007 sec) obclient [(none)]>
总结
1.回收站方式还原消耗时间最短，建议使用该方式进行还原，极大缩短业务故障时间。故此次事故使用的是回收站方式还原
2.生产租户建议在有物理备份的前提下开启回收站功能，保障在突发情况下在短期的时间内恢复业务
标签：OceanBase,LIST,obclient,1.8,租户,tpcc,单表,1006,bmsql From： https://blog.csdn.net/weixin_68243500/article/details/143793417