G Performing Role Transitions Using Old Syntax
Prior to Oracle Database 12c Release 1 (12.1), the procedures for performing switchovers and failovers to a physical standby database were different.
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 old procedures.
The following topics are discussed:
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.
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 topic) and the new procedures (described in Role Transitions), 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 SQL syntax
G.1.1 New Features When Using the Old Syntax
As of Oracle Database 12c Release 1 (12.1), the WITH SESSION SHUTDOWN
clause is no longer needed to kill active SQL sessions.
You can issue the following statement to automatically kill active SQL sessions:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
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 SQL syntax to perform switchovers and failovers to a physical standby database was different in releases prior to Oracle Database 12c Release 1 (12.1).
The following 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:
Role Transitions for information about how to prepare for switchovers and failovers
G.2.1 Performing a Switchover to a Physical Standby Database Using Old Syntax
A switchover is initiated on the primary database and is completed on the target standby database.
This topic describes how to perform a switchover to a physical standby database.
G.3 Troubleshooting Switchovers to Physical Standby Databases
These are some of the problems that can occur during a switchover to a physical standby database.
Note:
The following troubleshooting topics apply only when you are performing switchovers and failovers to a physical standby database using procedures available in releases prior to Oracle Database 12c Release 1 (12.1).
G.3.1 Switchover Fails Because Redo Data Was Not Transmitted
If a 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 is 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 Views Relevant to Oracle Data Guard 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 Performing a Switchover to a Physical Standby Database Using Old Syntax and try again to switch the target standby database to the primary role.
G.3.2 Switchover Fails with the ORA-01102 Error
These are some of the possible causes, and solutions, if you receive an 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 (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
If archived redo log files are not applied to the new standby database after the switchover, it may be 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 thelistener.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 theV$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
andLOG_ARCHIVE_DEST_STATE_
n
initialization parameters. -
Verify that the
LOG_ARCHIVE_FORMAT
initialization parameter is set correctly at the standby site. -
At the standby site, set the
DB_FILE_NAME_CONVERT
andLOG_FILE_NAME_CONVERT
initialization parameters. Set theSTANDBY_FILE_MANAGEMENT
initialization parameter toAUTO
to enable 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.
Take the following steps: