Before starting any role transitions, you must verify that each database is properly configured and that there are no redo transport errors or redo gaps at the standby database.

For an Oracle Data Guard configuration with multiple standby databases, there are a number of factors to consider when choosing the target standby database for a role transition.

These include the following:

• Locality of the standby database.

• The capability of the standby database (hardware specifications—such as the number of CPUs, I/O bandwidth available, and so on).

• The time it takes to perform the role transition. This is affected by how far behind the standby database is in applying redo data, and how much flexibility you have in terms of trading off application availability with data loss.

• Standby database type.

The type of standby chosen as the role transition target determines how other standby databases in the configuration behave after the role transition. If the new primary was a physical standby before the role transition, then all other standby databases in the configuration become standbys of the new primary. If the new primary was a logical standby before the role transition, then all other logical standbys in the configuration become standbys of the new primary, but physical standbys in the configuration continue to be standbys of the old primary and therefore, do not protect the new primary. In the latter case, a future switchover or failover back to the original primary database returns all standbys to their original role as standbys of the current primary. For the reasons described above, a physical standby is generally the best role transition target in a configuration that contains both physical and logical standbys.

Oracle Data Guard provides the V$DATAGUARD_STATS view, which you can use to evaluate each standby database in terms of the currency of the data in the standby database, and the time needed to perform a role transition if all available redo data is applied to the standby database. For example:

SQL> COLUMN NAME FORMAT A24
SQL> COLUMN VALUE FORMAT A16     
SQL> COLUMN DATUM_TIME FORMAT A24
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
 
NAME                     VALUE            DATUM_TIME
------------------------ ---------------- ------------------------
transport lag            +00 00:00:00     06/18/2009 12:22:06
apply lag                +00 00:00:00     06/18/2009 12:22:06
apply finish time        +00 00:00:00.000
estimated startup time   9

This query output shows that the standby database has received and applied all redo generated by the primary database. These statistics were computed using data received from the primary database as of 12:22.06 on 06/18/09.

The apply lag and transport lag metrics are computed based on data received from the primary database. These metrics become stale if communications between the primary and standby database are disrupted. An unchanging value in the DATUM_TIME column for the apply lag and transport lag metrics indicates that these metrics are not being updated and have become stale, possibly due to a communications fault between the primary and standby databases.

A switchover is typically used to reduce primary database downtime during planned outages.

Planned outages are events such as operating system or hardware upgrades, or rolling upgrades of the Oracle database software and patch sets.

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.

Figure 9-1 shows a two-site Oracle Data Guard configuration before the roles of the databases are switched. The primary database is in San Francisco, and the standby database is in Boston.

Figure 9-1 Oracle Data Guard Configuration Before Switchover

Description of "Figure 9-1 Oracle Data Guard Configuration Before Switchover"

Figure 9-2 shows the Oracle Data Guard environment after the original primary database was switched over to a standby database, but before the original standby database has become the new primary database. At this stage, the Oracle Data Guard configuration temporarily has two standby databases.

Figure 9-2 Standby Databases Before Switchover to the New Primary Database

Description of "Figure 9-2 Standby Databases Before Switchover to the New Primary Database"

Figure 9-3 shows the Oracle Data Guard environment after a switchover took place. The original standby database became the new primary database. The primary database is now in Boston, and the standby database is now in San Francisco.

Figure 9-3 Oracle Data Guard Environment After Switchover

Description of "Figure 9-3 Oracle Data Guard Environment After Switchover"

A failover is typically used only when the primary database becomes unavailable, and there is no possibility of restoring it to service within a reasonable period of time.

The specific actions performed during a failover vary based on whether a logical or a physical standby database is involved in the failover, the state of the Oracle Data Guard configuration at the time of the failover, and on the specific SQL statements used to initiate the failover.

Figure 9-4 shows the result of a failover from a primary database in San Francisco to a physical standby database in Boston.

Figure 9-4 Failover to a Standby Database

Description of "Figure 9-4 Failover to a Standby Database"

The DB_ROLE_CHANGE system event is signaled whenever a role transition occurs.

This system event is signaled immediately if the database is open when the role transition occurs, or the next time the database is opened if it is closed when a role transition occurs.

The DB_ROLE_CHANGE system event can be used to fire a trigger that performs a set of actions whenever a role transition occurs.

These steps describe how to perform a switchover to a physical standby database.

