create physical standby by using rman duplicate

1.summary of test env:

Environment: Oracle 10.2.0.4.0 database (both primary database & standby database)
OS: Enterprise Linux 5.4

An oracle physical standby database needs to be created.

The filesystems structure of these 2 nodes are similar so no file_name_convert is necessary.

On the primary node dg01 is already present as a standalone database. On the standby node the oracle software is already installed.

2.Solution

step 1:

primary & standby PFILE setup:

on the primary:

SQL> create pfile from spfile

then scp to the standby

Make sure that the oracle software is installed on both nodes.And both node are with the same filesystem structure.

Implement identical database structure on the standby server.Including BACKGROUND_DUMP file path.

step2:

Check that archiving is enabled on the primary database :
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

If not, place the database in archive log mode:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archive log;
SQL> alter database open;

SQL> archive log list

step 3:

Check if the primary database is in forced logging:

In a dataguard environment nologging operations are not allowed. Because nologging
operations will not be propagated to the standby database. Therefore the database must be
in the forced logging mode.

SQL> select force_logging from v$database;
FOR
---
NO

SQL> alter database force logging;
Database altered.

step 4:

create password file from primary & scp to the standby:

*In the Linux OS,the password file must be orapw$ORACLE_SID,otherwise,it may make error

oracle@kk01: ~/data> rapwfile=$ORACLE_HOME/dbs/orapwSID password=systempwd force=y

oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/dbs> scp orapwddg01 kk02:/opt/oracle/product/10.2.0/db_1/dbs

SQL> select * from v$pwfile_users;

step 5:

Adjust the init.ora parameters of primary database

oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/dbs> vi initdg01.ora

Now add/change the following in file $ORACLE_HOME/dbs/initprod.ora:

oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/dbs> more initdg01.ora
dg01.__db_cache_size=427819008
dg01.__java_pool_size=4194304
dg01.__large_pool_size=4194304
dg01.__shared_pool_size=155189248
dg01.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/dg01/adump'
*.background_dump_dest='/opt/oracle/admin/dg01/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/dg01/control01.ctl','/data/dg01/control02.ctl','/data/dg01/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/dg01/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg01'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/arch/dg01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES DB_UNIQUE_NAME=dg01'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=599785472
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/dg01/udump'
####################################################
*.db_name='dg01'
*.db_unique_name='dg01'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg01,standby)'
#*.log_archive_dest_2='SERVICE=STANDBY affirm lgwr max_failure=1 noreopen net_timeout=60'
*.log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=standby
*.FAL_CLIENT=dg01
*.STANDBY_FILE_MANAGEMENT=AUTO
*.standby_archive_dest='/arch/dg01'

Adjust the tnsnames.ora & listener.ora file on the primary database server:

Add the following:

oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/network/admin> more listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = dg01 )
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(SID_NAME = dg01)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kk01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

###The blue char is Static Service Settings

oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/network/admin> more tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DG01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kk01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg01)
)
)

standby=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kk02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg01)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

RMAN10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rmantest)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = rman10g)
)
)

on the standby :

oracle@kk02: ~/opt/oracle/product/10.2.0/db_1/dbs> more initdg01.ora
dg01.__db_cache_size=427819008
dg01.__java_pool_size=4194304
dg01.__large_pool_size=4194304
dg01.__shared_pool_size=155189248
dg01.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/dg01/adump'
*.background_dump_dest='/opt/oracle/admin/dg01/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/dg01/control01.ctl','/data/dg01/control02.ctl','/data/dg01/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/dg01/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg01'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/arch/dg01 valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=599785472
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/dg01/udump'
#####################################################
*.db_name='dg01'
*.db_unique_name='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg01,standby)'
#*.log_archive_dest_2='SERVICE=STANDBY affirm lgwr max_failure=1 noreopen net_timeout=60'
*.log_archive_dest_2='SERVICE=dg01 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg01'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=dg01
*.FAL_CLIENT=standby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.standby_archive_dest='/arch/dg01'

##################net service&Listener###################

tns:the same as primary
oracle@kk01: ~/opt/oracle/product/10.2.0/db_1/network/admin> more listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = dg01 )
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(SID_NAME = dg01)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kk02)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

step 6:

Making a backup of the target for standby:

oracle@kk01:~/data> rman target /
RMAN> Backup Database format '/data/backup/full_%T_%u_%s_%p';

Backup current controlfile for standby:
RMAN> backup current controlfile for standby format '/data/backup/ctl_%T_%u_%s_%p';

Backup archivelog :
RMAN> sql "alter system archive log current";
RMAN> backup format '/data/backup/arc_t%t_%s_p%p'(archivelog all delete input);

After that,scp the backups to the standby with the same path;

step 7:

Restoring the database to the standby location:

rman target / auxiliarysys/oracle@standby --on the primary
or
rman targetsys/oracle@dg01 auxiliary / --on the standby(recommand)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

step 8:

Put the Standby in Managed Recovery Mode:
On the standby:
SQL> alter database recover managed standby database disconnect from session;