Archive log GAP filling if required Archive log file is missing

A Physical Standby database sync with the Primary database and Sending Archive logs to Physical Standby database server. Once archive logs arive to Physical standby server ,The MRP process will automatically apply the logs on physical standby database.Archive logs which are applied on Physical standby database are no longer needed for recovery.

Incase archive logs are corrupted on primary database or is deleted or there is much network delay between primary and physical standby server, than there is 100% chances of Archive logs misses.

If Archive logs are missed than there is only way to take full rman backup and restore it in standby server. But with Oracle 10g there is an incremental backup, through that you can fill up the GAP.

Note: we have both ASM and OCFS2 file system in our environment.

CHECK THE SCN NUMBER ON DR-SERVER
sqlplus /nolog
SQL>conn /as sysdba
SQL> select current_scn from v$database;
1855019926

FROM PRIMARY SERVER TAKE RMAN INCREMENTAL BACKUP
rman target /
rman>BACKUP as compressed backupset device type disk INCREMENTAL FROM SCN 1855019926 DATABASE FORMAT ‘/u02/rmanforstandby_%U’ tag ‘FOR STANDBY’;

input datafile fno=00129 name=+ARCH/PROD/datafile/users.1231.786791795
 input datafile fno=00133 name=+DGROUP1/PROD/datafile/users.270.788951961
 input datafile fno=00003 name=+DGROUP1/PROD/datafile/sysaux.263.775673237
 input datafile fno=00122 name=+ARCH/PROD/datafile/edi_space01.dbf
 channel ORA_DISK_1: starting piece 1 at 06-SEP-12
 channel ORA_DISK_1: finished piece 1 at 06-SEP-12
 piece handle=/u02/rmanforstandby_92nkhigd_1_1 tag=FOR STANDBY comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:14:26
 channel ORA_DISK_1: starting compressed full datafile backupset
 channel ORA_DISK_1: specifying datafile(s) in backupset
 including current control file in backupset
 including current SPFILE in backupset
 channel ORA_DISK_1: starting piece 1 at 06-SEP-12
 channel ORA_DISK_1: finished piece 1 at 06-SEP-12
 piece handle=/u02/rmanforstandby_93nkhjbf_1_1 tag=FOR STANDBY comment=NONE
 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
 Finished backup at 06-SEP-12

MOVE THE BACKUP TO DR-SERVER

[oracle@rac-node1 u02]$ scp -r *rman DR-SERVER:/rman/rman/.
 rmanforstandby_90nkhggk_1_1 100% 4240MB 7.1MB/s 09:56
 rmanforstandby_91nkhhfn_1_1 100% 5473MB 7.0MB/s 13:03
 rmanforstandby_92nkhigd_1_1 100% 4666MB 7.0MB/s 11:03
 rmanforstandby_93nkhjbf_1_1 100% 2592KB 2.5MB/s 00:00
 [oracle@rac-node1 u02]$

LIST ON DR-SERVER

[oracle@DR-SERVER rman]$ ls -rtl
 total 14740916
 -rw-r----- 1 oracle oinstall 4446437376 Sep 6 16:42 rmanforstandby_90nkhggk_1_1
 -rw-r----- 1 oracle oinstall 5738668032 Sep 6 16:55 rmanforstandby_90nkhggk_1_1
 -rw-r----- 1 oracle oinstall 2654208 Sep 6 17:06 rmanforstandby_93nkhjbf_1_1
 -rw-r----- 1 oracle oinstall 4892164096 Sep 6 17:06 rmanforstandby_92nkhigd_1_1

CANCEL THE MRP PROCESS ON DR-SERVER

SQL>alter database recover managed standby database cancel;
 Media recovery complete.

START RMAN ON DR-SERVER (STANDBY DATABASE NAME IS PROD)

[oracle@DR-SERVER rman]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Sep 6 17:16:15 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PROD (DBID=754922818, not open)

CATALOG THE INCREMENTAL BACKUP

RMAN> catalog start with '/rman/rman';
using target database control file instead of recovery catalog
 searching for all files that match the pattern /rman/rman
List of Files Unknown to the Database
 =====================================
 File Name: /rman/rman/rmanforstandby_91nkhhfn_1_1
 File Name: /rman/rman/rmanforstandby_90nkhggk_1_1
 File Name: /rman/rman/rmanforstandby_92nkhigd_1_1
 File Name: /rman/rman/rmanforstandby_93nkhjbf_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
 cataloging files...
 cataloging done
