The Geek Diary

Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN

by

This post covers the detailed steps for creating a physical standby database on a normal file system using RMAN for a primary database in ASM.

Overview

Here the Standby database can be created using the following 2 methods:

  1. Normal Restore and Recovery using RMAN.
  2. Creating a Duplicate database for Standby using RMAN.

Method 1

  • Perform the backup of the ASM primary database ( Datafiles, Controlfile for standby and Archivelogs )
  • Make the backup pieces available on the Standby server.
  • Create the parameter file and password file on standby.
  • Configure Oracle net services on both the servers.
  • Perform restore and Recovery on standby database.
  • Configure the primary database to transfer the archives to standby.
  • Kick start the Redo apply by putting the database in Recover managed mode.

Method 2

Use RMAN Duplicate feature to create the standby database. Steps to accomplish the same are outlined in the following post.

How to Create a Physical Standby from ASM Primary

Method 1 – Normal Restore and Recovery using RMAN

1. Perform the backup of the ASM primary database ( Datafiles, Controlfile for standby & Archivelogs ) 

$ rman target / 
RMAN> backup database format '/backup/database_%U.bkp';
RMAN> backup archivelog all format '/backup/archivelog_%U.bkp';
RMAN> backup current controlfile for standby format '/backup/stdb_cntl_%U.bkp';
Note : For disk RMAN backup take backup to file system. You can also take backup directly to Tape using Media Manager.

2. Make the backup pieces available on the Standby server:
– FTP or SCP the backup pieces to the standby server. While doing FTP make sure to do it in binary mode only. OR
– NFS mount then mount the NFS on standby server with the same name as you mounted on the primary database. OR
– If the backups are on tape then make sure that you make proper changes on standby server so that you can restore the backups on the standby server.

3. Create the parameter file & password file on standby. Copy the primary database parameter file and make necessary changes:

db_unique_name='stnd_db'
instance_name='stnd_db'
standby_archive_dest='[location for archives coming from primary]'
db_file_name_convert='+DATA/prim_db/datafile/', '/u01/oradata/stnd_db/', '+DATA/prim_db/tempfile/', '/u01/oradata/stnd_db/' 
log_file_name_convert='+DATA/prim_db/onlinelog/', '/u01/oradata/stnd_db/'

Create the password file for standby database with the same password as primary

$ export ORACLE_SID=stand_db 
$ orapwd file=orapwstand_db password=oracle

4. Configure Oracle net services on both the servers

In standby server
  - configure listener.
In Primary server
  - configure tnsnames for standby.
  - check connectivity using tnsping.

5. Perform restore and Recovery on standby database. Startup Nomount the standby instance and restore the standby controlfile from the backup of the ‘standby controlfile’ performed in step 1.

$ export ORACLE_SID=stnd_db
$ rman target /
RMAN> restore standby controlfile from '/backup/stdb_cntl_3oqff2fb_.bkp';

– You must use option ‘standby controlfile‘ like 

RMAN> RESTORE STANDBY CONTROLFILE from ...

else convert parameter DB_FILE_NAME_CONVERT is NOT used.

– After STANDBY CONTROLFILE is restored, you can mount the AUX Instance and verify DB_FILE_NAME_CONVERT parameter is used and show correct datafile path/name. For example:

RMAN> REPORT SCHEMA ;       ( or sql> select name from v$datafile ; )

Mount the database

RMAN> Alter database mount;

If backup pieces are in exactly same directory structure or if the directory structure is NFS mounted on the standby server then directly restore the database else if the backup pieces are in different location you need to catalog them manually. For example:

RMAN> catalog backuppiece '/backup/database_2x21kd12.bkp';

For more information on cataloging options refer the post below:

How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database

Restore the Database
Restore the database using the following syntax(Use set newname)

RMAN> run { set newname for datafile 1 to '[new path]':
            Set newname for datafile 2 to '[newpath]';
            .
            .            
            restore database;
            Switch datafile all; }

Recover the database
Get to know the last sequence available in the archivelog backup using the following command.This will help us in recovering the database till that archivelog.

RMAN > list backup of archivelog all;

Let us assume the last sequence of last archivelog in the backup is 50. Since we are recovering the database here till the archivelog sequence 50 the sequence number in the UNTIL SEQUENCE clause should be 50 (+1)

RMAN> Recover database until sequence 51;

6. Configure the primary database to transfer the archives to standby. Set the following parameter in the primary database:

log_archive_dest_2='SERVICE=stnd_db'

Perform a log switch and make sure that archives are getting transferred to standby destination.

7. Kick start the Redo apply by putting the database in Recover managed mode. In Standby database start the managed recovery process:

SQL> Recover managed standby database disconnect from session;

You May Also Like