Oracle Database Role Change

                                        Objective

If the production database becomes unavailable because of a planned or an unplanned outage, Data Guard Role Management Services quickly switch a chosen standby database to the production role, minimizing the downtime and preventing data loss.

Approach

Switchover [Role change]

Allows the primary database to switch roles with its standby database, there is no data loss during a switchover. After a switchover, each database continues to participate in the Data Guard configuration with its new role. It is planned activity.

A switchover takes place in two phases. In the first phase, the existing primary database undergoes a transition to a standby role. In the second phase, a standby database undergoes a transition to the primary role.

Steps to Switchover to physical standby database

Step 1à  on the primary db

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select database_role from v$database;

SQL> Select switchover_status from v$database;

SQL> Alter database commit to switchover to physical standby;

 OR   [if output of the above query is SESSIONS ACTIVE]

SQL> Alter database commit to switchover to standby with session shutdown;

SQL> Shutdown immediate;

SQL> exit;

SQL> sqlplus / as sysdba

SQL> Startup nomount

SQL> Alter database mount standby database;

Step 2à On the Standby db

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select switchover_status from v$database;

SQL> Alter database commit to switchover to primary;

OR [if output of the above query is SESSIONS ACTIVE]

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

SQL> Shut immediate;

SQL> exit;

SQL> sqlplus / as sysdba

SQL> startup

Step 3à On the New Standby db [Previously Primary]

Start the recovery process

Runà cmd

SQL> sqlplus / as sysdba

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

SQL> exit;

Failover

During a failover, standby database takes the primary database role and the old primary database is rendered unable to participate in the configuration. Depending on the protection mode under which the old primary database was operating before the failover, there might be little or no data loss during a failover. A failover is typically used only when a primary database becomes unavailable and there is no possibility of restoring it to service within a reasonable amount of time.

Steps to failover to physical standby database

Runà cmd

SQL> sqlplus / as sysdba

SQL> Select database_role from v$database;

SQL> alter database recover managed standby database finish force;

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

SQL> alter database open;

SQL> select name, open_mode, database_role from v$database;

          Note: Shutdown and Backup the new primary database.

Primary database can be restored from physical standby database (Currently primary) by following steps….

1. Take offline Backup of the new primary [Previously Physical Standby]

2. Restore offline backup on the original primary server

3. Configure new physical standby on the primary server

4. Perform switchover

Note: Backup the Original Primary Database