7 Role Transitions

7.1.1 Preparing for a Role Transition (Failover or Switchover)

Before starting any role transition, perform the following preparations:

• Verify the initialization parameters for each database are configured correctly. See Chapter 3, "Creating a Physical Standby Database" and Chapter 4, "Creating a Logical Standby Database" for information about how to configure initialization parameters on the primary and standby databases so that the Data Guard configuration operates properly after the role transition.

  Also, see Section 3.1.3, "Configure a Standby Redo Log" for information about manually adding redo log files when creating a physical standby database.

  Note:

  You must define the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters on each standby database so that when a switchover or failover occurs, all standby sites continue to receive redo data from the new primary database. See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute to define role-based destinations in preparation for future role transitions.

• Verify the standby database that will become the new primary database is operating in ARCHIVELOG mode.

• Ensure temporary files exist on the standby database that match the temporary files on the primary database.

• Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.

• Verify that all but one RAC instance on the standby databases in a Real Application Clusters configuration are shut down.

  For a Real Application Clusters database, only one RAC instance on the standby database can be online during the role transition. Shut down all other instances before starting the role transition. Then, after the role transition completes, bring these instances back online.

  Note:

  Even though only one RAC instance on the standby database is open during the switchover, all other standby database instances will automatically undergo a transition to their new role correctly when they are opened.

7.1.2 Choosing a Target Standby Database for a Role Transition

For a 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 will take to perform the role transition. This is affected by how far behind the standby database is in terms of application of redo data, and how much flexibility you have in terms of trading off application availability with data loss.

Data Guard provides the V$DATAGUARD_STATS view that can be used to estimate the viability of each standby database in terms of the currency of the data in the standby database, and the time it will take to perform a role transition if all available redo data is applied to the standby database. For example:

SQL> COLUMN NAME FORMAT A18
SQL> COLUMN VALUE FORMAT A16
SQL> COLUMN TIME_COMPUTED FORMAT A24
SQL> SELECT * FROM V$DATAGUARD_STATS;
NAME               VALUE             TIME_COMPUTED
------------------ ----------------  ------------------------
apply finish time  +00 00:00:02.4    15-MAY-2005 10:32:49
       second(1)
       interval
apply lag          +00 0:00:04       15-MAY-2005 10:32:49
       second(0)
       interval
transport lag      +00 00:00:00      15-MAY-2005 10:32:49
       second(0)
       interval

This shows that for this standby database, there is no transport lag, that log apply services have not applied the redo generated in the last 4 seconds (apply lag), and that it will take log apply services 2.4 seconds to finish applying the unapplied redo (apply finish time). The time at which each of the statistics is computed is shown in the TIME_COMPUTED column.If the configuration contains both physical and logical standby databases, consider choosing a physical standby database to be the target standby database. A switchover or failover to a physical standby database is preferable because all databases in the configuration will be viable as standby databases to the new primary database after the role transition completes. Whereas a switchover or failover to a logical standby database will invalidate the other physical standby databases to the original primary database. You will then need to re-create the physical standby databases from a backup of the new primary database before you can reenable them.

7.1.3 Switchovers

A switchover is typically used to reduce primary database downtime during planned outages, such as operating system or hardware upgrades, or rolling upgrades of the Oracle database software and patch sets (described in Chapter 11, "Using SQL Apply to Upgrade the Oracle Database").

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 7-1 shows a two-site 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 7-1 Data Guard Configuration Before Switchover

Description of "Figure 7-1 Data Guard Configuration Before Switchover"

Figure 7-2 shows the 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 Data Guard configuration temporarily has two standby databases.

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

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

Figure 7-3 shows the 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 7-3 Data Guard Environment After Switchover

Description of "Figure 7-3 Data Guard Environment After Switchover"

Preparing for a Switchover

Ensure the prerequisites listed in Section 7.1.1 are satisfied. In addition, the following prerequisites must be met for a switchover:

• For switchovers involving a physical standby database, verify the primary database instance is open and the standby database instance is mounted.

  The standby database that you plan to change to the primary role must be mounted before you begin the switchover. Ideally, the physical standby database will also be actively applying redo when the database roles are switched. If the physical standby database is open for read-only access, the switchover still will take place, but will require additional time. See Section 6.3, "Applying Redo Data to Physical Standby Databases" for more information about Redo Apply.