1. Verify that the target standby database is ready for switchover.

   The new switchover statement has a VERIFY option that results in checks being performed of many conditions required for switchover. Some of the items checked are: whether Redo Apply is running on the switchover target; whether the release version of the switchover target is 12.1 or later; whether the switchover target is synchronized; and whether it has MRP running.

   Suppose the primary database has a DB_UNIQUE_NAME of BOSTON and the switchover target standby database has a DB_UNIQUE_NAME of CHICAGO. On the primary database BOSTON, issue the following SQL statement to verify that the switchover target, CHICAGO, is ready for switchover:

   SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
   ERROR at line 1:
   ORA-16470: Redo Apply is not running on switchover target
   

   If this operation had been successful, a Database Altered message would have been returned but in this example an ORA-16470 error was returned. This error means that the switchover target CHICAGO is not ready for switchover. Redo Apply must be started before the switchover operation.

   After Redo Apply is started, issue the following statement again:

   SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
   ERROR at line 1:
   ORA-16475: succeeded with warnings, check alert log for more details
   

   The switchover target, CHICAGO, is ready for switchover. However, the warnings indicated by the ORA-16475 error may affect switchover performance. The alert log contains messages similar to the following:

   SWITCHOVER VERIFY WARNING: switchover target has dirty online redo logfiles that require clearing. It takes time to clear online redo logfiles. This may slow down switchover process.
   

   You can fix the problems or if switchover performance is not important, those warnings can be ignored. After making any fixes you determine are necessary, issue the following SQL statement again:

   SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
   Database altered.
   

   The switchover target, CHICAGO, is now ready for switchover.

2. Initiate the switchover on the primary database, BOSTON, by issuing the following SQL statement:

   SQL> ALTER DATABASE SWITCHOVER TO CHICAGO;
   Database altered.
   

   If this statement completes without any errors, proceed to Step 3.

   If an error occurs, mount the old primary database (BOSTON) and the old standby database (CHICAGO). On both databases, query DATABASE_ROLE from V$DATABASE. There are three possible combinations of database roles for BOSTON and CHICAGO. The following table describes these combinations and provides the likely cause and a high level remedial action for each situation. For details on specific error situations, see “Troubleshooting Oracle Data Guard” in Oracle Data Guard Concepts and Administration.

   Value of DATABASE_ROLE column in V$DATABASE	Cause and Remedial Action
   BOSTON database is primary, CHICAGO database is standby	Cause: The BOSTON database failed to convert to a standby database role. Action: See the alert log for details on the error that prevented BOSTON from switching to a standby role, take the necessary actions to fix the error, reopen one of the nodes of BOSTON if necessary, and repeat the switchover process from Step 1.
   BOSTON database is standby, CHICAGO database is standby	Cause: The CHICAGO database failed to convert to a primary database role. Action: Issue the following SQL statement to convert either BOSTON or CHICAGO to a primary database: SQL> ALTER DATABASE SWITCHOVER TO target_db_name FORCE; For example: On the CHICAGO database, issue the following SQL statement to convert it to a primary database: ALTER DATABASE SWITCHOVER TO CHICAGO FORCE; On the BOSTON database, issue the following SQL statement to convert it to a primary database: ALTER DATABASE SWITCHOVER TO BOSTON FORCE; If the SQL statement fails with an ORA-16473 error, then you must start Redo Apply before reissuing the command. Restart Redo Apply as follows: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Reissue the switchover command as follows: SQL> ALTER DATABASE SWTICHOVER TO BOSTON FORCE; Database altered.
   BOSTON database is standby, CHICAGO database is primary	Cause: The BOSTON and CHICAGO databases have successfully switched to their new roles, but there was an error communicating the final success status back to BOSTON. Action: Continue to Step 3 to finish the switchover operation.

3. Issue the following SQL statement on the new primary database, CHICAGO, to open it.

   SQL> ALTER DATABASE OPEN;
   

4. Issue the following SQL statement to mount the new physical standby database, BOSTON:

   SQL> STARTUP MOUNT;
   

   Or, if BOSTON is an Oracle Active Data Guard physical standby database, then issue the following SQL statement to open it read only:

   SQL> STARTUP;
   

5. Start Redo Apply on the new physical standby database. For example:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

These steps describe how to perform a failover to a physical standby database.

1. If the primary database can be mounted, then flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.

   First, ensure that Redo Apply is active at the target standby database. Then mount, but do not open the primary database. If the primary database cannot be mounted, go to Step 2.

   If not already done, then set up the remote LOG_ARCHIVE_DEST_n configured at the primary to point to the target destination. (You may not have any remote LOG_ARCHIVE_DEST_n configured if the target destination was serviced by a far sync instance, or was a terminal standby in a cascaded configuration.) Also, ensure that the primary can connect to the target destination by verifying that the NET_ALIAS_TARGET_DB_NAME is valid and properly established.

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=NET_ALIAS_TARGET_DB_NAME -
   > ASYNC VALID_FOR=(online_logfile, primary_role) -
   > DB_UNIQUE_NAME="target_db_unique_name"' SCOPE=memory;
   

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;
   

   It is also assumed that the LOG_ARCHIVE_CONFIG specification includes the DB_UNIQUE_NAME of the target destination at the primary (and LOG_ARCHIVE_CONFIG at the target destination includes the DB_UNIQUE_NAME of the primary). If not, then add that information to the LOG_ARCHIVE_CONFIG at the primary and target destination as required.

   Issue the following SQL statement at the primary database:

   SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
   

   For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database.

   This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database.

   If this statement completes without any errors, go to Step 5.If the statement completes with any error, or if it must be stopped because you cannot wait any longer for the statement to complete, continue with Step 2.

