Data Guard RMAN Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard - Recovery Manager and Data Guard

Recovery Manager (RMAN) is a backup and recovery tool that is packaged and shipped with Oracle Database software. RMAN provides a flexible and scalable backup solution that can be integrated with media management sub-systems to facilitate end-to-end backup and recovery policies. The key task in creating a Data Guard configuration is to obtain a backup of the primary database along with all the necessary archived redo logs required to recover the database to the last SCN.

RMAN can be used to create this backup and duplicate the primary database as a standby database. In addition, RMAN can recover the newly created Oracle instance to the last SCN, after which, the managed recovery process can keep the standby database in sync with the primary database. In a nutshell, the Oracle instance creation process uses all three functionalities of the Recovery Manager: backup; restore; and recovery.

RMAN can be used to create various types of Data Guard configurations such as a physical Oracle instance on the same host, a physical Oracle instance on a separate host, a cascaded standby database, etc. However, RMAN cannot be used to create logical standby databases.

Recovery Manager (RMAN) and Data Guard

RMAN provides a functionality of incremental backup.  This can be very useful when a DBA needs to create a standby database from a very large, in the range of terabytes, primary database. Creating a conventional backup using operating system utilities may take several hours for this type of database.  In those circumstances the DBA can use the advantages offered by Recovery Manager. RMAN always compresses data files in a backup set such that the files in the backup set are a true representative of the amount of data in the database and not the size of the data files. The compression feature can be very useful when the need exists to transfer the backup set to a remote destination over Oracle Net in order to create a physical standby database.

A backup set, image copies, or a combination of both can be used to create a standby database. In this chapter, the information will focus on using RMAN to create standby databases. First, the procedure for creating a physical Oracle instance using a backup set will be presented.  This will be followed by the creation procedure using image copies. The text in this chapter will present the step-by-step process of creating these standby databases.  An example in each section will be used to illustrate the complexities of configuring Data Guard using RMAN.

Additionally, this chapter will present the details on how to create a backup of the Oracle instance using Recovery Manager. Familiarity with Recovery Manager prior to reading this chapter will be extremely useful. Oracle manual, Oracle9i Recovery manager User's Guide Release 2 (9.2), provides more information on the working of Recovery Manager. The first step will be to cover some of the RMAN terminology that will be used throughout this chapter.

• TARGET DATABASE - A Target Database is the primary database that will be backed up for Oracle instance creation. In RMAN's terminology, the term target database identifies the database that is undergoing a backup, restore or recovery operation by Recovery Manager.
   

• AUXILIARY DATABASE - An Auxiliary Database is a Oracle instance that will be created as a result of the duplication of the target database. In RMAN's terminology, Auxiliary instance identifies an instance which RMAN connects in order to execute the duplicate command.

RMAN channel Tips

Oracle RMAN commands Tips

What needs to be Backed Up for a Oracle instance Creation?

Similarly, for a Oracle instance creation on a remote host, if the file system containing archived redo log files on the primary site can be mounted to the remote host machine, a backup of the archived redo log files is not required.

In summary, if the local archival destination of the primary database can be made available to the standby host machine, a backup of archived redo logs is not required.

Creation Using a Backup Set

This section will provide a step-by-step guide to creating a physical Oracle instance using a backup set of the target database. The Oracle instance will share the same host with the primary database. As a consequence, the directory structure will not be exactly the same as the primary database. The change in directory structure can be accommodated using the db_file_name_convert and log_file_name_convert parameters or the SET NEWNAME command of Recovery Manager.

The example used in this section assumes that the name of the primary database is "appsdb" and the standby database is called "stdbydb".

Integrity Check of the Primary Database

Before creating an actual backup set containing data files of the primary database, the data files can be checked for any physical and logical corruption using the BACKUP VALIDATE command. This step is not essential; however, it will ensure that the primary database is free from errors and all the files required are in the correct location.

Integrity Check of the Primary Database

The BACKUP VALIDATE DATABASE command can be used to check the integrity of all data files. The ARCHIVELOG ALL keyword can be included to check the validity of archived logs as well. The following example shows the usage of these commands. Start the RMAN command line interface and connect to the primary database as the target. On the RMAN prompt, execute the following command to validate the data files of the primary database:

RMAN> BACKUP VALIDATE DATABASE;

Starting backup at 07-MAR-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00002 name=/oracle/appsdb/data/undotbs01.dbf
input datafile fno=00001 name=/oracle/appsdb/data/system01.dbf
input datafile fno=00007 name=/oracle/appsdb/data/logstdby02.dbf
input datafile fno=00003 name=/oracle/appsdb/data/userdata01.dbf
input datafile fno=00004 name=/oracle/appsdb/data/indxdata01.dbf
input datafile fno=00005 name=/oracle/appsdb/data/logstdby.dbf
input datafile fno=00006 name=/oracle/appsdb/data/userdata02.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
Finished backup at 07-MAR-04 

Integrity Check of the Primary Database

From the output, it is clear that the primary database is free from any physical or logical corruption. To check the integrity of archived redo logs, use the following statement:

RMAN> BACKUP VALIDATE ARCHIVELOG ALL;

