CREATING A CASCADE STANDBY

CREATING A CASCADE STANDBY

TESTDB = Primary Database(Prod) -> 10.69.1.102
SB_TESTDB = Existing Standby -> 10.69.1.180
SB2_TESTDB = New Standby(Will be the new Prod) -> 10.69.1.98

Steps:

1. Create Standby SB2_TESTDB on the new server.

a. Copy init file and password file from backup to Standby server.
cd /backup/prod/TESTDB

find . -name "TESTDB*" -mtime -1
./TESTDB_20100413_105811_RESTORE_INFO.TXT
./TESTDB_20100413_105811_LOG.TXT
./TESTDB_20100413_105811_initTESTDB.ora
./TESTDB_20100413_105811_orapwdTESTDB
./TESTDB_20100413_105811_CONTROL_FILE.sql
./TESTDB_20100211_004502_CONTROL_FILE.sql

cd $ORACLE_HOME/dbs
cp /backup/dr/TESTDB/TESTDB_20100413_105811_initTESTDB.ora initTESTDB.ora
cp /backup/dr/TESTDB/TESTDB_20100413_105811_orapwdTESTDB orapwdTESTDB

b. Copy the standby controlfile and restore scripts from backup to Standby server.

find . -name "standby*" -mtime -1
./standbycontrol_TESTDB_20100413_105811.ctl

find . -name "Restore*" -mtime -1
./Restore10gControlFile_TESTDB_20100413_105811.rcv
./Restore10gDataTempFiles_TESTDB_20100413_105811.rcv
./Restore10gRedo_TESTDB_20100413_105811.rcv
./Restore9iRedo_TESTDB_20100211_004502.rcv

cd $HOME/TESTDB_scripts
cp /backup/dr/TESTDB/standbycontrol_TESTDB_20100413_105811.ctl .
cp /backup/dr/TESTDB/Restore10gControlFile_TESTDB_20100413_105811.rcv .
cp /backup/dr/TESTDB/Restore10gDataTempFiles_TESTDB_20100413_105811.rcv .

c. Create similar directory structure as Primary database server on Standby Server. Add instance name to /etc/oratab file.
$ cat /etc/oratab
TESTDB:/u01/app/oracle/product/10.2.0.4/db:N
$ setsid TESTDB

- Create the administrator directories under $HOME/admin/xxxxxx
(adump, bdump, udump, cdump, etc.).

- If necessary, create the datafile directories for the new database (/u02/oradata/TESTDB, etc.)

d. Modify init file on Standby if directory structure is different than Primary. If directory structure is similar then ignore db_file_name_covert and log_file_name_convert parameters.
*.db_file_name_convert='/u04/','/u02/','/u05/','/u03/','/u07','/u04/'(need to change if you have different datafile mount point)
*.log_file_name_convert=''(need to change if you have different redo logs mount point)
*.db_unique_name='sb2_TESTDB'
*.fal_client='sb2_TESTDB'
*.fal_server='TESTDB'
*.log_archive_config='DG_CONFIG=(TESTDB,sb2_TESTDB,sb_TESTDB)'
*.log_archive_dest_1='LOCATION=/u02/oradata/arch/TESTDB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sb2_TESTDB'
*.log_archive_dest_2='SERVICE=TESTDB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB'
*.log_archive_dest_3='SERVICE=sb_TESTDB VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sb_TESTDB'
*.standby_archive_dest='/u02/oradata/arch/TESTDB'
*.standby_file_management='auto'

e. Create spfile from pfile on Standby database and startup in nomount stage.
idle> create spfile from pfile='?/dbs/initTESTDB.ora';
File created.

f. For all of the restore scripts copied from above, you need to edit DataTemp (Data and Temp for 9i) scripts and change the locations to match the new file locations on the box restoring to.

EXAMPLE:

run {
allocate channel 'RCHL1' device type disk;
allocate channel 'RCHL2' device type disk;

SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/xxxxxx/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/xxxxxx/undotbs01.dbf';
.
.
SET NEWNAME FOR TEMPFILE 1 TO '/u03/oradata/xxxxxx/temp01.dbf';
.
.
set until SCN 145405595;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

g. Create and copy TNS entry for Standby to Primary database and copy TNS Entry for Primary database to Standby database server.

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

SB_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.180)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

SB2_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

h. Connect to RMAN and start the restore process.

$ setsid TESTDB

$ORACLE_HOME/bin/rman target=/ NOCATALOG

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 22 13:28:05 2009

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

connected to target database (not started)

RMAN> startup force nomount;
Oracle instance started

Total System Global Area 1610612736 bytes

Fixed Size 2073088 bytes
Variable Size 989859328 bytes
Database Buffers 603979776 bytes
Redo Buffers 14700544 bytes

RMAN>@ Restore9iControlFile_TESTDB_20100211_004502.rcv

