Create a RAC Standby Database From Overnight RMAN Tape Backups

Here is a practical example of building a RAC standby database by using overnight  NetBackup RMAN backups.

Primary RAC servers: racnode1,racnode2
Standby RAC servers: stdbynode1,stdbynode2
Primary Database Name:  RACTEST
Primary Instance Names: RACTEST1/2
Standby Instance Names: STBTEST1/2

RAC Database version: 11.2.0.x

1)On primary database, check most recent RMAN backups are available.

$ rman target /  catalog rman/password@rman
RMAN> list backup of database;

2) On primary, create a standby controlfile.

SQL> alter database create standby controlfile as '/tmp/RACTEST_standby.ctl';
Database altered.

or

RMAN>  backup device type disk format '/tmp/RACTEST_standby.ctl' current controlfile for standby;

3)Copy standby controlfile  onto standby server stdbynode1:/tmp.

[RACTEST1] oracle@racnode1:/tmp$ scp RACTEST_standby.ctl stdbynode1:/tmp/

4) Copy primary database password file onto standby database server.

[RACTEST1] oracle@racnode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ scp orapwRACTEST1 stdbynode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTBTEST1
oracle@stdbynode1's password:
orapwRACTEST1
100% 1536 1.5KB/s 00:00
[RACTEST1] oracle@racnode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$scp orapwRACTEST1 stdbynode2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTBTEST2

5) On primary database, make change for FORCE LOGGING, only if database is not in “force logging” yet.

 SQL> ALTER DATABASE FORCE LOGGING;

6)  On primary database,  change or add below parameters .

DB_UNIQUE_NAME=RACTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(STBTEST,RACTEST)'
DB_FILE_NAME_CONVERT='STBTEST','RACTEST'
LOG_FILE_NAME_CONVERT='STBTEST','RACTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=STBTEST
FAL_CLIENT=RACTEST
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=RACTEST'
LOG_ARCHIVE_DEST_2='SERVICE=STBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBTEST'

7) On standby database, change or add below parameters.

DB_NAME=RACTEST
DB_UNIQUE_NAME=STBTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACTEST,STBTEST)'
DB_FILE_NAME_CONVERT='RACTEST','STBTEST'
LOG_FILE_NAME_CONVERT='RACTEST','STBTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=RACTEST
FAL_CLIENT=STBTEST
*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=STBTEST'
*.LOG_ARCHIVE_DEST_2='SERVICE=RACTEST LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACTEST'

8) On standby database, set standby *.control_files=(‘+DATA1′,’+FRA’), and startup database in nomount mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.5366E+10 bytes
Fixed Size 2165488 bytes
Variable Size 7650411792 bytes
Database Buffers 7650410496 bytes
Redo Buffers 63135744 bytes

9) On standby database, restore standby control files. Update parameter “control_files” accordingly.

[STBTEST1] oracle@stdbynode1:/tmp$ rman target /
RMAN> restore controlfile to '+FRA' from '/tmp/RACTEST2_standby.ctl';
RMAN> restore controlfile to '+DATA1'  from '/tmp/RACTEST2_standby.ctl';
control_files = '+DATA1/STBTEST/CONTROLFILE/current.343.925553399','+fra/STBTEST/CONTROLFILE/current.642.925553371'

10) On standby database, shutdown and mount standby database.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1.0262E+11 bytes
Fixed Size 2265224 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 8.9657E+10 bytes
Redo Buffers 74416128 bytes

SQL> alter database mount standby database;
Database altered.

11) On standby database, restore datafiles from overnight RMAN backups.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ cat restore_STBTEST.rman

run {
allocate channel c1 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c2 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c3 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c4 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ nohup rman target / catalog rman/password@rman cmdfile=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.rman log=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.log &
[1] 19448

12) On standby database, monitor the progressing by querying gv$session_longops.

SQL> select INST_ID,OPNAME,SOFAR*8/1024/ELAPSED_SECONDS,ELAPSED_SECONDS/60,TIME_REMAINING/60 from gv$session_longops where OPNAME like 'RMAN%' and TIME_REMAINING>6 order by 2;
 INST_ID OPNAME SOFAR*8/1024/ELAPSED_SECONDS ELAPSED_SECONDS/60 TIME_REMAINING/60
---------- ---------------------------------------------------------------- ---------------------------- ------------------ -----------------
 1 RMAN: aggregate input 425.803241 20.1666667 102.85
 1 RMAN: full datafile restore 105.103955 21.55 103.016667
 1 RMAN: full datafile restore 112.092109 21.9 94.7833333
 1 RMAN: full datafile restore 113.487487 21.8833333 93.4333333
 1 RMAN: full datafile restore 108.213722 21.8833333 99.35

13) Restore gap archivelogs.

RMAN>restore archivelog from logseq=100 until logseq=200 thread=1;

RMAN>restore archivelog from logseq=80 until logseq=180 thread=2;

14)  Add standby online logfiles ( n + 1 ) into both primary and standby database.

SQL> alter database add standby logfile thread 1 group 11 size 1024m;

Database altered.
....
...
..
.

15) On standby database,  create spfile if not yet.

SQL> create spfile='+DATA1' from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSTBTEST1.ora';

File created.

$ echo spfile='+DATA1/STBTEST/PARAMETERFILE/spfile.1323.925567677' > initSTBTEST1.ora

$ scp initSTBTEST1.ora stdbynode2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSTBTEST2.ora

16) On standby database, put standby database under recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.0489E+10 bytes
Fixed Size 2162560 bytes
Variable Size 5704253568 bytes
Database Buffers 4764729344 bytes
Redo Buffers 17899520 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

17) Add standby database into cluster.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ srvctl add database -n STBTEST -o /u01/app/oracle/product/11.2.0/dbhome_1 -d STBTEST -r physical_standby
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ srvctl modify database -d STBTEST -s mount
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST1 -n stdbynode1
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/11.2.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST2 -n stdbynode2

18) Configure archivelog deletion policy for both primary and standby databases.

a) On primary, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

b) On standby, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

19) Add a static registration into LISTENER.ora on all nodes of both primary and standby database.

#
# DGMGRL>switchover to <standby> Fails with ORA-12514 (Doc ID 1582927.1)
# Each Standby needs a static registration for the switchover to work
#
-- for node racnode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST1)
 )
 )

-- for node racnode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST2)
 )
 )

-- for node stdbynode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST1)
 )
 )

-- for node stdbynode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST2)
 )
 )


DGMGRL> show instance 'STBTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'STBTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST2)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.22)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST2)(SERVER=DEDICATED)))'
Advertisements
This entry was posted in DataGuard, GI and RAC, RMAN Restore and Recovery and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s