This article demonstrates how to resolve standby database issues, in case there is any corrupted archivelog which has caused the data guard to go out of sync. Same procedure can be used in case there is huge archivelog gap between primary and standby database
Step 1 :- (Standby Side) - Cancel the recovery process and get current SCN of standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1018738
Step 2 :- (Primary Side) - Run RMAN incremental backup from SCN on standby side and Copy those backup pieces to standby side
RMAN> BACKUP INCREMENTAL FROM SCN 1018738 DATABASE FORMAT '/tmp/Standby_%U';
Starting backup at 09-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
backup will be obsolete on date 16-SEP-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PRODPRIM/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PRODPRIM/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PRODPRIM/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PRODPRIM/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-SEP-17
channel ORA_DISK_1: finished piece 1 at 09-SEP-17
piece handle=/tmp/Standby_05se20su_1_1 tag=TAG20170909T191021 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
using channel ORA_DISK_1
backup will be obsolete on date 16-SEP-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-SEP-17
channel ORA_DISK_1: finished piece 1 at 09-SEP-17
piece handle=/tmp/Standby_06se20to_1_1 tag=TAG20170909T191021 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-SEP-17
RMAN> exit
[oracle@prim tmp]$ scp Standby_0* oracle@192.168.56.102:/tmp/
oracle@192.168.56.102's password:
Standby_05se20su_1_1 100% 28MB 27.7MB/s 00:00
Standby_06se20to_1_1 100% 9600KB 9.4MB/s 00:00
[oracle@prim tmp]$
Step 3 :- (Standby Side) - Catalog the backup pieces and recover the database using the incremental backup
RMAN> CATALOG START WITH '/tmp/Standby';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/Standby
List of Files Unknown to the Database
=====================================
File Name: /tmp/Standby_05se20su_1_1
File Name: /tmp/Standby_06se20to_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: /tmp/Standby_05se20su_1_1
File Name: /tmp/Standby_06se20to_1_1
RMAN> RECOVER DATABASE NOREDO;
Starting recover at 09-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/PRODPRIM/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/PRODPRIM/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/PRODPRIM/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/PRODPRIM/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/Standby_05se20su_1_1
channel ORA_DISK_1: piece handle=/tmp/Standby_05se20su_1_1 tag=TAG20170909T191021
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 09-SEP-17
Step 4 :- (Primary Side) - Run the current control file backup for standby and copy the backup piece to standby side
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/StandbyCTRL.bkp';
Starting backup at 09-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 09-SEP-17
channel ORA_DISK_1: finished piece 1 at 09-SEP-17
piece handle=/tmp/StandbyCTRL.bkp tag=TAG20170909T192131 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-SEP-17
[oracle@prim tmp]$ scp /tmp/StandbyCTRL.bkp oracle@192.168.56.102:/tmp/
oracle@192.168.56.102's password:
StandbyCTRL.bkp 100% 9600KB 9.4MB/s 00:00
Step 5 :- (Standby Side) - Restore the standby controlfile and restart the database
Check the datafile names in standby if there is any change in datafile when compared to primary
select file#, name from v$datafile order by file# ;
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 494929912 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/StandbyCTRL.bkp';
Starting restore at 09-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/PRODSTBY/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/PRODSTBY/control02.ctl
Finished restore at 09-SEP-17
RMAN> shutdown immediate
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 494929912 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
In case there is any change in directory structure. Perform below mentioned steps
RMAN> CATALOG START WITH '/u01/app/oracle/oradata/PRODPRIM/';
Starting implicit crosscheck backup at 09-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 09-SEP-17
Starting implicit crosscheck copy at 09-SEP-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-SEP-17
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_ncsnf_TAG20170815T142045_ds5fklmm_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_nnndf_TAG20170815T142045_ds5fh5fc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_annnn_TAG20170815T142041_ds5fh218_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/backup/o1_mf_annnn_TAG20170815T142204_ds5fknfn_.bkp
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_13_ds5j8fgh_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_14_ds5j8g27_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_9_ds5j87jl_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_11_ds5j8bg4_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_7_ds5j6bgs_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_10_ds5j888f_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_16_ds5j8jyq_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_8_ds5j6dn5_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_15_ds5j8jj2_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_08_15/o1_mf_1_12_ds5j8c68_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_21_dv7vbwbt_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_24_dv7vbxfj_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_19_dv7vbvtp_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_23_dv7vbx0s_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_20_dv7vbvsx_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_25_dv7vbxjr_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_22_dv7vbwob_.arc
File Name: /u01/app/oracle/flash_recovery_area/PRODSTBY/archivelog/2017_09_09/o1_mf_1_17_dv7vbxwy_.arc
searching for all files that match the pattern /u01/app/oracle/oradata/PRODPRIM/
no files found to be unknown to the database
In Case of unknows files found use this command
RMAN> SWITCH DATABASE TO COPY;
Step 6 :- (Standby Side) - Clear all the standby redolog groups
SQL> select GROUP#, member from v$logfile;
GROUP# MEMBER
---------- -----------------------------------------------------------------------
3 /u01/app/oracle/oradata/PRODPRIM/redo03.log
2 /u01/app/oracle/oradata/PRODPRIM/redo02.log
1 /u01/app/oracle/oradata/PRODPRIM/redo01.log
4 /u01/app/oracle/oradata/PRODPRIM/stndby1.log
5 /u01/app/oracle/oradata/PRODPRIM/stndby2.log
6 /u01/app/oracle/oradata/PRODPRIM/stndby3.log
7 /u01/app/oracle/oradata/PRODPRIM/stndby4.log
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
Database altered.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;
Database altered.
Step 7:- (Standby Side) - Start the recovery process on standby side and verify the system
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Media recovery complete.
Verify the standby database
SQL> select PROCESS, PID, STATUS, SEQUENCE#, BLOCK# from v$managed_standby;
PROCESS PID STATUS SEQUENCE# BLOCK#
--------- ---------- ------------ ---------- ----------
ARCH 3809 CLOSING 28 1
ARCH 3811 CLOSING 29 1
ARCH 3813 CONNECTED 0 0
ARCH 3815 CLOSING 30 1
MRP0 3825 WAIT_FOR_LOG 31 0
RFS 7025 IDLE 0 0
RFS 7037 IDLE 31 18
Compare the sequence and SCN with primary database
SQL>
select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
30
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1069536
No comments:
Post a Comment