ITPub博客

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

原创 作者:oliseh 时间:2015-11-04 09:49:02 0 删除 编辑

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响应时长要求较高的只读应用,那么应用访问库的效率可能会受到影响
而"方法一:延迟应用归档"的缺点在于增加了主备切换的时间。
下一篇: kill session的学问
请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1602542