Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 1 May 2013

Creating Physical Standby DB through RMAN Restore & Recovery

Here in below example we will see step by step procedure how to create standby database without using Recovery Manager DUPLICATE TARGET DATABASE command.
  1. Backup the database with all Archivelogs and controlfile for standby
  2. Move all your backup and standby controlfile to the standby server.
  3. Make proper changes in Pfile of both primary and standby server.
  4. Perform Restore & Recovery on Standby site
  5. Put the Standby DB in Recovery Managed Mode.
Take a full valid backup of Primary database
C:\> rman target /
connected to target database: SADHAN (DBID=63198018)
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0 format 'H:\ORABACK\i001_%T_%u_s%s_p%p' database;
backup format 'E:\ORABACK\i001_arch_%T_%u_s%s_p%p' archivelog all;
backup current controlfile for standby format'E:\ORABACK\i001_CF_%T_%u_s%s_p%p' ; 
Create Standby Control file on Primary database
connected to target database: SADHAN (DBID=63198018)
RMAN> backup current controlfile for standby format=’H:\oraback\sadstby_CFile.%U’;
Starting backup at 25-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 25-FEB-13
channel ORA_DISK_1: finished piece 1 at 25-FEB-13
piece handle=H:\oraback\sadstbycontrol.ctl tag=TAG20130225T020241 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-FEB-13
Move the Backup to the Standby Server
You can move the backup from primary site to standby site by using any of the below method:
         You can move the backup exactly the same location on standby as they were created on primary database.
         You can use CATALOG BACKUPIECE command (on 10g onwards) if you are moving the backup to a different location.
         If the backup are on NFS then mount the NFS on standby server with the same name as you mounted on primary database.
         Use FTP or SCP to move the backup.
         If you taken the backup on tape then you must do proper changes on standby server to restore the backup using tape.
Make proper changes in Pfile for both primary and standby server:
Copy the Primary Database PFILE to Standby site and make necessary change. Consider you have already primary database pfile required changing.
Note: Do not forget to create service for standby database and make sure to tns network connectivity for primary as well as standby database.
Perform the Restore & Recovery on Standby site:
C:\> rman target /
connected to target database (not started)
RMAN> startup nomount;
RMAN> SET DBID = 63198018
executing command: SET DBID
RMAN> restore controlfile from ‘H:\oraback\sadstbycontrol.ctl’  --for 9i
RMAN> restore standby controlfile from 'H:\oraback\sadstbycontrol.ctl'; --for 10g onwards
RMAN> sql 'alter database mount standby database';
RMAN> restore database;
Now try to list all the archivelogs which are backed up and from this list identify the maximum sequence for recovery.
RMAN> list backup of archivelog all;
RMAN> recover database until sequence 58;
Note: Do not be panic if you find any error indicating file was not restored sufficiently with old backup. You can safely ignore that error and move for next step.
Put the Standby DB in Recovery Managed Mode:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Now try to generate some log and check the sync the log from primary are shipped over to standby archive destination.


  1. BlueHost is definitely the best website hosting provider for any hosting services you need.