CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Scott Foulk, CommitDBA Senior Database Administrator

Syncing your Oracle Standby Database with the Primary Database

Scott Foulk, CommitDBA Senior Database Administrator

June 20, 2012


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 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. Because of 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 started 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 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. Below 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# ---------------------------- 1
1
1
0
0
0
0
0
0
0
ARCHIVED_SEQ# ----------------------- 14596
14596
14595
0
0
0
0
0
0
0
APPLIED_THREAD# -------------------------- 0
0
1
0
0
0
0
0
0
0
APPLIED_SEQ#
---------------------
0
0
14595
0
0
0
0
0
0
0












10 rows selected.

As you 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. You will now have a syncing standby database and good backups in case of an outage or disaster.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today