11G and earlier
主库操作
step 1.执行命令，将主库切换为备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
执行完整个命令，主库自动停机，需要启动起来。
step 2.打开数据库，应用日志
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE DISCONNECT FROM SESSION;
如果要打开数据库，请先断开同步连接，打开数据库后再执行同步
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE DISCONNECT FROM SESSION;
备库操作
step 1.执行命令，将备库切换为主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO primary;
step 2.手动打开数据库
SQL> ALTER DATABASE OPEN;
After 11G
Performing a Switchover to a Physical Standby Database
These steps describe how to perform a switchover to a physical standby database.
Note:If there is a far sync instance (or a combination of preferred and alternate far sync instances) connecting the primary and standby databases, then the procedure to switchover to the standby is the same as described in this topic. Whether the far sync instances are available or unavailable does not affect switchover. During switchover, the primary and standby must be able to communicate directly with each other and perform the switchover role transition steps oblivious of the far sync instances. See “Using Far Sync Instances” in Oracle Data Guard Concepts and Administration for examples of how to set up such configurations correctly so that the far sync instances can service the new roles of the two databases after switchover.
step 1.Verify that the target standby database is ready for switchover.
The switchover statement has a VERIFY option that results in checks being performed of many conditions required for switchover. Some of the items checked are: whether Redo Apply is running on the switchover target; whether the release version of the switchover target is 12.1 or later; whether the switchover target is synchronized; and whether it has MRP running.
Suppose the primary database has a DB_UNIQUE_NAME of BOSTON and the switchover target standby database has a DB_UNIQUE_NAME of CHICAGO. On the primary database BOSTON, issue the following SQL statement to verify that the switchover target, CHICAGO, is ready for switchover:
SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target
If this operation had been successful, a Database Altered message would have been returned but in this example an ORA-16470 error was returned. This error means that the switchover target CHICAGO is not ready for switchover. Redo Apply must be started before the switchover operation.
After Redo Apply is started, issue the following statement again:
SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
The switchover target, CHICAGO, is ready for switchover. However, the warnings indicated by the ORA-16475 error may affect switchover performance. The alert log contains messages similar to the following:
SWITCHOVER VERIFY WARNING: switchover target has dirty online redo logfiles that require clearing. It takes time to clear online redo logfiles. This may slow down switchover process.
You can fix the problems or if switchover performance is not important, those warnings can be ignored. After making any fixes you determine are necessary, issue the following SQL statement again:
SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;
Database altered.
The switchover target, CHICAGO, is now ready for switchover.
step 2.Initiate the switchover on the primary database, BOSTON, by issuing the following SQL statement:
SQL> ALTER DATABASE SWITCHOVER TO CHICAGO;
Database altered.
If this statement completes without any errors, proceed to Step 3.
If an error occurs, mount the old primary database (BOSTON) and the old standby database (CHICAGO). On both databases, query DATABASE_ROLE from V$DATABASE. There are three possible combinations of database roles for BOSTON and CHICAGO. The following table describes these combinations and provides the likely cause and a high level remedial action for each situation. For details on specific error situations, see “Troubleshooting Oracle Data Guard” in Oracle Data Guard Concepts and Administration.
step 3.Issue the following SQL statement on the new primary database, CHICAGO, to open it.
SQL> ALTER DATABASE OPEN;
step 4.Issue the following SQL statement to mount the new physical standby database, BOSTON:
SQL> STARTUP MOUNT;
Or, if BOSTON is an Oracle Active Data Guard physical standby database, then issue the following SQL statement to open it read only:
SQL> STARTUP;
step 5.Start Redo Apply on the new physical standby database. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Performing a Failover to a Physical Standby Database
These steps describe how to perform a failover to a physical standby database.
step 1.If the primary database can be mounted, then 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.
First, ensure that Redo Apply is active at the target standby database. Then mount, but do not open the primary database. If the primary database cannot be mounted, go to Step 2.
If not already done, then set up the remote LOG_ARCHIVE_DEST_n configured at the primary to point to the target destination. (You may not have any remote LOG_ARCHIVE_DEST_n configured if the target destination was serviced by a far sync instance, or was a terminal standby in a cascaded configuration.) Also, ensure that the primary can connect to the target destination by verifying that the NET_ALIAS_TARGET_DB_NAME is valid and properly established.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6='SERVICE=NET_ALIAS_TARGET_DB_NAME -
> ASYNC VALID_FOR=(online_logfile, primary_role) -
> DB_UNIQUE_NAME="target_db_unique_name"' SCOPE=memory;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;
It is also assumed that the LOG_ARCHIVE_CONFIG specification includes the DB_UNIQUE_NAME of the target destination at the primary (and LOG_ARCHIVE_CONFIG at the target destination includes the DB_UNIQUE_NAME of the primary). If not, then add that information to the LOG_ARCHIVE_CONFIG at the primary and target destination as required.
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.Query the V$ARCHIVED_LOG view on the target standby database to obtain the highest log sequence number for each redo thread.
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.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
step 3.Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.
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.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
step 4.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), then you can expect some data loss during the failover.
step 5.Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
step 6.Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE FAILOVER TO target_db_name;
For example, suppose the target standby database is named CHICAGO:
SQL> ALTER DATABASE FAILOVER TO CHICAGO;
If this statement completes without any errors, proceed to Step 10.
If there are errors, go to Step 7.
step 7.If an error occurs, try to resolve the cause of the error and then reissue the statement.
- If successful, go to Step 10.
- If the error still occurs and it involves a far sync instance, go to Step 8.
- If the error still occurs and there is no far sync instance involved, go to Step 9.
step 8.This step is for far sync instance error cases only. If the error involves a far sync instance (for example, it is unavailable) and you have tried resolving the issue and reissuing the statement without success, then you can use the FORCE option. For example:
SQL> ALTER DATABASE FAILVOVER TO CHICAGO FORCE;
The FORCE option instructs the failover to ignore any failures encountered when interacting with the far sync instance and proceed with the failover, if at all possible. (The FORCE option has meaning only when the failover target is serviced by a far sync instance.)
If the FORCE option is successful, go to Step 10.
If the FORCE option is unsuccessful, go to Step 9.
step 9.Perform a data loss failover.
If an 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;
In the following example, the failover operation fails with an ORA-16472 error. That error means the database is configured in MaxAvailability or MaxProtection mode but data loss is detected during failover.
SQL> ALTER DATABASE FAILOVER TO CHICAGO;
ERROR at line 1:
ORA-16472: failover failed due to data loss
You can complete the data loss failover by issuing the following SQL statement:
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
step 10.Open the new primary database:
SQL> ALTER DATABASE OPEN;
step 11.Oracle recommends that you perform a full backup of the new primary database.
step 12.If Redo Apply has stopped at any of the other physical standby databases in your Data Guard configuration, then restart it. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
step 13.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 Converting a Failed Primary Into a Standby Database Using Flashback Database or Converting a Failed Primary into a Standby Database Using RMAN Backups, or it can be re-created as a physical standby database from a backup of the new primary database using the method described in Step-by-Step Instructions for Creating a Physical Standby Database.
Once the original primary database is running in the standby role, a switchover can be performed to restore it to the primary role.
