Dataguard中主库发生误操作后，当然可以利用flashback transaction、flashback query、flashback table等技术进行恢复。
但是当上述方法都有其局限性，比如受制于undo表空间是否还存在Before-image，回收站里的表否被清理等。
本文介绍的是如何把备库上完好的数据恢复到主库上，前提是备库必须：开启延迟应用归档或者打开flashback database

*********************************
** 方法一：延迟应用归档
*********************************
prmy:tstdb1
physical stdby:tstdb1_stdby2
protection mode: maximum performance

###主库打开minimal supplemental logging，为使用logminer作准备，设定log_archive_dest_n指向备库，指定备库上的延迟应用archivelog的时间为5分钟
---tstdb1:
alter database add supplemental log data;

alter system set log_archive_dest_3='service=tstdb1_stdby2 ASYNC valid_for=(online_logfiles,primary_roles) delay=5 db_unique_name=tstdb1_stdby2';

###主库上创建测试表，每隔30秒往表里填充数据
---tstdb1:
create table scott.t1103_1 (id number,tm date) tablespace xdbts;

declare
begin
while ( true )
loop
insert into scott.t1103_1 values(1,sysdate);
commit;
dbms_lock.sleep(30);
end loop;
end;
/

###由于事务量较小，主库上设定归档生成速度为1分钟一个，以生成一定量的归档日志，便于观察备库的recover过程
---tstdb1:
alter system set archive_lag_target=60;

###备库开启MRP
---tstdb1_stdby2:
col db_unique_name format a15
set linesize 180
select db_unique_name,database_role,protection_mode,protection_level,open_mode,SWITCHOVER_STATUS from v$database;

DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE SWITCHOVER_STATUS
--------------- ---------------- -------------------- -------------------- -------------------- --------------------
tstdb1_stdby2 PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY WITH APPLY NOT ALLOWED

***tstdb1_Stdby2的alert.log显示类似下列内容，表明tstdb1侧设置的应用延迟是有效的
Tue Nov 03 13:28:48 2015
Archived Log entry 26 added for thread 1 sequence 479 ID 0x7ad95829 dest 1:
ARC3: Archive log thread 1 sequence 479 available in 5 minute(s)

//////////////////////// Step 1：误操作发生 /////////////////////////

###模拟误操作之前先将主库上运行的插入过程看下主库、备库上的表的内容
---tstdb1:
select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:54:43 58

---tstdb1_stdby2:
select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:48:43 46

###模拟误操作，drop掉scott.t1103_1表
---tstdb1:
drop table scott.t1103_1;

###发现表丢失后立即停止备库上的MRP
---tstdb1_stdby2:
alter database recover managed standby database cancel;

select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:49:43 48

###检查此时备库上还未应用的日志
col name format a90
set linesize 160 pagesize 90 numwidth 16
select sequence#,name,applied,first_change#,next_change# from v$archived_log where applied='NO';
SEQUENCE# NAME APPLIED FIRST_CHANGE# NEXT_CHANGE#
---------------- ------------------------------------------------------------------------------------------ --------- ---------------- ----------------
504 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_504_1n2kiAGDm_.arc NO 12723366734182 12723366734264
505 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_505_1n2kllQUl_.arc NO 12723366734264 12723366734343
506 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_506_1n2kpKeHL_.arc NO 12723366734343 12723366734425
507 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_507_1n2ksvtSs_.arc NO 12723366734425 12723366734505
508 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_508_1n2kwU-KS_.arc NO 12723366734505 12723366734606
509 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_509_1n2k-4Fvu_.arc NO 12723366734606 12723366734688
510 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_510_1n2l1fU4w_.arc NO 12723366734688 12723366734821
511 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_511_1n2l5EbAX_.arc NO 12723366734821 12723366734900
512 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_512_1n2l8pdNQ_.arc NO 12723366734900 12723366734994
513 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_513_1n2lCOYFg_.arc NO 12723366734994 12723366735075
514 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_514_1n2lFzfXt_.arc NO 12723366735075 12723366735423
515 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_515_1n2lJYpfr_.arc NO 12723366735423 12723366735504
516 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_516_1n2lN7wjB_.arc NO 12723366735504 12723366735584
517 /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_517_1n2lQj8Mg_.arc NO 12723366735584 12723366735675

