Oracle 10g Dataguard Switchover.

This article is based on the dataguard tutorial written earlier.

Configure the follwoing parameters on both the databases.

SQL> show parameter fal_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
fal_server                           string      standby

SQL> alter system set fal_server = 'orcl','standby';

System altered.

SQL> show parameter fal_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
fal_server                           string      orcl, standby



On primary database.

Execute the following commands on the primary database and make sure
that redo logs are being applied to the standby server without any 
problem.

SQL> alter system switch logfile;

System altered.
 
SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- --------------------------------------------------------
VALID

Since the status column shows valid that means redo transportation is
working fine.


On standby server

Execute the following comman and verify that redo apply service
is working, which is shown by the MRP0 process.

SQL> select client_process, process, sequence#, status

 from v$managed_standby;

CLIENT_P PROCESS    SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH     ARCH               0 CONNECTED
ARCH     ARCH               0 CONNECTED
ARCH     ARCH               0 CONNECTED
ARCH     ARCH               0 CONNECTED
N/A      MRP0              15 WAIT_FOR_LOG
UNKNOWN  RFS                0 IDLE
LGWR     RFS               15 IDLE
N/A      RFS                0 IDLE

8 rows selected.

If redo apply service is not working execute the following 

commands to start the redo apply.

SQL> startup mount

SQL> alter database recover managed standby database disconnect;

after this check for the redo apply service again and if the
status of MRP0 process is WAIT_FOR_GAP, then the switchover
operation cannot occur until the gap is resolved.

On primary database

Since the standby database was configured with standby redo log files
make sure the primary database also has standby redo log files
by executing the following command.



 SQL>  select group#, status, type, count(*) as "Members"
       from v$logfile
       group by group#, status, type
       order by group#


    GROUP# STATUS  TYPE       Members
---------- ------- ------- ----------
         1         ONLINE           1
         2         ONLINE           1
         3         ONLINE           1
         4         STANDBY          1
         5         STANDBY          1
         6         STANDBY          1
         7         STANDBY          1

7 rows selected.



SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

The switchover_status should show a value of "TO STANDBY", 
but instead it is showing a value of "SESSIONS ACTIVE",
So we have to use the "WITH SESSION SHUTDOWN" clause 
in the switchover command.

If status is TO STANDBY

SQL> alter database commit to switchover to physical standby;

or If the status is SESSIONS ACTIVE.

SQL> alter database commit to switchover to physical
 standby with session shutdown;

Database altered.

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


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size             100664912 bytes
Database Buffers          180355072 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL>

On standby database.


SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY


If SWITCHOVER_STATUS is TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

or if SWITCHOVER_STATUS returned SESSIONS ACTIVE in the
previous step, use:

SQL> alter database commit to switchover to primary 
 with session shutdown;

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>


SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

On former primary database.

SQL> alter database recover managed standby database disconnect;

Database altered.