Problem :
When oraarch from Primary Database (DC) got deleted or corrupted before apply to Standby Database (DR), because of this log apply has stopped and both the system are not in sync.
Solution :
1. Verify log sequence at both DC and DR
Run below command at both DC and DR.
Find out last log number generated and last log applied at
DR. Verify if there is a gap.
select max(sequence#) from v$archived_log where applied='YES';
2. Stop oraarch shipping from DC to DR.
Set log_archive_dest_state_2 parameter to ‘DEFER’ at DC database in order to stop log shipping.
At Primary Database:
Verify Parameter value:
show parameter log_archive_dest_state_2;
log_archive_dest_state_2 string ENABLE
Change Parameter value:
alter system set log_archive_dest_state_2='DEFER' scope=both;
Verify Parameter value:
show parameter log_archive_dest_state_2;
log_archive_dest_state_2 string DEFER
3. Find out current SCN number of DR Database
At Standby Database:
Run below command to find out SCN number:
SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
TO_CHAR(CURRENT_SCN)
264020244345
4. Take incremental backup (through RMAN utility) of DC Database from SCN number
captured in last step .
At Primary Database:
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
backup incremental from scn 264020244345 database tag='STANDBY_24092021' format
'/media/database_%d_%u_%s';
release channel ch1;
release channel ch2;
release channel ch3;
}
5. Create a standby control file to restore it in the standby database.
At Primary Database:
Run below command to create standby control file.
alter database create standby controlfile as '/media/cntrlEHS.dbf';
6. Move the incremental backup files and control files from DC to DR through SCP/rsync
7. Stop the recovery process (MRP) in DR.
At Standby Database:
Run below command to stop MRP process at DR.
alter database recover managed standby database cancel;
8. Shutdown the database and replace control files
At Standby Database:
Run below command to shut down the database
SQL> shutdown immediate;
Take backup of current control files and then replace all control files
9. Add incremental backup files to RMAN catalog and start the recovery.
At Standby Database:
Add backup files to catalog with below command.
RMAN> catalog backuppiece ‘/media/database_EHS_o902a1as_5897’;
Start RMAN recovery with below command.
RMAN> RECOVER DATABASE FROM TAG STANDBY_24092021;
10. After completion of restore start the log shipping from DC to DR
At Primary Database:
Run below command to start log shipping.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
11. Start the log shipping from DC to DR
At Primary Database.
Run below command to start log shipping.
alter system set log_archive_dest_state_2='ENABLE' scope=both;
12. Verify that logs are being shipped from DC to DR and getting applied at DR. Once all logs
have been applied, check last log applied at both DC and DR.
