Switch over and switch back – Oracle 11g Manual Data Guard Part-II

In my Previous article we have covered How 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", status READY, 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, 2009, 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", status READY, 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,open_mode,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 on How to open Standby Database in Read ONLY mode.

Thank you. 🙂

Leave a Reply