实验DG 跳归档恢复

当前备库正常状态

主库切换归档

SQL> alter system switch logfile;

备库应用71号归档日志，等待72号归档日志

2017-07-01T21:51:28.075316+08:00Archived Log entry 5 added for T-1.S-71 ID 0x22fd0e8 LAD:12017-07-01T21:51:28.075490+08:00RFS[5]: Selected log 5 for T-1.S-72 dbid 36700136 branch 9475434032017-07-01T21:51:28.273330+08:00Media Recovery Waiting for thread 1 sequence 72 (in transit)2017-07-01T21:51:28.273507+08:00Recovery of Online Redo Log: Thread 1 Group 5 Seq 72 Reading mem 0  Mem# 0: /u01/app/oracle/oradata/newcdb/stdbyredo02.log

检查主库的最近的归档日志文件是71号

RMAN> crosscheck archivelog all;using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=120 device type=DISKvalidation succeeded for archived logarchived log file name=/archive/1_70_947543403.dbf RECID=163 STAMP=948232255validation succeeded for archived logarchived log file name=/archive/1_71_947543403.dbf RECID=165 STAMP=948232288Crosschecked 2 objects

模拟故障

关闭备库

SQL> shutdown immediate

主库生成测试数据

SQL> desc test;ERROR:ORA-04043: object test does not existSQL> create table test as select * from dba_objects;Table created.

主库切换归档

SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.

修改归档文件

[oracle@12cr2 archive]$ mv 1_72_947543403.dbf 1_72_947543403.dbf.bak

启动备库

SQL> startup mount

dgmgrl发现归档日志有断档

DGMGRL> SHOW CONFIGURATION;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    Error: ORA-16724: cannot resolve gap for one or more members    newcdbdg - Physical standby database       Warning: ORA-16809: multiple warnings detected for the memberFast-Start Failover: DISABLEDConfiguration Status:ERROR   (status updated 2 seconds ago)

备库alert日志发现有断档

2017-07-01T22:00:55.860626+08:00FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 72-72 DBID 36700136 branch 947543403FAL[client]: All defined FAL servers have been attempted.-------------------------------------------------------------------------Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that's sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.

跳断档恢复备机

备库SCN号

SQL> col CURRENT_SCN format 9999999999999999SQL> SELECT CURRENT_SCN FROM V$DATABASE;      CURRENT_SCN-----------------          2020795

主库SCN号

SQL> col CURRENT_SCN format 9999999999999999SQL> SELECT CURRENT_SCN FROM V$DATABASE;      CURRENT_SCN-----------------          2026985

主库指定备库的SCN做增量备份并传输到备库

RMAN> BACKUP INCREMENTAL FROM SCN 1576857 DATABASE FORMAT '/home/oracle/sdataguru_%U' tag 'FOR_STANDBY';[oracle@12cr2 ~]$ scp sdataguru_* 192.168.16.130:/home/oracle/sdataguru

主库备份控制文件并传输到备库

SQL> alter database create standby controlfile as '/home/oracle/std_con.ctl';[oracle@12cr2 ~]$ scp std_con.ctl 192.168.16.130:/home/oracle/

关闭备库，进行恢复

SQL> shutdown immediate

备库恢复控制文件

[oracle@oracle sdataguru]$ rman target /Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 1 22:07:51 2017Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database (not started)RMAN> startup nomount;Oracle instance startedTotal System Global Area     838860800 bytesFixed Size                     8798312 bytesVariable Size                322965400 bytesDatabase Buffers             503316480 bytesRedo Buffers                   3780608 bytesRMAN> restore standby controlfile from '/home/oracle/std_con.ctl';Starting restore at 01-JUL-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=36 device type=DISKchannel ORA_DISK_1: copied control file copyoutput file name=/u01/app/oracle/oradata/newcdb/control01.ctloutput file name=/u01/app/oracle/oradata/newcdb/control02.ctlFinished restore at 01-JUL-17

