Situation
Your company’s primary database writes archive logs to a physical standby database using Oracle Dataguard and they are then applied. A nightly RMAN backup is performed and then all applied archive logs are purged from the archive log directory.
Problem
The RMAN backup is failing because flash_back_recovery area is full and the RMAN job is not purging the archive logs because they still have not been applied to the standby database. Upon investigation you find that someone has brought the standby database online and opened it. Due to this the standby will not apply the archive logs that have been shipped to it. You have tried shutting down the standby database and bringing it up in a mounted state and starting managed recovery. However, some of the archive logs to apply to standby are missing.
Solution
This scenario could have disastrous consequences if it is not taken care of immediately. There is a simple solution to resolve this problem however; it can be a little scary to a less experienced Oracle DBA. What needs to happen to remedy the problem is to re-sync the standby database with the primary database and get the archive logs to begin applying to the standby. The simple solution to remedy this issue is described below.
Tips
Connect to the primary database and perform a full RMAN backup including the archive logs and the control files. Since the flash_back_recovery area is full you will need to pass in a different directory to write to. Here is an example of the RMAN backup script.
Set the Oracle environment to the production database and start RMAN:
$ rman
connect target /
run{
change archivelog all crosscheck;
allocate channel ch1 type disk;
backup incremental level 0 database format
'C:\oracle\orabase\backupfile\bk_inc0_%s_%p' setsize=8250000 include current
controlfile for standby ;
sql "alter system archive log current";
backup archivelog all format 'Your Location\al_%s_%p';
release channel ch1;
}
Once this is complete, copy the files from the location of your backup to the same location on the standby server. Once the files are copied you will need to connect to the standby database and start RMAN to re-create standby. Below is an example of the script that you will need to use modifying it to fit your environment.
$ rman
connect primary sys/password@primary
connect auxiliary /
run {
allocate auxiliary channel ch1 type disk;
duplicate target database for standby dorecover nofilenamecheck;
release channel ch1;
}
The restore of the standby database is now complete. RMAN has started the standby database in mount standby
mode which is correct for us to continue. For reference the commands to start a standby database are:
SQL> startup nomount
SQL> alter database mount standby database;
Once the database is mounted connect to the standby database and put it in managed recovery mode so the archive logs from primary will apply automatically after they have been shipped over.
The command to do this is:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Once you have issued this command give the process a few minutes and then check to see if the archive logs are being shipped and applied to standby. An easy way to tell is to query the V$ARCHIVE_DEST_STATUS.
The command to do this is:
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,
APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
Which will give you a result similar to the below example:
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 14596 0 0
1 14596 0 0
1 14595 1 14595
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
10 rows selected.
As we can see from the query in this example the current applied archive log on primary is 14596 and the applied archive log on standby is 14595.
Once the logs are correctly applying from primary to standby go into RMAN on the primary database server and clean up the applied archive logs and run another full backup. Once that is complete Oracle will free up the flash_back_recovery area and all RMAN errors will have been resolved and you now have a syncing standby database and good backups in case of an outage or disaster.
Your company’s primary database writes archive logs to a physical standby database using Oracle Dataguard and they are then applied. A nightly RMAN backup is performed and then all applied archive logs are purged from the archive log directory.
Problem
The RMAN backup is failing because flash_back_recovery area is full and the RMAN job is not purging the archive logs because they still have not been applied to the standby database. Upon investigation you find that someone has brought the standby database online and opened it. Due to this the standby will not apply the archive logs that have been shipped to it. You have tried shutting down the standby database and bringing it up in a mounted state and starting managed recovery. However, some of the archive logs to apply to standby are missing.
Solution
This scenario could have disastrous consequences if it is not taken care of immediately. There is a simple solution to resolve this problem however; it can be a little scary to a less experienced Oracle DBA. What needs to happen to remedy the problem is to re-sync the standby database with the primary database and get the archive logs to begin applying to the standby. The simple solution to remedy this issue is described below.
Tips
Connect to the primary database and perform a full RMAN backup including the archive logs and the control files. Since the flash_back_recovery area is full you will need to pass in a different directory to write to. Here is an example of the RMAN backup script.
Set the Oracle environment to the production database and start RMAN:
$ rman
connect target /
run{
change archivelog all crosscheck;
allocate channel ch1 type disk;
backup incremental level 0 database format
'C:\oracle\orabase\backupfile\bk_inc0_%s_%p' setsize=8250000 include current
controlfile for standby ;
sql "alter system archive log current";
backup archivelog all format 'Your Location\al_%s_%p';
release channel ch1;
}
Once this is complete, copy the files from the location of your backup to the same location on the standby server. Once the files are copied you will need to connect to the standby database and start RMAN to re-create standby. Below is an example of the script that you will need to use modifying it to fit your environment.
$ rman
connect primary sys/password@primary
connect auxiliary /
run {
allocate auxiliary channel ch1 type disk;
duplicate target database for standby dorecover nofilenamecheck;
release channel ch1;
}
The restore of the standby database is now complete. RMAN has started the standby database in mount standby
mode which is correct for us to continue. For reference the commands to start a standby database are:
SQL> startup nomount
SQL> alter database mount standby database;
Once the database is mounted connect to the standby database and put it in managed recovery mode so the archive logs from primary will apply automatically after they have been shipped over.
The command to do this is:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Once you have issued this command give the process a few minutes and then check to see if the archive logs are being shipped and applied to standby. An easy way to tell is to query the V$ARCHIVE_DEST_STATUS.
The command to do this is:
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,
APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
Which will give you a result similar to the below example:
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 14596 0 0
1 14596 0 0
1 14595 1 14595
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
10 rows selected.
As we can see from the query in this example the current applied archive log on primary is 14596 and the applied archive log on standby is 14595.
Once the logs are correctly applying from primary to standby go into RMAN on the primary database server and clean up the applied archive logs and run another full backup. Once that is complete Oracle will free up the flash_back_recovery area and all RMAN errors will have been resolved and you now have a syncing standby database and good backups in case of an outage or disaster.
No comments:
Post a Comment