当前位置：首页 > article >正文

OceanBase单表恢复(4.2.1.8)

article 2024/11/18 10:08:58

前言

有一天正在查看社区的问题帖子，突然电话响起，开发人员反馈有一张业务表误操作被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.生产租户建议在有物理备份的前提下开启回收站功能，保障在突发情况下在短期的时间内恢复业务

查看全文

http://www.kler.cn/a/398543.html

AI 提示词（Prompt）入门十：最佳实践｜详细询问，提供细节！

git push时报错! [rejected] master -＞ master (fetch first)error: ...

web——upload-labs——第三关——后缀黑名单绕过

Cursor安装Windows / Ubuntu

Cartographer激光雷达slam -20241116

【人工智能】迁移学习在深度学习中的应用：用Python实现自定义数据集图像分类

【SSL证书】腾讯云SSL续签备忘录

VScode+opencv——关于opencv多张图片拼接成一张图片的算法

深入剖析Kubernetes监控体系：Prometheus、Metrics Server与Kubernetes监控体系

二五、pxe自动装机

C# WPF .NET6程序可以直接运行？不需要装.NET运行时?

【jvm】HotSpot中方法区的演进

【java】值传递引用传递

JAVA中对象实体与对象引用有何不同？举例说明

Transformer学习笔记（一）

机器学习基础03

Android 关于使用videocompressor库压缩没有声音和异常的问题

专题二十_动态规划_简单多状态dp问题_买卖股票系列问题_算法专题详细总结

「Qt Widget中文示例指南」如何创建一个窗口标志？（二）

Android Framework层介绍

半导体器件与物理篇3 P-N结

Redis的Zset在排行榜中应用

【数据结构】树——顺序存储二叉树

面试题1111

使用Kafka实现大规模数据流处理的最佳实践

用哈希表封装myunordered_map/_set--C++

前言