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
or
/opt/tivoli
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.
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 ;
}
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.
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.ora file from the
primary node to $ORACLE_HOME/dbs on the standby node.
Create a pfile from this spfile
$
export ORACLE_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
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_ROLES)'
*.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.
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)
(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)
)
)
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.
……………………..
………………………………………..
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.
No hay comentarios:
Publicar un comentario