This appendix describes how to perform switchovers and failovers to a physical 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.
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 |
---|---|
To switchover to a physical standby database, on the primary database:
On the physical standby database:
|
To switchover to a physical standby database:
|
To failover to a physical standby database, (Step 6 and Step 8 in "Performing a Failover to a Physical Standby Database Using Old Syntax"):
and
|
To failover to a physical standby database, the following statement replaces the two statements previously required:
|
See Also:
Oracle Database SQL Language Reference for more information about the SQL syntax discussed in this appendix
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.
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
See Also:
Chapter 9 for information about how to prepare for switchovers and failoversThis 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.
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.
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.
For example:
SQL> STARTUP MOUNT;
At this point in the switchover process, the original primary database is a physical standby database.
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
.
Issue the following SQL statement on the target physical standby database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Note:
TheWITH SESSION SHUTDOWN
clause can be omitted from the switchover statement if the query performed in Step 4 returned a value of TO PRIMARY
.For example:
SQL> ALTER DATABASE OPEN;
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE - > DISCONNECT FROM SESSION;
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE - > DISCONNECT FROM SESSION;
This section describes how to perform a failover to a physical 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.
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';
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';
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.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
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.
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.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Note:
TheWITH 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
.For example:
SQL> ALTER DATABASE OPEN;
Oracle recommends that a full backup be taken of the new primary database.
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE - > DISCONNECT FROM SESSION;
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.
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.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 SQL 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 SWITCHO
VER_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.
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.
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.
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).