System Environment

Suppose you have an oracle data guard environment with one primary database and one physical standby database running.

You perform online backups with RMAN from the PRIMARY database. Your users complain about slowness during the night. You notice the slowness correlates with the time the online backup runs. Thanks to the physical standby database you can offload your backups to the standby database. This short guide shows how to do that.

First of all the ability to offload backups from primary to standby is a major plus in physical standby configurations. Note that this is not possible on logical standby configurations.

Furthermore the offload to the standby database requires to have a more or less separated storage (either separated storage boxes, separated disks, separated ports,….).General speaking: the more you separate primary from standby storage the better it is from performance point of view.

Implementation on primary site

Database requirements

In order to use the automatic deletion of archive logs on the primary site after they have been applied on the standby database you need to enable the FRA (flashback recovery area) on the primary database set setting the following two parameters:

db_recovery_file_dest='<path>’
db_recovery_file_dest_size=xyG

The value for db_recovery_file_dest must be a valid path and db_recovery_file_dest_size specified the total size of the FRA in GB (specified by the suffix “G”), MB (suffix “M”) and so on.

Note that the FRA will fill up to this value before starting to delete archive logs applied on standby. So if you configure the FRA to a size of 200 GB you will end up with 200 GB archive logs stored in the FRA.

You also need to unset the parameter archive_log_dest_n if it points to a local directory. Note that dataguard uses archive_log_dest_n parameter for specifying the remote archive log destination.

Once you set everything you can check it with “archive log list” from SQLPlus. The output should look like this:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     62686
Next log sequence to archive   62695
Current log sequence           62695

If you have configured the environment correctly the archive destination should show up as “USE_DB_RECOVERY_FILE_DEST”.

Backup on primary site

On primary site all you need to backup is the current control file. For use with EMC networker i am using the following script. If you backup with another backup software you just need to alter the SBT_TAPE configuration accordingly to the documentation or backup the control file to disk rather than to tape.

connect target sys/password@primary;
connect catalog rman_catalog/catalog_password@catalogd;
run {
 backup
 (current controlfile format 'ctrl_PRIMARY_s%s_p%p');
 sql 'alter database backup controlfile to trace';
 }
run {
copy current controlfile to '/u01/sicherung/controlfiles/ctl_PRIMARY.ctl';
}

The script shown above is just an example. You need to check and alter it to suit your needs. As you may assume the database in this example is named “PRIMARY”; the standby is named “STANDBY”.

You can see i am quite paranoid about control file backups: I backup them to tape, to trace in a readable format and directly to disk as a copy.

In restore scenarios this enables:

restore of the control file with rman
re-creatation of the control file from the dumped control file
copy the control file from /u01/sicherung/controlfiles/ctl_PRIMARY.ctl to the destinations named in the database parameter “control_files”
(Note: The file created with “copy controlfile” is not wrapped in an rman stream… therefore you can just “copy” it with cp for instance)

Implementation on standby site

Required INIT.ORA parameters

If you use dataguard in a Max Performance or Max Availability mode you need to set

_log_deletion_policy='ALL'

in your standby database init.ora. This is a know problem documented in Metalink Note 331924.1.

Required RMAN configuration

In order to enable automatic deletion on primary site you need to configure rman on standby site as follows:

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

This tells oracle to make archive logs on primary size egible for deleteion after they have been applied on the standby site.

Backup scripts

Full backup

The following script shows how to perform an full backup with rman on the STANDBY database. Primary and standby databases share the same recovery catalog (because they are basically the same databases)!

My recommendation is to daily backup your database fully. With large databases a full backup might take longer than 24 hours. In this cases you can do daily incremental and weekly full backups.

connect target sys/password@standby;
connect catalog rman_catalog/catalog_password@catalogd;
run {
 CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4;
 backup check logical INCREMENTAL LEVEL 0 filesperset 8
 format 'FULL0_%d_%u'
 (database include current controlfile);
 backup not backed up 1 times filesperset 10
 (archivelog all delete input format  'al_STANDBY_%s_%p');
 backup
 (current controlfile format 'ctrl_STANDBY_s%s_p%p'); }
run {
copy current controlfile to '/u01/sicherung/controlfiles/ctl_STANDBY.ctl';
}

Archive log only backup

The following script shows how to backup the archive logs on STANDBY site. The script can be started any time.

I recommend to backup the archive logs every hour. Thanks to “backup not backed up 1 times” we only backup the archive logs once even if they are still on disk when we backup the archive logs once again.

connect target sys/password@standby;
connect catalog rman_catalog/catalog_password@catalogd;
run {
 CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 4;
 backup not backed up 1 times as compressed backupset filesperset 10
 (archivelog all delete input format  'al_STANDBY_%s_%p');
 backup
 (current controlfile format 'ctrl_STANDBY_s%s_p%p');
 }
run {
copy current controlfile to '/u01/sicherung/controlfiles/ctl_STANDBY.ctl';
}

You also do not need to worry about RMAN deleting archive logs which are not yet applied on standby (in MAXIMUM PERFORMANCE mode). If rman tries to delete an archive log which is not yet applied on standby you see the following message:

archive log filename=/u02/oradata/STANDBY/arch/1_62487_646478938.dbf
  thread=1 sequence=62487
RMAN-08120: WARNING: archive log not deleted, not yet applied by standby

Test it

After you configured everything you need to TEST you configuration. This involves:

What happens to the primary database if the standby database is down and does not apply archive logs?
Does the archive logs expire once they are applied on the standby?
and so ….

Query the FRA

The following query queries the current status of the FRA and shows how much space is occupied in the FRA:

select * from v$flash_recovery_area_usage;

In the screenshot above you can see the FRA is filled to 99.83 % with 58.29% reclaimable (this means: 58.29% of space occupied by archive logs in the FRA are eligible for deletetion because they are already applied on the standby database). This query was performed on a data guard enviroment running in MAXIMUM AVAILABLILITY mode.

Oracle Dataguard – automatic archive log file management on primary site