Oracle 11g physical dataguard以快照方式打开挖掘归档日志

• 以快照方式打开数据库

stop日志应用
>>alter database recover managed standby database cancel;

检查闪回是否已打开，FLASHBACK_ON为NO，表示没有打开

>>select flashback_on,database_role,open_mode from v$database;  
FLASHBACK_ON       DATABASE_ROLE    OPEN_MODE
------------------ ---------------- --------------------
NO                 PHYSICAL STANDBY READ ONLY WITH APPLY

打开闪回
>>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 51000M
shut
>>alter system set db_recovery_file_dest='/data';
>>alter system set db_recovery_file_dest_size=50G;

>>down immediate;
>>startup mount;
>>alter database flashback on;

将数据库以快照的方式打开
>>alter database convert to snapshot standby;
>>alter  database open; 

检查数据库的open_mode
>>select open_mode, database_role, protection_mode from v$database;

查询flash_back开始的SCN
>>select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

• 使用dbms_logmnr日志挖掘

查询需要挖掘的归档日志
select thread#,
       SEQUENCE#,
       REGISTRAR,
       APPLIED,
       to_char(first_TIME, 'yyyy-mm-dd-hh24:mi:ss'),
       to_char(COMPLETION_TIME, 'yyyy-mm-dd-hh24:mi:ss')
  from v$archived_log
 order by first_TIMe;

execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3521_1011051456.arc',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3706_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3522_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3523_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3707_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3524_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3708_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3709_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3525_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3710_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3526_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3527_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3711_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3712_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3528_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3713_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3714_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3715_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3529_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3716_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3717_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3718_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3530_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3719_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3720_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3721_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/2_3531_1011051456.arc',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/isimdg/arch/1_3722_1011051456.arc',options=>dbms_logmnr.addfile);
execute 

dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);


create table crm.bak as select * from v$logmnr_contents  where sql_redo like "%delete%";

• 回切

>>shutdown immediate
>>startup mount;
>>alter database convert to physical standby;

>>shutdown immediate
>>startup
检查数据库的状态
select name,open_mode,database_role,flashback_on from v$database;
开启备库的日志应用进程

alter database recover managed standby database using current logfile disconnect from session;