Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE

 Creating Physical Standby Using RMAN DUPLICATE FROM ACTIVE DATABASE

Steps are below how to create a physical standby database using RMAN DUPLICATE  FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take backup)

Note: This is feature available in 11g onwards.

Database Name :- Islamabad
Primary db_unique_name :- Islamabad
standby db_unique_name :- Karachi

1.Make the necessary changes to the primary database.
a. Enable force logging.
b. Creating the password file if one does not exist.
c. Create standby redologs.
d. Modify the parameter file suitable for Dataguard.
2. Ensure that the sql*net connectivity is working fine.
3. Create the standby database over the network using the active(primary) database files.
a. Create the password file
b. Create the initialization parameter file for the standby database (auxiliary database)
c. Create the necessary mount points or the folders for the database files

 PROCEDURE :

 While creating the standby database we use the active database files i.e., this command will be useful in creating the physical standby database using active database files over the network.

 1. Prepare the production database to be the primary database
  a. Ensure that the database is in archivelog mode .
SQL> select log_mode from v$database;

 LOG_MODE
 ------------
 ARCHIVELOG
b. Enable force logging
SQL> ALTER DATABASE FORCE LOGGING;

 c. Create standby redologs
SQL> alter database add standby logfile '<name>' size <size>;

d. Modify the primary initialization parameter for dataguard on primary,

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(Islamabad,karachi)';
 System altered.

 SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/Islamabad/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Islamabad';
 System altered.

 SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=karachi LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=karachi';
 System altered.

 SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
 System altered.

 SQL> alter system set FAL_SERVER=karachi;
 System altered.

 SQL> alter system set FAL_CLIENT=Islamabad;
 System altered.

 SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/karachi/data/','/u01/app/oracle/databases/Islamabad/data' scope=spfile;
 System altered.

 SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/karachi/redo/','/u01/app/oracle/databases/Islamabad/redo' scope=spfile;
 System altered.

2. Ensure that the sql*net connectivity is working fine.

Insert a static entry for Karachi in the listener.ora file of the standby system.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = karachi.pk.com)
      (ORACLE_HOME = /u01/app/oracle/product/OraHome111)
      (SID_NAME = karachi)
     )
    )

 LISTENER =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = DB-DRSERVER)(PORT = 1521))
   )

TNSNAMES.ORA for the Primary and Standby should have BOTH entries

ISLAMABAD =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = DB-PRIMARY)(PORT = 1521))
     )
     (CONNECT_DATA = (SERVICE_NAME = Islamabad.pk.com))
   ) 

 KARACHI =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = DB-DRSERVER>)(PORT = 1521))
     )
     (CONNECT_DATA = (SERVICE_NAME = karachi.pk.com))
   )

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

% tnsping Islamabad
% tnsping karachi
3. Create the standby database

a. Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
b. Create a initialization parameter with only one parameter DB_NAME.

DB_NAME=Islamabad
DB_UNIQUE_NAME=karachi

c. Create the necessary directories in the standby location to place the datafiles and the trace files in the $ADR_HOME.

d. Set the environment variable ORACLE_SID to the standby service and start the standby-instance.

% export ORACLE_SID=karachi
 % sqlplus "/ as sysdba"
   SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcore1.ora
NOTE : Better to use SPFILE
SQL> create spfile from pfile=$ORACLE_HOME/dbs/initcore1.ora;
SQL>shutdown immediate;
SQL>startup nomount;

 e. Verify the connection 'AS SYSDBA' is working
% sqlplus /nolog
 SQL> connect sys/<passwd<@karachi AS SYSDBA
      connect sys/<passwd>@Islamabad AS SYSDBA

f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database ( i.e., the standby)

