Creating a Standby Database using RMAN and Tivoli

Storage Manager
Confirm that TSM is installed on the host/hosts and tdpo.opt file has been
configured.
Contact the TSM administrator and confirm that all pre-requisite tasks related to the TSM
set-up have been completed on BOTH primary as well as standby machines.
For example on Linux machines the tdpo.opt file should exist in the following location :
/opt/tivoli/tsm/client/oracle/bin/
or
/opt/tivoli/tsm/client/oracle/bin64/
If the ORACLE_SID is dgtest9i, then the tdpo.opt file is set up with the name tdpo.dgtest9id.opt
We can test the TDP set up using the sbttest command
export TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt
[oracle@itlinuxdevblade08 bin64]$ sbttest test
The sbt function pointers are loaded from libobk.so library.
-- sbtinit succeeded
Return code -1 from sbtinit, bsercoer = 0, bsercerrno = 0
Message 0 not found; product=RDBMS; facility=SBT

Pre-requisite tasks to be completed on the Primary Database

Ensure that the database has been registered with the RMAN catalog and that the database is
also running in ARCHIVELOG mode.
If already not created, create a password file. Please make a note of the password used as it
should be the same one used when creating the password file on the standby site.
Also ensure that the parameter remote_login_password_file is set to EXCLUSIVE
Ensure that the Primary database is functioning in force logging mode.

Gavin Soorma

Page

9/15/2016

It is a best practice to place the primary database in force logging mode so that all operation
are captured in the redo stream. To place the primary database in force logging mode issue
the following SQL
SQL > alter database force logging;

Take a backup of the Primary Database

Once we have tested the TSM setup on the machine, we can take an online backup of the
Primary database using RMAN which will be restored on the standby site to create the
standby database.
Ensure that the database has been registered with the RMAN catalog and that the database is
also running in ARCHIVELOG mode.
Make a note of the current log sequence number. This is will be used as a starting point when
we specify the range of archive log files that need to be backed up to tape.
$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarydb.rcv
backup_primarydb.rcv
run {
allocate channel ch1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';
backup database ;
release channel ch1 ;
}

Create the Standby Control File

Using RMAN we can take a backup of the current control file which will be restored on the
standby machine as a standby control file.
$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarycf.rcv
backup_primarycf.rcv
run {
allocate channel ch1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';
set limit channel ch1 kbytes=3145728 readrate 200;
backup current controlfile for standby;
release channel ch1 ;
}

Gavin Soorma

Page

9/15/2016

READ RATE specifies the number of buffers to be read per second by RMAN
backup or copy operations

Backup the Archive Log Files