• For switchovers involving a logical standby database, verify both the primary and standby database instances are open and that SQL Apply is active. See Section 6.4, "Applying Redo Data to Logical Standby Databases" for more information about SQL Apply.

• For switchovers involving a primary database in a Real Applications Cluster, all but one instance must be shut down. Once the switchover is performed successfully, you can bring all other instances back online.

When a database transitions from one role to another, the DB_ROLE_CHANGE system event fires. You can write a trigger that's associated with this system event to manage tasks after a switchover occurs. The event fires when the database opens for the first time after the switchover regardless of its new role (that is, regardless of whether the switchover caused it to open for the first time as a primary database, as a logical standby, or as a physical standby in read-only mode). You can query the DATABASE_ROLE column of the V$DATABASE view to determine a database's current role. See the table of system manager events in Oracle Database Application Developer's Guide - Fundamentals for more details.

7.1.4 Failovers

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 Data Guard configuration at the time of the failover, and on the specific SQL statements used to initiate the failover.

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

Figure 7-4 Failover to a Standby Database

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

Preparing for a Failover

If possible, before performing a failover, you should transfer as much of the available and unapplied primary database redo data as possible to the standby database.

Ensure the prerequisites listed in Section 7.1.1, "Preparing for a Role Transition (Failover or Switchover)" are satisfied. In addition, the following prerequisites must be met for a failover:

• If a standby database currently running in maximum protection mode will be involved in the failover, first place it in maximum performance mode by issuing the following statement on the standby database:

  SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
  
  

  Then, if appropriate standby databases are available, you can reset the desired protection mode on the new primary database after the failover completes.

  This is required because you cannot fail over to a standby database that is in maximum protection mode. In addition, if a primary database in maximum protection mode is still actively communicating with the standby database, issuing the ALTER DATABASE statement to change the standby database from maximum protection mode to maximum performance mode will not succeed. Because a failover removes the original primary database from the Data Guard configuration, these features serve to protect a primary database operating in maximum protection mode from the effects of an unintended failover.

  Note:

  Do not fail over to a standby database to test whether or not the standby database is being updated correctly. Instead:

  • See Section 3.2.7, "Verify the Physical Standby Database Is Performing Properly"

  • See Section 4.2.6, "Verify the Logical Standby Database Is Performing Properly"

When a database transitions from one role to another, the DB_ROLE_CHANGE system event fires. You can write a trigger that's associated with this system event to manage tasks after a failover occurs. The event fires when the database opens for the first time after the failover regardless of its new role (in the case of a failover to a physical standby database, the system event will fire when the database is opened for the first time after a failover operation). You can query the DATABASE_ROLE column of the V$DATABASE view to determine a database's current role. See the table of system manager events in Oracle Database Application Developer's Guide - Fundamentals for more details.

To perform a failover involving a physical standby database, see Section 7.2.2, "Failovers Involving a Physical Standby Database". To perform a failover involving a logical standby database, see Section 7.3.2, "Failovers Involving a Logical Standby Database". To perform a failover using the Data Guard broker, see the chapter about "Switchover and Failover Operations" in Oracle Data Guard Broker.

7.2.1 Switchovers Involving a Physical Standby Database

This section describes how to perform a switchover. A switchover must be initiated on the current primary database and completed on the target standby database. The following steps describe how to perform a switchover.

Step 1 Verify it is possible to perform a switchover.

On the current primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify it is possible to perform a switchover. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS 
 ----------------- 
 TO STANDBY 
 1 row selected 

The TO STANDBY value in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the Data Guard configuration is functioning correctly (for example, verify all LOG_ARCHIVE_DEST_n parameter values are specified correctly).

If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement described in Step 2.

See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view.

Step 2 Initiate the switchover on the primary database.

To change the current primary database to a physical standby database role, use the following SQL statement on the primary database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

After this statement completes, the primary database is converted into a standby database. The current control file is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file, if necessary.

Step 3 Shut down and restart the former primary instance.

Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

At this point in the switchover process, both databases are configured as standby databases (see Figure 7-2).

Step 4 Verify the switchover status in the V$DATABASE view.

After you change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, you should verify if the switchover notification was processed by the target standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database.

For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 
SWITCHOVER_STATUS 
----------------- 
TO_PRIMARY 
1 row selected

If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can proceed to Step 5, and append the WITH SESSION SHUTDOWN clause to the switchover statement. See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view

Step 5 Switch the target physical standby database role to the primary role.