RMAN> run {
2> set controlfile autobackup format for device type disk to ' /backup/prod/TESTDB/cf_%F_full_ctrl';
3> restore controlfile from autobackup maxdays 7;
4> alter database mount;
5> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 22-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1964 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20090622
channel ORA_DISK_1: autobackup found: /backup/prod/TESTDB/ cf_c-4234214917-20100210-01_full_ctrl
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/TESTDB/control01.ctl
output filename=/u02/oradata/TESTDB/control02.ctl
output filename=/u03/oradata/TESTDB/control03.ctl
Finished restore at 22-JUN-09

database mounted
released channel: ORA_DISK_1

RMAN> **end-of-file**


RMAN>@ Restore9iDataFiles_TESTDB_20100211_004502.rcv

RMAN> run {
2> allocate channel 'RCHL1' device type disk;
3> allocate channel 'RCHL2' device type disk;
4>
5> SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/TESTDB/system01.dbf';
6> SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/TESTDB/undotbs01.dbf';

…..

34> set until SCN 146575303;
35> restore database;
36> switch datafile all;
37> switch tempfile all;
38> recover database;
39> }
allocated channel: RCHL1
channel RCHL1: sid=1964 devtype=DISK


allocated channel: RCHL2
channel RCHL2: sid=1963 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

….

executing command: SET until clause

Starting restore at 09-FEB-10

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00046 to /u04/oradata/TESTDB/IDX_DATA02.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/prod/TESTDB/df_TESTDB_20100208_004501_tbl5f5sm_1_1_full_dbf tag=TESTDB_20100208_004501 params=NULL
channel ORA_AUX_DISK_1: restore complete
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /u03/oradata/TESTDB/SYSTEM01.dbf
Finished restore at 22-JUN-09

…



datafile 1 switched to datafile copy
input datafile copy recid=692 stamp=690212214 filename=/u02/oradata/TESTDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=693 stamp=690212214

Starting recover at 10-FEB-10

starting media recovery

channel RCHL1: starting archive log restore to default destination
channel RCHL1: restoring archive log
archive log thread=1 sequence=19117
channel RCHL1: restoring archive log
archive log thread=1 sequence=19118
channel RCHL1: restoring archive log
archive log thread=1 sequence=19119
channel RCHL1: restoring archive log
archive log thread=1 sequence=19120
channel RCHL1: restoring archive log
archive log thread=1 sequence=19121
channel RCHL1: restoring archive log

…

media recovery complete, elapsed time: 00:01:49
Finished recover at 22-JUN-09
released channel: RCHL1
released channel: RCHL2

RMAN> **end-of-file**

RMAN>
RMAN>

RMAN> quit



i. Shutdown the database and copy the Standby controlfile to replace the current one being used.

sqlplus '/as sysdba'
idle>shutdown immediate
idle>quit
cd $HOME/TESTDB_scripts

cp standbycontrol_TESTDB_20100210_004501.ctl /u01/app/oracle/oradata/TESTDB/control01.ctl
cp standbycontrol_TESTDB_20100210_004501.ctl /u02/oradata/TESTDB/control02.ctl
cp standbycontrol_TESTDB_20100210_004501.ctl /u03/oradata/TESTDB/control03.ctl

j. Add the following on BigBrother.ini to avoid email alert.
cd $HOME/local/dba
vi BigBrother.ini

# DONT_RUN_ANYTHING: TESTDB


Note: DONT' START THE STANDBY DATABASE YET!

2. Defer the log shipping from Primary(TESTDB)

SQL> alter system set log_archive_dest_state_2='defer';
System altered.

3. Change the parameter setting on Primary(TESTDB) to apply log on the new created standby(SB2_TESTDB)

- create a backup of spfile.

SQL> create pfile from spfile;

cp initTESTDB.ora initTESTDB.ora.04152010

- change the parameter setting.

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTDB,sb2_TESTDB)';
alter system set fal_client='TESTDB';
alter system set fal_server='sb2_TESTDB';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u02/oradata/arch/TESTDB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TESTDB';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=sb2_TESTDB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sb2_TESTDB';

4. Modify the existing tnsnames.ora to reflect this new setting on existing Primary(TESTDB).

- backup the existing tnsnames.ora
cp tnsnames.ora tnsnames.ora.04152010

TNS Entry:

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

SB2_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

5. Shutdown the existing standby(SB_TESTDB).

idle> alter database recover managed standby database cancel;
idle> shutdown immediate;

6. Change the parameter setting on exiting Standby(SB_TESTDB) to received log on the new created standby(SB2_TESTDB)

- create a backup of spfile.

SQL> create pfile from spfile;

cp initTESTDB.ora initTESTDB.ora.04152010

- change the parameter setting.

