Oracle Data Guard -- Switchover and Failover

Oracle Data Guard Concepts and Administration 11g Release 2 (11.2) E41134-03
https://docs.oracle.com/cd/E11882_01/server.112/e41134.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. 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:
■ Introduction to Role Transitions
■ Role Transitions Involving Physical Standby Databases
■ Role Transitions Involving Logical Standby Databases
■ Using Flashback Database After a Role Transition

8.1 Introduction to Role Transitions
8.1.1 Preparing for a Role Transition
8.1.2 Choosing a Target Standby Database for a Role Transition
8.1.3 Switchovers

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.

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" on
page 8-2 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"

---

---

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

The following sections describe how to perform a switchover or failover to a physical
standby database:
■ Performing a Switchover to a Physical Standby Database
■ Performing a Failover to a Physical Standby Database

8.2.1 Performing a Switchover to a Physical Standby Database
This section describes how to perform a switchover to a physical standby database.
A switchover is initiated on the primary database and is completed on the target
standby database.

Step 1 Verify that the primary database can be switched to the standby role.
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary
database.
For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS

TO STANDBY

1 row selected

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be
switched to the standby role. If neither of these values is returned, a switchover is not
possible because redo transport is either misconfigured or is not functioning properly.
See Chapter 6 for information about configuring and monitoring redo transport.
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"
Role Transitions Involving Physical Standby Databases
Role Transitions 8-9

Step 2 Initiate the switchover on the primary database.
Issue the following SQL statement on the primary database to switch it to the standby
role:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

This statement converts the primary database into a physical 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 then mount the former primary database.
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
At this point in the switchover process, the original primary database is a physical
standby database (see Figure 8–2).

Step 4 Verify that the switchover target is ready to be switched to the primary role.
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby
database.
For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS

TO_PRIMARY
1 row selected

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is
ready to be switched to the primary role. If neither of these values is returned, verify
that Redo Apply is active and that redo transport is configured and working properly.
Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

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

Issue the following SQL statement on the target physical standby database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Note: The WITH SESSION SHUTDOWN clause can be omitted from the
switchover statement if the query performed in the previous step
returned a value of TO STANDBY.

Note: In Oracle Database 11g release 2 (11.2.0.4) and later, it is not
necessary to issue the SHUTDOWN ABORT statement in this step because
the database instance is shut down by default when the ALTER
DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION
SHUTDOWN statement is issued.

Step 6 Open the new primary database.
SQL> ALTER DATABASE OPEN;

Step 7 Start Redo Apply on the new physical standby database.
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 8 Restart Redo Apply if it has stopped at any of the other physical standby
databases in your Data Guard configuration.
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

---

---

8.2.2 Performing a Failover to a Physical Standby Database

This section describes how to perform a failover to a physical standby database.

Step 1 Flush any unsent redo from the primary database to the target standby database.

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

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

Issue the following SQL statement at the primary database:

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database.
This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database.

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

Step 2 Verify that the standby database has the most recently archived redo log file for each primary database redo thread.

Query the V$ARCHIVED_LOG view on the target standby database to obtain the highest log sequence number for each redo thread.
For example:
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
THREAD LAST

---

1 100

If possible, copy the most recently archived redo log file for each primary database redo thread
to the standby database if it does not exist there, and register it. This must be done for each redo thread.
For example:
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 3 Identify and resolve any archived redo log gaps.

Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.
For example:
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---

1 90 92

In this example the gap comprises archived redo log files with sequence numbers 90, 91, and 92 for thread 1.
If possible, copy any missing archived redo log files to the target standby database from the primary database and register them at the target standby database.
This must be done for each redo thread.
For example:
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 4 Repeat Step 3 until all gaps are resolved.
The query executed in Step 3 displays information for the highest gap only. After resolving a gap, you must repeat the query until no more rows are returned.
If, after performing Step 2 through Step 4, you are not able to resolve all gaps in the archived redo log files (for example, because you do not have access
to the system that hosted the failed primary database), some data loss will occur during the failover.

Step 5 Stop Redo Apply.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 6 Finish applying all received redo data.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If this statement completes without any errors, proceed to Step 7.
If an error occurs, some received redo data was not applied. Try to resolve the cause of the error and re-issue the statement before proceeding to the next step.

Note that if there is a redo gap that was not resolved in Step 3 and Step 4, you will receive an error stating that there is a redo gap.
If the error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement
on the target standby database:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Proceed to Step 9 when the ACTIVATE statement completes.

Step 7 Verify that the target 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

A value of either TO PRIMARY or SESSIONS ACTIVE indicates that the standby database
is ready to be switched to the primary role. If neither of these values is returned, verify
that Redo Apply is active and continue to query this view until either TO PRIMARY or
SESSIONS ACTIVE is returned.

Step 8 Switch the physical standby database to the primary role.
Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Step 9 Open the new primary database.
SQL> ALTER DATABASE OPEN;

Step 10 Back up the new primary database.
Oracle recommends that a full backup be taken of the new primary database.

Step 11 Restart Redo Apply if it has stopped at any of the other physical standby databases in your Data Guard configuration.
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 12 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 is running in the standby role, a switchover can be performed to restore it to the primary role.