使用dbms_logmnr找到scn进行恢复删除的表

环境:
OS:Centos 7
DB:12.2.0.1

1.全备份数据库

run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
backup as compressed backupset full filesperset 10 database format '/u01/rmanbak/fullbk_%d_%s_%u_%T.bak';
sql 'alter system archive log current';
backup as compressed backupset archivelog all format '/u01/rmanbak/daily_arch_%d_%s_%u_%T.bak' delete input;
backup current controlfile format '/u01/rmanbak/daily_ctl_%d_%s_%u_%T.bak';
backup spfile format '/u01/rmanbak/daily_spfile_%d_%s_%u_%T.bak';
release channel ch1;
release channel ch2;
release channel ch3;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt obsolete;
}

 

2.模拟删除表
alter session set container=pdb;
drop table hxl.tb_ogg_test;

 

3.使用dbms_logmnr找到删除表的scn
添加在线日志,若是已经归档的了话需要加入归档日志

在cdb模式下操作

begin
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora19c/redo01.log',options=>dbms_logmnr.new);
end;

begin
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora19c/redo02.log',options=>dbms_logmnr.addfile);
end;


begin
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ora19c/redo03.log',options=>dbms_logmnr.addfile);
end;

开始分析
begin
dbms_logmnr.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;

 

 

在上面执行的会话窗口下执行
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%';
-----------------------------------------
1738957 "drop table tb_ogg_test;"

 

4.进行恢复pdb(下面操作可以重复执行)
关闭pdb
alter pluggable database pdb close immediate;

删除该pdb的数据文件
[oracle@19c pdbbak]$ cd /u01/app/oracle/oradata/ora19c/pdb
[oracle@19c pdb]$ rm *.dbf

创建辅佐目录
mkdir -p /u01/recover

执行不完全恢复
基于scn(可行)
run{
set until scn 1738957;
restore pluggable database pdb;
recover pluggable database pdb auxiliary destination '/u01/recover';
}

打开pdb
alter pluggable database pdb open resetlogs;

5.检查是否恢复
SQL> select count(1) from hxl.tb_ogg_test;

COUNT(1)
----------
100000