Switch over and switch back – Oracle 11g Manual Data Guard Part-II
Publishedabout a year agobyJignesh Jethwa
In my PrevIoUs article we have coveredHow to configure oracle 11g Manual Data Guard,Now we will look into switch-over and switch-back activity.
Switch-over Pre-requisites:
One:
Check listener status on primary and standby database.
Primary Database Listener:
[oracle@PR ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31 Copyright (c) 1991,2009,Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 11-MAY-2016 22:44:06 Uptime 0 days 0 hr. 5 min. 25 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521))) Services Summary... Service "RTS" has 1 instance(s). Instance "RTS",status READY,has 1 handler(s) for this service... Service "RTSXDB" has 1 instance(s). Instance "RTS",has 1 handler(s) for this service... The command completed successfully
Standby Database Listener:
[oracle@DR ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55 Copyright (c) 1991,Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 11-MAY-2016 22:45:18 Uptime 0 days 0 hr. 6 min. 37 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521))) Services Summary... Service "RTSDR" has 1 instance(s). Instance "RTS",has 1 handler(s) for this service... The command completed successfully
Two:
Check database mode on primary and standby database.
Primary Database Mode:
sql> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- RTS READ WRITE
Standby Database Mode:
sql> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- RTS MOUNTED
Three:
Check database role on primary and standby database.
Primary Database:
sql> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- RTS PRIMARY
Standby Database:
sql> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- RTS PHYSICAL STANDBY
Four:
No datafiles should be in recovery mode on Primary and Standby database.
sql> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER'); no rows selected
Five:
No datafiles should be in offline mode on Primary and Standby database.
sql> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE'); no rows selected
Six:
No datafiles should be in backup mode on Primary and Standby database.
sql> select file# from v$backup where status='ACTIVE'; no rows selected
Switch-over steps:
Step-A:
Shutdown primary database: sql> shut immediate; Database closed. Database dismounted. ORACLE instance shut down.
Step-B:
Copy control file to temporary location on primary database.
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/ [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/
Step-C:
Copy SPFile to temporary location on primary database.
[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/
Step-D:
Copy Redo log files to temporary location on primary database.
[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/ [oracle@PR RTS]$ ll *.log -rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log -rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log -rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log [oracle@PR RTS]$ cp *.log /home/oracle/prtemp/
Step-E:
Copy needed archive log to DR site.
[oracle@PR RTS]$ cd /home/oracle/archdir/ [oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/
Step-F:
Get primary database to READ ONLY mode.
sql> startup mount; ORACLE instance started. Total System Global Area 755769344 bytes Fixed Size 2217184 bytes Variable Size 473959200 bytes Database Buffers 276824064 bytes Redo Buffers 2768896 bytes Database mounted.
sql> alter database open read only; Database altered.
Step-G:
Apply all needed archive logs to standby database.
sql> recover standby database until cancel; ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1 ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf ORA-00280: change 957836 for thread 1 is in sequence #15 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1 ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf ORA-00280: change 967269 for thread 1 is in sequence #16 ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery
ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1 ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf ORA-00280: change 967272 for thread 1 is in sequence #17 ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery . .. ... ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1 ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf ORA-00280: change 969653 for thread 1 is in sequence #29 ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Step-H:
Shutdown standby database.
sql> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
Step-I:
Copy control file to temporary location on standby database.
[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/ [oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/
Step-J:
Copy SPFile to temporary location on standby database.
[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/
Step-K:
On Standby database,copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).
[oracle@DR ~]$ cd /home/oracle/drtemp/ [oracle@DR drtemp]$ ll total 19044 -rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl -rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl -rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora [oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr
Step-L:
On Primary database,copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).
[oracle@PR ~]$ cd /home/oracle/prtemp/ [oracle@PR prtemp]$ ll total 172656 -rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl -rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl -rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log -rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log -rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log -rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora [oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr
Step-M:
On Standby database,replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:
[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl [oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl [oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora [oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/
Step-N:
Perform role switching on old standby database: (old standby database to new Primary database)
one:
Startup mount old standby database.
[oracle@DR ~]$ sqlplus / as sysdba ... Connected to an idle instance. sql> startup mount; ORACLE instance started. Total System Global Area 755769344 bytes Fixed Size 2217184 bytes Variable Size 473959200 bytes Database Buffers 276824064 bytes Redo Buffers 2768896 bytes Database mounted.
Two:
Recover database.
sql> recover database; Media recovery complete.
Three:
Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)
Four:
Open database:
sql> alter database open; Database altered.
Five:
Create new tempfile:
sql> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited; Tablespace altered.
On Primary database,replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:
[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl [oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl [oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora
Shutdown old primary database.
sql> shut immediate;
Step-O:
Perform role switching on old primary database: (old primary database to new standby database)
One:
Start database with nomount state:
sql> startup nomount;
Two:
Alter database to mount state with standby database:
sql> alter database mount standby database; Database altered.
Cheers!!Switch over activity successfully carried out,now lets perform post checks on both the database servers:
Post checks:
Check database mode and role on new primary database,it shall be READ WRITE and Primary.
sql> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- RTS READ WRITE PRIMARY
Check database mode and role on new standby database,it shall be MOUNTED and PHYSICAL STANDBY.
sql> select name,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- RTS MOUNTED PHYSICAL STANDBY
We are done with switch over activity.
Switch-back activity steps:
There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!
Your suggestions and comments are highly appreciated,if any.
Stay tuned with my next article onHow to open Standby Database in Read ONLY mode.
Thank you.
