When a standby database is not being updated, due to missing or corrupt archive logs, the following steps are to be followed:-
TO RECOVER A STANDBY DATABASE IN CASE OF MISSING/CORRUPT ARCHIVE LOGS
When a standby database is not being updated, due to missing or corrupt archive logs, the following steps are to be followed:-
1. In the standby database find and note the current SCN (system change number), using the following command:-
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1.3772E+10+
2. Take the incremental back in the Main/Primary database using RMAN. In the Main/Primery database connect to RMAN utility from oracle user.
Defer the archive_log_dest_state for standby DB
oracle~]$rman target/
Start the incremental backup from RMAN utility.
RMAN>run
2>{
3>allocate channel ch1 device type disk
4>formate=’/u03/back_%U’;
5>backup incremental from SCN database;
6>}
The backup will be taken in the ‘/u03’ path with the file names prefixed with “back” which is defined in the “format” clause of the RMAN incremental backup. There will be two to three backup files created by RMAN.
3. On successful completion of the backup, copy the backup file from ‘/u03’ of the main/primary database server to the same location in the standby database server.
4. Once all the backup files are copied to the standby database. Start to restore the standby database with RMAN utility using the copied backup files. In the standby database connect to RMAN utility from oracle user:-
oracle~]$ rman target/
Before starting the recovery process it is necessary to define the location of the backup files to the RMAN utility
RMAN>catalog start with ‘/u03/’;
Start the recovery of the standby database, ensure that the standby database is in the mount stage.
RMAN>run
2>{
3>allocate channel ch1 device type disk;
4>recover database noredo ;
5>}
5. When the recovery process of the standby database is complete. Create a standby control file in the Main/Primary database server.
SQL> alter database create standby controlfile as ‘/u03sbctrl.ctl’;
6. Shutdown the standby database. Copy the standby controlfile from the Main/Primary database server to the standby database server.Replace all the control file of the standby with the newly created control file. Replace all the control files with their old or previous names itself (if the old control files name was sbctl01.ctl the new filename should also be sbctl01.ctl)
To find out the path and name of the control files run the following command in the standby database:-
SQL> show parameter control_files;
7. Once all the control files are replaced , the standby database is to be started in the
Mount stage and recovery process of archive log should be started as mentioned below:-
SQL> startup mount
SQL> alter database recover automatic standby database;
8. In the main/Primary database if you had DEFER the state of the standby archive log destination of the database which is being restored , is to be changed to ENABLE. (Let us assume that the standby database which requires to be restored is defined in log_archive_dest_3 parameter of the Main/Primary database). To know about the state of the archive log destination execute the following query:-
SQL>show parameter log_archive_dest
The result of the above command shows that the log_archive_dest_state_3 as DEFER,means that ,the process of pushing the archive logs generated at the Main/Primary database is stopped for that particular destinationor standby database.
To change the state of the log_archive_dest_state_3 execute the following command :-
SQL>alter system set log_archive_dest_state_3=”ENABLE” scope=both;
This will start the process of the pushing the archive logs being generated in the Main/Primary to the standby database which is set in log_archive_dest_3.
9. Once when the log_archive_dest_state_3 is set to enable, all the archive logs will automatically be pushed to the log_archive_dest_3 i.e. standby database which was restored.
10. Ensure from the standby database, that all the archive logs are being received as and when they are generated in the Main/Primary database (path of archive logs is /u03/archlog). When executing automatic recovery process as mentioned in para 7
above, the recovery process will terminate with the mentioned error. You should start the managed recovery process as mentioned below:-
SQL>alter database recover managed standby database disconnect;
11. In the standby database run the following query to know the status of the archive log being applied.
SQL> select sequence#,first_time,completion_time,applied,status from v$archived_log where sequence# >=(select max(sequence#) from v$archived_log where applied=’YES’) order by sequence# desc;
SEQUENCE# FIRST_TIM COMPLETIO APPLIED S
———- ——— ——— ——— –
73756 25-APR-17 25-APR-17 NO A
73756 25-APR-17 25-APR-17 NO A
73755 25-APR-17 25-APR-17 YES A
73755 25-APR-17 25-APR-17 NO A
12. To check the pushing of archive log, in the Main/Primary database execute the following command to generate new archive log.
SQL>alter system switch logfile;