Skip Headers

Oracle® Data Guard Concepts and Administration
10g Release 1 (10.1)

Part Number B10823-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

7
Role Management

A Data Guard configuration consists of one database that functions in the primary role and one or more databases that function in standby roles. Typically, the role of each database does not change. However, if Data Guard is used to maintain service in response to a primary database outage, or when performing hardware or software maintenance, you must transition the role of the primary database and one standby database in the configuration. To see the current role of the databases, query the DATABASE_ROLE column in the V$DATABASE view.

The number, location, and type (physical or logical) of standby databases in the Data Guard configuration and the way in which redo data from the primary database is propagated to each standby database determine, in advance, the role-management options available to you in response to a planned or unplanned primary database outage.

This chapter describes Data Guard role management services and operations that allow you to change and manage roles of the databases in a Data Guard configuration. It contains the following topics:

See Oracle Data Guard Broker for information about using Oracle Data Guard broker's distributed management framework to automate the switchover and failover processes into a single command. The Data Guard broker provides GUI and command-line interfaces that automate and centralize the creation, maintenance, and monitoring of Data Guard configurations.

7.1 Introduction to Role Transitions

A database operates in one of the following mutually exclusive roles: primary or standby. Data Guard enables you to change these roles dynamically by issuing the SQL statements described in this chapter, or by using either of the Data Guard broker's interfaces. Oracle Data Guard supports two role transition operations:

Section 7.1.1 helps you choose the role transition that best minimizes downtime and risk of data loss. Switchovers and failovers are described in more detail in Section 7.1.2 and Section 7.1.3, respectively.


Note:

Oracle Data Guard switchovers and failovers are not invoked automatically. You must initiate a switchover or failover manually using a SQL statement or a Data Guard broker interface.


7.1.1 Which Role Transition to Use

During any role transition, the amount of downtime required to complete the operation, the potential for data loss, and the effects on other standby databases in the configuration are determined by:

The goal is to perform the role transition as quickly as possible with little or no data loss.


Note:

The time required to complete a role transition is minimized when the real-time apply feature is enabled and active, as described in Section 6.2.1. Real-time apply allows log apply services to recover and apply redo data from standby redo log files at the same time these files are receiving new redo data from the primary database. This ensures the lag between the standby database and the primary database is as small as possible.


The decision tree presented in Figure 7-1 can help you choose the role transition that best minimizes downtime and risk of data loss.

Figure 7-1 Role Transition Decision Tree

Text description of rms_decision.gif follows.

Text description of the illustration rms_decision.gif

In general, consider if it would be faster to repair the primary database than to perform a role transition. If you can repair the primary database, you also do not have to reconfigure client applications to connect to a new database. However, if the repair operation results in any data loss, you might be able to flash back the standby databases as described in Section 10.4. If you do not have Flashback Database enabled, you might need to re-create all other standby databases in the configuration from a backup copy of the repaired primary database.

If you decide that a role transition is appropriate and the configuration contains one or more physical standby databases, Oracle recommends that you perform the role transition using the best available physical standby database. Role transitions involving a logical standby database:

See Section 10.2 for information about how to choose the best available physical or logical standby database

Once you determine the type of role transition you want to perform, proceed to one of the following sections:

7.1.2 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 Section 9.2).

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

Figure 7-2 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-2 Data Guard Configuration Before Switchover

Text description of before.gif follows.

Text description of the illustration before.gif

Figure 7-3 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-3 Standby Databases Before Switchover to the New Primary Database

Text description of between.gif follows.

Text description of the illustration between.gif

Figure 7-4 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-4 Data Guard Environment After Switchover

Text description of switch.gif follows.

Text description of the illustration switch.gif

7.1.2.1 Preparing for a Switchover

Although switchovers can be performed between the primary database and either a logical or a physical standby database in the Data Guard configuration, a physical standby database is preferred (as described in Section 7.1.1). To minimize downtime, carefully plan each switchover so that the primary and standby databases involved have as small a transactional lag as possible. Also, consider using the Data Guard broker to automate and simplify the switchover procedure into one easy step. See Oracle Data Guard Broker for more information.

