Create standby database with 11g using RMAN duplicate from active database

Hello to all the oracle dba bloggers, I always used to appreciate the so many thousands of bloggers who post their work which immensely benefits to so many DBA's. I also decided to post my work and spread accross the knowledge amongst us. This is my first posting on the Data Guard implementation.


Primary Host = collab1
Standby Host = collab2
DB Version : 11.2.0.1
This example has the DB_NAME as mobpro on the primary and standby
Create primary database as usual with the following parameters

LISTENER.ORA for primary

SID_LIST_LISTENER = 
(SID_LIST = 
(SID_DESC =
(GLOBAL_DBNAME =mobpro) 
(ORACLE_HOME = /u01/app/oracle/product/11.2.0) 
(SID_NAME = mobpro) 
) ) 



LISTENER = 
(DESCRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521)) 
) 

TNSNAMES.ora for both primary and standby

MOBPRO_COLLAB1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

MOBPRO_COLLAB2_DG =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Initialization parameters on the Primary (these paqrameters to be set on the host collab1)

db_name='mobpro'
db_unique_name=mobpro_collab1

##COMMON TO BOTH PRIMARY AND STANDBY ROLES

LOG_ARCHIVE_CONFIG='DG_CONFIG=(mobpro_collab1,mobpro_collab2)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/mobpro/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mobpro_collab1'

LOG_ARCHIVE_DEST_2='SERVICE=mobpro_collab2_dg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mobpro_collab2' VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=2

LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

#SPECIFIC TO STANDBY ROLE

  
STANDBY_FILE_MANAGEMENT=AUTO 

STANDBY_ARCHIVE_DEST='/u01/app/oracle/admin/mobpro/arch'

FAL_SERVER=MOBPRO_COLLAB2_DG

FAL_CLIENT=MOBPRO_COLLAB1_DG

Make sure you create the standby redo log on the primary, RMAN will create it automatically on the standby

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/u01/app/oracle/admin/mobpro/srl01.log') SIZE 10M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/u01/app/oracle/admin/mobpro/srl02.log') SIZE 10M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/u01/app/oracle/admin/mobpro/srl03.log') SIZE 10M; 

Let us now prepare the STANDBY system

• Create a static listener,

       SID_LIST_LISTENER = 
       (SID_LIST = 
       (SID_DESC = 
       (GLOBAL_DBNAME =mobpro) 
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0) 
       (SID_NAME = mobpro) 
       )) 
    
      LISTENER = 
      (DESCRIPTION = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521)) 
     ) 

• Create a pfile under $ORACLE_HOME/dbs/initmobpro.ora with only one entry
  db_name=mobpro
• Create a password file under the dbs directory, make sure it matches the password of sys of the primary, in my example the password is for sys is sys.
  orapwd file=orapwmobpro password=sys
• Startup the Standby Instance
  SQL>STARTUP NOMOUNT
  

Now we all are set to use RMAN to create the STANBY system

RMAN> connect target sys/sys@mobpro_collab1_dg
connected to target database: MOBPRO (DBID=694528147)
RMAN> connect AUXILIARY sys/sys@mobpro_collab2_dg
connected to auxiliary database: MOBPRO (not mounted)

RMAN> run {
 allocate channel pm1 type disk;
 allocate channel pm2 type disk;
 allocate channel pm3 type disk;
 allocate channel pm4 type disk;
 allocate channel pm5 type disk;
 allocate auxiliary channel st1 type disk;
 duplicate target database for standby from active database
 spfile
 set 'db_unique_name'='mobpro_collab2'
 set control_files='/u01/app/oracle/oradata/mobpro/control.ctl'
 set db_create_online_log_dest_1='/u01/app/oracle/oradata/mobpro/'
 set db_create_online_log_dest_2='/u01/app/oracle/oradata/mobpro/'
 nofilenamecheck;
 }


using target database control file instead of recovery catalog

