Skip Headers
Oracle® Data Guard Concepts and Administration
11g Release 1 (11.1)

Part Number B28294-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
Contact Us

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

8 Role Transitions

A Data Guard configuration consists of one database that functions in the primary role and one or more databases that function in the standby role. 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, you must initiate a role transition between the current 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 of standby databases in a Data Guard configuration and the way in which redo data from the primary database is propagated to each standby database determine the role-management options available to you in response to a primary database outage.

This chapter describes how to manage role transitions in a Data Guard configuration. It contains the following topics:

The role transitions described in this chapter are invoked manually using SQL statements. You can also use the Oracle Data Guard broker to simplify role transitions and automate failovers.

See Also:

Oracle Data Guard Broker for information about using the Oracle Data Guard broker to:

8.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 the following role transitions:

Section 8.1.1, "Preparing for a Role Transition" 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 8.1.3, "Switchovers" and Section 8.1.4, "Failovers", respectively.

8.1.1 Preparing for a Role Transition

Before starting any role transition, perform the following preparations:

  • Verify that each database is properly configured for the role that it is about to assume. See Chapter 3, "Creating a Physical Standby Database" and Chapter 4, "Creating a Logical Standby Database" for information about how to configure database initialization parameters, archivelog mode, standby redo logs, and online redo logs on primary and standby databases.

    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.
  • 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.

  • Before performing a switchover from an Oracle RAC primary database to a physical standby database, shut down all but one primary database instance. Any primary database instances shut down at this time can be started after the switchover completes.

    Before performing a switchover or a failover to an Oracle RAC physical standby database, shut down all but one standby database instance. Any standby database instances shut down at this time can be restarted after the role transition completes.

8.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.

  • Standby database type.

The type of standby chosen as the role transition target determines how other standby databases in the configuration will behave after the role transition. If the new primary was a physical standby before the role transition, all other standby databases in the configuration will 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 will become standbys of the new primary, but physical standbys in the configuration will continue to be standbys of the old primary and will therefore not protect the new primary. In the latter case, a future switchover or failover back to the original primary database will return 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.

Note:

A snapshot standby cannot be the target of a role transition.

Data Guard provides the V$DATAGUARD_STATS view that can be used to evaluate 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

The time at which each of the statistics is computed is shown in the TIME_COMPUTED column. The V$DATATGUARD_STATS.TIME_COMPUTED column is a timestamp taken when the metric in a V$DATATGUARD_STATS row is computed. This column indicates the freshness of the associated metric. This shows that for this standby database, there is no transport lag, that apply services has not applied the redo generated in the last 4 seconds (apply lag), and that it will take apply services 2.4 seconds to finish applying the unapplied redo (apply finish time). The APPLY LAG and TRANSPORT LAG metrics are computed based on information received from the primary database, and these metrics become stale if communications between the primary and standby database are disrupted. An unchanging value in this column for the APPLY LAG and TRANSPORT LAG metrics indicates that these metrics are not being updated (or have become stale), possibly due to a communications fault between the primary and standby databases.

8.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 12, "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 8-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 8-1 Data Guard Configuration Before Switchover

Description of Figure 8-1 follows
Description of "Figure 8-1 Data Guard Configuration Before Switchover"

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

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

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

Description of Figure 8-3 follows
Description of "Figure 8-3 Data Guard Environment After Switchover"

Preparing for a Switchover

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

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

Figure 8-4 Failover to a Standby Database

Description of Figure 8-4 follows
Description of "Figure 8-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 8.1.1, "Preparing for a Role Transition" 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:

8.1.5 Role Transition Triggers

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.

8.2 Role Transitions Involving Physical Standby Databases

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

8.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 8-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 neither TO_PRIMARY nor SESSIONS_ACTIVE, verify that redo apply is active and that redo transport is working properly, and continue to query this view until either TO_PRIMARY or SESSIONS_ACTIVE is displayed.

If the value in the SWITCHOVER_STATUS column is TO_PRIMARY, go to step 5.

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.

Issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

SQL> ALTER DATABASE OPEN;

Step 7   Start redo apply on the new physical standby database.

For example, issue the following statement on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
 DISCONNECT FROM SESSION;

See Chapter 7, "Apply Services" for more information about how to configure and start apply services.

8.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 8.1.4, "Failovers" as possible to prepare the selected standby database for a failover, then proceed to Section 8.2.2, "Failovers Involving a Physical Standby Database" 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 may cause data loss.

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, no gaps in the archived redo log files should exist, and you can proceed directly to Step 6. 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.

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   Stop Managed Recovery.

Issue the following statement to stop managed recovery:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 5   Verify that the standby database is ready to become a primary database.

Query the SWITCHOVER_STATUS column of the V$DATABASE 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 neither TO_PRIMARY nor SESSIONS_ACTIVE, verify that redo apply is active and continue to query this view until either TO_PRIMARY or SESSIONS_ACTIVE is displayed.

If the value in the SWITCHOVER_STATUS column is TO_PRIMARY, go to step 6.

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 6, 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 6   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;

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

Issue the following statement to open the new primary database:

SQL> ALTER DATABASE OPEN;

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

Issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

SQL> ALTER DATABASE OPEN;

Step 9   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 10   Optionally, restore the failed primary database.

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 Section 13.2 or Section 13.7, or it can be re-created as a physical standby database from a backup of the new primary database using the method described in Section 3.2.

Once the original primary database has been converted into a standby database, a switchover can be performed to restore it to the primary role.

8.3 Role Transitions Involving Logical Standby Databases

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

8.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 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 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 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 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 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

Note:

You can cancel the switchover operation by issuing the following statements in the order shown:
  1. Cancel switchover on the primary database:

    SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
    
  2. Cancel the switchover on the logical standby database:

    SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
    

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$TRANSACTION 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. As described in Section 8.1.2, all other logical standbys in the configuration will become standbys of the new primary, but any physical standby databases will remain standbys of the original primary database.

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 IMMEDIATE;

8.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 13.2 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.


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   Enable remote destinations.

If you have not previously configured role-based destinations, 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.

Step 3   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 4   Recovering other standby databases after a failover

Follow the method described in Section 13.1 to ensure existing logical standby databases can continue to provide protection for the new primary database.

Step 5   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 6   Restore the failed primary database.

After a failover, the original primary database can be converted into a logical standby database of the new primary database using the method described in Section 13.2, or it can be recreated as a logical standby database from a backup of the new primary database as described in Chapter 4.

Once the original primary database has been converted into a standby database, a switchover can be performed to restore it to the primary role.

8.4 Using Flashback Database After a Role Transition

After a role transition, you can optionally use the FLASHBACK DATABASE command to revert the databases to a point in time or system change number (SCN) prior to when the role transition occurred.

In a physical standby database environment, you may need to flash back the primary database and all standby databases to maintain the Data Guard configuration. If you flash back the primary database to a certain SCN or time, you must flash back all the standby databases to either the same (or earlier) SCN or time. This way, after starting Redo Apply, the physical standby databases will automatically begin applying redo data received from the primary database.When flashing back primary or standby databases in this way, you do not have to be aware of past switchovers. Oracle can automatically flashback across past switchovers if the SCN/time is before any past switchover.

Note:

Flashback Database must be enabled on the databases before the role transition occurs. See Oracle Database Backup and Recovery User's Guide for more information

8.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.

8.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 13.2, "Converting a Failed Primary Into a Standby Database Using Flashback Database" for the complete step-by-step procedure.