Use RMAN DUPLICATE... FROM ACTIVE DATABASE Step-by-Step Guide to Creating Physical Standby Databases (Document ID 1526160.1)

Apply to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and higher
 The information contained in this document is applicable to all platforms

target

How to create a step-by-step guide to physical standby database by using RMAN DUPLICATE... FROM ACTIVE DATABASE command instead of closing the main database, instead of using the active files of the main database (without backup).
This feature is available from version 11g.

Database Name :- chicago
Primary db_unique_name :- chicago
standby db_unique_name :- Boston

For replication of NONE-Standby, see:

Note 452868.1 RMAN 'Duplicate From Active Database' Feature in 11G

Solution

1.Make necessary changes to the main database.
a. Enable force logging. 
b. If there is no password file, create the password file.
c. Create spare redo Journal.
d. Modify the parameter file to apply Dataguard. 
2. ensure sql*net The connection is normal.
3. Create a standby database through the network using the active file of the main database.
a. Create a password file
b. Create initialization parameter files for standby databases (auxiliary databases)
c. Create the required loading points or folders for database files
d. Connect to the main database as its target database to run and create a standby ON STANDBY. 
DUPLICATE TARGET DATABASE  
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
   PARAMETER_VALUE_CONVERT '', ''
   SET DB_FILE_NAME_CONVERT '', ''
   SET LOG_FILE_NAME_CONVERT '', ''
   SET SGA_MAX_SIZE 200M
   SET SGA_TARGET 125M;

Check log transfer and application.

Procedure:

We use the master database active file to create a standby database, that is, this command will be useful when creating a physical standby database using the master database active file over the network.

Prepare for the production database to become the main database

a.Ensure that the database is archivelog Pattern.
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

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

c. Create spare redo Journal
SQL> alter database add standby logfile '<name>' size <size>;

d. Modify the main initialization parameters to make them suitable for the main database dataguard
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)';
System altered.

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

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

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

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

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

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

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

Make sure the sql*net connection is normal.

In reserve listener.ora File insertion Boston Static entries.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = boston.us.oracle.com)
     (ORACLE_HOME = /u01/app/oracle/product/OraHome111)
     (SID_NAME = boston)
    )
   )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <auxiliary host>)(PORT = 1521))
  )

//TNSNAMES for main and standby Libraries.ORA There should be two entries
CHICAGO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <target host>)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = chicago.us.oracle.com))
  ) 

BOSTON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <auxiliary host>)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = boston.us.oracle.com))
  )
//Check the SQL*Net configuration using the following commands in the primary and standby databases
% tnsping chicago
% tnsping boston

Create a standby database

a. Copy the password file from the main database $ORACLE_HOME/dbs and rename it to the alternate database name.
The user name must be SYS and the password of the primary database and the standby database must be the same.
The best practice here is to copy the password file as recommended.
The password file name must match the instance name / SID used on the standby site, not DB_NAME.

b. Create initialization parameters with only one parameter DB_NAME.
DB_NAME=chicago
DB_UNIQUE_NAME=boston
DB_BLOCK_SIZE=<same as primary>

c. Create the required directory in the standby to place the data files and trace files in $ADR_HOME.

d. Set the environment variable ORACLE_SID as a standby service and start the standby instance.
% export ORACLE_SID=boston
% sqlplus "/ as sysdba"
    SQL> startup nomount pfile=$ORACLE_HOME/dbs/initcore1.ora
 Note: Use PFILE or SPFILE
 # Additional Notes
 # If you use DUPLICATE without TARGET connection, you cannot use SPFILE 
# Otherwise, you will get the following error message

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

e. Verify that the "AS SYSDBA" connection is running
% sqlplus /nolog
SQL> connect sys/<passwd<@boston AS SYSDBA
        connect sys/<passwd>@chicago AS SYSDBA

Execute RMAN on the main system and connect to the main database and auxiliary database (i.e. standby database)

$ rman target sys/sys@chicago auxiliary sys/sys@boston

connected to target database: CHICAGO (DBID=761464750)
connected to auxiliary database: CHICAGO (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 'chicago','boston'
  set db_unique_name='boston'
  set db_file_name_convert='/chicago/','/boston/'
  set log_file_name_convert='/chicago/','/boston/'
  set control_files='/u01/app/oracle/oradata/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='boston'
  set fal_server='chicago'
  set standby_file_management='MANUAL'
  set log_archive_config='dg_config=(chicago,boston)'
  set log_archive_dest_2='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'
;
}

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/boston/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=boston'' comment='''' scope=spfile";
sql clone "alter system set db_unique_name =''boston'' comment='''' scope=spfile";
sql clone "alter system set db_file_name_convert =''/chicago/'', ''/boston/'' comment='''' scope=spfile";
sql clone "alter system set log_file_name_convert =''/chicago/'', ''/boston/'' 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 =''boston'' comment='''' scope=spfile";
sql clone "alter system set fal_server =''chicago'' 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=(chicago,boston)'' comment='''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =''service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'' comment='''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u02/app/oracle/admin/boston/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=boston'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''boston'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/chicago/'', ''/boston/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/chicago/'', ''/boston/'' 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 = ''boston'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''chicago'' 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=(chicago,boston)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'' 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_chicago.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/boston/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to "/u02/app/oracle/oradata/boston/system01.dbf";
set newname for datafile 2 to "/u02/app/oracle/oradata/boston/sysaux01.dbf";
set newname for datafile 3 to "/u02/app/oracle/oradata/boston/undotbs01.dbf";
set newname for datafile 4 to "/u02/app/oracle/oradata/boston/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format "/u02/app/oracle/oradata/boston/system01.dbf" 
datafile 2 auxiliary format "/u02/app/oracle/oradata/boston/sysaux01.dbf" 
datafile 3 auxiliary format "/u02/app/oracle/oradata/boston/undotbs01.dbf" 
datafile 4 auxiliary format "/u02/app/oracle/oradata/boston/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

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

Now connect to the standby database sqlplus and start MRP (Management Recovery Process). Compare the latest sequence of master database with the application sequence of MRP (Management Recovery Process).
Example:

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

If you are a customer who has purchased an ADG license, you can open DB in read-only mode and start recovery.

SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect;