Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 18-Apr-2011***

Goal

This document offers step by step procedure to create physical standby from ASM primary.

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

Primary Hostname :- raca.idc.oracle.com
standby Hostname :- core1.idc.oracle.com

Fix

1. Enable Force Logging,

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

2. On the primary node, create a staging directory. For example:

[oracle@raca chicago]$ mkdir –p /home/oracle/stage

3. Create the same exact path on the standby host:

[oracle@core1 ~]$ mkdir -p /home/oracle/stage

4. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:

create pfile='/home/oracle/stage/boston.ora' from spfile;

5. On the primary node, perform. an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:

RMAN> run{
2> backup device type disk format '/home/oracle/stage/%U' database;
3> backup device type disk format '/home/oracle/stage/%U' current controlfile for standby;
4> }

Starting backup at 24-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=30 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/chicago/datafile/system.271.679675991
input datafile fno=00003 name=+DATA/chicago/datafile/sysaux.273.679676023
input datafile fno=00002 name=+DATA/chicago/datafile/undo1.272.679676015
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/02k8651v_1_1 tag=TAG20090224T154323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-FEB-09

Starting backup at 24-FEB-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-09
channel ORA_DISK_1: finished piece 1 at 24-FEB-09
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-FEB-09

RMAN>

6. Copy the contents of the staging directory from primary to standby's staging location. For example

[oracle@raca stage]$ scp /home/oracle/stage/* oracle@core1:/home/oracle/stage/
oracle@core1's password:
01k8650r_1_1 100% 236MB 10.7MB/s 00:22
02k8651v_1_1 100% 6016KB 5.9MB/s 00:00
03k86525_1_1 100% 5984KB 5.8MB/s 00:01
boston.ora 100% 458 0.5KB/s 00:00
[oracle@raca stage]$

7. Prepare the initialization file for standby instance

[oracle@core1 ~]$ mkdir -p /u01/app/oracle/product/10.2/oradata
[oracle@core1 ~]$ cd /u01/app/oracle/product/10.2/oradata
[oracle@core1 oradata]$ mkdir redo data ctrl bdump udump srl arc1
[oracle@core1 oradata]$

Set the below parameters in init.ora of standby,

*.db_name='chicago'
*.db_create_file_dest='/u01/app/oracle/product/10.2/oradata/boston/data/'
*.db_create_online_log_dest_1='/u01/app/oracle/product/10.2/oradata/boston/redo/'
*.db_file_name_convert='+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/','/u01/app/oracle/product/10.2/oradata/boston/data/' *.log_file_name_convert='+DATA/chicago/onlinelog/','/u01/app/oracle/product/10.2/oradata/boston/redo/' *.db_unique_name=boston

8. Create password file for standby database

[oracle@core1 boston]$ export ORACLE_SID=boston
[oracle@core1 dbs]$orapwd file=orapwboston password=oracle
[oracle@core1 dbs]$ ls -ltr *boston*
-rw-r----- 1 oracle oinstall 1536 Mar 3 15:55 orapwboston

9. Compose a tnsnames or connect string at standby server

chicago =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = chicago)
)
)

boston =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = boston)
)
)

Note:-

chicago will connect to primary while boston connects to standby itself

10. Create standby database

[oracle@core1 boston]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 3 16:55:19 2009

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2/oradata/boston/initboston.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 1218844 bytes
Variable Size 150996708 bytes
Database Buffers 104857600 bytes
Redo Buffers 70082560 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@core1 boston]$ /u01/app/oracle/product/10.2/bin/rman target sys/oracle@chicago auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 3 16:56:09 2009

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

connected to target database: CHICAGO (DBID=1289394690)
connected to auxiliary database: CHICAGO (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 03-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

contents of Memory Script.
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script.

Starting restore at 03-MAR-09
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/03k86525_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/product/10.2/oradata/boston/ctrl/ctrl01.ctl
Finished restore at 03-MAR-09

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

contents of Memory Script.
{
set newname for tempfile 1 to
"/u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991";
set newname for datafile 2 to
"/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015";
set newname for datafile 3 to
"/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023";
restore
check readonly
clone database
;
}
executing Memory Script.

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 03-MAR-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991
restoring datafile 00002 to /u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015
restoring datafile 00003 to /u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/01k8650r_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 03-MAR-09

contents of Memory Script.
{
switch clone datafile all;
}
executing Memory Script.

datafile 1 switched to datafile copy
input datafile copy recid=4 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991
datafile 2 switched to datafile copy
input datafile copy recid=5 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015
datafile 3 switched to datafile copy
input datafile copy recid=6 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023
Finished Duplicate Db at 03-MAR-09

RMAN>

11 Add standby redo logs to standby database

SQL> alter database add standby logfile group 3 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl3a.log' size 150m;

Database altered.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl4a.log' size 150m;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl5a.log' size 150m;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL>

12. Establish the communication to primary from standby

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
CHICAGO PHYSICAL STANDBY

SQL> alter system set standby_archive_dest='/u01/app/oracle/product/10.2/oradata/boston/arc1/';

System altered.

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/10.2/oradata/boston/arc1/ valid_for=(all_logfiles,all_roles) db_unique_name=boston';

SQL> alter system set log_archive_config='dg_config=(chicago,boston)';

System altered.

SQL> alter system set log_archive_dest_2='service=chicago lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=chicago';

System altered.

SQL> alter system set fal_client=boston;

System altered.

SQL> alter system set fal_server=chicago;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

13. Compose tnsnames or connect strings at Primary server

chicago =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = chicago)
)
)

boston =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = boston)
)
)

Note:-

Boston will connect to standby while chicago connects to primary itself

14. Establish the communication to standby from primary

SQL> alter system set log_archive_config='dg_config=(chicago,boston)';

System altered.

SQL> alter system set log_archive_dest_2='service=boston lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=boston';

System altered.

SQL> alter system set fal_client=chicago;

System altered.

SQL> alter system set fal_server=boston;

System altered.

SQL> select name,database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
CHICAGO PRIMARY

SQL>

15. Set role transition specific parameters for current primary

SQL> alter system set db_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/redo/','+DATA/chicago/onlinelog/' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

16. Configure a Standby Redo Log,

SQL> alter database add standby logfile group 3 size 150m;

Database altered.

SQL>alter database add standby logfile group 4 size 150m

Database altered.

SQL>alter database add standby logfile group 5 size 150m

Database altered.

NOTE: To check the number of SRL,

(maximum number of logfiles for each thread + 1) * maximum number of threads

For example, if the primary database has 3 log files for each thread and 2 threads, then 8 standby redo log file groups are needed on the standby database.

Verify the standby redo log file groups were created