Saturday, October 3, 2015

Perform Switchover Operation In Oracle Data Guard.

1. Verify primary database whether it is possible to Switchover .

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE ;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
2. Check that there is no active users connected to the databases.
SQL> SELECT DISTINCT OSUSER,USERNAME,STATUS FROM V$SESSION WHERE STATUS = 'ACTIVE';
OSUSER                         USERNAME                       STATUS
------------------------------ ------------------------------ --------
oracle                         CBSUSER                             ACTIVE
oracle                                                        ACTIVE

3. If session are active check any process are active, if process are active inform user to commite the transaction.

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE='USER' AND SID <>(SELECT DISTINCT SID FROM V$MYSTAT);
no rows selected

4. Switch current logfile.

SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
SQL>

5. Initiate the switchover.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
SQL>

6. Now shutdown Primary database.

SQL> SHUTDOWN IMMEDIATE;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

7. Now connect in primary database and start database in nomount mode.

[oracle@drsdb1 dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 12 12:22:39 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> CONN /AS SYSDBA
Connected to an idle instance.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size                  2228024 bytes
Variable Size             805306568 bytes
Database Buffers          469762048 bytes
Redo Buffers                8769536 bytes

8. Mount database as standby database :

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL>

9. Start the recovery :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
--------------- IN Standby Site (Presently Primary) ---------

10. Cancel Recovery :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>

11. Use the following  if there is time lag is used.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER
*
ERROR at line 1:
ORA-16177: media recovery is not required

SQL>

12. Now switchover in Standby as Primary database :

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL>

13. Now start the Database

SQL> ALTER DATABASE OPEN;
Database altered.
SQL>

14. Now check Primary (Old Standby ) Database.

SQL> SET LINE 200
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,SWITCHOVER_STATUS FROM V$DATABASE;
NAME      OPEN_MODE            DB_UNIQUE_NAME                 SWITCHOVER_STATUS
--------- -------------------- ------------------------------ --------------------
SBLCBS    READ WRITE           DCDBCBS                         TO STANDBY
SQL>