You can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following SQL statement on the physical standby database that you want to change to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6 Finish the transition of the standby database to the primary role.

The task you perform is dependent on if the physical standby database has ever been opened in read-only mode:

• If the physical standby database has not been opened in read-only mode since the last time it was started, issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

  SQL> ALTER DATABASE OPEN;
  
  

  Then, go to step 7.

• If the physical standby database has been opened in read-only mode since the last time it was started, you must shut down the target standby database and restart it:

  SQL> SHUTDOWN IMMEDIATE;
  SQL> STARTUP;
  
  

The target physical standby database has now undergone a transition to the primary database role. See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute to ensure the Data Guard configuration operates properly after a role transition.

Step 7 If necessary, restart log apply services on the standby databases.

For the new physical standby database and for each other physical or logical standby database in the Data Guard configuration, if log apply services were not previously configured to continue operating through a switchover, use an appropriate command to restart log apply services. See Chapter 6, "Log Apply Services" for more information about how to configure and start log apply services.

Step 8 Begin sending redo data to the standby databases.

Issue the following statement on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

7.2.2 Failovers Involving a Physical Standby Database

This section describes how to perform failovers involving a physical standby database.

During failovers involving a physical standby database:

• In all cases, after a failover, the original primary database can no longer participate in the Data Guard configuration.

• In most cases, other logical or physical standby databases not directly participating in the failover remain in the configuration and do not have to be shut down or restarted.

• In some cases, it might be necessary to re-create all standby databases after configuring the new primary database.

These cases are described, where appropriate, within the failover steps below.

Before starting the failover, perform as many of the steps documented in Section 7.1.4, "Failovers" as possible to prepare the selected standby database for a failover, then proceed to Section 7.2.2, "Failovers Involving a Physical Standby Database" for the failover steps.

Failover Steps

This section describes the steps that must be performed to transition the selected physical standby database to the primary role. Any other physical or logical standby databases that are also part of the configuration will remain in the configuration and will not need to be shut down or restarted.

If the target standby database was operating in maximum protection mode or maximum availability mode using the log writer process (LGWR), no gaps in the archived redo log files should exist, and you can proceed directly to Step 4. Otherwise, begin with Step 1 to determine if any manual gap resolution steps must be performed.

Step 1 Identify and resolve any gaps in the archived redo log files.

To determine if there are gaps in the archived redo log files on the target standby database, query the V$ARCHIVE_GAP view.

The V$ARCHIVE_GAP view contains the sequence numbers of the archived redo log files that are known to be missing for each thread. The data returned reflects the highest gap only.

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 sequences 90, 91, and 92 for thread 1. If possible, copy all of the identified missing archived redo log files to the target standby database from the primary database and register them. This must be done for each thread.

For example:

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 2 Repeat Step 1 until all gaps are resolved.

The query executed in Step 1 displays information for the highest gap only. After resolving that gap, you must repeat Step 1 until the query returns no rows.

Step 3 Copy any other missing archived redo log files.

To determine if there are any other missing archived redo log files, query the V$ARCHIVED_LOG view on the target standby database to obtain the highest sequence number for each thread.

For example:

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

    THREAD       LAST
---------- ----------
         1        100

Copy any available archived redo log files from the primary database that contains sequence numbers higher than the highest sequence number available on the target standby database to the target standby database and register them. This must be done for each thread.

For example:

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

After all available archived redo log files have been registered, query the V$ARCHIVE_GAP view as described in Step 1 to verify no additional gaps were introduced in Step 3.

Step 4 Initiate a failover on the target physical standby database.

Issue the following statement to initiate the failover:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

The FORCE keyword terminates active RFS processes on the target physical standby database, so that failover can proceed immediately without waiting for network connections to time out:

Step 5 Convert the physical standby database to the primary role.

Once the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE...FINISH FORCE statement completes successfully, change the physical standby database to the primary database role by issuing the following SQL statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

After issuing this SQL statement, the target standby database is undergoes a transition to the primary role. As a result, you can no longer use this database as a standby database and any subsequent redo received from the original primary database cannot be applied. During the failover process, the standby redo log files were automatically archived and recovered on all other standby databases derived from the original primary database. This will happen only if the standby destinations are correctly defined on the new primary database.

There is no need to shut down and restart any of the other standby databases in the configuration that were not participants in the failover.

Step 6 Finish the transition of the standby database to the primary database role.

The task you perform in this step depends on if the physical standby database was ever opened in read-only mode:

