20230621_Oracle truncate drop partition nopartition pdb no_pdb统一恢复方法

问题概述

本方法用于恢复 truncate/drop partition/nopartition pdb/非pdb,统一恢复方案。 

下载地址

链接：https://pan.baidu.com/s/1Q7Ym_YgTXfV7KabW-w4SWQ 
提取码：7fpa  

恢复过程

### 1. 创建测试表
create user hsql identified by hsql;
grant connect,resource,dba to hsql;
drop tablespace hsql including contents and datafiles;
create tablespace hsql datafile '/oradata/db11/db11/hsql01.dbf' size 100M autoextend off;
alter tablespace hsql add datafile '/oradata/db11/db11/hsql02.dbf' size 100M autoextend off;
alter tablespace hsql add datafile '/oradata/db11/db11/hsql03.dbf' size 100M autoextend off;
alter tablespace hsql add datafile '/oradata/db11/db11/hsql04.dbf' size 100M autoextend off;

drop table hsql.drop_par_1 purge;

create table hsql.drop_par_1(c1 int,c2 int) 
PARTITION BY RANGE(c1)
 (
     PARTITION p_2000 VALUES LESS THAN (20000) TABLESPACE hsql,
     PARTITION p_4000 VALUES LESS THAN (40000) TABLESPACE hsql,
     PARTITION p_6000 VALUES LESS THAN (60000) TABLESPACE hsql,
     PARTITION p_8000 VALUES LESS THAN (80000) TABLESPACE hsql,
     PARTITION p_maxvalue VALUES LESS THAN (maxvalue) TABLESPACE hsql
);

begin
for i in 1 .. 100000 loop
insert into hsql.drop_par_1 values(i,mod(i,400));
end loop;
commit;
end;
/


select count(1) from hsql.drop_par_1;

###  2. drop/truncate table

SQL> alter system archive log current;

System altered.

SQL>  select * from v$log;

   GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         52   52428800        512          1 YES ACTIVE                 1257480 21-JUN-23      1259725 21-JUN-23
         2          1         53   52428800        512          1 NO  CURRENT                1259725 21-JUN-23   2.8147E+14
         3          1         51   52428800        512          1 YES INACTIVE               1257127 21-JUN-23      1257480 21-JUN-23
         
SQL> drop table hsql.drop_par_1 purge;
SQL> alter system archive log current;

System altered.

SQL> select * from v$log;

   GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         52   52428800        512          1 YES ACTIVE                 1257480 21-JUN-23      1259725 21-JUN-23
         2          1         53   52428800        512          1 YES ACTIVE                 1259725 21-JUN-23      1259812 21-JUN-23
         3          1         54   52428800        512          1 NO  CURRENT                1259812 21-JUN-23   2.8147E+14


### 3. 查询归档日志
set linesize 300 pagesize 9999
col name for a100
col f_scn for a20
col n_scn for a20
select SEQUENCE#,name,status,to_char(FIRST_CHANGE#) f_scn,FIRST_TIME,to_char(NEXT_CHANGE#) n_scn,NEXT_TIME from v$archived_log
where first_time>=to_date('2023-06-25 14:07:31','yyyy-mm-dd hh24:mi:ss')
and first_time<=to_date('2023-06-25 14:40:31','yyyy-mm-dd hh24:mi:ss');

### 4. logmnr挖掘变更信息
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/arch/1_53_1138411758.dbf', OPTIONS => DBMS_LOGMNR.NEW);

Step 5   Start LogMiner.   
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
   OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
              DBMS_LOGMNR.COMMITTED_DATA_ONLY);               
Step 6   Query the V$LOGMNR_CONTENTS view.
set linesize 200 pagesize 9999
col USR for a10
col sql_redo for a100
col SEG_OWNER for a10
col SEG_NAME for a10
SELECT to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') t_time,SEG_OWNER,SEG_NAME,USERNAME AS usr,OPERATION,SQL_REDO FROM  
   V$LOGMNR_CONTENTS
   WHERE TABLE_NAME='OBJ$';
 Step 6   End the LogMiner session.
EXECUTE DBMS_LOGMNR.END_LOGMNR();

### 7. 整理变更信息
87530
87531
87532
87533
87534

将内容填入 dataobj.txt
### 8. 查询数据文件信息
select name from v$datafile where ts#=8;

将内容填入 control.txt

### 9. 创建中间表
#### 相同表空间添加一个数据文件
alter system set deferred_segment_creation=false;
alter tablespace hsql add datafile '/oradata/db11/db11/hsql05.dbf' size 10M autoextend off;

create table sys.rectab1209_oldtbs(c1 int,c2 int) tablespace hsql;
create table sys.rectab1209_newtbs tablespace users as select * from sys.rectab1209_oldtbs where 1=2;

### 10. 设置表空间read only <--可以不设置，丢失覆盖数据
alter tablespace hsql read only;

### 11. 恢复操作

#### 工具说明
sh x_rec.sh -h
#### 方案一
sh x_rec.sh no_pdb auto

#### 方案二
##### 11.1 scan dataobj
sh x_rec.sh no_pdb scan
##### 11.2 恢复dataobj
sh x_rec.sh no_pdb test 87530  <--return 1,继续操作，否则 需要检查原因
sh x_rec.sh no_pdb recovery 87530
##### 不同dataobj重复 以上操作 


### 12. 业务数据检查
select count(1) from sys.rectab1209_newtbs;

### 13. 设置表空间read write
alter tablespace hsql read write;

### 14. 清理中间表
drop table sys.rectab1209_oldtbs purge;
drop table sys.rectab1209_newtbs purge;

注意事项

warning: every objd need to run 'alter system flush shared_pool;' once,so run this command in no peak time.

说明

如果该工具对您有帮助，请提前在测试环境测试，并反馈以下信息，谢谢
表空间大小(M)：1024
恢复表数据量(条): 100000
恢复时间(秒): 600
数据库版本：19.3.0.0.0
是否使用pdb: 是