“So, I now have a standby database, what can I do with my backups? Do I backup my standby database? Do I still need to backup my primary?” These are questions we have heard many times over the years, and this post will provide you with more detail on the options available to you when you have a standby database or even multiple standby databases implemented in your production environment.
Before you just implement backups it is always a good idea to look at what you would like to achieve. It is recommended you review the company standard policies and requirements around backup and recovery. Taking this into account you can then design and implement a backup strategy for your Oracle environment that will be in line with your other company policies.
This post is a follow-on from the “Three Steps to Getting Started with RMAN” post so if you have not read that one yet, you can find it here.
This post is broken into two parts:
First, an example on backing up a standby database and;
Using the standby backup to perform a primary database restore.
Before getting into the details it is good to provide more information about the environment. We are making use of Oracle Database Standard Edition 11g (11.2.0.3) running on Oracle Linux 5 x86_64 update 8. In the test cases below we will be using an Oracle 11g RAC environment with a single instance standby database configured using Standby™️. But don’t worry, the backup and recovery steps for single instance environments are similar. To establish and manage the standby database environment we have the latest Standby™️ software, which can be found here. I am only looking at using local disk-based backups in the examples below.
Now there are many options available when doing backups. Should I use daily full backups or incremental differential backups or incremental cumulative backups or image copy backups? The answer to this depends on a lot of factors such as your company backup and recovery policy, retention period, size of database, system load and many others. We will not go into this as the method that might suit one site, would not work in another so I will leave this up to you to work it out and decide. We personally do like the use of Incremental cumulative backups. In the examples below we will be using Full Backups and will not focus on Incremental backups or image copies. For more details on this we do recommend you review the Oracle documentation.
Now to get started with an example…
Standby™️ 6.0.34 is installed on all nodes in /usr/local/dbvisit
No RMAN recovery catalog is used.
My Oracle 11g RAC database is called PROD with instances PROD1 (node1) and PROD2 (node2) using shared ASM storage.
The Standby database is a single instance database called PROD and is also making use of ASM for storage.
We have the following directory on all nodes (this will be the backup location):
/backup/rman
Note: This folder can be a shared folder between all nodes. But in my case it is local to each server.
The following parameters are set in my Standby™️ DDC configuration files:
ARCHDEST= /u01/app/oracle/archive/PROD
COMPRESS = No
UNCOMPRESS = No
LEAVE_COMPRESS_SOURCE = No
LEAVE_COMPRESS_DEST = No
This should be done in all the RAC node DDC files, and in my case, these files were updated:
/usr/local/dbvisit/standby/dbv_prod1.env (node1)
/usr/local/dbvisit/standby/dbv_prod2.env (node2)
In this version of Standby™️ the archive logs on the standby server will be located in the ARCHDEST folder. The ARCHDEST folder is set during standby configuration. The other 4 variables are set to ensure that the archive logs are not compressed, but will stay in the ARCHDEST folder in an uncompressed state. This is important if you want to be able to backup your archive logs on the standby database. My backups will be stored in the /backup/rman folder. It is recommended to perform the backup during a period when the Standby™️ schedule is not running on the standby database. You do not need to stop the primary node schedules.
Step 1: Set The Environment
First I configure my RMAN environment on the standby database. For more details on this see here.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD.f'; # default
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> configure device type disk backup type to compressed backupset;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored
RMAN> configure channel device type disk format '/backup/rman/PROD_%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/PROD_%U';
new RMAN configuration parameters are successfully stored
RMAN> configure controlfile autobackup format for device type disk to '/backup/rman/PROD_cfc_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/PROD_cfc_%F';
new RMAN configuration parameters are successfully stored
Step 2: Catalog The ARCHDEST Contents
As part of my standby database backup, we would also like to include my archive logs. But as the logs are currently in the ARCHDEST folder and not registered with the controlfile, we first need to catalog the archive logs before backing them up. This can easily be done with the “catalog” RMAN command:
RMAN> catalog start with '/u01/app/oracle/archive/PROD/' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/archive/PROD/
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1373.466.790786927
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1522.311.791223039
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1558.1028.791244677
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1561.1076.791246481
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1158.2203.790683059
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1680.774.791311711
..
..
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1328.943.790720273
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1430.1827.791162491
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1575.2057.791250061
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1260.981.790745457
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1373.466.790786927
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1522.311.791223039
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1558.1028.791244677
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1561.1076.791246481
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1158.2203.790683059
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1680.774.791311711
..
..
File Name: /u01/app/oracle/archive/PROD/thread_1_seq_1328.943.790720273
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1430.1827.791162491
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1575.2057.791250061
File Name: /u01/app/oracle/archive/PROD/thread_2_seq_1260.981.790745457
RMAN>
The standby database controlfile now is aware of these archive logs and we can continue with the backup.
Step 3: Backup The Database and Archivelogs
To backup the database you can use incremental or full backups. In this scenario we will perform a full backup of the whole standby database including all the archive logs. To do this we execute the following:
RMAN> run {
2> backup filesperset 5 database;
3> backup filesperset 200 archivelog all;
4> }
Starting backup at 15/08/2012:12:38:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/prod/datafile/soe.280.789026541
input datafile file number=00003 name=+DATA/prod/datafile/undotbs1.276.789009379
input datafile file number=00004 name=+DATA/prod/datafile/users.277.789009389
channel ORA_DISK_1: starting piece 1 at 15/08/2012:12:38:11
channel ORA_DISK_1: finished piece 1 at 15/08/2012:12:40:36
piece handle=/backup/rman/PROD_2bnin31j_1_1 tag=TAG20120815T123811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/prod/datafile/system.274.789009289
input datafile file number=00006 name=+DATA/prod/datafile/data.279.789026541
input datafile file number=00002 name=+DATA/prod/datafile/sysaux.275.789009341
input datafile file number=00005 name=+DATA/prod/datafile/undotbs2.278.789009399
input datafile file number=00008 name=+DATA/prod/datafile/ind.282.789030219
channel ORA_DISK_1: starting piece 1 at 15/08/2012:12:40:36
channel ORA_DISK_1: finished piece 1 at 15/08/2012:12:41:51
piece handle=/backup/rman/PROD_2cnin364_1_1 tag=TAG20120815T123811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 15/08/2012:12:41:51
Starting backup at 15/08/2012:12:41:52
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=901 RECID=192 STAMP=791376608
input archived log thread=1 sequence=1014 RECID=534 STAMP=791376610
input archived log thread=2 sequence=902 RECID=14 STAMP=791376607
input archived log thread=2 sequence=903 RECID=559 STAMP=791376610
input archived log thread=1 sequence=1015 RECID=1113 STAMP=791376613
..
..
..
input archived log thread=1 sequence=1683 RECID=1404 STAMP=791376615
input archived log thread=1 sequence=1684 RECID=1024 STAMP=791376612
input archived log thread=2 sequence=1693 RECID=1091 STAMP=791376613
input archived log thread=1 sequence=1685 RECID=751 STAMP=791376611
input archived log thread=2 sequence=1694 RECID=662 STAMP=791376611
channel ORA_DISK_1: starting piece 1 at 15/08/2012:12:43:30
channel ORA_DISK_1: finished piece 1 at 15/08/2012:12:43:33
piece handle=/backup/rman/PROD_2mnin3bi_1_1 tag=TAG20120815T124153 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15/08/2012:12:43:33
Starting Control File and SPFILE Autobackup at 15/08/2012:12:43:33
piece handle=/backup/rman/PROD_cfc_c-198828821-20120815-01 comment=NONE
Finished Control File and SPFILE Autobackup at 15/08/2012:12:43:34
RMAN>
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
84 Full 23.20M DISK 00:00:01 15/08/2012:12:43:34
BP Key: 84 Status: AVAILABLE Compressed: NO Tag: TAG20120815T124333
Piece Name: /backup/rman/PROD_cfc_c-198828821-20120815-01
Standby Control File Included: Ckp SCN: 5935111 Ckp time: 14/08/2012:18:41:11
The end result from the above is that you now have a backup of the database. You can also add another line between the database and archive log backups, to ensure all archive logs that might have arrived on the standby database while the database is being backed up are included in the archivelog backup. The backup script will then look like this:
run {
backup filesperset 5 database;
catalog start with '/u01/app/oracle/archive/PROD/' noprompt;
backup filesperset 200 archivelog all;
}
This backup can now be used to perform restore and recovery operations on the primary if required. My personal preference is always to make at least one more archive log backup during a day. Example, if backups are performed nightly, lets say 10pm, we might run an extra archivelog backup on the standby at 10am, then we would also use other options such as only backing up archive logs that have not been backed up twice, but let's leave discussing these options for another post.
Step 1: Copy backups to Primary Node
In this example we will show you how to perform a full restore of the RAC primary database using the backup that was created on the standby server from the previous step. The first step is to get the backups copied from the standby to the primary server. In this example we have the same backup location on both systems, which is just local attached storage. But this can possibly be shared storage such as an NFS Share. Use “rsync” and “ssh” to copy the files. From the primary node1 (dbvrlin303) execute the following from the /backup/rman directory:
oracle@dbvrlin303[/backup/rman]: pwd
/backup/rman
oracle@dbvrlin303[/backup/rman]: rsync -av -e ssh oracle@dbvrlin305:/backup/rman/ .
receiving incremental file list
./
PROD_2bnin31j_1_1
PROD_2cnin364_1_1
PROD_2dnin38m_1_1
PROD_2enin38u_1_1
PROD_2fnin396_1_1
PROD_2gnin39d_1_1
PROD_2hnin39t_1_1
PROD_2inin3a4_1_1
PROD_2jnin3aj_1_1
PROD_2knin3ar_1_1
PROD_2lnin3b2_1_1
PROD_2mnin3bi_1_1
PROD_cfc_c-198828821-20120815-01
sent 261 bytes received 1505094435 bytes 24081515.14 bytes/sec
total size is 1504909824 speedup is 1.00
oracle@dbvrlin303[/backup/rman]: ls -l
total 1471164
-rw-r----- 1 oracle asmadmin 873512960 Aug 15 12:40 PROD_2bnin31j_1_1
-rw-r----- 1 oracle asmadmin 382369792 Aug 15 12:41 PROD_2cnin364_1_1
-rw-r----- 1 oracle asmadmin 16034304 Aug 15 12:42 PROD_2dnin38m_1_1
-rw-r----- 1 oracle asmadmin 12039680 Aug 15 12:42 PROD_2enin38u_1_1
-rw-r----- 1 oracle asmadmin 19487744 Aug 15 12:42 PROD_2fnin396_1_1
-rw-r----- 1 oracle asmadmin 25485312 Aug 15 12:42 PROD_2gnin39d_1_1
-rw-r----- 1 oracle asmadmin 29348864 Aug 15 12:42 PROD_2hnin39t_1_1
-rw-r----- 1 oracle asmadmin 37273088 Aug 15 12:42 PROD_2inin3a4_1_1
-rw-r----- 1 oracle asmadmin 28918272 Aug 15 12:43 PROD_2jnin3aj_1_1
-rw-r----- 1 oracle asmadmin 25312768 Aug 15 12:43 PROD_2knin3ar_1_1
-rw-r----- 1 oracle asmadmin 25631744 Aug 15 12:43 PROD_2lnin3b2_1_1
-rw-r----- 1 oracle asmadmin 5148672 Aug 15 12:43 PROD_2mnin3bi_1_1
-rw-r----- 1 oracle asmadmin 24346624 Aug 15 12:43 PROD_cfc_c-198828821-20120815-01
As you can see above all the backup files are now on my Primary Node 1 called dbvrlin303.
Step 2: Performing Restore
The next step is the restore and recovery of the primary database. As this is a RAC environment with 2 nodes, media recovery is only done from one instance. In this case we perform the required tasks from the 1st instance PROD1 on node dbvrlin303. To simulate the failure we did a shutdown of the primary database, and removed the datafiles, controlfiles and online logs from ASM. It is important to note that in this case both the primary and standby servers use the exact same ASM disk configuration. If you were using a different configuration you will need to perform renaming of your datafiles when performing the restore. The database instance on node 2 (dbvrlin304) is not started and remains down at this stage. The example below shows the restore steps.
oracle@dbvrlin303[/backup/rman]: rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Aug 15 13:01:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 448790896 bytes
Database Buffers 272629760 bytes
Redo Buffers 7061504 bytes
RMAN> restore controlfile from '/backup/rman/PROD_cfc_c-198828821-20120815-01';
Starting restore at 15/08/2012:13:02:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 instance=PROD1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/prod/controlfile/current.266.791326951
output file name=+FRA/prod/controlfile/current.1575.791326951
Finished restore at 15/08/2012:13:02:34
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 15/08/2012:13:04:51
Starting implicit crosscheck backup at 15/08/2012:13:04:51
allocated channel: ORA_DISK_1
Crosschecked 12 objects
Finished implicit crosscheck backup at 15/08/2012:13:04:54
Starting implicit crosscheck copy at 15/08/2012:13:04:54
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 15/08/2012:13:04:55
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DATA/prod/datafile/undotbs1.276.789009379
channel ORA_DISK_1: restoring datafile 00004 to +DATA/prod/datafile/users.277.789009389
channel ORA_DISK_1: restoring datafile 00007 to +DATA/prod/datafile/soe.280.789026541
channel ORA_DISK_1: reading from backup piece /backup/rman/PROD_2bnin31j_1_1
channel ORA_DISK_1: piece handle=/backup/rman/PROD_2bnin31j_1_1 tag=TAG20120815T123811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/prod/datafile/system.274.789009289
channel ORA_DISK_1: restoring datafile 00002 to +DATA/prod/datafile/sysaux.275.789009341
channel ORA_DISK_1: restoring datafile 00005 to +DATA/prod/datafile/undotbs2.278.789009399
channel ORA_DISK_1: restoring datafile 00006 to +DATA/prod/datafile/data.279.789026541
channel ORA_DISK_1: restoring datafile 00008 to +DATA/prod/datafile/ind.282.789030219
channel ORA_DISK_1: reading from backup piece /backup/rman/PROD_2cnin364_1_1
channel ORA_DISK_1: piece handle=/backup/rman/PROD_2cnin364_1_1 tag=TAG20120815T123811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 15/08/2012:13:09:10
RMAN> alter database open resetlogs;
database opened
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
Now let's start the database using the Oracle srvctl utility.
oracle@dbvrlin303[/backup/rman]: srvctl start database -d PROD
oracle@dbvrlin303[/backup/rman]: srvctl status database -d PROD
Instance PROD1 is running on node dbvrlin303
Instance PROD2 is running on node dbvrlin304
oracle@dbvrlin303[/backup/rman]: srvctl status database -d PROD -v
Instance PROD1 is running on node dbvrlin303 with online services PRODSRVC. Instance status: Open.
Instance PROD2 is running on node dbvrlin304 with online services PRODSRVC. Instance status: Open.
Now the primary database is restored using the standby database backup. In this example, a full restore was done, but you could have used the backup for other restore and recovery purposes, such as just restoring a datafile.
There are a few other options available, but the example above will give you an idea as to what can be done, and get you started on how you can off-load backups onto the standby database. Yes, if the primary dies, you will most likely switch to the standby database and activate it as the primary instead of restoring the primary database. That is why you have a standby, to reduce downtime when disaster strikes. But this example just shows you how you can use the standby to perform backups that can then be used on the primary database for restore and recovery purposes. An extra option is that you run your backups on your standby server and then “rsync” them back to your primary. That way you have backups ready on both systems when required. If you have not realized it yet, you can also use the backup to restore your standby database if required.
One of the most important steps following a full recovery is to create a new backup! This is good practice. And in this case my standby database is now out of sync and will require a rebuild. This can easily be done by running the Dbvisit Create Standby Database (CSD) procedure from either the GUI under the setup menu option, or from the command line using option 7 when running dbvisit_setup (/usr/local/dbvisit/standby/setup_dbvisit)
Some extra useful notes:
From Oracle Database version 11g onwards, controlfile backups are interchangeable. What this means is that if you create a controlfile backup on the standby, it can be restored on the primary during which time the controlfile type will automatically changed from “STANDBY” to “CURRENT”. This is only applicable to physical standby databases and not logical standby databases.
Take a free online Standby™️ Test Drive, or download a free Standby™️ 10 Day Trial today.