G Performing Role Transitions Using Old Syntax

This appendix describes how to perform switchovers and failovers to a physic al standby database using procedures available in releases prior to Oracle Database 12c Release 1 (12.1). These procedures are still supported, but Oracle recommends you use the new procedures described in "Role Transitions Involving Physical Standby Databases".

If you are using a release prior to Oracle Database 12c Release 1 (12.1), then you must use the procedures described in this appendix.

G.1 SQL Syntax for Role Transitions Involving Physical Standbys

Oracle Database 12c Release 1 (12.1) introduces new SQL syntax for performing switchover and failover operations to a physical standby database. Do not mix syntax from the old procedures (described in this appendix) and the new procedures (described in Chapter 9), unless you are specifically directed to do so.

Pre-12c Role Transition Syntax for Physical Standby Databases 12c Role Transition Syntax for Physical Standby Databases

Pre-12c Role Transition Syntax for Physical Standby Databases	12c Role Transition Syntax for Physical Standby Databases
To switchover to a physical standby database, on the primary database: `SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;` On the physical standby database: `SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;`	To switchover to a physical standby database: `SQL> ALTER DATABASE SWITCHOVER TO` `target_db_name` `[FORCE] [VERIFY]`;
To failover to a physical standby database, (Step 6 and Step 8 in "Performing a Failover to a Physical Standby Database Using Old Syntax"): `SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH`; and `SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY`;	To failover to a physical standby database, the following statement replaces the two statements previously required: `SQL> ALTER DATABASE FAILOVER TO` `target_db_name`;

To switchover to a physical standby database, on the primary database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

On the physical standby database:

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

To switchover to a physical standby database:

SQL> ALTER DATABASE SWITCHOVER TO target_db_name [FORCE] [VERIFY];

To failover to a physical standby database, (Step 6 and Step 8 in "Performing a Failover to a Physical Standby Database Using Old Syntax"):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

and

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

To failover to a physical standby database, the following statement replaces the two statements previously required:

SQL> ALTER DATABASE FAILOVER TO target_db_name;

G.1.1 New Features When Using the Old Syntax

As of Oracle Database 12c Release 1 (12.1), you can issue the following statement without having to include the WITH SESSION SHUTDOWN clause:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

This statement results in active SQL sessions being killed automatically. The WITH SESSION SHUTDOWN clause is no longer needed to kill active SQL sessions.

Additionally, when you perform a switchover from an Oracle RAC primary database to a physical standby database, it is no longer necessary to shut down all but one primary database instance. All the instances are shut down automatically after the switchover is complete.

G.2 Role Transitions Involving Physical Standby Databases

The following sections describe how to perform switchovers and failovers to a physical standby database using SQL syntax that was in place in releases prior to Oracle Database 12c Release 1 (12.1).

These are the procedures that must be used if you are running a release prior to 12.1:

Performing a Switchover to a Physical Standby Database Using Old Syntax
Performing a Failover to a Physical Standby Database Using Old Syntax

G.2.1 Performing a Switchover to a Physical Standby Database Using Old Syntax

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 7 for information about configuring and monitoring redo transport.

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;

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 Mount the former primary database.

For example:

SQL> STARTUP MOUNT;

At this point in the switchover process, the original primary database is a physical standby database.

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 Step 4 returned a value of TO PRIMARY.

Step 6 Open the new primary database.

For example:

SQL> ALTER DATABASE OPEN;

Step 7 Start Redo Apply on the new physical standby database.

For example:

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

Step 8 Restart Redo Apply if it has stopped at any of the other physical standby databases in your Oracle Data Guard configuration.

For example:

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

G.2.2 Performing a Failover to a Physical Standby Database Using Old Syntax

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 errors, 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 reissue 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;

Note:

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

Step 9 Open the new primary database.

For example:

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 Oracle Data Guard configuration.

For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> 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 15.2 or Section 15.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.

G.3 Troubleshooting Switchovers to Physical Standby Databases

In most cases, following the steps described in this appendix will result in a successful switchover. However, if the switchover is unsuccessful, the following sections may help you to resolve the problem:

Note:

This troubleshooting information applies only when you are using the procedures described in this appendix.

Switchover Fails Because Redo Data Was Not Transmitted
Switchover Fails with the ORA-01102 Error
Redo Data Is Not Applied After Switchover
Roll Back After Unsuccessful Switchover and Start Over

G.3.1 Switchover Fails Because Redo Data Was Not Transmitted