List of Cataloged Files
 =======================
 File Name: /rman/rman/rmanforstandby_91nkhhfn_1_1
 File Name: /rman/rman/rmanforstandby_90nkhggk_1_1
 File Name: /rman/rman/rmanforstandby_92nkhigd_1_1
 File Name: /rman/rman/rmanforstandby_93nkhjbf_1_1
RECOVER STANDBY DATABASE
 RMAN> recover database noredo;
Starting recover at 06-SEP-12
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=159 devtype=DISK
 channel ORA_DISK_1: starting incremental datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 destination for restore of datafile 00001: /database/oradata/PROD/datafile/system.267.775673237
 destination for restore of datafile 00005: /database/oradata/PROD/datafile/undotbs2.257.775673301
 destination for restore of datafile 00009: /database/oradata/PROD/datafile/undotbs2.278.776018291
 destination for restore of datafile 00010: /database/oradata/PROD/datafile/users.279.776018383
 destination for restore of datafile 00014: /database/oradata/PROD/datafile/audit_data_space.286.776019565
 destination for restore of datafile 00017: /database/oradata/PROD/datafile/audit_data_space.289.776019715
 destination for restore of datafile 00020: /database/oradata/PROD/datafile/audit_data_space.292.776019827
 destination for restore of datafile 00023: /database/oradata/PROD/datafile/msc_data_space.295.776020085
 destination for restore of datafile 00026: /database/oradata/PROD/datafile/msc_data_space.298.776020147
 destination for restore of datafile 00029: /database/oradata/PROD/datafile/msc_data_space.301.776020187
 destination for restore of datafile 00032: /database/oradata/PROD/datafile/msc_data_space.304.776020227
 destination for restore of datafile 00035: /database/oradata/PROD/datafile/msc_data_space.307.776020279
 destination for restore of datafile 00038: /database/oradata/PROD/datafile/msc_data_space.310.776020459
 destination for restore of datafile 00041: /database/oradata/PROD/datafile/msc_data_space.313.776020515
 destination for restore of datafile 00044: /database/oradata/PROD/datafile/msc_data_space.316.776020559
 destination for restore of datafile 00047: /database/oradata/PROD/datafile/msc_data_space.319.776020659
 destination for restore of datafile 00050: /database/oradata/PROD/datafile/msc_data_space.322.776020729
 destination for restore of datafile 00053: /database/oradata/PROD/datafile/msc_data_space.325.776020771
 destination for restore of datafile 00056: /database/oradata/PROD/datafile/msc_data_space.328.776020853
 destination for restore of datafile 00059: /database/oradata/PROD/datafile/msc_data_space.331.776020893
 destination for restore of datafile 00062: /database/oradata/PROD/datafile/msc_data_space.334.776020959
 destination for restore of datafile 00065: /database/oradata/PROD/datafile/msc_data_space.337.776021027
 destination for restore of datafile 00068: /database/oradata/PROD/datafile/msc_index_space.340.776021367
 destination for restore of datafile 00071: /database/oradata/PROD/datafile/msc_index_space.343.776021413
 destination for restore of datafile 00074: /database/oradata/PROD/datafile/msc_index_space.346.776021459
 destination for restore of datafile 00077: /database/oradata/PROD/datafile/msc_index_space.349.776021695
 destination for restore of datafile 00080: /database/oradata/PROD/datafile/msc_idx_space.352.776021867
 destination for restore of datafile 00083: /database/oradata/PROD/datafile/msc_idx_space.355.776022023
 destination for restore of datafile 00086: /database/oradata/PROD/datafile/msc_idx_space.358.776022117
 destination for restore of datafile 00089: /database/oradata/PROD/datafile/msc_edi_space.361.776022435
 destination for restore of datafile 00092: /database/oradata/PROD/datafile/msc_edi_space.364.776022537
 destination for restore of datafile 00095: /database/oradata/PROD/datafile/undotbs1.367.776072827
 destination for restore of datafile 00099: /database/oradata/PROD/datafile/edi_space.370.779817759
 destination for restore of datafile 00102: /database/oradata/PROD/datafile/users.373.779819227
 destination for restore of datafile 00105: /database/oradata/PROD/datafile/users.376.779819371
 destination for restore of datafile 00108: /database/oradata/PROD/datafile/users.379.779819441
 destination for restore of datafile 00111: /database/oradata/PROD/datafile/users.382.779819539
 destination for restore of datafile 00114: /database/oradata/PROD/datafile/users.385.779819657
 destination for restore of datafile 00117: /database/oradata/PROD/datafile/users.1571.784664399
 destination for restore of datafile 00120: /database/oradata/PROD/datafile/users.2448.785063775
 destination for restore of datafile 00124: /database/oradata/PROD/datafile/users4.dbf
 destination for restore of datafile 00127: /database/oradata/PROD/datafile/users5.dbf
 destination for restore of datafile 00130: /database/oradata/PROD/datafile/users.1281.786901255
 destination for restore of datafile 00131: /database/oradata/PROD/datafile/undotbs2.393.787061983
 destination for restore of datafile 00134: /database/oradata/PROD/datafile/o1_mf_audit_da_82xdcs59_.dbf
 channel ORA_DISK_1: reading from backup piece /rman/rman/rmanforstandby_91nkhhfn_1_1