//////////////////////// Step 2：确定主库上误操作的时间点 /////////////////////////
###通过上一步备库上还未apply的archivelog列表我们可以从主库的seq# 504这个归档日志开始，利用logminer进行解析
---tstdb1:
col name format a90
set linesize 150
select first_change#,name from v$archived_log where sequence#=504 and STANDBY_DEST='NO';

FIRST_CHANGE# NAME
---------------- ------------------------------------------------------------------------------------------
12723366734182 /oradata06/fra/TSTDB1/archivelog/2015_11_03/o1_mf_1_504_1n2kXxVPI_.arc

set numwidth 16
select current_scn from v$database;
CURRENT_SCN
----------------
12723366747009

###以12723366734182作为startscn，12723366747009作为endscn进行logminer
---tstdb1:
exec dbms_logmnr.start_logmnr(startscn=>12723366734182,endscn=>12723366747009,options=>dbms_logmnr.CONTINUOUS_MINE + dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

col sql_redo format a80
set linesize 180
select scn,sql_redo,timestamp from v$logmnr_contents where table_name='T1103_1';
SCN SQL_REDO TIMESTAMP
---------------- -------------------------------------------------------------------------------- -----------------
12723366734586 ALTER TABLE "SCOTT"."T1103_1" RENAME TO "BIN$I52IE3ksAuzgUwoKjdG+sA==$0" ; 20151103 13:54:48
12723366734589 drop table scott.t1103_1 AS "BIN$I52IE3ksAuzgUwoKjdG+sA==$0" ; 20151103 13:54:48 <---此处请忽略掉回收站的功能

要恢复的目标时间点在SCN=12723366734586之前，我们取12723366734585作为恢复目标SCN

//////////////////////// Step 3：对备库实施不完全恢复 /////////////////////////
###将12723366734585(12723366734586-1)作为备库的恢复目标时间，对备库做不完全恢复
---tstdb1_stdby2:
SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:49:43 48

shutdown immediate
startup mount;

RMAN> recover database until scn 12723366734585;

Starting recover at 20151103 16:27:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=728 device type=DISK

starting media recovery

archived log for thread 1 with sequence 504 is already on disk as file /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_504_1n2kiAGDm_.arc
archived log for thread 1 with sequence 505 is already on disk as file /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_505_1n2kllQUl_.arc
archived log for thread 1 with sequence 506 is already on disk as file /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_506_1n2kpKeHL_.arc
archived log for thread 1 with sequence 507 is already on disk as file /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_507_1n2ksvtSs_.arc
archived log for thread 1 with sequence 508 is already on disk as file /oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_508_1n2kwU-KS_.arc
archived log file name=/oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_504_1n2kiAGDm_.arc thread=1 sequence=504
archived log file name=/oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_505_1n2kllQUl_.arc thread=1 sequence=505
archived log file name=/oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_506_1n2kpKeHL_.arc thread=1 sequence=506
archived log file name=/oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_507_1n2ksvtSs_.arc thread=1 sequence=507
archived log file name=/oradata06/teststdby2/fra/TSTDB1_STDBY2/archivelog/2015_11_03/o1_mf_1_508_1n2kwU-KS_.arc thread=1 sequence=508
media recovery complete, elapsed time: 00:00:01
Finished recover at 20151103 16:27:18

###只读方式打开备库，验证数据已经恢复到了误删除前的状态
---tstdb1_stdby2:
alter database open read only;

SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:54:43 58

###至此备库上的数据已经恢复到了误删除前的状态，可以使用datapump、或者使用dblink等方法将数据导回至主库
---tstdb1:
create public database link system_tstdb1_stdby2 connect to system identified by "773946" using 'tstdb1_stdby2';
create table scott.t1103_1 tablespace omftbs1 as select * from scott.t1103_1@system_tstdb1_stdby2;

SYS@tstdb1-SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------------
20151103 13:26:13 20151103 13:54:43 58

###重新开启备库上的MRP
alter database recover managed standby database using current logfile disconnect;

*********************************
** 方法二：备库开启Flashback Database
*********************************
prmy: tstdb1
physical stdby: tstdb1_stdby2
protection mode: maximum performance

###备库开启flashback database
---tstdb1_stdby2:
alter database recover managed standby database cancel;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

alter database recover managed standby database using current logfile disconnect;

###备库工作在RTA模式，实时应用主库的归档
---tstdb1：
SYS@tstdb1-SQL> SELECT DESTINATION,db_unique_name,type,STATUS,database_mode,recovery_mode,ARCHIVED_THREAD#,ARCHIVED_SEQ#,applied_seq#,synchronization_status,gap_status FROM V$ARCHIVE_DEST_STATUS WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

DESTINATION DB_UNIQUE_NAME TYPE STATUS DATABASE_MODE RECOVERY_MODE ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_SEQ# SYNCHRONIZATION_STAT GAP_STAT
--------------- --------------- ---------- -------- --------------- --------------- ---------------- ---------------- ---------------- -------------------- --------
tstdb1 LOCAL VALID OPEN IDLE 1 523 0 CHECK CONFIGURATION
tstdb1_stdby2 tstdb1_stdby2 PHYSICAL VALID OPEN_READ-ONLY MANAGED REAL TI 1 523 508 CHECK CONFIGURATION NO GAP
ME APPLY

###主库发生误操作将表scott.t1103_1 drop掉
SYS@tstdb1-SQL> drop table scott.t1103_1 purge;

Table dropped.

SYS@tstdb1-SQL> select count(*) from scott.t1103_1;
select count(*) from scott.t1103_1
*
ERROR at line 1:
ORA-00942: table or view does not exist

###还是通过logminer搜索一定范围内的archivelog，确定drop操作对应的准确SCN号
exec dbms_logmnr.start_logmnr(startscn=>12723366736062,endscn=>12723366755497,options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

col sql_redo format a110
set linesize 180
select scn,sql_redo,timestamp from v$logmnr_contents where table_name='T1103_1';
SCN SQL_REDO TIMESTAMP
---------------- -------------------------------------------------------------------------------------------------------------- -----------------
12723366751454 create table scott.t1103_1 tablespace omftbs1 as select * from scott.t1103_1@system_tstdb1_stdby2; 20151103 16:41:26
12723366755411 drop table scott.t1103_1 purge; 20151103 17:22:51

确定flashback database的目标时间为12723366755410(12723366755411-1)

###在备库上执行flashback database
---tstdb1_stdby2:
SQL> select count(*) from scott.t1103_1;
select count(*) from scott.t1103_1
*
ERROR at line 1:
ORA-00942: table or view does not exist

alter database recover managed standby database cancel;

SQL> flashback database to scn 12723366755410;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:54:43 58

###使用dblink或者datapump将数据恢复到主库上
---tstdb1:
create public database link system_tstdb1_stdby2 connect to system identified by "773946" using 'tstdb1_stdby2';
create table scott.t1103_1 tablespace omftbs1 as select * from scott.t1103_1@system_tstdb1_stdby2;

SYS@tstdb1-SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:54:43 58

SQL> select last_ddl_time from dba_objects where object_name='T1103_1';

LAST_DDL_TIME
-----------------
20151103 16:41:26

###重新开启备库上的MRP
SQL> alter database recover managed standby database using current logfile disconnect ;

Database altered.

SQL> select min(tm),max(tm),count(*) from scott.t1103_1;

MIN(TM) MAX(TM) COUNT(*)
----------------- ----------------- ----------
20151103 13:26:13 20151103 13:54:43 58

SQL> select last_ddl_time from dba_objects where object_name='T1103_1';

LAST_DDL_TIME
-----------------
20151103 17:36:40 <---T1103_1的时间已经和主库上重建的时间保持一致

总结：
本文的所描述的情况只适用于个别表发生误操作的情况下，如果误操作影响范围很广，涉及到多个数据文件上的多张表，推荐使用备库或者主库之前的备份通过RMAN来进行恢复，这时主库需要停止；
如果误操作涉及的是DML语句，而不是Truncate或者Drop等DDL操作，为了确保数据库的一致性，建议将备库恢复到误操作时间点之前->激活备库->将应用切换到备库运行，避免数据紊乱；
显而易见，"方法二：备库开启Flashback Database"在操作便捷性上优于"方法一：延迟应用归档"，但Flashback log的持续写入对physical standby增加了一定的性能开销，如果physical standby上承载了对IO响应时长要求较高的只读应用，那么应用访问库的效率可能会受到影响
而"方法一：延迟应用归档"的缺点在于增加了主备切换的时间。

ITPub博客

DG主库发生表误删除后利用备库进行恢复的方法实践

最新文章