While performing the recovery on the standby database after restoring the primary database
backup, we need to go to a point in time after which we created the standby control file. We
then need to record the last sequence number from V$ARCHIVED_LOG and ensure that the
archivelogs until that particular sequence are backed up via RMAN.
SQL> alter system switch logfile;
SQL > alter system switch logfile;
SQL > select max(sequence#) from v$archived_log;
Make a note of this log sequence number. This will be used as the end point when we specify
the range of archive log files that need to be backed up via RMAN to tape.
This can be verified by issuing the archive log list command as well as physically checking
the log_archive_dest location to crosscheck the last online redo log that has been archived.
We will then backup the range of archive log files that have been generated since the time the
online backup of database was performed. These will be applied to the standby database as
part of the recovery process.
$ rman target / catalog rman/rman@rmanp cmdfile=backup_primarydb_arch.rcv
backup_primarydb_arch.rcv
run {
allocate channel ch1 type 'SBT_TAPE' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';
backup archivelog from logseq 16 until logseq 35 thread 1 ;
release channel ch1;
}

Preparing the Standby site for Data Guard

Create the directory structure on the standby site keeping in mind the mount points and
directory structure on the primary node.

Gavin Soorma

Page

9/15/2016

Remember while creating the $BDUMP, $UDUMP, $CDUMP and $PFILE directories that
the ORACLE_SID will be the same as the primary database.
Also try and ensure that the size of the mount points exactly mirror the primary node
otherwise we will face issues both while creating the standby database as well as when we
perform housekeeping tasks on the primary like adding or resizing a datafile.
We then need to create the password file using the same password for the user SYS as what
we have used while creating the password file on the primary node.
Copy the init<SID>.ora file from the primary node to $ORACLE_HOME/dbs on the standby
node.
Create a pfile from this spfile
$ export ORACLE_SID=<SID>
SQL> create pfile from spfile;
In this example, we are making the following assumptions:

ORACLE_SID=dba01

Primary Node = itlinux01

Standby Node = itlinux02

Mount Points on the primary node are /itlinux01db01 and /itlinux01db02

Mount points on the standby node are /itlinux02db01 and /itlinux02db02

Important:
Please ensure that the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT
parameters are carefully entered and every mount point which has a datafile or logfile of the
primary database is mentioned in the string list.
We will now edit the pfile to include parameters specific to the standby site .
FAL_SERVER=dba01_itlinux01
FAL_CLIENT=dba01_itlinux02
DB_FILE_NAME_CONVERT=(/itlinux01db01,/itlinux02db01,/itlinux01db02,/itlinux02db02)
LOG_FILE_NAME_CONVERT=(/itlinux01db01,/itlinux02db01,/itlinux01db02,/itlinux02db02)
STANDBY_ARCHIVE_DEST=/itlinux02db01/ORACLE/dba01/arch
STANDBY_FILE_MANAGEMENT=AUTO

Gavin Soorma

Page

9/15/2016

The following parameters will only apply when the standby database assumes the role of a
primary database after a switchover is performed. They can be excluded at this point from the
init.ora file, but a best practice is to include it now itself so that when a switchover is made in
the future, no further changes to the init.ora need to be made.
LOG_ARCHIVE_DEST_2='SERVICE=dba01_itlinux01 ARCH
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_DEST_1='LOCATION=/itlinux02db01/ORACLE/dba01/arch
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_FORMAT=%d_%t_%s.arc

Note: if we are using Oracle 10g then we can use the following parameters:
*.log_archive_dest_1='location=/itlinux02db01/ORACLE/dba01/arch','valid_for=(ALL_LOGFILES,ALL_RO
LES)'
*.log_archive_dest_2='SERVICE=dba01_itlinux01 VALID_for=(ONLINE_LOGFILES, PRIMARY_ROLE)
ARCH

After making the required changes to the init.ora create a spfile from this.
SQL > create spfile from pfile;

Network Configuration Files On Standby Machine

We will be configuring TNS aliases dba01_itlinux01 and dba01_itlinux02 in the
tnsnames.ora file these are nothing but the FAL_SERVER and FAL_CLIENT parameters
that we are defining in the init.ora file.
j
The tnsnames.ora and listener.ora are defined keeping the following in mind .

Client connections use the alias dba01 accessing via port 1526

Log shipping is using port 1522

On the primary machine there are two listeners running one for accepting client
connections to the database and the other for the log shipping. ( ports 1526 and 1522
respectively)

On the standby machine we have only one listener running which is being used for
the log shipping between the two sites ( port 1522). The listener listening on port
1526 will ONLY be started when the standby site assumes the role of a primary site
after a failover or switchover is performed.

Gavin Soorma

Page

9/15/2016

This is a sample copy of the tnsnames.ora and listener.ora files we need to ensure that
tnsnames.ora file on BOTH sites have the same entries for FAL_SERVER and FAL_CLIENT
as well.
listener.ora
# LISTENER.ORA Network Configuration File: /opt/oracle/product9206/network/admin/listener.ora
# Generated by Oracle configuration tools.
ITLINUX02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = itlinux02.hq.emirates.com)(PORT = 1522))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
)
)
)
#LISTENER.ORA Network Configuration File
#Created by Oracle Enterprise Manager Clone Database tool
SID_LIST_ITLINUX02 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product9206)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ANY")
)
(SID_DESC =
(GLOBAL_DBNAME = dba01)
(ORACLE_HOME = /opt/oracle/product9206)
(SID_NAME = dba01)
)
)
TRACE_LEVEL_ITLINUX02 = OFF
LOGGING_ITLINUX02 = OFF
TRACE_LEVEL_PRIMARY = OFF
LOGGING_PRIMARY = OFF
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = itlinux02.hq.emirates.com)(PORT = 1526))
)
SID_LIST_PRIMARY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=dba01)
(ORACLE_HOME = /opt/oracle/product9206)