2. Query the V$ARCHIVED_LOG view on the target standby database to obtain the highest log sequence number for each redo thread.

   For example:

   SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -
   > OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
   
       THREAD       LAST
   ---------- ----------
            1        100
   

   If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it. This must be done for each redo thread.

   For example:

   SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
   

3. Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.

   For example:

   SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
   
   THREAD#    LOW_SEQUENCE# HIGH_SEQUENCE#
   ---------- ------------- --------------
            1            90             92
   

   In this example, the gap comprises archived redo log files with sequence numbers 90, 91, and 92 for thread 1.

   If possible, copy any missing archived redo log files to the target standby database from the primary database and register them at the target standby database. This must be done for each redo thread.

   For example:

   SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
   

4. The query executed in Step 3 displays information for the highest gap only. After resolving a gap, you must repeat the query until no more rows are returned.

   If, after performing Step 2 through Step 4, you are not able to resolve all gaps in the archived redo log files (for example, because you do not have access to the system that hosted the failed primary database), then you can expect some data loss during the failover.

5. Issue the following SQL statement on the target standby database:

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   

6. Issue the following SQL statement on the target standby database:

   SQL> ALTER DATABASE FAILOVER TO target_db_name;
   

   For example, suppose the target standby database is named CHICAGO:

   SQL> ALTER DATABASE FAILOVER TO CHICAGO;
   

   If this statement completes without any errors, proceed to Step 10.

   If there are errors, go to Step 7.

7. If an error occurs, try to resolve the cause of the error and then reissue the statement.

   • If successful, go to Step 10.

   • If the error still occurs and it involves a far sync instance, go to Step 8.

   • If the error still occurs and there is no far sync instance involved, go to Step 9.

8. This step is for far sync instance error cases only. If the error involves a far sync instance (for example, it is unavailable) and you have tried resolving the issue and reissuing the statement without success, then you can use the FORCE option. For example:

   SQL> ALTER DATABASE FAILVOVER TO CHICAGO FORCE;
   

   The FORCE option instructs the failover to ignore any failures encountered when interacting with the far sync instance and proceed with the failover, if at all possible. (The FORCE option has meaning only when the failover target is serviced by a far sync instance.)

   If the FORCE option is successful, go to Step 10.

   If the FORCE option is unsuccessful, go to Step 9.

9. Perform a data loss failover.

   If an error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement on the target standby database:

   SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
   

   In the following example, the failover operation fails with an ORA-16472 error. That error means the database is configured in MaxAvailability or MaxProtection mode but data loss is detected during failover.

   SQL> ALTER DATABASE FAILOVER TO CHICAGO;
   ERROR at line 1:
   ORA-16472: failover failed due to data loss
   

   You can complete the data loss failover by issuing the following SQL statement:

   SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
   Database altered.
   

10. Open the new primary database:

    SQL> ALTER DATABASE OPEN;
    

11. Oracle recommends that you perform a full backup of the new primary database.
12. If Redo Apply has stopped at any of the other physical standby databases in your Data Guard configuration, then restart it. For example:

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    

13. After a failover, the original primary database can be converted into a physical standby database of the new primary database using the method described in Converting a Failed Primary Into a Standby Database Using Flashback Database or Converting a Failed Primary into a Standby Database Using RMAN Backups, or it can be re-created as a physical standby database from a backup of the new primary database using the method described in Step-by-Step Instructions for Creating a Physical Standby Database.

    Once the original primary database is running in the standby role, a switchover can be performed to restore it to the primary role.

When you perform a switchover that changes roles between a primary database and a logical standby database, always initiate the switchover on the primary database and complete it on the logical standby database.

A failover role transition involving a logical standby database necessitates taking corrective actions on the failed primary database and on all bystander logical standby databases.

After a switchover, you can return databases to a time or system change number (SCN) prior to when the switchover occurred using the FLASHBACK DATABASE command.

If the switchover involved a physical standby database, the primary and standby database roles are preserved during the flashback operation. The role in which the database is running does not change when the database is flashed back to the target SCN or time to which you flashed back the database. A database running in the physical standby role after the switchover but prior to the flashback still runs in the physical standby database role after the Flashback Database operation.

If the switchover involved a logical standby database, flashing back changes the role of the standby database to what it was at the target SCN or time to which you flashed back the database.

You can use Flashback Database to convert the failed primary database to a point in time before the failover occurred and then convert it into a standby database.