• If the physical standby database has not been opened in read-only mode since the last time it was started, issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

  SQL> ALTER DATABASE OPEN;
  
  

  Then, go to step 7.

• If the physical standby database has been opened in read-only mode since the last time it was started, you must shut down the target standby database and restart it:

  SQL> SHUTDOWN IMMEDIATE;
  SQL> STARTUP;
  
  

The target physical standby database has now undergone a transition to the primary database role.

See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute so the Data Guard configuration operates properly after a role transition.

Step 7 Back up the new primary database.

Before issuing the STARTUP statement, back up the new primary database. Performing a backup immediately is a necessary safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database.

As a result of the failover, the original primary database can no longer participate in the Data Guard configuration, and all other standby databases are now receiving and applying redo data from the new primary database.

Step 8 Optionally, restore the failed primary database.

After a failover, the original primary database no longer participates in the configuration. After performing a failover, you may be able to restore the failed primary database as a new standby database using either of the following methods:

• Use Flashback Database to restore the failed primary database to a point in time before the failover occurred and then convert it into a standby database following the procedure in Section 12.4, "Using Flashback Database After a Failover".

  Note:

  You must have already enabled Flashback Database on the old primary database before the failover. See Oracle Database Backup and Recovery Basics for more information.

• Re-create the failed database and add it to the configuration as a new standby database. To reuse the old primary database in the new configuration, you must re-create it as a standby database using a backup copy of the new primary database. This procedure is described in Section 3.2, "Step-by-Step Instructions for Creating a Physical Standby Database"or Section 4.2, "Step-by-Step Instructions for Creating a Logical Standby Database".

• Use Oracle Enterprise Manager or the DGMGRL REINSTATE DATABASE command to re-create the failed primary database as a standby database in the new configuration when a connection to it is reestablished. Step-by-step instructions for reinstatement are described in Oracle Data Guard Broker. This option requires that Flashback Database is enabled prior to the failover.

Once the failed primary database has been restored and is running in the standby role, you can optionally perform a switchover to perform a role transition of the databases to their original (pre-failure) roles. See Section 7.2.1, "Switchovers Involving a Physical Standby Database" for more information.

7.3.1 Switchovers Involving a Logical Standby Database

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. These steps must be performed in the order in which they are described or the switchover will not succeed.

Step 1 Verify it is possible to perform a switchover on the primary database.

On the current primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify it is possible to perform a switchover.

For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected

A value of TO STANDBY or SESSIONS ACTIVE in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the logical standby role. If one of these values is not displayed, then verify the Data Guard configuration is functioning correctly (for example, verify all LOG_ARCHIVE_DEST_n parameter values are specified correctly). See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view.

Step 2 Prepare the current primary database for the switchover.

To prepare the current primary database for a logical standby database role, issue the following SQL statement on the primary database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

This statement notifies the current primary database that it will soon switch to the logical standby role and begin receiving redo data from a new primary database. You perform this step on the primary database in preparation to receive the LogMiner Multiversioned Data Dictionary to be recorded in the redo stream of the current logical standby database, as described in step 3.

The value PREPARING SWITCHOVER is displayed in the V$DATABASE.SWITCHOVER_STATUS column if this operation succeeds.

Step 3 Prepare the target logical standby database for the switchover.

Use the following statement to build a LogMiner Multiversioned Data Dictionary on the logical standby database that is the target of the switchover:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 

This statement also starts redo transport services on the logical standby database that begins transmitting its redo data to the current primary database and to other standby databases in the Data Guard configuration. The sites receiving redo data from this logical standby database accept the redo data but they do not apply it.

Depending on the work to be done and the size of the database, the switchover can take some time to complete.

The V$DATABASE.SWITCHOVER_STATUS on the logical standby database initially shows PREPARING DICTIONARY while the LogMiner Multiversioned Data Dictionary is being recorded in the redo stream. Once this has completed successfully, the SWITCHOVER_STATUS column shows PREPARING SWITCHOVER.

Step 4 Ensure the current primary database is ready for the future primary database's redo stream.

Before you can complete the role transition of the primary database to the logical standby role, verify the LogMiner Multiversioned Data Dictionary was received by the primary database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database. Without the receipt of the LogMiner Multiversioned Data Dictionary, the switchover cannot proceed, because the current primary database will not be able to interpret the redo records sent from the future primary database. The SWITCHOVER_STATUS column shows the progress of the switchover.