Before starting a switchover:

For switchovers involving a physical standby database, see Section 7.2.1. For switchovers involving a logical standby database, see Section 7.3.1. If you configured your environment using Oracle Data Guard broker distributed management framework, refer instead to Oracle Data Guard Broker for information about how to use the Switchover wizard to automate the switchover process.

7.1.3 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-5 shows the result of a failover from a primary database in San Francisco to a physical standby database in Boston.

Figure 7-5 Failover to a Standby Database

Text description of failover.gif follows.

Text description of the illustration failover.gif

7.1.3.1 Preparing for 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 by following the steps described in this section. Consider using the Data Guard broker to automate and simplify the failover procedure into one easy step. See Oracle Data Guard Broker for more information.

Before initiating a failover, perform the following steps:

To perform a failover involving a physical standby database, see Section 7.2.2. To perform a failover involving a logical standby database, see Section 7.3.2.

7.2 Role Transitions Involving Physical Standby Databases

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

7.2.1 Switchovers Involving a Physical Standby Database

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

On the current primary database:
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 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 transition 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-3).

On the target physical standby database:
Step 4 Verify the switchover status in the V$DATABASE view.

After you transition 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 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 mounted in one of these modes so that the primary database switchover request can be coordinated. After you mount the standby database in an appropriate mode, issue the following SQL statement on the physical standby database that you want to transition to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Also see Chapter 3 for information about manually adding redo log files when creating a physical standby database.

Step 6 Shut down and restart the target standby database.

Shut down the target standby database and restart it using the appropriate initialization parameters for the primary role:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

The target physical standby database is now transitioned to the primary database role.


Note:

There is no need to shut down and restart other standby databases (not involved in the switchover) that are online at the time of the switchover. These standby databases will continue to function normally after the switchover completes.


On the new physical standby database and on all other standby databases:
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 for more information about how to configure and start log apply services.

On the new primary database:
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:

Before starting the failover, perform as many of the steps documented in Section 7.1.3.1 as possible to prepare the selected standby database for the failover operation, then proceed to Section 7.2.2.1 for the failover steps.


Note:

Oracle recommends you use only the failover steps and commands described in the following sections to perform a failover. Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE to perform a failover, because this statement causes data loss.


7.2.2.1 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, 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. This 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.


Note::

If, while performing Steps 1 through 3, you are not able to resolve gaps in the archived redo log files (for example, because you do not have access to the system that hosted the failed primary database), some data loss will occur during the failover.


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

If the target physical standby database has standby redo log files configured, issue the following statement to initiate the failover:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If the target physical standby database does not have standby redo log files configured, include the FINISH SKIP STANDBY LOGFILE clause:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
  2> FINISH SKIP STANDBY LOGFILE;


Note:

The failover operation adds an end-of-redo marker to the header of the last log file being archived and sends the redo to all enabled destinations that are valid for the primary role (specified with the VALID_FOR=(PRIMARY_ROLE, *_LOGFILES) or the VALID_FOR=(ALL_ROLES, *_LOGFILES) attributes).


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

Once the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE...FINISH statement completes successfully, transition 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 transitioned 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.

On the new primary database:
Step 6 Shut down and restart the new primary database.

To complete the failover, you need to shut down the new primary database and restart it in read/write mode using the proper traditional initialization parameter file (or server parameter file) for the primary role:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

See Chapter 3 and Chapter 4 for information about how to configure initialization parameters on both the primary and standby databases so that your Data Guard configuration operates properly after a role transition.

Step 7 Optionally, back up the new primary database.

Optionally, before issuing the STARTUP statement, you might want to perform a closed back up of the new primary database. In place of a closed backup, instead consider performing an open backup of the database after issuing the STARTUP statement. Although performing a backup immediately is not required, it is a recommended 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 optionally restore the failed primary database as a new standby database using either of the following methods:

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.2.1, "Switchovers Involving a Physical Standby Database" for more information.

