Physical Standby database creation using RMAN(Draft)

Backup the primary database using RMAN.

Setup the standby directory structures similar to Primary and create the init.ora

#Standby specific Configuration  
*.fal_client=’MYDB_S’  
*.fal_server=’MYDB_P’  
*.log_archive_dest_1=’LOCATION=/export/oracle/archive/MYDB/log mandatory’  
*.log_archive_dest_2=’SERVICE=MYDB_S optional reopen=60′  
*.log_archive_dest_state_1=’ENABLE’  
*.log_archive_dest_state_2=’DEFER’  
*.service_names=’PRODCDW’  
*.standby_file_management=’AUTO’  
*.standby_archive_dest=’LOCATION=/export/oracle/archive/MYDB/log’  
log_archive_format = ‘arch_%t_%r_%s.dbf’  
log_checkpoint_interval = 0  
*.aq_tm_processes=0

start the standby database listener. And start the instance with nomount.

oracle@myserver.mydomain>MYDB>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 18 13:53:53 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;  
ORACLE instance started.

Total System Global Area 1660944384 bytes  
Fixed Size                  2073184 bytes  
Variable Size             385879456 bytes  
Database Buffers         1258291200 bytes  
Redo Buffers               14700544 bytes  
SQL> exit  
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production  
With the Partitioning and Data Mining options

Connect to the Catalog database:

oracle@myserver.mydomain>MYDB>rman catalog rman/rman_pass@RMANDB

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Mar 18 13:54:07 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to recovery catalog database

Connect to the Standby database (Auxiliary):

RMAN> connect auxiliary /

connected to auxiliary database: MYDB (not mounted)

connect to the Target database (Primary):

RMAN> connect target sys@MYDB_P

target database Password:  
connected to target database: MYDB (DBID=3647575971)

Start RMAN restore with ‘duplicate’  command as below:

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

Starting Duplicate Db at 18-MAR-08  
allocated channel: ORA_AUX_DISK_1  
channel ORA_AUX_DISK_1: sid=1640 devtype=DISK

contents of Memory Script:  
{  
   restore clone standby controlfile;  
   sql clone ‘alter database mount standby database’;  
}  
executing Memory Script

Starting restore at 18-MAR-08  
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file  
channel ORA_AUX_DISK_1: copied control file copy  
input filename=/tmp/standby.ctl  
output filename=/u01/oracle/MYDB/control01.ctl  
output filename=/u02/oracle/MYDB/control02.ctl  
output filename=/u03/oracle/MYDB/control03.ctl  
Finished restore at 18-MAR-08

sql statement: alter database mount standby database  
released channel: ORA_AUX_DISK_1

contents of Memory Script:  
{  
   set newname for tempfile  1 to  
"/u02/oracle/MYDB/temp.df01.dbf";  
   switch clone tempfile all;  
   set newname for datafile  1 to  
"/u01/oracle/MYDB/system.df01.dbf";  
   set newname for datafile  2 to  
"/u03/oracle/MYDB/undotbs.df01.dbf";  
…………..  
"/u03/oracle/MYDB/dw_etl_i.df04.dbf";  
   set newname for datafile  31 to  
"/u03/oracle/MYDB/dw_etl_i.dbf3.dbf";  
   set newname for datafile  32 to  
"/u01/oracle/MYDB/dw_etl_i.df02.dbf";  
   set newname for datafile  33 to  
"/u02/oracle/MYDB/dw_etl_i.df05.dbf";  
   restore  
   check readonly  
   clone database  
   ;  
}  
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u02/oracle/MYDB/temp.df01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

…

RMAN will leave the database in mounted state after it has been restored.

You can started the managed recovery process by :

Sqlplus "/as sysdba"

Sql> alter database recover managed standby database disconnect from session;