LogMiner应用3：确定闪回时间

本文使用DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG

一、案例环境和思路：
1、环境：Oracle 19C，开启归档、闪回、附加日志，使用LogMiner用户，数据泵的导出目录DATA_PUMP_DIR

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO hr;

hr@PDB>col table_name for a30
hr@PDB>select table_name,count(*) from user_constraints group by table_name order by 1;
TABLE_NAME                       COUNT(*)
------------------------------ ----------
COUNTRIES                               3
DEPARTMENTS                             4
EMPLOYEES                              10
EMP_DETAILS_VIEW                        1
JOBS                                    2
JOB_HISTORY                             9
LOCATIONS                               3
REGIONS                                 2
hr@PDB>select table_name,count(*) from user_indexes group by table_name order by 1;
TABLE_NAME                       COUNT(*)
------------------------------ ----------
COUNTRIES                               1
DEPARTMENTS                             2
EMPLOYEES                               6
JOBS                                    1
JOB_HISTORY                             4
LOCATIONS                               4
REGIONS                                 1

2、思路过程：删除hr.employees表，通过日志挖掘找到表被删除时的SCN，闪回数据库到该点，使用数据泵导出该表，完全恢复数据库，导入。

二、测试
1、删除表hr.employees

hr@PDB>drop table employees cascade constraints purge;

2、挖掘，确定drop的scn

conn / as sysdba
alter system switch logfile; # 把在线重做日志变成归档日志，这样分析归档日志就可以了
conn c##roma_logminer/password
# 查询归档
col first_time for a20
select name,sequence#,first_change#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "first_time"
    ,dictionary_begin,dictionary_end from v$archived_log;
NAME                            SEQUENCE# FIRST_CHANGE# first_time           DICTIONAR DICTIONAR
------------------------------ ---------- ------------- -------------------- --------- ---------
/u01/app/oracle/fast_recovery_         14       2707852 2021-01-03 18:00:23   NO        NO
area/orcl/ORCL/archivelog/2021
_03_25/o1_mf_1_14_j5qlo7ql_.arc
# 添加归档文件，启动LogMiner
exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/orcl/ORCL/archivelog/2021_03_25/o1_mf_1_14_j5qlo7ql_.arc',options=>dbms_logmnr.new);
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);   # 启动LogMiner
# LogMiner查询
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col OPERATION for a10
col TIMESTAMP for a20
col SQL_REDO for a100
select scn,operation,timestamp,sql_redo from v$logmnr_contents Where seg_owner='HR' and seg_name ='EMPLOYEES';
   2745284 DDL        2021-03-25 07:25:54  drop table employees cascade constraints purge;
## 确定 SCN 为 2745284
EXECUTE DBMS_LOGMNR.END_LOGMNR; # 停止LogMiner

3、闪回数据库

# 添加验证完全恢复的数据
grant execute on dbms_flashback to hr;   # grant by sys on pdb
conn hr/hrpw@pdb
create table t01(pname varchar(10),id number,ptime timestamp); 
insert into t01 values('inc1_test',dbms_flashback.get_system_change_number,sysdate);
commit;
hr@PDB>select * from t01;
PNAME                                 ID PTIME
------------------------------ --------- ---------------------------------------------------------------------------
inc1_test                        2746931 25-MAR-21 07.31.46.000000 AM
# 备份数据库
rman target /
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
backup as compressed backupset database plus archivelog delete all input;
BACKUP AS BACKUPSET CURRENT CONTROLFILE FORMAT '/home/oracle/ctl_%d_%T_%s_%p.bak'
# 记录control备份：/home/oracle/ctl_ORCL_20210325_8_1.bak
# 闪回数据库
startup mount force;
flashback database to scn 2745284;
alter database open resetlogs;
alter pluggable database pdb open;

注释：如果闪回数据库用read only打开，则需要exp导出，或者用其他数据库通过dblink导出表的备份。

4、数据泵导出表employees

expdp hr/hrpw@pdb directory=DATA_PUMP_DIR dumpfile=tables_employees.dmp logfile=tables.log tables=hr.employees

5、完全恢复数据库

sqlplus / as sysdba
shut immediate
rman target /
set dbid=1588657765;
startup nomount;
#注释：如果开启了控制文件自动备份，这里也不能使用自动备份的控制文件，因为闪回数据库重新备份了新控制文件
restore controlfile from '/home/oracle/ctl_ORCL_20210325_8_1.bak';
alter database mount;
list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1588657765       PARENT  1          2019/04/17 00:55:59
2       2       ORCL     1588657765       CURRENT 1920977    2021/01/03 16:03:51
startup mount force;
reset database to incarnation 2;
restore database;
recover database;
alter database open resetlogs;
alter pluggable database all open;

6、导入表

impdp hr/hrpw@pdb directory=DATA_PUMP_DIR dumpfile=tables_employees.dmp logfile=tables.log tables=hr.employees

7、测试结果

# 查约束
col table_name for a30
select table_name,count(*) from user_constraints group by table_name order by 1;
TABLE_NAME                       COUNT(*)
------------------------------ ----------
COUNTRIES                               3
DEPARTMENTS                             3   #少一个
EMPLOYEES                              10
EMP_DETAILS_VIEW                        1
JOBS                                    2
JOB_HISTORY                             8   #少一个
LOCATIONS                               3
REGIONS                                 2
# 查索引
hr@PDB>select table_name,count(*) from user_indexes group by table_name order by 1;
TABLE_NAME                       COUNT(*)
------------------------------ ----------
COUNTRIES                               1
DEPARTMENTS                             2
EMPLOYEES                               6
JOBS                                    1
JOB_HISTORY                             4
LOCATIONS                               4
REGIONS                                 1
# 查验证表t01
hr@PDB>select * from t01;
PNAME                                 ID PTIME
------------------------------ --------- ---------------------------------------------------------------------------
inc1_test                        2746931 25-MAR-21 07.31.46.000000 AM

由结果来看，数据泵导入导出总数少了2个约束，EMPLOYEES本身的约束没少，因此需注意此问题。

三、另外的思路
1、通过LogMiner确定时间后，闪回数据库到改时间点后以只读的方式打开数据库，使用exp导出表

exp scott/tigerfile=/u01/apps/scott.ep2 log=scottep2.log indexes=n  tables=emp2

2、完全恢复数据库
3、导入表

imp scott/tigerfile=/u01/apps/scott.ep2 tables=emp2 fromuser=scott touser=scott ignore=y