When the query returns the TO LOGICAL STANDBY value, you can proceed with Step 5. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO LOGICAL STANDBY
1 row selected

Step 5 Switch the primary database to the logical standby database role.

To complete the role transition of the primary database to a logical standby database, issue the following SQL statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; 

This statement waits for all current transactions on the primary database to end and prevents any new users from starting new transactions, and establishes a point in time where the switchover will be committed.

Executing this statement will also prevent users from making any changes to the data being maintained in the logical standby database. To ensure faster execution, ensure the primary database is in a quiet state with no update activity before issuing the switchover statement (for example, have all users temporarily log off the primary database). You can query the V$TRANSACTIONS view for information about the status of any current in-progress transactions that could delay execution of this statement.

The primary database has now undergone a role transition to run in the standby database role.

When a primary database undergoes a role transition to a logical standby database role, you do not have to shut down and restart the database.

Step 6 Ensure all available redo has been applied to the target logical standby database that is about to become the new primary database.

After you complete the role transition of the primary database to the logical standby role and the switchover notification is received by the standby databases in the configuration, you should verify the switchover notification was processed by the target standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database. Once all available redo records are applied to the logical standby database, SQL Apply automatically shuts down in anticipation of the expected role transition.

The SWITCHOVER_STATUS value is updated to show progress during the switchover. When the status is TO PRIMARY, you can proceed with Step 7.

For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO PRIMARY
1 row selected

See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view.

Step 7 Switch the target logical standby database to the primary database role.

On the logical standby database that you want to switch to the primary role, use the following SQL statement to switch the logical standby database to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

There is no need to shut down and restart any logical standby databases that are in the Data Guard configuration. Other existing logical standby databases will continue to function normally after a switchover completes. All existing physical standby databases, however, are rendered unable to participate in the Data Guard configuration after the switchover.

Step 8 Start SQL Apply on the new logical standby database.

On the new logical standby database, start SQL Apply:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

7.3.2 Failovers Involving a Logical Standby Database

This section describes how to perform failovers involving a 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. If Flashback Database was not enabled on the failed primary database, you must re-create the database from backups taken from the current primary database. Otherwise, you can follow the procedure described in Section 12.4 to convert a failed primary database to be a logical standby database for the new primary database.

Depending on the protection mode for the configuration and the attributes you chose for redo transport services, it might be possible to automatically recover all or some of the primary database modifications.

If the target standby database was operating in a no data loss mode, no gaps in archived redo log files will exist and you can proceed directly to Step 2. Otherwise, begin with Step 1 to determine if any manual gap resolution steps must be performed.

Step 1 Copy and register any missing archived redo log files to the target logical standby database slated to become the new primary database.

Depending on the condition of the components in the configuration, you might have access to the archived redo log files on the primary database. If so, do the following:

1. Determine if any archived redo log files are missing on the logical standby database.

2. Copy missing log files from the primary database to the logical standby database.

3. Register the copied log files.

You can register an archived redo log files with the logical standby database by issuing the following statement. For example:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 
  2> '/disk1/oracle/dbs/log-%r_%s_%t.arc';
Database altered.

Step 2 Ensure all available archived redo log files were applied.

On the logical standby database you are transitioning to the primary role, verify all available archived redo log files were applied by querying the V$LOGSTDBY_PROGRESS view. For example:

SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN
----------- ----------
     190725     190725

When the APPLIED_SCN and LATEST_SCN values are equal, all attainable data is applied and the logical standby database now contains as much data as possible from the primary database.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY FINISH;
Database altered.

See Chapter 9, "Managing a Logical Standby Database" and Chapter 12, "Data Guard Scenarios" for information about the V$LOGSTDBY_PROGRESS view.

Step 3 Enable remote destinations.

If you have not previously configured role-based destinations as described in Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute", identify the initialization parameters that correspond to the remote logical standby destinations for the new primary database, and manually enable archiving of redo data for each of these destinations.

For example, to enable archiving for the remote destination defined by the LOG_ARCHIVE_DEST_2 parameter, issue the following statement:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

To ensure this change will persist if the new primary database is later restarted, update the appropriate text initialization parameter file or server parameter file. In general, when the database operates in the primary role, you must enable archiving to remote destinations, and when the database operates in the standby role, you must disable archiving to remote destinations.

See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute to define role-based destinations in preparation for future role transitions.

