Dataguard archive loss (not in the master database), and the slave database recovers Based on SCN

Dataguard archive loss (not in the master database). The slave database restores the Environment Based on SCN: OS: centos 6.5db: Oracle 10.2.0.5 1. master database environment master database: 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 wri Te netdata_pd primary maximum performancesql> archive log list; database Log mode archive modeautomatic archival enabledarchive destination/u01/APP/Oracle/archive/netdataoldest online log sequence 65 next log sequence to archive 67 current log sequence 67sql> slave database: 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_uniq UE _ database_role protection_mode ---------- ------------ ---------- ------------------ failed 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 63 next log sequence to archive 0 Current Log sequence 67sql> 2. simulate archive loss slave database operation: the slave database cancels archive application SQL> alter database recover managed standby database cancel; database altered. the standby database opens SQL> alter database open read only; database altered in read-only mode. SQL> This table is a test table created by me. insert data using a job (insert an SQL statement every 3 seconds> select count (*) from HR. test; count (*) ---------- 28501sql> master database operation: SQL> select count (*) from HR. test; count (*) ---------- 31143sql> you can see that the number of records is inconsistent, because the return is not transmitted over the master/backup archive, the master database operation 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/ipv52_857898543.arc 52 No/u01/APP/Oracle/archive/netdata/41053_857898543.arc 53 No/u01/APP/Oracle/archive/netdata/login 54 No/u01/APP/Oracle/archive/netdata/41555_857898543.arc 55 No/u01/APP/Oracle/archive/netdata/41556_857898543.arc 5 6 NO/u01/APP/Oracle/archive/netdata/201757_857898543.arc 57 No/u01/APP/Oracle/archive/netdata/201758_857898543.arc 58 No/u01/APP/Oracle/archive/netdata /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/10962_857898543.arc 62 No/u01/APP/Oracle/archive/netdata/10963_857898543.arc 63 No/u01 /APP/Oracle/archive/netdata/ipv64_857898543.arc 64 No/u01/APP/Oracle/archive/netdata/10965_857898543.arc 65 NO/u01/APP/Oracle/archive/netdata/route 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 y Es inactive 715138 13-sep-1413 1 66 104857600 2 Yes inactive 715158 13-sep-1414 1 67 104857600 2 no current 715164 13-sep-14sql> standby database operation 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 sequence ---------- ---/u01/APP/Oracle/archive/netdata/sequence 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 /Optional 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 C Learing 715138 13-sep-1413 1 63 104857600 2 Yes clearing 714611 13-sep-1414 1 67 104857600 2 Yes clearing_current 715164 13-sep-14 master database operation SQL> alter system set log_archive_dest_state_2 = 'defer'; 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> alter system archive log current; System altered. SQL> Delete archive SQL> archive log list; database Log mode archive modeautomatic archival enabledarchive destination/u01/APP/Oracle/archive/netdataoldest online log sequence 70 next log sequence to archive 72 current log sequence 72sql> delete an archive [email protected] netdata] $ Rm-RVF 1 \ _ 7 * deleted "201770_857898543.arc" deleted "201771_857898543.arc" deleted "1 _ 7_857893401.dbf "the master database enables the backup database archiving SQL> alter system set log_archive_dest_state_2 = 'enable'; the slave database restarts the application SQL> alter database recover managed standby database disconnect from session; database altered. SQL> system altered. SQL> query the standby Database Alert. logwaiting for all non-current orls to be archived... media recovery waiting for thread 1 sequence 67 fetching gap sequence in thread 1, Gap sequence 67-70sat Sep 13 21:00:27 CST 2014 Completed: 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 initializationparamete R is defined to a value that is sufficiently largeenough to maintain adequate log switch information to resolvearchivelog gaps. --------------------------------------------------------------- a gap generates the Select name, sequence #, applied from V $ archived_log A where. sequence # = (select max (sequence #) from V $ archived_log); Name sequence # app ------------------------------------------------ ---------- ---/u01 /APP/Oracle/archive/netdata/1_66_857898543.arc 66 Yes master database query SQL> select sequence #, first_change #, next_change # from V $ archived_log where sequence #> 67 order by 1; sequence # first_change # next_change # ---------- ----------- ------------ 68 745961 74597169 745971 74598470 745984 74598871 745988 715164 746005sql> the master database backs up RMAN Based on SCN incremental> Backup Device Type disk incremental from SCN database format '/u01/ backup/n Etdata_incret % 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.d Bfinput datafile fno = 00006 name =/u01/APP/Oracle/oradata/netdata/htsdk_01.dbfinput datafile fno = 00007 name =/u01/APP/Oracle/oradata/netdata/parse 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- 14 piece handle =/u01/backup/partition tag = partition comment = nonechannel ora_disk_1: Backup set complete, elapsed time: 00: 01: 18 channel 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 o Ra_disk_1: finished piece 1 at 13-sep-14piece handle =/u01/backup/container tag = tag20140913t212904 comment = nonechannel ora_disk_1: Backup set complete, elapsed time: 00: 00: 04 finished backup at 13-sep-14rman> the master database regenerates the control file SQL> alter database create standby controlfile as '/u01/backup/standby. CTL '; database altered. copy the file to the standby database [[email protected] backup] $ SCP * [email protected]:/u01/backu P/[email protected]'s password: netdata_incre0jpie810_00001.bbk 100% 17 MB 8.5 Mb/s 00:02 netdata_incre0kpie83e_00001.bbk 100% 7200kb 7.0 Mb/s 00:01 standby. CTL 100% 7.0 kb Mb/s slave database operation 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 recovery control file 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/netdat A/control01.ctloutput filename =/u01/APP/Oracle/oradata/netdata/control02.ctloutput filename =/u01/APP/Oracle/oradata/netdata/control03.ctlfinished restore at 13-sep-14rman> RMAN> catstart alog '/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_00001.bbkfi Le name:/u01/backup/standby. ctlfile name:/u01/backup/control01.ctlfile name:/u01/backup/netdata_incre0kpie83e_00001.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_00001.bbkfile Name: /u01/backup/standby. ctlfile name:/u01/backup/control01.ctlfile name:/u01/backup/recovery> recover database noredo; starting recover at least channel restart 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/export ora_disk_1: restored backup piece 1 piece handle =/u01/backup/export tag = tag20140913t212904channel ora_disk_1: restore complete, elapsed time: 00: 00: 04 finished recover at 13-sep-14rman> query Alert Log sat Sep 13 22:04:34 CST 2014rfs logminer: client disabled from further notificationsat Sep 13 22:04:54 CST 2014 incremental restore complete of datafile 4/u01/APP/Oracle/oradata/netdata/users01.dbf checkpoint is 753898 incremental restore complete of datafile 2/u01/ APP/Oracle/oradata/netdata/undotbs01.dbf checkpoint is 753898 last deallocation SCN is 669702 incremental restore complete of datafile 5/u01/APP/Oracle/oradata/netdata/example01.dbf checkpoint is 753898 last SCN is 399417 incremental restore complete of datafile 6/u01/APP/Oracle/oradata/netdata/htsdk_01.dbf checkpoint is 753898 incremental restore complete of datafile 7/u01/APP/Oracle/oradata/netdata /required checkpoint is 753898 incremental restore complete of datafile 3/u01/APP/Oracle/oradata/netdata/sysaux01.dbf checkpoint is 753898 last deallocation SCN is 421688 incremental restore complete of datafile 1/u01/ APP/Oracle/oradata/netdata/system01.dbf checkpoint is 753898 last deallocation SCN is 472342 slave database re-applied SQL> alter database recover managed standby database disconnect from session; database altered. SQL> test whether the archive is SQL> alter system switch logfile; System altered. SQL> query the archive log list for Master/Slave database operations; database Log mode archive modeautomatic archival enabledarchive destination/u01/APP/Oracle/archive/netdataoldest online log sequence 71 next log sequence to archive 0 current 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. sequence # = (select max (sequence #) from V $ archived_log ); name sequence # app route ---------- ---/u01/APP/Oracle/archive/netdata/route 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 Response --------- ------------------ log_archive_dest_1 validlog_archive_dest_2 validsql> archive log list for master database operations; Col name format A50; database Log mode archive modeautomatic archival enabledarchive destination/u01/APP/Oracle/archive/netdataoldest online log sequence 71 next log sequence to archive 73 current log sequence 73col dest_name format a40col error format a20set line 200; select name, sequence #, applied from V $ archived_log A where. sequence # = (select max (sequence #) from V $ archived_log ); name sequence # app route ---------- ---/u01/APP/Oracle/archive/netdata/route 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 cause --------- -------------------- log_archive_dest_1 validlog_archive_dest_2 validsql>

 

Dataguard archive loss (not in the master database), and the slave database recovers Based on SCN