启动备库到mount状态

RMAN> alter database mount;

备库注册主库的RMAN备份文件

[oracle@oracle ~]$ cd sdataguru/[oracle@oracle sdataguru]$ lltotal 68176-rw-r----- 1 oracle oinstall 33587200 Jul  1 22:06 sdataguru_5ls89ot8_1_1-rw-r----- 1 oracle oinstall  6406144 Jul  1 22:06 sdataguru_5ms89otc_1_1-rw-r----- 1 oracle oinstall  5144576 Jul  1 22:06 sdataguru_5ns89otf_1_1-rw-r----- 1 oracle oinstall  5652480 Jul  1 22:06 sdataguru_5os89otg_1_1-rw-r----- 1 oracle oinstall 19021824 Jul  1 22:06 sdataguru_5qs89oth_1_1RMAN> catalog start with '/home/oracle/sdataguru';searching for all files that match the pattern /home/oracle/sdataguruList of Files Unknown to the Database=====================================File Name: /home/oracle/sdataguru/sdataguru_5qs89oth_1_1File Name: /home/oracle/sdataguru/sdataguru_5ls89ot8_1_1File Name: /home/oracle/sdataguru/sdataguru_5ns89otf_1_1File Name: /home/oracle/sdataguru/sdataguru_5os89otg_1_1File Name: /home/oracle/sdataguru/sdataguru_5ms89otc_1_1Do you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /home/oracle/sdataguru/sdataguru_5qs89oth_1_1File Name: /home/oracle/sdataguru/sdataguru_5ls89ot8_1_1File Name: /home/oracle/sdataguru/sdataguru_5ns89otf_1_1File Name: /home/oracle/sdataguru/sdataguru_5os89otg_1_1File Name: /home/oracle/sdataguru/sdataguru_5ms89otc_1_1

recover database noredo

RMAN> recover database noredo;Starting recover at 01-JUL-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=54 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle/oradata/newcdb/system01.dbfdestination for restore of datafile 00003: /u01/app/oracle/oradata/newcdb/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle/oradata/newcdb/undotbs01.dbfdestination for restore of datafile 00007: /u01/app/oracle/oradata/newcdb/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/sdataguru_5ls89ot8_1_1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 07/01/2017 22:11:44ORA-19870: error while restoring backup piece /home/oracle/sdataguru/sdataguru_5ls89ot8_1_1ORA-19573: cannot obtain exclusive enqueue for datafile 1ORA-45909: restore, recover or block media recovery may be in progress

以上报错原因，由于DG broker会自动启动备机的日志同步，此时需要关闭日志同步

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;Media recovery complete.

重新recover 数据库