Step 4 Activate the new primary database.

Issue the following statement on the target logical standby database (that you are transitioning to the new primary role):

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

This statement stops the RFS process, applies remaining redo data in the standby redo log file before the logical standby database becomes a primary database, stops SQL Apply, and activates the database in the primary database role.

If the FINISH APPLY clause is not specified, then unapplied redo from the current standby redo log file will not be applied before the standby database becomes the primary database.

Step 5 Prepare to recover the other standby databases.

Depending on how much redo data you were able to apply to the new primary database, you might be able to add other existing logical standby databases back into the Data Guard configuration to serve as standby databases for the new primary database. Perform the following steps on each logical standby database to prepare to add it back into the Data Guard configuration:

1. Create a database link on each logical standby database.

   Use the ALTER SESSION DISABLE GUARD statement to bypass the database guard and allow modifications to the tables in the logical standby database. For example, the following creates a database link to the primary database chicago:

   SQL> ALTER SESSION DISABLE GUARD;
   SQL> CREATE DATABASE LINK chicago 
     2> CONNECT TO username IDENTIFIED BY password USING 'chicago';
   SQL> ALTER SESSION ENABLE GUARD;
   
   

   The database user account specified in the CREATE DATABASE LINK statement must have the SELECT_CATALOG_ROLE role granted to it on the primary database.

   Note:

   You must perform the dictionary build operation after the primary database has been opened but before any DDL statements have been executed. If any DDL statements are executed before the dictionary build operation is performed, the backup will be invalidated as a source for creating a logical standby database.

   See Oracle Database Administrator's Guide for more information about creating database links.

2. Verify the database link.

   On the logical standby database, verify the database link was configured correctly by executing the following query using the database link:

   SQL> SELECT * FROM DBA_LOGSTDBY_PARAMETERS@chicago; 
   
   

   If the query succeeds, then that confirms the database link created in Step 1 can be used during role transitions.

Step 6 Start SQL Apply.

Start SQL Apply on each logical standby database.

For example, the following statement starts SQL Apply on the chicago database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY chicago; 

When this statement completes, all remaining archived redo log files will have been applied. Depending on the work to be done, this operation can take some time to complete.

If the ORA-16109 error is returned, you must re-create the logical standby database from a backup copy of the new primary database, and then add it to the Data Guard configuration.

The following example shows a failed attempt to start SQL Apply on a logical standby database in the new configuration where chicago is the service name that points to the new primary database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY chicago;
ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY chicago
                                                       *
ERROR at line 1:
ORA-16109: failed to apply log data from previous primary

Step 7 Back up the new primary database.

Back up the new primary database immediately after the Data Guard database failover. Immediately performing a backup is a necessary safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database.

Step 8 Restore the failed primary database.

After performing a failover, you can optionally restore the failed primary database as a new standby database using one of the following methods:

• 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 following the procedure in Section 12.4, "Using Flashback Database After a Failover".

  Note:

  You must have already enabled Flashback Database on the old primary database before the failover. See Oracle Database Backup and Recovery Basics for more information.

• Use the DBMS_LOGSTDBY.REBUILD PL/SQL procedure to rebuild the primary database as a new standby database. Before you run the procedure, you must verify:

  • Query the V$STANDBY_LOG or V$LOGFILE view to verify that standby redo log files have been archived

  • Query the DBA_LOGSTDBY_EVENTS view to verify that the LogMiner dictionary build completed successfully

  See Also:

  The DBMS_LOGSTDBY package in Oracle Database PL/SQL Packages and Types Reference for information about the REBUILD subprogram

• Use Oracle Enterprise Manager or the DGMGRL REINSTATE DATABASE command to re-create the failed primary database as a standby database in the new configuration when a connection to it is reestablished. Step-by-step instructions for reinstatement are described in Oracle Data Guard Broker.

Re-create the failed database and add it to the configuration as a new standby database following the procedure in Section 3.2, "Step-by-Step Instructions for Creating a Physical Standby Database" or Section 4.2, "Step-by-Step Instructions for Creating a Logical Standby Database".

Once the failed primary database has been restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See Section 7.3.1, "Switchovers Involving a Logical Standby Database" for more information.

7.4.1 Using Flashback Database After a Switchover

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. That is, 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 will still be running 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.

7.4.2 Using Flashback Database After a Failover

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. See Section 12.4, "Using Flashback Database After a Failover" for the complete step-by-step procedure.