OeanBase误删除表(租户)场景案例

IT那活儿 2025-08-25

100

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

一

drop表恢复方法

确认误删除表时间：

select   gmt_create,ddl_stmt_str from  
__all_virtual_ddl_operation where upper(ddl_stmt_str) like '%IM_XXX_DETAIL%' order by gmt_create desc  limit 100;
找到ddl记录
| 2024-08-01 19:54:53.298915 | DROP TABLE "IM"."IM_XXX_DETAIL"

检查回收站是否开启：

show variables like '%recycle%';

确保备份以及归档正常：

select log_archive_round,tenant_id,status,min_first_time,max_next_time from cdb_ob_backup_archivelog_summary;
select incarnation,tenant_id,bs_key,start_time,completion_time,elapsed_secondes,time_taken_display,status from cdb_ob_backup_set_details order by start_time,tenant_id;

找同版本集群创建恢复资源。

创建unit：

create resource unit restore_unit min_cpu=3,max_cpu=3,min_memory=12884901888,max_memory
=12884901888,min_iops=10240,max_iops=10240,max_disk_size='10G',max_session_num=64;

创建resource pool：

create  resource pool restore_pool unit ='restore_unit',unit_num=1,zone_list=('zone1','zone2','zone3');

确认集群资源：

show parameters like 'backup_dest';

select * from __all_tenant;

select * from __all_cluster;

执行单表恢复命令：

alter system restore IM.IM_XXX_DETAIL for restore_zy from 
obxxx at 'file:///OBDATABAK' until '2024-08-01 19:54:00' with 'backup_cluster_name=obxxxx&backup_cluster_id=10&pool_list=restore_pool';

恢复完成后，可根据实际情况，通过oms/datax/obdump/sql命令等不同方式导回原库。

业务确认数据没问题，恢复完成后，对于恢复的临时租户清理：

Drop tenant restore_zy force;
Drop resource pool restore_pool;

二

delete表恢复操作

恢复方法：闪回冻结版本(该操作只能恢复到上次合并版本的数据，合并之后如果有插入之后再删除是没有办法闪回的)。

undo_retention
用于设置系统应保留的多版本数据范围，在转储时控制多版本数据的回收。默认是1800秒。

租户开启多版本转储后，大版本合并会保留对应的增量转储文件，但Major SSTable 中会存放多版本数据。建议该参数不要设置过大，防止因参数过大导致保留的SSTable数量超限。

备注：用济宁营业测试环境该表测试USER01.T_TAB__SUM_BAK。

obclient> select * from USER01.T_TAB__SUM_BAK ;
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| 201409 |531       | MPAYFEE |101882864.19| 0 |101882864.19| 0 |    214524| -214524 | NULL | JSDZ_003_06_531_201409.txt |
| 201408 |531       | MPAYFEE |118422554.01| 0 |118422554.01| 0 |163452.5| -163452.5 | NULL | JSDZ_003_06_531_201408.txt |
| 201407 |531       | MPAYFEE |109217834.34| 0 |109217834.34| 0 |195169.98| -195169.98 | NULL | JSDZ_003_06_531_201407.txt |
| 201406 |531       | MPAYFEE |94986780.94| 0 |94986780.94| 0 |207488.7| -207488.7 | NULL | JSDZ_003_06_531_201406.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+--------------------------

插入一条数据进行测试：

insert into  USER01.T_TAB__SUM_BAK values ('201410','531','MPAYFEE','101882864.19','0','101882864.19','0','214524','-214524','NULL','JSDZ_003_06_531_201409.txt') ;

obclient> select * from USER01.T_TAB__SUM_BAK ;
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| 201409 |531       | MPAYFEE |101882864.19| 0 |101882864.19| 0 |    214524| -214524 | NULL | JSDZ_003_06_531_201409.txt |
| 201408 |531       | MPAYFEE |118422554.01| 0 |118422554.01| 0 |163452.5| -163452.5 | NULL | JSDZ_003_06_531_201408.txt |
| 201407 |531       | MPAYFEE |109217834.34| 0 |109217834.34| 0 |195169.98| -195169.98 | NULL | JSDZ_003_06_531_201407.txt |
| 201406 |531       | MPAYFEE |94986780.94| 0 |94986780.94| 0 |207488.7| -207488.7 | NULL | JSDZ_003_06_531_201406.txt |
| 201410 |531      | MPAYFEE |101882864.19| 0 |101882864.19| 0 |    214524| -214524 | NULL | JSDZ_003_06_531_201409.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+

