Step by step Create an Oracle 11gR2 Physical Standby Database

In this article I will show step by step procedure to create an Oracle 11gR2 physical standby database. This example environment is as follows:

– Both primary and standby database are on RAC environment. Both are 2 nodes RAC.
– The distance between primary and standby database is 300 kilometers and they are within Bangladesh.
– There is 10 Mbps dedicated bandwidth between primary and standby database.
– All 4 nodes are using Red Hat Linux Enterprise version 5 operating system and same hardware structure.
– Oracle 11gR2 is installed both in primary and physical standby database.
– Oracle datafiles and clusterware are on ASM file system.

Though this example is based on RAC and ASM, you can use this document to setup any type of physical standby database. I will tell explicitly what to do based on whether your environment is non-RAC or not, whether you use ASM or not.

In this example the following values are used:
– Database name is bddipdc
– Primary database unique name as well as oracle net service name is bddipdc
– Standby database unique name as well as oracle net service name is bddipdrs
– Primary database hostname is DC-DB-01 and standby database hostname is DRS-DB-01
– Primary database IP Address is 192.168.100.101 and standby database IP Address is 192.168.105.101

Step 01: Prepare the Primary and physical standby database environment: (Both bddipdc and bddipdrs)
i) After you have physically placed hardware in both site install operating system in both end. The site in which primary database reside called as Data Center (DC) and the site in which standby database reside called as Disaster Recovery Site (DRS) and that is I choose the unique name as bddipdc and bddipdrs correspondingly.

ii) Install Oracle software as well as oracle database both in primary and standby database site. Though database creation in standby site is not mandatory but you can create database specially if you are in RAC environment. Because in that case you don’t need to do many manual tasks like register database to cluster registry etc. Note that both in primary and standby site, while creating database keep the database name same that is bddipdc.

Step 02: Prepare the Primary Database for Standby Database Creation: (Only bddipdc)
i) Enable force logging on primary database.
SQL> ALTER DATABASE FORCE LOGGING;

ii) Set the database initialization parameters.

#Primary database role initialization parameters.
DB_NAME=bddipdc
DB_UNIQUE_NAME=bddipdc
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(bddipdc,bddipdrs)’
LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc’
LOG_ARCHIVE_DEST_2=’SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

# Standby role initialization parameters.
FAL_SERVER=bddipdrs
DB_FILE_NAME_CONVERT=’bddipdrs’,’bddipdc’
LOG_FILE_NAME_CONVERT=’bddipdrs’,’bddipdc’
STANDBY_FILE_MANAGEMENT=AUTO

As primary database is using spfile so run the following commands on primary database.

alter system set DB_UNIQUE_NAME=bddipdc scope=spfile sid=’*’;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(bddipdc,bddipdrs)’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid=’*’;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid=’*’;
alter system set FAL_SERVER=bddipdrs scope=both sid=’*’;
alter system set FAL_CLIENT=bddipdc scope=both sid=’*’;

We have not set any DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters because in standby database we want to keep same file structure as it is in primary database.

iii) Enable archivelog in primary database.
If the primary database is not in archivelog mode then enable archivelog in primary database. If your database is in archivelog mode then you can simply forward to next phase.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
If your database is in RAC environment then you can follow Enable archivelog for RAC. If you are in non RAC environment then you can follow Change archivelog mode .

Step 03: Determine datafiles, controlfiles, password file, initialization parameter files to be copied from primary to standby database (bddipdc):
You can create standby database using RMAN. But in this post I want to show all steps manually because RMAN has several bug while creating another copy of database over the network.

i)Connect to one Oracle instance and determine the location of datafiles and tempfiles. Save the file location in a notepad.
Determine the location of datafile,
SQL> select name from v$datafile;
NAME
—————————————————-
D:\ORADATA\A\SYSTEM01.DBF
D:\ORADATA\A\UNDOTBS01.DBF
D:\ORADATA\A\SYSAUX01.DBF
D:\ORADATA\A\USERS01.DBF
D:\ORADATA\A\EXAMPLE01.DBF

Determine the location of oracle tempfile.
SQL> select name from v$tempfile;
NAME
———————————-
D:\ORADATA\A\TEMP01.DBF

ii)Create the pfile from spfile of primary database.
SQL> conn / as sysdba
SQL> create pfile=’/tmp/bddipdc/initbddipdc.ora’ from spfile;

iii) Shutdown the primary database. If you are in RAC environment, then shut down all instances.
SQL> shut immediate;

Using srvctl,
$srvctl stop database -d bddipdc

iv) This step is only applicable if your database files are in ASM. As our datafiles and tempfiles are in ASM file system so we need to copy them under /tmp/bddipdc in order to move the datafiles and tempfiles into another computer.

