Oracle Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

    -- Convert primary database to standby
    CONNECT / AS SYSDBA
    ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

    -- Shutdown primary database
    SHUTDOWN IMMEDIATE;

    -- Mount old primary database as standby database
    STARTUP NOMOUNT;
    ALTER DATABASE MOUNT STANDBY DATABASE;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

    -- Convert standby database to primary
    CONNECT / AS SYSDBA
    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

    -- Shutdown standby database
    SHUTDOWN IMMEDIATE;

    -- Open old standby database as primary
    STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.
Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
    ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.
Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.
Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE OPEN READ ONLY;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.
Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;

Make sure managed recovery is disabled.

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.

    SELECT flashback_on FROM v$database;

    FLASHBACK_ON
    ------------------
    NO

    ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
    ALTER DATABASE OPEN;
    SELECT flashback_on FROM v$database;

    FLASHBACK_ON
    ------------------
    RESTORE POINT ONLY

    SQL>

You can now do treat the standby like any read-write database.

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
    SHUTDOWN IMMEDIATE;
    STARTUP NOMOUNT;
    ALTER DATABASE MOUNT STANDBY DATABASE;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    SELECT flashback_on FROM v$database;

    FLASHBACK_ON
    ------------------
    NO

    SQL>

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.