destination for restore of datafile 00123: /oradata/msctrapp_index_space36.dbf
 channel ORA_DISK_1: reading from backup piece /ora_rman_backup/rman/inc/forstandby_h4nkht8a_1_1
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/ora_rman_backup/rman/inc/forstandby_h4nkht8a_1_1 tag=FOR STANDBY
 channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished recover at 07-SEP-12
RMAN>

CREATE STANDBY CONTROL FILE FROM PRIMARY

SQL> Alter database create standby controlfile as '/u02/rman/standby.ctl';

COPY THE STANDBY CONTROLFILE TO DR-SERVER

SHUTDOWNT THE DR-SERVER
 SQL> shutdown immediate
 SQL> startup nomount
 SQL> exit

RESTORE STANDBY CONTROLFILE TO DR-SERVER

rman target /
 rman> restore controlfile from '/u02/rman/standby.ctl';
restore completed.
 SQL> shu immediate
 SQL> STARTUP MOUNT
IF THERE IS DIFFERENCE IN PATH FOR DATAFILES THAN NEEDS TO BE MODIFIED BY 
 SQL> alter database rename file 'primary-sever-file-location' to 'dr-server-file-location';

IN OUR CANSE FILES PATH ARE SAME IN BOTH PRIMARY AND STANDBY SERVERS AND DATABASE NAME IS SAME LIKE PROD ON BOTH SERVERS

IF DATAFILES ARE ADDED TO DATABASE DURING THE ARCHIVELOG GAP THAN FIRST DATAFILES NEEDS TO BE RESTORED AS BELOW
As per our environment there are four datafiles were not created due to ARCHIVE GAP on DR-SERVER

RMAN> RUN {
 RESTORE DATAFILE 129,130,131,132;
 }
 2> 3>
 Starting restore at 07-SEP-12
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 using channel ORA_DISK_3
 using channel ORA_DISK_4
 using channel ORA_DISK_5
 using channel ORA_DISK_6
 using channel ORA_DISK_7
 using channel ORA_DISK_8
 using channel ORA_DISK_9
 using channel ORA_DISK_10
 using channel ORA_DISK_11
 using channel ORA_DISK_12
 using channel ORA_DISK_13
 using channel ORA_DISK_14
 using channel ORA_DISK_15
 using channel ORA_DISK_16
channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00131 to /oradata/msctrapp_data_space12.dbf
 channel ORA_DISK_1: reading from backup piece /ora_rman_backup/rman/db_793314873_29237_p1
 channel ORA_DISK_2: starting datafile backupset restore
 channel ORA_DISK_2: specifying datafile(s) to restore from backup set
 restoring datafile 00132 to /oradata/msctraudit_data_space31.dbf
 channel ORA_DISK_2: reading from backup piece /ora_rman_backup/rman/db_793321416_29239_p1
 channel ORA_DISK_3: starting datafile backupset restore
 channel ORA_DISK_3: specifying datafile(s) to restore from backup set
 restoring datafile 00129 to /oradata/msctrapp_data_space10.dbf
 channel ORA_DISK_3: reading from backup piece /ora_rman_backup/rman/inc/forstandby_hankhte5_1_1
 channel ORA_DISK_4: starting datafile backupset restore
 channel ORA_DISK_4: specifying datafile(s) to restore from backup set
 restoring datafile 00130 to /oradata/msctrapp_data_space11.dbf
 channel ORA_DISK_4: reading from backup piece /ora_rman_backup/rman/inc/forstandby_hbnkhthf_1_1
 ORA-19870: error reading backup piece /ora_rman_backup/rman/db_793314873_29237_p1
 ORA-19505: failed to identify file "/ora_rman_backup/rman/db_793314873_29237_p1"
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 ORA-19870: error reading backup piece /ora_rman_backup/rman/db_793321416_29239_p1
 ORA-19505: failed to identify file "/ora_rman_backup/rman/db_793321416_29239_p1"
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
 channel ORA_DISK_3: restored backup piece 1
 piece handle=/ora_rman_backup/rman/inc/forstandby_hankhte5_1_1 tag=FOR STANDBY
 channel ORA_DISK_3: restore complete, elapsed time: 00:03:36
 channel ORA_DISK_4: restored backup piece 1
 piece handle=/ora_rman_backup/rman/inc/forstandby_hbnkhthf_1_1 tag=FOR STANDBY
 channel ORA_DISK_4: restore complete, elapsed time: 00:03:51
 failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00131 to /oradata/msctrapp_data_space12.dbf
 channel ORA_DISK_1: reading from backup piece /ora_rman_backup/rman/inc/forstandby_ggnkhse7_1_1
 channel ORA_DISK_2: starting datafile backupset restore
 channel ORA_DISK_2: specifying datafile(s) to restore from backup set
 restoring datafile 00132 to /oradata/msctraudit_data_space31.dbf
 channel ORA_DISK_2: reading from backup piece /ora_rman_backup/rman/inc/forstandby_ginkhsee_1_1
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/ora_rman_backup/rman/inc/forstandby_ggnkhse7_1_1 tag=FOR STANDBY
 channel ORA_DISK_1: restore complete, elapsed time: 00:03:35
 channel ORA_DISK_2: restored backup piece 1
 piece handle=/ora_rman_backup/rman/inc/forstandby_ginkhsee_1_1 tag=FOR STANDBY
 channel ORA_DISK_2: restore complete, elapsed time: 00:05:00
 Finished restore at 07-SEP-12