*.db_unique_name='sb_TESTDB' *.fal_client='sb_TESTDB'
*.fal_server='sb2_TESTDB'
*.log_archive_config='DG_CONFIG=(sb2_TESTDB,sb_TESTDB)'
*.log_archive_dest_1='LOCATION=/u02/oradata/arch/TESTDB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sb_TESTDB'
*.log_archive_dest_2='SERVICE=sb2_TESTDB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sb2_TESTDB'

- remove log_archive_dest_state_1 and log_archive_dest_state_2 from initTESTDB.ora

- create the new spfile
idle> create spfile from pfile='?/dbs/initTESTDB.ora';

7. Modify the existing tnsnames.ora to reflect this new setting on existing Standby(SB_TESTDB).

- backup the existing tnsnames.ora
cp tnsnames.ora tnsnames.ora.04152010

TNS Entry:

SB_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.180)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

SB2_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)




8. Check the Primary(TESTDB) if the parameter settings are correct.

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
fal_client string TESTDB
fal_server string sb2_TESTDB

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_config string DG_CONFIG=(TESTDB,sb2_TESTDB)
log_archive_dest string
log_archive_dest_1 string LOCATION=/u02/oradata/arch/pri
mdb/ VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=TESTDB
log_archive_dest_2 string SERVICE=sb2_TESTDB VALID_FOR=(
ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=sb2_TESTDB

9. Add the new Standby(SB2_TESTDB) on the listener and restart the listener.

- Backup the listener.ora

cd $ORACLE_HOME/network/admin
cp listener.ora listener.ora.04292010

- Add the following

(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0.4/db)
(SID_NAME = TESTDB)
)

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.98 )(PORT = 1521))

- Stop the Listener
lsnrctl stop

- Start the Listener
lsnrctl start


10. Check the new Cascading Standby(SB2_TESTDB) if the parameter settings are correct.

idle> startup nomount;
idle> alter database mount standby database;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
fal_client string sb2_TESTDB
fal_server string TESTDB

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_config string DG_CONFIG=(TESTDB,sb2_TESTDB,s
b_TESTDB)
log_archive_dest string
log_archive_dest_1 string LOCATION=/u02/oradata/arch/pri
mdb/ VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=sb2_p
rimdb
log_archive_dest_2 string SERVICE=TESTDB VALID_FOR=(ONLI
NE_LOGFILES,PRIMARY_ROLE) DB_U
NIQUE_NAME=TESTDB
log_archive_dest_3 string SERVICE=sb_TESTDB VALID_FOR=(STAN
DBY_LOGFILES,STANDBY_ROLE) DB_
UNIQUE_NAME=sb_TESTDB

11. Check the old Standby(SB_TESTDB) if the parameter settings are correct.

idle> startup nomount;
idle> alter database mount standby database;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
fal_client string sb_TESTDB
fal_server string sb2_TESTDB

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_archive_config string DG_CONFIG=(sb2_TESTDB,sb_primd
b)
log_archive_dest string
log_archive_dest_1 string LOCATION=/u02/oradata/arch/pri
mdb/ VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=sb_pr
imdb
log_archive_dest_2 string SERVICE=sb2_TESTDB VALID_FOR=(
ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=sb2_TESTDB



12. Start the new Standby(SB2_TESTDB)

- if you have renamed the datafile to a new location during the RMAN restore do the following:

Shutdown the standby:
Idle>shutdown immediate
Idle>startup nomount;
Idle>alter database mount standby database;
Idle>alter system set standby_file_management=manual;
Idle>alter database rename file '/u03/oradata/undotbs01.dbf' TO '/u02/oradata/undotbs01.dbf';
Idle>alter system set standby_file_management=auto;
Idle>alter database recover managed standby database disconnect;

- If not do the following:
idle> alter database recover managed standby database disconnect;

13. Start the old Standby(SB_TESTDB)

idle> alter database recover managed standby database disconnect;
14. Enable log shipping on Primary(TESTDB)

SQL> alter system set log_archive_dest_state_2='enable';
System altered.

15. Check all the Standby if archive logs are being applied.

Primary(TESTDB)

SQL>alter system switch logfile;
SQL>/

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/arch/TESTDB/
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61


New Standby(SB2_TESTDB)

SQL> select sequence#, archived, applied from v$archived_log order by sequence#;

SEQUENCE# ARC APP
---------- --- ---
56 YES YES
57 YES YES
58 YES YES
59 YES YES
60 YES YES

Old Standby(SB_TESTDB)

SQL> select sequence#, archived, applied from v$archived_log order by sequence#;

SEQUENCE# ARC APP
---------- --- ---
56 YES YES
57 YES YES
58 YES YES
59 YES YES
60 YES YES

Note: Shipping of archivelogs on the new standby might take a few minutes before applying.
All the Sequence and DGID are being configured on the new Standby(SB2_TESTDB). Ignore the errors
on the alert log this is normal on the first startup on Standby.