A Physical Standby database relies on the continuous application of archive logs from a Primary Database to be in sync with it. In Oracle Database versions prior to 10g in the event of an archive log gone missing or corrupt, you had to rebuild the standby database from scratch. From 10g you can use an incremental backup from SCN and recover the standby using the same to compensate for the missing archive logs. In this, we will see how to recover the standby database from a missing archive log
So here are the steps for how to recover the standby database from a missing archive log
Step 1:
On the standby database check the current SCN
sqlplus "/ as sysdba" SQL>set numwidth 30; SQL>select current_scn from v$database; CURRENT_SCN ----------- 6746747647647
Step 2:
On the primary database create the needed incremental backup from the above SCN
rman target /
RMAN> {
allocate channel c1 type disk;
BACKUP INCREMENTAL FROM SCN 6746747647647 DATABASE
FORMAT /tmp/inc_standby_%U';
}
We can use parallel workers to speed up the backup creation if the database has generated a lot of changes
run
{allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
allocate channel d9 type disk;
allocate channel d10 type disk;
BACKUP INCREMENTAL FROM SCN 6746747647647 DATABASE
FORMAT /tmp/inc_standby_%U';
}
Step 3:
Cancel managed recovery at the standby database
sqlplus "/ as sysdba" SQL>alter database recover managed standby database cancel; Media recovery complete.
Step 4:
- scp the backup files to the standby server to /tmp folder.
- Catalog the Incremental Backup Files in the Standby Database
rman target / RMAN> CATALOG START WITH '/tmp/'; searching for all files that match the pattern /tmp/ List of Files Unknown to the Database =====================================…… Do you really want to catalog the above files (enter YES or NO)? YES cataloging files… cataloging done
Step 5:
Apply the Incremental Backup to the Standby Database
rman target / RMAN>RECOVER DATABASE NOREDO;
Step 6:
Put the standby database back to managed recovery mode.
sqlplus "/ as sysdba" SQL>recover managed standby database disconnect; Media recovery complete.
From the alert.log you will notice that the standby database is still looking for the old log files
FAL[client]: Failed to request gap sequence GAP - thread 1 sequence ….
This is because the controlfile has not been updated. Hence the standby controlfile has to be recreated
Step 7:
On the primary create new standby controlfile
sqlplus "/ as sysdba" SQL> alter database create standby controlfile as ‘/tmp/standby01.ctl’; System altered.
Step 8:
Capture datafile information in the STANDBY database.
The standby controlfile will need to be refreshed from the backup taken at step #7. Since the data file names are likely different than the primary, save the names of your standby data file names for reference after restoring the controlfile from the primary backup. Run the below query in the standby database and save the results for further use.
spool standby_datafile_names.txt
set pagesize 1000;
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
Step 9:
Copy the standby control file to the standby site. Shutdown the standby database and replace the standby control files and restart the standby database in the managed recovery mode using the below command
RMAN> SHUTDOWN IMMEDIATE ; RMAN> STARTUP NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/standby01.ctl';
Step 10:
Mount the standby
RMAN> ALTER DATABASE MOUNT;
Step 11:
This step is required if the location of the datafiles is different in standby and Primary
If the primary and standby have identical structures and data file names, this step can be skipped.
Oracle recommends checking the incarnation for primary and standby before completing this step.
example:
RMAN> list incarnation;
Since we have restored the controlfile from PRIMARY, the datafile location names in this restored STANDBY controlfile will be the same as those of the PRIMARY database. If the directory structure is different between the standby and primary databases or if you are using Oracle-managed file names OMF then it will not be able to identify the standby files. So we can catalog the STANDBY datafiles with RMAN to execute the rename operation.
Perform the below step in STANDBY for each diskgroup (or directory) where the standby data files reside.
RMAN> CATALOG START WITH '+DATA/STBY/datafile/';
If any data files have been added to primary AFTER the backup SCN (in our example, scn 6746747647647) those data files will not be created on the standby server automatically regardless of standby_file_management parameter setting. The added data files will have to be restored to the standby server. To determine if any files have been added to Primary since the standby current SCN
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 6746747647647
If it returns any rows, then we have to restore those files from primary to standby
RMAN> backup datafile <missing-1>,<missing-2> ,<missing-3> , format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
Copy these to standby and then catalog and restore them
CATALOG START WITH '/tmp/ForStandby';
run
{
set newname for datafile X to '+DISKGROUP';
set newname for datafile Y to '+DISKGROUP';
set newname for datafile Z to '+DISKGROUP';
etc.
restore datafile x,y,z,….;
}
Now we can switch database to copy
RMAN> SWITCH DATABASE TO COPY;
If the above query returns with 0 zero rows
RMAN> SWITCH DATABASE TO COPY;
Step 11
On STANDBY database, clear all standby redo log groups:
SQL> select GROUP# from v$logfile where TYPE=’STANDBY’ group by GROUP#;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
….
Step 12
Now you can start the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Hope you like these detailed steps on how to recover a standby database from a missing archive log. Please do provide feedback. There may be a mistake.
Also Reads
Non ASM to ASM
how to find archive log sequence number in oracle
how to check alert log errors in oracle
RMAN backup commands
RMAN List backup commands
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)