Gavin Soorma

Page

9/15/2016

(SID_NAME = dba01)
)
)

tnsnames.ora
dba01_itlinux01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(HOST = itlinux01.hq.emirates.com)
(PORT = 1522)
)
)
(CONNECT_DATA =
(SID = dba01)
)
)
dba01_itlinux02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(HOST = itlinux02.hq.emirates.com)
(PORT = 1522)
)
)
(CONNECT_DATA =
(SID = dba01)
)
)
dba01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP)
(PROTOCOL = TCP)
(HOST = itlinux01.hq.emirates.com)
(PORT = 1526)
)
)
(CONNECT_DATA =
(SID = dba01)
)
)

Gavin Soorma

Page

9/15/2016

Start the Standby Database in NOMOUNT mode

RMAN will restore the controlfile backup that we had taken on the primary site to the
locations specified by the CONTROL_FILES parameter in the init.ora file.
We need to start the standby database in NOMOUNT mode.
SQL> startup nomount;

Restore and recover the Standby Database

On the PRIMARY Site we will start the standby database restore and recovery via RMAN using the
TSM tape backup that we had earlier taken.
Any RMAN duplicate command requires us to initiate an auxiliary channel as well.
The auxiliary channel will establish a connection to the remote standby database using the TNS alias
that we earlier created i.e dba01_itlinux02
The set until logseq should include the log sequence number which is one higher than the last log
sequence pertaining to the archive log files that we had backed up earlier. So if we have backed up
archive log files until sequence 1215 then we should include the command set until logseq=1216.

$ rman target / catalog rman/rman@rmanp \

auxiliary sys/syspassword @dba01_itlinux02 cmdfile=create_standby.rcv
create_standby.rcv
run {
allocate channel ch1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';
allocate auxiliary channel aux1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.mrhbld.opt)';
set until logseq=35 thread 1;
duplicate target database for standby
dorecover;
release channel ch1 ;
release channel aux1 ;
}

These are the last few lines from the output of the above command. Note the last archive log
sequence number applied was 1215 and NOT log sequence number 1216.
..
..

Gavin Soorma

Page

9/15/2016

channel clone_default: deleting archive log(s)

archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1213.log recid=3 stamp=583459445
archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1214.log thread=1 sequence=1214
channel clone_default: deleting archive log(s)
archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1214.log recid=2 stamp=583459433
archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1215.log thread=1 sequence=1215
channel clone_default: deleting archive log(s)
archive log filename=/itlinux21ar01/ORACLE/cisl/arch/arch1215.log recid=1 stamp=583459425
media recovery complete
Finished recover at 27-FEB-06
Finished Duplicate Db at 27-FEB-06
released channel: ch1
released channel: aux1
Recovery Manager complete.

Start Managed Recovery on the Standby Database

We will then put the standby database in managed recovery mode by issuing the following
command :
SQL> recover managed standby database disconnect;
Confirm that managed recovery is running by checking if the MRP process is running in the
background.
SQL> !ps -ef |grep mrp
oracle 17740 1 0 07:26 ?

00:00:00 ora_mrp0_dba01

If for any reason some further recovery is required, we can manually copy all the archive log
files from the production machine to the standby machine standby_archive_dest and do the
recovery MANUALLY via the following command:
SQL> RECOVER STANDBY DATABASE ( apply the required archive log file when
prompted)
This example will put the standby database in MAXIMUM PERFORMANCE mode. In case
we wish to configure the standby database for MAXIMUM AVAILABILITY or MAXIMUM
PROTECTION we need to create additional standby redo log files ( at least one more than
the number of the current online redo log files and make sure they are the same size as the
online redo log files).
We also will need to use the LGWR keyword in the parameter log_archive_dest_2 and issue
the command on the primary database while it is mount state.

Gavin Soorma

Page

9/15/2016