Ensure that your ORACLE_SID and ORACLE_HOME points to ASM home and ASM SID.
Invoke asmcmd.
$asmcmd
ASMCMD> cp +DATA/bddipdc/datafile/system.267.728507607 /tmp/bddipdc/system
ASMCMD> cp +DATA/bddipdc/datafile/sysaux.259.728507607 /tmp/bddipdc/sysaux
ASMCMD> cp +DATA/bddipdc/datafile/undotbs1.258.728507609 /tmp/bddipdc/undotbs1
ASMCMD> cp +DATA/bddipdc/datafile/users.257.728507609 /tmp/bddipdc/users
ASMCMD> cp +DATA/bddipdc/datafile/undotbs2.270.728507783 /tmp/bddipdc/undotbs2
ASMCMD> cp +DATA/bddipdc/tempfile/temp.271.728507701 /tmp/bddipdc/temp

v) Start one instance of primary database in mount state. If you are in non RAC environment then just start database in mount state.
SQL> startup mount;

Create the standby control file.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/bddipdc/control01.ora’;

Step 04: Copy password file, datafile, standby controlfile, pfile from primary database to standby database.
Up to now we have datafile, tempfile, standby controlfile, pfile under one directory /tmp/bddipdc in the primary database. Now also we need to move password file into /tmp/bddipdc directory so that we can simply scp to remote location and with one command all files will be copied to standby database.
$ scp -r /tmp/bddipdc oracle@drs-db-01:/tmp/bddipdc

Copy node 1 of primary database password file across all nodes in standby database (This one is only applicable for RAC)
The password file of node 1 of primary database need to be copied across all instances in standby database as well as all other nodes in primary database as well.

Step 05: Start the Standby database and set initialization parameters. (bddipdrs)
At this stage we have all required files under location /tmp/bddipdc to setup physical standby database. We have already one database created in standby site. So we can use that spfile for our standby database creation. Though we have pfile in the location /tmp/bddipdc (which we copied from primary database) we will use current spfile of existing standby database in order minimize the steps.

i) Shut down standby database (in RAC shut down all instances) and start in nomount state (in RAC start one instance in nomount stage).
SQL> shut immediate;
SQL> startup nomount;

Set the initialization parameters.
SQL> alter system set DB_UNIQUE_NAME=bdafisdc scope=spfile sid=’*’;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(bdafisdc,bdafisdrs)’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bdafisdc’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=bdafisdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bdafisdrs’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid=’*’;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid=’*’;
alter system set FAL_SERVER=bdafisdrs scope=both sid=’*’;
alter system set FAL_CLIENT=bdafisdc scope=both sid=’*’;

We also set the location of control_files. We will copy standby control file into this location.
alter system set control_files=’+DATA/bddipdc/controlfile/control01.ctl’ scope=spfile sid=’*’;

iii) shutdown the standby database.
SQL> shut immediate;

iv) Delete all standby control file, datafile, tempfile and copy these standby control file, datafile, tempfile into ASM file system. Note that if you are not in ASM file system then instead of +DATA use your desired location.

$ asmcmd
ASMCMD> cp /tmp/bddipdc/control01.ora +DATA/bddipdc/controlfile/control01.ctl
ASMCMD> cp /tmp/bddipdc/system +DATA/bddipdc/datafile/system
ASMCMD> cp /tmp/bddipdc/sysaux +DATA/bddipdc/datafile/sysaux
ASMCMD> cp /tmp/bddipdc/undotbs1 +DATA/bddipdc/datafile/undotbs1
ASMCMD> cp /tmp/bddipdc/undotbs2 +DATA/bddipdc/datafile/undotbs2
ASMCMD> cp /tmp/bddipdc/users +DATA/bddipdc/datafile/users
ASMCMD> cp /tmp/bddipdc/temp /tmp/bddipdc/temp

v) Start the standby database in mount state.
SQL> startup mount

Set the undo_management parameter manual.
SQL> alter system set undo_management=MANUAL scope=both sid=’*’;

Now rename datafiles and tempfiles in order to use the correct location as we copied to ASM file system.
SQL> alter database rename file ‘+DATA/bddipdc/datafile/system.267.728507607’ to ‘+DATA/bddipdc/datafile/system’;

SQL> alter database rename file ‘+DATA/bddipdc/datafile/sysaux.259.728507607’ to ‘+DATA/bddipdc/datafile/sysaux’;

SQL> alter database rename file ‘+DATA/bddipdc/datafile/undotbs1.258.728507609’ to ‘+DATA/bddipdc/datafile/undotbs1’;

SQL> alter database rename file ‘+DATA/bddipdc/datafile/users.257.728507609’ to ‘+DATA/bddipdc/datafile/users’;

SQL> alter database rename file ‘+DATA/bddipdc/datafile/undotbs2.270.728507783’ to ‘+DATA/bddipdc/datafile/undotbs2’;

SQL> alter database rename file ‘+DATA/bddipdc/tempfile/temp.271.728507701’ to ‘+DATA/bddipdc/datafile/temp’;