If the switchover does not complete successfully, you can query the SEQUENCE# column in the V$ARCHIVED_LOG view to see if the last redo data transmitted from the original primary database was applied on the standby database. If the last redo data was not transmitted to the standby database, you can manually copy the archived redo log file containing the redo data from the original primary database to the old standby database and register it with the S QL ALTER DATABASE REGISTER LOGFILE file_specification statement. If you then start apply services, the archived redo log file will be applied automatically. Query the SWITCHOVER_STATUS column in the V$DATABASE view. A switchover to the primary role is now possible if the SWITCHOVER_STATUS column returns TO PRIMARY or SESSIONS ACTIVE:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS 
----------------- 
TO PRIMARY 
1 row selected

See Chapter 19 for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view.

To continue with the switchover, follow the instructions in Section G.2.1 and try again to switch the target standby database to the primary role.

G.3.2 Switchover Fails with the ORA-01102 Error

Suppose the standby database and the primary database reside on the same site. After both the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY and the ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY statements are successfully executed, shut down and restart the physical standby database and the primary database.

Note:

It is not necessary to shut down and restart the physical standby database if it has not been opened read-only since the instance was started.

However, the startup of the second database fails with an ORA-01102 cannot mount database in EXCLUSIVE mode error.

This could happen during the switchover if you did not set the DB_UNIQUE_NAME parameter in the initialization parameter file that is used by the standby database (that is, the original primary database). If the DB_UNIQUE_NAME parameter of the standby database is not set, the standby and the primary databases both use the same mount lock and cause the ORA-01102 error during the startup of the second database.

Action: Add DB_UNIQUE_NAME=unique_database_name to the initialization parameter file used by the standby database, and shut down and restart the standby and primary databases.

G.3.3 Redo Data Is Not Applied After Switchover

The archived redo log files are not applied to the new standby database after the switchover.

This might happen because some environment or initialization parameters were not properly set after the switchover.

Action:

Check the tnsnames.ora file at the new primary site and the listener.ora file at the new standby site. There should be entries for a listener at the standby site and a corresponding service name at the primary site.
Start the listener at the standby site if it has not been started.
Check if the LOG_ARCHIVE_DEST_n initialization parameter was set to properly transmit redo data from the primary site to the standby site. For example, query the V$ARCHIVE_DEST fixed view at the primary site as follows:
```
SQL> SELECT DEST_ID, STATUS, DESTINATION FROM V$ARCHIVE_DEST;
```
If you do not see an entry corresponding to the standby site, you need to set LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n initialization parameters.
Set the STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT initialization parameters correctly at the standby site so that the archived redo log files are applied to the desired location. (Note that the STANDBY_ARCHIVE_DEST parameter has been deprecated and is supported for backward compatibility only.)
At the standby site, set the DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT initialization parameters. Set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO if you want the standby site to automatically add new data files that are created at the primary site.

G.3.4 Roll Back After Unsuccessful Switchover and Start Over

For physical standby databases in situations where an error occurred and it is not possible to continue with the switchover, it might still be possible to revert the new physical standby database back to the primary role by using the following steps. (This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).)

Shut down and mount the new standby database (old primary).
Start Redo Apply on the new standby database.
Verify that the new standby database is ready to be switched back to the primary role. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the new 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 new standby database is ready to be switched to the primary role. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.
Issue the following statement to convert the new standby database back to the primary role:
```
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
```
If this statement is successful, the database will be running in the primary database role, and you do not need to perform any more steps.

If this statement is unsuccessful, then continue with Step 5.
When the switchover to change the role from primary to physical standby was initiated, a trace file was written in the log directory. This trace file contains the SQL statements required to re-create the original primary control file. Locate the trace file and extract the SQL statements into a temporary file. Execute the temporary file from SQL*Plus. This will revert the new standby database back to the primary role.
Shut down the original physical standby database.
Create a new standby control file. This is necessary to resynchronize the primary database and physical standby database. Copy the physical standby control file to the original physical standby system. Section 3.2.2 describes how to create a physical standby control file.
Restart the original physical standby instance.

If this procedure is successful and archive gap management is enabled, the FAL processes will start and re-archive any missing archived redo log files to the physical standby database. Force a log switch on the primary database and examine the alert logs on both the primary database and physical standby database to ensure the archived redo log file sequence numbers are correct.

See Section 7.6.3.1 for information about archive gap management and Appendix F for information about locating the trace files.
Try the switchover again.

At this point, the Oracle Data Guard configuration has been rolled back to its initial state and you can try the switchover operation again (after correcting any problems that might have led to the initial unsuccessful switchover).