Starting backup at 07-MAR-04
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/07/2004 12:13:56
RMAN-06059: expected archived log not found, lost of archived log compromises
recoverability
ORA-19625: error identifying file /oracle/appsdb/arch/appsdb_1_716.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3 

From the output, all the archived redo log files since the last backup are not available on the system. Therefore, the validate command terminates with the error RMAN-03002/RMAN-06059. In this case, to create a Oracle instance a new backup should be created first as all of the required archived redo logs needed to recover the database are available.

RMAN backup set Tips

Creating Physical Standby Databases using RMAN

In this section, the details of creating physical standby databases using Recovery Manager will be presented. It will cover the backup requirements and the step-by-step guide to creating physical standby databases. Oracle instance creation cannot be completely automated using RMAN as it can by using Data Guard Broker.

Before the creation of the Oracle instance using RMAN can be started, the server parameter file and Oracle net configuration files will have to be modified manually to suit the Data Guard configuration.

Server Parameter and Oracle Net Configuration

The Oracle Net configuration file must be modified to create a service for the new standby database.

Duplicate and Recover as the Standby Database

Once the initialization parameter file and Oracle Net files are modified, the process of duplicating the primary database as the Oracle instance can be started. The first step in this process is to start the Oracle instance without mounting it. After starting the standby database, connect the Oracle instance as auxiliary and the primary database as target using the following statement:

            To create a Oracle instance on remote host, copy all the backup pieces of the set to the remote host in the same directory structure. If the same structure cannot be created, use a symbolic link to simulate the structure.

DBA@jrbk01; export ORACLE_SID=stdbydb
DBA@jrbk01; rman auxiliary / target sys/change_on_install@appsdb

On the RMAN prompt, start duplicating the database as shown below:

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;

The output of this statement shows that RMAN first restores the file from the most recent backup and then recovers the database.

# Extract from the output of duplicate command:
Starting Duplicate Db at 07-MAR-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=11 devtype=DISK

Duplicate and Recover as the Standby Database

printing stored script: Memory Script
{
   restore clone standby controlfile to clone_cf;
   replicate clone controlfile from clone_cf;
   sql clone 'alter database mount standby database';
}
executing script: Memory Script
 
Starting restore at 07-MAR-04
 
using channel ORA_AUX_DISK_1
. . . . .
. . . . .
Finished restore at 07-MAR-04
 
replicating controlfile
input filename=/oracle/stdbydb/control/standbycontrol.ctl
 
sql statement: alter database mount standby database
. . . . .
. . . . .
datafile 7 switched to datafile copy
input datafilecopy recid=24 stamp=520184020
filename=/oracle/stdbydb/data/logstdby02.dbf

printing stored script: Memory Script

{
   set until scn  3126804;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing script: Memory Script
 
executing command: SET until clause
 
Starting recover at 07-MAR-04
using channel ORA_AUX_DISK_1
 
starting media recovery
 
archive log thread 1 sequence 2032 is already on disk as file
/oracle/appsdb/arch/appsdb_1_2032.dbf
archive log filename=/oracle/appsdb/arch/appsdb_1_2032.dbf thread=1
sequence=2032
media recovery complete
Finished recover at 07-MAR-04
Finished Duplicate Db at 07-MAR-04

Duplicate and Recover as the Standby Database

From the output, it is apparent that RMAN uses the archived redo log file appsdb_1_2032.dbf required for recovery from the local archival destination of the primary database. At this point the Oracle instance is created and is ready to be put into managed recovery mode.

The Oracle instance must always be recovered when it is created using RMAN. This is due to the fact that when RMAN is used to create the backup, the primary or target database has been open for read-write, so the backup is not consistent. This is similar to creating a Oracle instance using a hot backup of the primary database.

Starting the Managed Recovery Process

The next step in creating the Oracle instance is to start the managed recovery process. Connect to the standby database using the SYSDBA account and execute the following statement:

Sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

This statement can also be executed within the RMAN environment. Connect to the Oracle instance as the target database using RMAN and execute the following statement:

RMAN> sql "ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION";

Testing the Standby Database

Once the Oracle instance is created and managed recovery is started, check the alert log file of both the primary and the Oracle instance for any errors. Additionally, the Oracle instance can be put in read-only mode and few database queries executed to ensure that the Oracle instance has been created successfully. Chapter 3 provides more details on verifying a newly created physical standby database.

Creation Using Image Copies

As mentioned earlier in this chapter, an image copy of data files of the target database is similar to the backup created using operating system utilities. In order to use image copies to create the standby database, the use of RMAN may not be needed. However, in one scenario in which the goal is to create and recover a standby database, RMAN should be used to simplify the creation process.

This section will describe the use of an image copy to create and recover a Oracle instance using RMAN. Some steps from the previous section remain valid. These steps are Server Parameter and Oracle Net Configuration, Starting Managed Recovery Process and Testing the Standby Database.

One of the requirements for using an image copy is that the primary database should be closed cleanly, then it should be mounted but not open. This limitation may make the use of image copies for a standby creation a less attractive option compared to the use of a backup set.