7.3 Role Transitions Involving Logical Standby Databases

This section describes how to perform switchovers and failovers involving a logical standby database.

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. The following steps describe how to perform the switchover.

On the primary database:
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

A value of TO STANDBY, TO LOGICAL 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:

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.

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

Use the following statement to build a LogMiner 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 log 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, this operation can take some time to complete.

On the current primary database:
Step 4 Verify the switchover status in the V$DATABASE view.

Before you transition the primary database to the logical standby role, verify the LogMiner dictionary was received by the primary database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the 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 transition the primary database to a logical standby database role, 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. It also puts a marker in the redo data to provide a synchronization point for logical standby database operations. 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 is now transitioned to run in the standby database role.

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

On the target logical standby database (new primary database):
Step 6 Verify the switchover status in the V$DATABASE view.

After you transition 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. 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 Ensure all standby databases begin receiving redo data.

On the new primary database, perform a log switch to ensure all logical standby databases begin receiving redo data from the new primary database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

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.

During failovers involving a logical standby database:

Before starting the failover, perform as many of the steps documented in Section 7.1.3.1 as possible to prepare the selected standby database for the failover. Depending on the protection mode for the configuration and the attributes you chose for log 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 3. Otherwise, begin with Step 1 to determine if any manual gap resolution steps must be performed.

On the logical standby database being transitioned to the primary role:
Step 1 Copy and register any missing archived redo log files.

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 the missing log files from the primary database to the logical standby database.
  3. Register the copied log files.

On the logical standby database, query the DBA_LOGSTDBY_LOG view to determine which log files are missing and then register them. For example, the following query indicates there is a gap in the sequence of archived redo log files because it displays two files for THREAD 1 on the logical standby database. (If there are no gaps, the query will show only one file for each thread.) The output shows the highest registered file is sequence number 10, but there is a gap at the file shown as sequence number 6:

SQL> COLUMN FILE_NAME FORMAT a55;
SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L
  2> WHERE NEXT_CHANGE# NOT IN
  3> (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
  4> ORDER BY THREAD#,SEQUENCE#;

   THREAD#  SEQUENCE# FILE_NAME
---------- ---------- -----------------------------------------------
         1          6 /disk1/oracle/dbs/log-1292880008_6_1.arc
         1         10 /disk1/oracle/dbs/log-1292880008_10_1.arc

To resolve the gap, copy the missing archived redo log files for THREAD 1 (with sequence numbers 7, 8, and 9). Then, register these archived redo log files on the logical standby database. For example:

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

After you copy and register the missing archived redo log files to the logical standby system, query the DBA_LOGSTDBY_LOG view again to ensure there are no more gaps and the next thread and sequence number needed by the target logical standby database do not exist.

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 DBA_LOGSTDBY_PROGRESS view. For example:

SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

APPLIED_SCN NEWEST_SCN
----------- ----------
     190725     190725

When the APPLIED_SCN and NEWEST_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.


Note:

If SQL Apply is not active on the target logical standby database, issue the following statement on the target standby database to start SQL Apply:

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

See Chapter 9 and Chapter 10 for information about the DBA_LOGSTDBY_PROGRESS view.

Step 3 Enable remote destinations.

If you have not previously configured role-based destinations as described in Section 7.1.3.1, 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 and Chapter 12 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 statements on the target logical standby database (that you are transitioning to the new primary role) to stop SQL Apply and activate the database in the primary database role:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
On all other logical standby databases:
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 DATABASE 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.

    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 by issuing this SQL statement on each logical standby 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
On the new primary database:
Step 7 Optionally, back up the new primary database.

Optionally, perform a closed backup of the new primary database. In place of a closed backup, instead consider performing an open backup of the database. Immediately performing a backup, while not required, is a recommended safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database.

Step 8 Optionally, restore the failed primary database.

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

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.