RMAN>

CREATE STANDBY LOGFILE ON STANDBY SERVER

WE HAVE 3 NODES RAC SERVER AT PRIMARY AND 2 NODES DR-SERVER STANDBY LOCATIONS

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 100 '/oradata2/prod/standby1101.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 101 '/oradata2/prod/standby102.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 102 '/oradata2/prod/standby103.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 103 '/oradata2/prod/standby104.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 104 '/oradata2/prod/standby105.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 200 '/oradata2/prod/standby201.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 201 '/oradata2/prod/standby202.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 202 '/oradata2/prod/standby203.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 203 '/oradata2/prod/standby204.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 204 '/oradata2/prod/standby205.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 GROUP 401 '/oradata2/prod/standby401.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 GROUP 402 '/oradata2/prod/standby402.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 GROUP 403 '/oradata2/prod/standby403.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 GROUP 404 '/oradata2/prod/standby404.log' size 150M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 4 GROUP 405 '/oradata2/prod/standby505.log' size 150M;

 

START THE RECOVERY PROCESS (MRP) ON DR-SERVER

SQL>alter database recover managed standby database disconnect from session;

IN STANDBY ALERT LOG WE CAN SEE ARCHIVE LOG SHIPING STARTED AND GAP IS GETING RESOLVED.

CHECK ARCHIVE LOGS SEQUENCE ON PRIMARY RAC SERVER

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# 
 order by 1 ;
 SQL> /
THREAD# Last Primary Seq Generated
 ---------- --------------------------
 1 20076
 2 11251
 4 10119
SQL>

AFTER SOME TIME VERIFY THE ARCHIVE LOGS STATUS ON DR-SERVER

SQL> select THREAD#,SEQUENCE#,status,ARCHIVED,APPLIED from v$archived_log where (THREAD#=1 and SEQUENCE# > &Node1_archive_10 )or (THREAD#=2 and SEQUENCE# > &Node2_archive_10)
or (THREAD#=4 and SEQUENCE# > &Node3_archive_10 ) order by THREAD#,SEQUENCE#;
Enter value for node1_archive_10: 20066
Enter value for node2_archive_10: 11245
Enter value for node3_archive_10: 10112
THREAD# SEQUENCE# S ARC APP
———- ———- – — —
1 20067 A YES YES
1 20068 A YES YES
1 20069 A YES YES
1 20070 A YES YES
1 20071 A YES YES
1 20072 A YES YES
1 20073 A YES YES
1 20074 A YES YES
1 20075 A YES YES
1 20076 A YES YES
2 11246 A YES YES
THREAD# SEQUENCE# S ARC APP
———- ———- – — —
2 11247 A YES YES
2 11248 A YES YES
2 11249 A YES YES
2 11250 A YES YES
2 11251 A YES NO
4 10113 A YES YES
4 10114 A YES YES
4 10115 A YES YES
4 10116 A YES YES
4 10117 A YES YES
4 10118 A YES YES
THREAD# SEQUENCE# S ARC APP
———- ———- – — —
4 10119 A YES NO
23 rows selected.
SQL>

IT MEANS ARCHIVE LOGS ARE APPLYING ON DR-SERVER

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle DataGaurd. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s