dataguard 归档丢失(主库中无此丢失归档处理),备库基于SCN恢复环境:OS: CentOS 6.5DB: Oracle 10.2.0.5 １.主备库环境主库:SQL> select dbid,name,LOG_MODE,open_mode,db_unique_name,DATABASE_ROLE,PROTECTION_MODE from v$database;      DBID NAME      LOG_MODE	  OPEN_MODE  DB_UNIQUE_NAME	  DATABASE_ROLE    PROTECTION_MODE---------- --------- ------------ ---------- -------------------- ---------------- -------------------- 351758316 NETDATA   ARCHIVELOG   READ WRITE netdata_pd 	  PRIMARY	   MAXIMUM PERFORMANCESQL> archive log list;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /u01/app/oracle/archive/netdataOldest online log sequence     65Next log sequence to archive   67Current log sequence	       67SQL> 备库:SQL> select dbid,name,LOG_MODE,open_mode,db_unique_name,DATABASE_ROLE,PROTECTION_MODE from v$database;      DBID NAME       LOG_MODE	   OPEN_MODE  DB_UNIQUE_ DATABASE_ROLE	  PROTECTION_MODE---------- ---------- ------------ ---------- ---------- ---------------- -------------------- 351758316 NETDATA    ARCHIVELOG   MOUNTED    netdata_sd PHYSICAL STANDBY MAXIMUM PERFORMANCESQL> archive log list;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /u01/app/oracle/archive/netdataOldest online log sequence     63Next log sequence to archive   0Current log sequence	       67SQL> 2.模拟归档丢失备库操作:备库取消归档应用SQL> ALTER DATABASE recover managed standby DATABASE cancel;Database altered.备库以只读模式打开SQL> alter database open read only;Database altered.SQL> 这张表是我建的测试表，用一个job跑的插入数据(每隔3S插入一条SQL> select count(*) from hr.test;  COUNT(*)----------     28501SQL> 主库操作:SQL> select count(*) from hr.test;  COUNT(*)----------     31143SQL> 可以看到记录数不一致，这是因为归还没有传输过来 主备归档情况主库操作SQL> set line 200SQL> set pagesize 200SQL> col name format A50SQL> select name,SEQUENCE#,APPLIED from  v$archived_log where dest_id=1;/u01/app/oracle/archive/netdata/1_52_857898543.arc	   52 NO/u01/app/oracle/archive/netdata/1_53_857898543.arc	   53 NO/u01/app/oracle/archive/netdata/1_54_857898543.arc	   54 NO/u01/app/oracle/archive/netdata/1_55_857898543.arc	   55 NO/u01/app/oracle/archive/netdata/1_56_857898543.arc	   56 NO/u01/app/oracle/archive/netdata/1_57_857898543.arc	   57 NO/u01/app/oracle/archive/netdata/1_58_857898543.arc	   58 NO/u01/app/oracle/archive/netdata/1_59_857898543.arc	   59 NO/u01/app/oracle/archive/netdata/1_60_857898543.arc	   60 NO/u01/app/oracle/archive/netdata/1_61_857898543.arc	   61 NO/u01/app/oracle/archive/netdata/1_62_857898543.arc	   62 NO/u01/app/oracle/archive/netdata/1_63_857898543.arc	   63 NO/u01/app/oracle/archive/netdata/1_64_857898543.arc	   64 NO/u01/app/oracle/archive/netdata/1_65_857898543.arc	   65 NO/u01/app/oracle/archive/netdata/1_66_857898543.arc	   66 NO64 rows selected.SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS	    FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------	12	    1	      65  104857600	     2 YES INACTIVE		   715138 13-SEP-14	13	    1	      66  104857600	     2 YES INACTIVE		   715158 13-SEP-14	14	    1	      67  104857600	     2 NO  CURRENT		   715164 13-SEP-14SQL> 备库操作SQL> set line 200SQL> set pagesize 200SQL> col name format A50SQL> select name,SEQUENCE#,APPLIED from  v$archived_log where dest_id=1;NAME						    SEQUENCE# APP-------------------------------------------------- ---------- ---/u01/app/oracle/archive/netdata/1_64_857898543.arc	   64 YES/u01/app/oracle/archive/netdata/1_65_857898543.arc	   65 YES/u01/app/oracle/archive/netdata/1_66_857898543.arc	   66 YES/u01/app/oracle/archive/netdata/1_63_857898543.arc	   63 YESSQL> select * from v$Log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS	    FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------	12	    1	      65  104857600	     2 YES CLEARING		   715138 13-SEP-14	13	    1	      63  104857600	     2 YES CLEARING		   714611 13-SEP-14	14	    1	      67  104857600	     2 YES CLEARING_CURRENT	   715164 13-SEP-14主库操作SQL>  ALTER system SET log_archive_dest_state_2 = ‘defer‘;System altered.SQL> SQL> alter system archive log current;System altered.SQL> alter system archive log current;System altered.SQL> alter system archive log current;System altered.SQL> alter system archive log current;System altered.SQL> alter system archive log current;System altered.SQL> 删除归档SQL> archive log list;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /u01/app/oracle/archive/netdataOldest online log sequence     70Next log sequence to archive   72Current log sequence	       72SQL> 删除归档[oracle@oracle10g-dg1-213-100 netdata]$ rm -rvf 1\_7*已删除"1_70_857898543.arc"已删除"1_71_857898543.arc"已删除"1_7_857893401.dbf"主库开启备库归档SQL>  ALTER system SET log_archive_dest_state_2 = ‘enable‘;备库重新开启应用SQL> ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;Database altered.SQL> System altered.SQL> 查询备库alert.logWaiting for all non-current ORLs to be archived...Media Recovery Waiting for thread 1 sequence 67Fetching gap sequence in thread 1, gap sequence 67-70Sat Sep 13 21:00:27 CST 2014Completed: ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSIONSat Sep 13 21:00:57 CST 2014FAL[client]: Failed to request gap sequence  GAP - thread 1 sequence 67-70 DBID 351758316 branch 857898543FAL[client]: All defined FAL servers have been attempted.-------------------------------------------------------------Check that the CONTROL_FILE_RECORD_KEEP_TIME initializationparameter is defined to a value that is sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps.-------------------------------------------------------------有gap产生了备库查询select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);NAME						    SEQUENCE# APP-------------------------------------------------- ---------- ---/u01/app/oracle/archive/netdata/1_66_857898543.arc	   66 YES主库查询SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log WHERE SEQUENCE# > 67 ORDER BY 1; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------	68	  745961       745971	69	  745971       745984	70	  745984       745988	71	  745988       746005SQL> 主库基于SCN增量备份RMAN> backup device type disk incremental from scn 715164 database format ‘/u01/backup/netdata_incre%U.bbk‘;Starting backup at 13-SEP-14using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/netdata/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/netdata/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/netdata/example01.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/netdata/HTSDK_01.dbfinput datafile fno=00007 name=/u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/netdata/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/netdata/users01.dbfchannel ORA_DISK_1: starting piece 1 at 13-SEP-14channel ORA_DISK_1: finished piece 1 at 13-SEP-14piece handle=/u01/backup/netdata_incre0jpie810_1_1.bbk tag=TAG20140913T212904 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:18channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 13-SEP-14channel ORA_DISK_1: finished piece 1 at 13-SEP-14piece handle=/u01/backup/netdata_incre0kpie83e_1_1.bbk tag=TAG20140913T212904 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04Finished backup at 13-SEP-14RMAN> 主库重新生成控制文件SQL> ALTER DATABASE CREATE standby controlfile AS ‘/u01/backup/standby.ctl‘;   Database altered.拷贝文件至备库[oracle@oracle10g-dg1-213-100 backup]$ scp * oracle@192.168.213.101:/u01/backup/oracle@192.168.213.101‘s password: netdata_incre0jpie810_1_1.bbk                                                                       100%   17MB   8.5MB/s   00:02    netdata_incre0kpie83e_1_1.bbk                                                                       100% 7200KB   7.0MB/s   00:01    standby.ctl                                                                                         100% 7120KB   7.0MB/s   00:01  备库操作SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area  704643072 bytesFixed Size		    2098912 bytesVariable Size		  192940320 bytesDatabase Buffers	  503316480 bytesRedo Buffers		    6287360 bytesSQL> quit恢复控制文件RMAN> restore controlfile from ‘/u01/backup/standby.ctl‘;Starting restore at 13-SEP-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=541 devtype=DISKchannel ORA_DISK_1: copied control file copyoutput filename=/u01/app/oracle/oradata/netdata/control01.ctloutput filename=/u01/app/oracle/oradata/netdata/control02.ctloutput filename=/u01/app/oracle/oradata/netdata/control03.ctlFinished restore at 13-SEP-14RMAN> RMAN> catalog start with ‘/u01/backup‘2> ;searching for all files that match the pattern /u01/backupList of Files Unknown to the Database=====================================File Name: /u01/backup/netdata_incre0jpie810_1_1.bbkFile Name: /u01/backup/standby.ctlFile Name: /u01/backup/control01.ctlFile Name: /u01/backup/netdata_incre0kpie83e_1_1.bbkDo you really want to catalog the above files (enter YES or NO)? yescataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/backup/netdata_incre0jpie810_1_1.bbkFile Name: /u01/backup/standby.ctlFile Name: /u01/backup/control01.ctlFile Name: /u01/backup/netdata_incre0kpie83e_1_1.bbkRMAN>  recover DATABASE noredo;Starting recover at 13-SEP-14using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle/oradata/netdata/system01.dbfdestination for restore of datafile 00002: /u01/app/oracle/oradata/netdata/undotbs01.dbfdestination for restore of datafile 00003: /u01/app/oracle/oradata/netdata/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle/oradata/netdata/users01.dbfdestination for restore of datafile 00005: /u01/app/oracle/oradata/netdata/example01.dbfdestination for restore of datafile 00006: /u01/app/oracle/oradata/netdata/HTSDK_01.dbfdestination for restore of datafile 00007: /u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbfchannel ORA_DISK_1: reading from backup piece /u01/backup/netdata_incre0jpie810_1_1.bbkchannel ORA_DISK_1: restored backup piece 1piece handle=/u01/backup/netdata_incre0jpie810_1_1.bbk tag=TAG20140913T212904channel ORA_DISK_1: restore complete, elapsed time: 00:00:04Finished recover at 13-SEP-14RMAN> 查询alert 日志Sat Sep 13 22:04:34 CST 2014RFS LogMiner: Client disabled from further notificationSat Sep 13 22:04:54 CST 2014Incremental restore complete of datafile 4 /u01/app/oracle/oradata/netdata/users01.dbf  checkpoint is 753898Incremental restore complete of datafile 2 /u01/app/oracle/oradata/netdata/undotbs01.dbf  checkpoint is 753898  last deallocation scn is 669702Incremental restore complete of datafile 5 /u01/app/oracle/oradata/netdata/example01.dbf  checkpoint is 753898  last deallocation scn is 399417Incremental restore complete of datafile 6 /u01/app/oracle/oradata/netdata/HTSDK_01.dbf  checkpoint is 753898Incremental restore complete of datafile 7 /u01/app/oracle/oradata/netdata/HTSDK_INDEX_01.dbf  checkpoint is 753898Incremental restore complete of datafile 3 /u01/app/oracle/oradata/netdata/sysaux01.dbf  checkpoint is 753898  last deallocation scn is 421688Incremental restore complete of datafile 1 /u01/app/oracle/oradata/netdata/system01.dbf  checkpoint is 753898  last deallocation scn is 472342备库重新应用SQL>  ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;Database altered.SQL> 测试归档是否SQL> alter system switch logfile;System altered.SQL> 查询主备备库操作archive log list;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /u01/app/oracle/archive/netdataOldest online log sequence     71Next log sequence to archive   0Current log sequence	       73col name format A50;col dest_name format A40col error format A20set line 200;select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);NAME						    SEQUENCE# APP-------------------------------------------------- ---------- ---/u01/app/oracle/archive/netdata/1_72_857898543.arc	   72 YEScol dest_name format A40SQL>  select dest_name,status,error from v$archive_dest where rownum<3; DEST_NAME				 STATUS    ERROR---------------------------------------- --------- --------------------LOG_ARCHIVE_DEST_1			 VALIDLOG_ARCHIVE_DEST_2			 VALIDSQL> 主库操作archive log list;col name format A50;Database log mode	       Archive ModeAutomatic archival	       EnabledArchive destination	       /u01/app/oracle/archive/netdataOldest online log sequence     71Next log sequence to archive   73Current log sequence	       73col dest_name format A40col error format A20set line 200;select name,sequence#,applied from v$archived_log a where a.sequence#=(select max(sequence#) from v$archived_log);NAME						    SEQUENCE# APP-------------------------------------------------- ---------- ---/u01/app/oracle/archive/netdata/1_72_857898543.arc	   72 NOnetdata_sd						   72 YEScol dest_name format A40SQL>  select dest_name,status,error from v$archive_dest where rownum<3; DEST_NAME				 STATUS    ERROR---------------------------------------- --------- --------------------LOG_ARCHIVE_DEST_1			 VALIDLOG_ARCHIVE_DEST_2			 VALIDSQL>