delete刚插入的该条数据：

obclient> delete from USER01.T_TAB__SUM_BAK where YEARMON ='201410';
Query OK, 1 row affected (0.00 sec)
obclient> commit ;
Query OK, 0 rows affected (0.00 sec)

obclient> select * from USER01.T_TAB__SUM_BAK ;
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+
| 201409 |531       | MPAYFEE |101882864.19| 0 |101882864.19| 0 |    214524| -214524 | NULL | JSDZ_003_06_531_201409.txt |
| 201408 |531       | MPAYFEE |118422554.01| 0 |118422554.01| 0 |163452.5| -163452.5 | NULL | JSDZ_003_06_531_201408.txt |
| 201407 |531       | MPAYFEE |109217834.34| 0 |109217834.34| 0 |195169.98| -195169.98 | NULL | JSDZ_003_06_531_201407.txt |
| 201406 |531       | MPAYFEE |94986780.94| 0 |94986780.94| 0 |207488.7| -207488.7 | NULL | JSDZ_003_06_531_201406.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+-----------+------------+---------+----------------------------+

确定冻结版本：

MySQL [oceanbase]> select svr_ip,data_version,count(*) from __all_virtual_meta_table group by 1,2;
+-------------+--------------+----------+
| svr_ip | data_version | count(*) |
+-------------+--------------+----------+
| XXX.XX.16.1 | 52 | 96211 |
+-------------+--------------+----------+

无法查出合并之后插入的数据：

obclient> select *+ frozen_version(52)*/* from USER01.T_TAB__SUM_BAK where YEARMON ='201410' ;
Empty set (0.01 sec)

合并之前的数据可以查出：

obclient> select *+ frozen_version(52)*/* from USER01.T_TAB__SUM_BAK where YEARMON ='201409' ;
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| 201409 | 531 | MPAYFEE | 101882864.19 | 0 | 101882864.19 | 0 | 214524 | -214524 | NULL | JSDZ_003_06_531_201409.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+

确认undo的时间：

obclient [SYS]> show variables like'%undo_retention%'; 
+----------------+-------+
| VARIABLE_NAME | VALUE |
+----------------+-------+
| undo_retention | 1800 |
+----------------+-------+

通过undo 指定时间可以恢复：

obclient> select * from USER01.T_TAB__SUM_BAK as of timestamp to_timestamp('2025-03-26 17:02:00','yyyy-mm-dd hh24:mi:ss') where YEARMON ='201410' ;
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| 201410 |  531      | MPAYFEE | 101882864.19 | 0 | 101882864.19 | 0 |   214524 | -214524 | NULL | JSDZ_003_06_531_201409.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+

obclient> create table USER01.T_TAB__SUM_BAK_1 as 
    -> select * from USER01.T_TAB__SUM_BAK as of timestamp to_timestamp('2025-03-26 17:02:00','yyyy-mm-dd hh24:mi:ss') where YEARMON ='201410' ;
Query OK, 1 row affected (0.30 sec)

obclient> select * from USER01.T_TAB__SUM_BAK_1 ;
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| YEARMON | PROV_CODE | REC_TYPE | CHARGE_DEBIT | CHARGE_CREDIT | CHARGE_SUM | V_DEBIT | V_CREDIT | V_SUM | IN_FILE | OUT_FILE |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
| 201410 |531      | MPAYFEE |101882864.19| 0 |101882864.19| 0 |   214524| -214524 | NULL | JSDZ_003_06_531_201409.txt |
+---------+-----------+----------+--------------+---------------+--------------+---------+----------+---------+---------+----------------------------+
1 row in set (0.00 sec)

三

truncate操作恢复

truncate之后只能通过单表恢复：

obclient> truncate table USER01.T_TAB__SUM_BAK ;
Query OK, 0 rows affected (0.66 sec)

obclient> select *+ frozen_version(52)*/* from USER01.T_TAB__SUM_BAK where YEARMON in ('201406','201407','201408','201409') ;
Empty set (0.00 sec)

obclient> select * from USER01.T_TAB__SUM_BAK as of timestamp to_timestamp('2025-03-26 17:25:00','yyyy-mm-dd hh24:mi:ss') where  YEARMON in ('201406','201407','201408','201409') ;
Empty set (0.00 sec)

END

本文作者：郝斌(上海新炬中北团队）

本文来源：“IT那活儿”公众号

select

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

OeanBase误删除表(租户)场景案例

本文作者：郝 斌(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论

本文作者：郝斌(上海新炬中北团队）