Connect
Physical Standby
using RMAN
Standby DB
Connect
_____________________________________________________________________________________________________
Creation of oracle 9i standby datbase through RMAN:-
PRE-REQUISITES: RMAN catalog in use and use of external media management layer
configure on both machines.
PRIMARY SITE: ITLINUXDEVBLADE07
STANDBY SITE:ITLINUXDEVBLADE08
DATABASE: dgtest9i.
Login to catalog and set configuration from primary database:
Export ORACLE_SID=dgtest9i
rman target / catalog rman/rman@rmanp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO \’SBT_TAPE\’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
\’/u01/ORACLE/dgtest9i/arch/%F\’;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE \’SBT_TAPE\’ TO \’%F\’;
CONFIGURE DEVICE TYPE \’SBT_TAPE\’ PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE \’SBT_TAPE\’ TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE \’SBT_TAPE\’ TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE \’SBT_TAPE\’ PARMS \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
FORMAT \’%d.%T.%s.%p.%c.%t\’;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO \’/opt/oracle/product9204/dbs/snapcf_dgtest9i.f\’;
PRIMARY SITE-ITLINUXDEVBLADE07:
Additional entries for init.ora on primary site for Dataguard.
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.db_file_name_convert=\’/opt/oracle/dgtest9i/\’,\’/opt/oracle/oradata/dgtest9i/\’
*.log_file_name_convert=\’/opt/oracle/ dgtest9i/\’,\’/opt/oracle oradata/dgtest9i/\’
*.fal_client=\’DGTEST9I_BLADE07\’*.fal_server=\’DGTEST9I_BLADE08\’
*.log_archive_dest_1=\’LOCATION=/opt/oracle/dgtest9i/arch\’
*.standby_archive_dest=/opt/oracle/dgtest9i/arch
*.log_archive_dest_2=\’SERVICE=dgtest9i_blade08 lgwr sync affirm nodelay\’
*.log_archive_dest_state_1=\’ENABLE\’
*.log_archive_dest_state_2=\’ENABLE\’………
*.log_archive_format=\’arch%s.log\’
*.log_archive_start=TRUE
*.standby_file_management=\’AUTO\’
STANDBY SITE: ITLINUXBLADE08:
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.db_file_name_convert=\’/opt/oracle/oradata/dgtest9i/\’,\’/opt/oracle/dgtest9i/\’
*.log_file_name_convert=\’/opt/oracle/oradata/dgtest9i/\’,\’/opt/oracle/dgtest9i/\’
*.fal_client=\’DGTEST9I_BLADE08\’
*.fal_server=\’DGTEST9I_BLADE07\’
*.log_archive_dest_1=\’LOCATION=/opt/oracle/dgtest9i/arch\’
*.standby_archive_dest=/opt/oracle/dgtest9i/arch
*.log_archive_dest_2=\’SERVICE=dgtest9i_blade07 lgwr sync affirm nodelay\’
*.log_archive_dest_state_1=\’ENABLE\’
*.log_archive_dest_state_2=\’DEFER\’………
*.log_archive_format=\’arch%s.log\’
*.log_archive_start=TRUE
*.standby_file_management=\’AUTO\’
2. Create orapwd file in $ORACLE_HOME/dbs
> orapwd file=orapwdgtest9i entries=10 password=oracle
3. Make changes to tnsnames.ora and listener.ora to include log_shipping entries
on both machines and both listeners should be running
4. Export ORACLE_SID=dgtest9i ( on standby site)
SQL> startup nomount ;
PRIMARY SITE- ITLINUXDEVBLADE07:
Four scripts will be used….Please create these .rcv files in /opt/oracle/rman/scripts
area.
cd /opt/oracle/rman/scripts.
Create the four scripts…
1. backup_database.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup current controlfile
for standby;
release channel ch1 ;
}
2. standby_control_file.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup current controlfile
for standby;
release channel ch1 ;
}
3. arch_backup_range.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup archivelog
from logseq
1 until logseq
32 thread 1 ;
release channel ch1 ;
}
4. makeme_standby.rcv
run {
allocate channel ch1 type \’sbt_tape\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
allocate auxiliary channel aux1 type \’sbt_tape\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
set limit channel ch1 kbytes=3145728
readrate
200;
set until sequence = 32 thread =1;
duplicate target database for standby
dorecover
;
release channel ch1 ;
release channel aux1 ;
}
Now start the process on the PRIMARY site.
1. BACKUP DATABASE PLUS ARCHIVELOGS….
rman target / catalog rman/rman@rmanp cmdfile=backup_database.rcv
After backup is complete.
2. CREATE STANDBY CONTROLFILE.
rman target / catalog rman/rman@rmanp cmdfile=standby_control_file.rcv
After standby controlfile is created.
3. SWITCH A FEW LOGS.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> alter system switch logfile
2 /
System altered.
SQL> /
System altered.
SQL>
cd $ARCV
-rw-r—– 1 oracle dba 13312 Apr 22 12:35 arch30.log
-rw-r—– 1 oracle dba 2560 Apr 22 12:40 arch31.log
-rw-r—– 1 oracle dba 1536 Apr 22 12:40 arch32.log …..LAST LOG
FILE…NOTE DOWN THIS NUMBER.
4. BACKUP ARCHIVE LOGS
[oracle@itlinuxdevblade07 arch]$rman target / catalog rman/rman@rmanp cmdfile=arch_backup_range.rcv
( Last log seq should be as per last log in archive area)
After archivelogs are backed up…
[oracle@itlinuxdevblade07 rman]$ sqlplus rman/rman@rmanp
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 22 12:46:00 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> Select sequence# from rc_backup_redolog where db_name=\’DGTEST9I\’;
Last few…
30
31
32
96 rows selected.
SQL>
Make sure listeners used for log-shippng on both sides are up for alias
dgtest_blade07 and dgtest_blade08..
Check connection to standby database….which should be in NOMOUNT state on
ITLINUXDEVBLADE08.
[oracle@itlinuxdevblade07 admin]$ rman target / auxiliary
sys/oracle@DGTEST9I_BLADE08
Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DGTEST9I (DBID=227642821)
connected to auxiliary database: dgtest9i (not mounted) …..IMPORTANT to
confirm primary is not being touched.
RMAN>EXIT
Make sure path of db directory(dgtest9i) exists for area mentioned in
db_file_name_convert and log_file_name_convert in ITLINUXDEVBLADE08 machine….i.e
/opt/oracle/dgtest9i.
Now you are ready to create standby…
NOW RUN THE ACTUAL CREATION OF STANDBY….
[oracle@itlinuxdevblade07 rman]$ rman target / catalog rman/rman@rmanp
auxiliary sys/oracle@DGTEST9I_BLADE08 cmdfile=make_standby.rcv
Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DGTEST9I (DBID=227642821)
connected to recovery catalog database
connected to auxiliary database: dgtest9i (not mounted)
RMAN> run {
2> allocate channel ch1 type \’sbt_tape\’ parms \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
3> allocate auxiliary channel aux1 type \’sbt_tape\’ parms \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
4> set limit channel ch1 kbytes=3145728 readrate 200;
5> set until sequence = 32 thread =1;
> duplicate target database for standby dorecover
7> ;
8> release channel ch1 ;
9> release channel aux1 ;
10> }
11>
allocated channel: ch1
channel ch1: sid=10 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.2.4.0
allocated channel: aux1
channel aux1: sid=14 devtype=SBT_TAPE
channel aux1: Data Protection for Oracle: version 5.2.4.0
executing command: SET until clause
Starting Duplicate Db at 22-APR-06
printing stored script: Memory Script
{
restore clone standby controlfile to clone_cf;
replicate clone controlfile from clone_cf;
sql clone \’alter database mount standby database\’;
}
executing script: Memory Script
Starting restore at 22-APR-06
channel aux1: starting datafile backupset restore
channel aux1: restoring controlfile
output filename=/opt/oracle/dgtest9i/control01.ctl
channel aux1: restored backup piece 1
piece handle=d_DGTEST9I_2chh5e82_1_1 tag=TAG20060422T123930 params=NULL
channel aux1: restore complete
Finished restore at 22-APR-06
replicating controlfile
input filename=/opt/oracle/dgtest9i/control01.ctl
sql statement: alter database mount standby database
printing stored script: Memory Script
{
set until scn 1361050;
set newname for datafile 1 to
\”/opt/oracle/dgtest9i/system01.dbf\”;
set newname for datafile 2 to
\”/opt/oracle/dgtest9i/undotbs01.dbf\”;
set newname for datafile 3 to
\”/opt/oracle/dgtest9i/users01.dbf\”;
restore
check readonly
clone database
;
}
executing script: Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-APR-06
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/dgtest9i/system01.dbf
restoring datafile 00002 to /opt/oracle/dgtest9i/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/dgtest9i/users01.dbf
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.70.1.2.588427511 tag=TAG20060422T120511 params=NULL
channel aux1: restore complete
Finished restore at 22-APR-06
printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=32 stamp=588433731
filename=/opt/oracle/dgtest9i/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=33 stamp=588433731
filename=/opt/oracle/dgtest9i/users01.dbf
printing stored script: Memory Script
{
set until scn 1361050;
recover
standby
clone database
delete archivelog
;
}
executing script: Memory Script
executing command: SET until clause
Starting recover at 22-APR-06
starting media recovery
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=29
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.71.1.2.588427538 tag=TAG20060422T120538 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch29.log thread=1 sequence=29
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch29.log recid=1
stamp=588433731
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=30
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.74.1.1.588429302 tag=TAG20060422T123502 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch30.log thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch30.log recid=2
stamp=588433735
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=31
channel aux1: restored backup piece 1
piece handle=d_DGTEST9I_2ehh5eg0_1_1 tag=TAG20060422T124344 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch31.log thread=1 sequence=31
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch31.log recid=3
stamp=588433737
media recovery complete
Finished recover at 22-APR-06
Finished Duplicate Db at 22-APR-06
released channel: ch1
released channel: aux1
Recovery Manager complete.
[oracle@itlinuxdevblade07 rman]$sql
Now create standby redologs in standby and primary sites.
Sql>
Alter database add standby logfile ‘ /opt/oracle/dgest9i/standby01,log’ size
10m;
The size of the standby redologs must be the same as the primary online redologs.
Create one more standby redolog that number of online redologs.
Next on standby …recover managed standby database disconnect;…to start
managed recovery.
The database protection_mode will be maximum performance.
Select protection_mode from v$database;
To change to maximum availability, a restart of the primary database is
required.
To change the protection_mode to maximum availability:
Shutdown primary.
shutdown immediate;
startup mount;
Alter database set standby database to maximize availability;
alter database open;
Warning: The alter database open command will fail if there are no standby logs
created on standby site when database protection_mode is maximum availability.