$ rman target sys/sys@Islamabad auxiliary sys/sys@karachi

 connected to target database: ISLAMABAD (DBID=597647698)
 connected to auxiliary database: KARACHI (not mounted)

 RMAN> run {
 allocate channel prmy1 type disk;
 allocate channel prmy2 type disk;
 allocate channel prmy3 type disk;
 allocate channel prmy4 type disk;
 allocate auxiliary channel stby type disk;

 duplicate target database for standby from active database
 spfile
   parameter_value_convert 'Islamabad','karachi'
   set db_unique_name='karachi'
   set db_file_name_convert='/Islamabad/','/karachi/'
   set log_file_name_convert='/Islamabad/','/karachi/'
   set control_files='/u01/app/oracle/oradata/control01.ctl'
   set log_archive_max_processes='5'
   set fal_client='karachi'
   set fal_server='Islamabad'
   set standby_file_management='AUTO'
   set log_archive_config='dg_config=(Islamabad,karachi)'
   set log_archive_dest_1='service=Islamabad ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Islamabad'
 ;
 }

 using target database control file instead of recovery catalog
 allocated channel: prmy1
 channel prmy1: SID=147 device type=DISK

 allocated channel: prmy2
 channel prmy2: SID=130 device type=DISK

 allocated channel: prmy3
 channel prmy3: SID=137 device type=DISK

 allocated channel: prmy4
 channel prmy4: SID=170 device type=DISK

 allocated channel: stby
 channel stby: SID=98 device type=DISK

 Starting Duplicate Db at 19-MAY-08

 contents of Memory Script:
 {
 backup as copy reuse
 file '/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/orapwcore1' 
 file'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore.ora' auxiliary format'/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora' ;
 sql clone "alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''";
 }
 executing Memory Script

 Starting backup at 19-MAY-08
 Finished backup at 19-MAY-08

 sql statement: alter system set spfile= ''/u02/app/oracle/product/11.1.0/db_1/dbs/spfilecore1.ora''

 contents of Memory Script:
 {
 sql clone "alter system set audit_file_dest =''/u02/app/oracle/admin/karachi/adump'' comment='''' scope=spfile";
 sql clone "alter system set dispatchers =''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment='''' scope=spfile";
 sql clone "alter system set log_archive_dest_2 =''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=karachi'' comment='''' scope=spfile";
 sql clone "alter system set db_unique_name =''karachi'' comment='''' scope=spfile";
 sql clone "alter system set db_file_name_convert =''/Islamabad/'', ''/karachi/'' comment='''' scope=spfile";
 sql clone "alter system set log_file_name_convert =''/Islamabad/'', ''/karachi/'' comment='''' scope=spfile";
 sql clone "alter system set control_files =''/u01/app/oracle/oradata/control01.ctl'' comment='''' scope=spfile";
 sql clone "alter system set log_archive_max_processes =5 comment='''' scope=spfile";
 sql clone "alter system set fal_client =''karachi'' comment='''' scope=spfile";
 sql clone "alter system set fal_server =''Islamabad'' comment='''' scope=spfile";
 sql clone "alter system set standby_file_management =''AUTO'' comment='''' scope=spfile";
 sql clone "alter system set log_archive_config =''dg_config=(Islamabad,karachi)'' comment='''' scope=spfile";
 sql clone "alter system set log_archive_dest_1 =''service=Islamabad ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Islamabad'' comment='''' scope=spfile";
 shutdown clone immediate;
 startup clone nomount ;
 }
 executing Memory Script

 sql statement: alter system set audit_file_dest = ''/u02/app/oracle/admin/karachi/adump'' comment= '''' scope=spfile
 sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=core1XDB)'' comment= '''' scope=spfile
 sql statement: alter system set log_archive_dest_2 = ''service=core11 arch async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=karachi'' comment= '''' scope=spfile
 sql statement: alter system set db_unique_name = ''karachi'' comment= '''' scope=spfile
 sql statement: alter system set db_file_name_convert = ''/Islamabad/'', ''/karachi/'' comment= '''' scope=spfile
 sql statement: alter system set log_file_name_convert = ''/Islamabad/'', ''/karachi/'' comment= '''' scope=spfile
 sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
 sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
 sql statement: alter system set fal_client = ''karachi'' comment= '''' scope=spfile
 sql statement: alter system set fal_server = ''Islamabad'' comment= '''' scope=spfile
 sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
 sql statement: alter system set log_archive_config = ''dg_config=(Islamabad,karachi)'' comment= '''' scope=spfile
 sql statement: alter system set log_archive_dest_1 = ''service=Islamabad ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Islamabad'' comment= '''' scope=spfile

 Oracle instance shut down

 connected to auxiliary database (not started)
 Oracle instance started

 Total System Global Area 845348864 bytes

 Fixed Size 1303188 bytes
 Variable Size 482348396 bytes
 Database Buffers 356515840 bytes
 Redo Buffers 5181440 bytes

 contents of Memory Script:
 {
 backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/control01.ctl';
 sql clone 'alter database mount standby database';
 }
 executing Memory Script

 Starting backup at 19-MAY-08
 channel prmy1: starting datafile copy
 copying standby control file
 output file name=/u02/app/oracle/product/11.1.0/db_1/dbs/snapcf_Islamabad.f tag=TAG20080519T173406 RECID=2 STAMP=655148053
 channel prmy1: datafile copy complete, elapsed time: 00:00:03
 Finished backup at 19-MAY-08

 sql statement: alter database mount standby database

 contents of Memory Script:
 {
 set newname for tempfile 1 to"/u02/app/oracle/oradata/karachi/temp01.dbf";
 switch clone tempfile all;
 set newname for datafile 1 to "/u02/app/oracle/oradata/karachi/system01.dbf";
 set newname for datafile 2 to "/u02/app/oracle/oradata/karachi/sysaux01.dbf";
 set newname for datafile 3 to "/u02/app/oracle/oradata/karachi/undotbs01.dbf";
 set newname for datafile 4 to "/u02/app/oracle/oradata/karachi/users01.dbf";
 backup as copy reuse
 datafile 1 auxiliary format "/u02/app/oracle/oradata/karachi/system01.dbf" 
 datafile 2 auxiliary format "/u02/app/oracle/oradata/karachi/sysaux01.dbf" 
 datafile 3 auxiliary format "/u02/app/oracle/oradata/karachi/undotbs01.dbf" 
 datafile 4 auxiliary format "/u02/app/oracle/oradata/karachi/users01.dbf" ;
 sql 'alter system archive log current';
 }
 executing Memory Script

 executing command: SET NEWNAME

 renamed tempfile 1 to /u02/app/oracle/oradata/karachi/temp01.dbf in control file

 executing command: SET NEWNAME
 executing command: SET NEWNAME
 executing command: SET NEWNAME
 executing command: SET NEWNAME

 Starting backup at 19-MAY-08
 channel prmy1: starting datafile copy
 input datafile file number=00001 name=/u02/app/oracle/oradata/Islamabad/system01.dbf
 channel prmy2: starting datafile copy
 input datafile file number=00002 name=/u02/app/oracle/oradata/Islamabad/sysaux01.dbf
 channel prmy3: starting datafile copy
 input datafile file number=00003 name=/u02/app/oracle/oradata/Islamabad/undotbs01.dbf
 channel prmy4: starting datafile copy
 input datafile file number=00004 name=/u02/app/oracle/oradata/Islamabad/users01.dbf
 output file name=/u02/app/oracle/oradata/karachi/undotbs01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
 channel prmy3: datafile copy complete, elapsed time: 00:00:24
 output file name=/u02/app/oracle/oradata/karachi/users01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
 channel prmy4: datafile copy complete, elapsed time: 00:00:16
 output file name=/u02/app/oracle/oradata/karachi/system01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
 channel prmy1: datafile copy complete, elapsed time: 00:02:32
 output file name=/u02/app/oracle/oradata/karachi/sysaux01.dbf tag=TAG20080519T173421 RECID=0 STAMP=0
 channel prmy2: datafile copy complete, elapsed time: 00:02:32
 Finished backup at 19-MAY-08

 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=2 STAMP=655148231 file name=/u02/app/oracle/oradata/karachi/system01.dbf
 datafile 2 switched to datafile copy
 input datafile copy RECID=3 STAMP=655148231 file name=/u02/app/oracle/oradata/karachi/sysaux01.dbf
 datafile 3 switched to datafile copy
 input datafile copy RECID=4 STAMP=655148231 file name=/u02/app/oracle/oradata/karachi/undotbs01.dbf
 datafile 4 switched to datafile copy
 input datafile copy RECID=5 STAMP=655148231 file name=/u02/app/oracle/oradata/karachi/users01.dbf
 Finished Duplicate Db at 19-MAY-08
 released channel: prmy1
 released channel: prmy2
 released channel: prmy3
 released channel: prmy4

4. Now connect to standby sqlplus and start the MRP (Managed Recovery Process). Compare the primary last sequence and MRP (Managed Recovery Process)applying sequence.

Example :

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

 5. For ADG license customer open DB in read only and start the recovery.

 SQL>alter database recover managed standby database cancel;

 SQL>alter database open;

 SQL>alter database recover managed standby database disconnect;
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Oracle DataGaurd. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s