RMAN> recover database noredo;Starting recover at 01-JUL-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=48 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle/oradata/newcdb/system01.dbfdestination for restore of datafile 00003: /u01/app/oracle/oradata/newcdb/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle/oradata/newcdb/undotbs01.dbfdestination for restore of datafile 00007: /u01/app/oracle/oradata/newcdb/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/sdataguru_5ls89ot8_1_1channel ORA_DISK_1: errors found reading piece handle=/home/oracle/sdataguru_5ls89ot8_1_1channel ORA_DISK_1: failover to piece handle=/home/oracle/sdataguru/sdataguru_5ls89ot8_1_1 tag=FOR_STANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00009: /u01/app/oracle/oradata/newcdb/dgpdb1/system01.dbfdestination for restore of datafile 00010: /u01/app/oracle/oradata/newcdb/dgpdb1/sysaux01.dbfdestination for restore of datafile 00011: /u01/app/oracle/oradata/newcdb/dgpdb1/undotbs01.dbfdestination for restore of datafile 00012: /u01/app/oracle/oradata/newcdb/dgpdb1/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/sdataguru_5ms89otc_1_1channel ORA_DISK_1: errors found reading piece handle=/home/oracle/sdataguru_5ms89otc_1_1channel ORA_DISK_1: failover to piece handle=/home/oracle/sdataguru/sdataguru_5ms89otc_1_1 tag=FOR_STANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00013: /u01/app/oracle/oradata/newcdb/dgpdb2/system01.dbfdestination for restore of datafile 00014: /u01/app/oracle/oradata/newcdb/dgpdb2/sysaux01.dbfdestination for restore of datafile 00015: /u01/app/oracle/oradata/newcdb/dgpdb2/undotbs01.dbfdestination for restore of datafile 00016: /u01/app/oracle/oradata/newcdb/dgpdb2/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/sdataguru_5ns89otf_1_1channel ORA_DISK_1: errors found reading piece handle=/home/oracle/sdataguru_5ns89otf_1_1channel ORA_DISK_1: failover to piece handle=/home/oracle/sdataguru/sdataguru_5ns89otf_1_1 tag=FOR_STANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00017: /u01/app/oracle/oradata/newcdb/dgpdb3/system01.dbfdestination for restore of datafile 00018: /u01/app/oracle/oradata/newcdb/dgpdb3/sysaux01.dbfdestination for restore of datafile 00019: /u01/app/oracle/oradata/newcdb/dgpdb3/undotbs01.dbfdestination for restore of datafile 00020: /u01/app/oracle/oradata/newcdb/dgpdb3/users01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/sdataguru_5os89otg_1_1channel ORA_DISK_1: errors found reading piece handle=/home/oracle/sdataguru_5os89otg_1_1channel ORA_DISK_1: failover to piece handle=/home/oracle/sdataguru/sdataguru_5os89otg_1_1 tag=FOR_STANDBYchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished recover at 01-JUL-17

DG broker enable备库

DGMGRL> enable database newcdbdg;Enabled.DGMGRL> SHOW CONFIGURATION;Configuration - dg_newcdb  Protection Mode: MaxPerformance  Members:  newcdb   - Primary database    newcdbdg - Physical standby database Fast-Start Failover: DISABLEDConfiguration Status:SUCCESS   (status updated 2 seconds ago)DGMGRL> show database newcdbdg;Database - newcdbdg  Role:               PHYSICAL STANDBY  Intended State:     APPLY-ON  Transport Lag:      0 seconds (computed 0 seconds ago)  Apply Lag:          0 seconds (computed 0 seconds ago)  Average Apply Rate: 18.00 KByte/s  Real Time Query:    OFF  Instance(s):    newcdbDatabase Status:SUCCESS

SQL> alter database open;DGMGRL> show database newcdbdg;Database - newcdbdg  Role:               PHYSICAL STANDBY  Intended State:     APPLY-ON  Transport Lag:      0 seconds (computed 1 second ago)  Apply Lag:          0 seconds (computed 1 second ago)  Average Apply Rate: 39.00 KByte/s  Real Time Query:    ON  Instance(s):    newcdbDatabase Status:SUCCESSSQL> select count(*) from test;  COUNT(*)----------     72683

备库72号归档不存在，已使用增量恢复。

[oracle@oracle archive]$ lltotal 22680-rw-r----- 1 oracle oinstall   144896 Jul  1 21:50 1_70_947543403.dbf-rw-r----- 1 oracle oinstall    20480 Jul  1 21:51 1_71_947543403.dbf-rw-r----- 1 oracle oinstall     5632 Jul  1 22:08 1_73_947543403.dbf-rw-r----- 1 oracle oinstall    78848 Jul  1 22:08 1_74_947543403.dbf-rw-r----- 1 oracle oinstall    80384 Jul  1 22:08 1_75_947543403.dbf-rw-r----- 1 oracle oinstall  4111360 Jul  1 22:08 1_76_947543403.dbf-rw-r----- 1 oracle oinstall 18172928 Jul  1 22:08 1_77_947543403.dbf-rw-r----- 1 oracle oinstall   595968 Jul  1 22:13 1_78_947543403.dbf