allocated channel: pm1
channel pm1: SID=47 device type=DISK
allocated channel: pm2
channel pm2: SID=17 device type=DISK
allocated channel: pm3
channel pm3: SID=48 device type=DISK
allocated channel: pm4
channel pm4: SID=44 device type=DISK
allocated channel: pm5
channel pm5: SID=43 device type=DISK
allocated channel: st1
channel st1: SID=20 device type=DISK
Starting Duplicate Db at 18-OCT-09
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbs/orapwmobpro' auxiliary format 
'/u01/app/oracle/product/11.2.0/dbs/orapwmobpro' targetfile 
'/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora' auxiliary format 
'/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora''";
}
executing Memory Script
Starting backup at 18-OCT-09
Finished backup at 18-OCT-09
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbs/spfilemobpro.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name = 
''mobpro_collab2'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/mobpro/control.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_1 = 
''/u01/app/oracle/oradata/mobpro/'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_online_log_dest_2 = 
''/u01/app/oracle/oradata/mobpro/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''mobpro_collab2'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/mobpro/control.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_1 = ''/u01/app/oracle/oradata/mobpro/'' comment= '''' scope=spfile
sql statement: alter system set db_create_online_log_dest_2 = ''/u01/app/oracle/oradata/mobpro/'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
oracle instance started
Total System Global Area 828608512 bytes
Fixed Size 1339684 bytes
Variable Size 620760796 bytes
Database Buffers 201326592 bytes
Redo Buffers 5181440 bytes
allocated channel: st1
channel st1: SID=18 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/mobpro/control.ctl';
}
executing Memory Script
Starting backup at 18-OCT-09
channel pm1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbs/snapcf_mobpro.f tag=TAG20091018T163844 RECID=5 STAMP=700591126
channel pm1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-OCT-09
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/mobpro/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/mobpro/system01.dbf";
set newname for datafile 2 to 
"/u01/app/oracle/oradata/mobpro/sysaux01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/mobpro/undotbs01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/mobpro/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/mobpro/system01.dbf" datafile 
2 auxiliary format 
"/u01/app/oracle/oradata/mobpro/sysaux01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/mobpro/undotbs01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/mobpro/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/mobpro/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-OCT-09
channel pm1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/mobpro/system01.dbf
channel pm2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf
channel pm3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf
channel pm4: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/mobpro/users01.dbf
output file name=/u01/app/oracle/oradata/mobpro/users01.dbf tag=TAG20091018T163858
channel pm4: datafile copy complete, elapsed time: 00:01:08
output file name=/u01/app/oracle/oradata/mobpro/system01.dbf tag=TAG20091018T163858
channel pm1: datafile copy complete, elapsed time: 00:02:38
output file name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf tag=TAG20091018T163858
channel pm2: datafile copy complete, elapsed time: 00:02:38
output file name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf tag=TAG20091018T163858
channel pm3: datafile copy complete, elapsed time: 00:02:38
Finished backup at 18-OCT-09
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=700591299 file name=/u01/app/oracle/oradata/mobpro/users01.dbf
Finished Duplicate Db at 18-OCT-09
released channel: pm1
released channel: pm2
released channel: pm3
released channel: pm4
released channel: pm5
released channel: st1




Let us set up few parameters on the standby system

SQL> alter system set fal_server=MOBPRO_COLLAB2_DG scope=botSQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=mobpro_collab1_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mobpro_collab1' scope=both;h;
SQL> alter system set fal_client=MOBPRO_COLLAB1_DG scope=both;

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/mobpro/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mobpro_collab2' scope=both; 


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT; 2 

Database altered.



Identify existing files on standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
150 18-OCT-09 18-OCT-09
151 18-OCT-09 18-OCT-09


Switch a log on the primary

SQL> alter system switch logfile;

System altered.

Confirm it on standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;




SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

150 18-OCT-09 18-OCT-09
151 18-OCT-09 18-OCT-09
152 18-OCT-09 18-OCT-09



Let us see if the real time apply works with out a log switch

On the standby do this

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


SQL> ALTER DATABASE OPEN READ ONLY; 

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

  
Now on the primary insert a value into a table t

SQL> insert into t values(00000);

1 row created.

SQL> commit;

Commit complete.


On the standby now you query to check if this updated value is seen 

SQL> select * from t;


N
---------
1971
0

Yes it works, quiet a simple method to implement Data Guard in 11g.

Regards

Asad