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響應時長要求較高的只讀應用，那麼應用訪問庫的效率可能會受到影響
而"方法一：延遲應用歸檔"的缺點在於增加了主備切換的時間。

DG主庫發生表誤刪除後利用備庫進行恢復的方法實踐

相關文章