备查 （ 11.2 Data Guard Physical Standby Switchover Best Practices using SQL*Plus (Doc ID 1304939.1)）
In this Document
| Goal |
| Solution |
| I. Prerequisites / Preparation |
| Apply Latest Patch Bundle. |
| Review Primary Database Initialization Parameters |
| Verify the Setup |
| Understand and Test Fallback Options |
| II. Pre-Switchover Checks |
| Verify Managed Recovery is running on the standby |
| Verify Sufficient Number of Archiver Processes |
| Ensure Online Redo Log Files on the Target Physical Standby have been cleared |
| Verify there are no large Gaps |
| Verify Primary and Standby tempfiles match and all datafiles are ONLINE |
| III. Switchover |
| Cancel any apply delay for the target standby |
| Clear Potential Blocking Parameters & Jobs |
| Shutdown all mid-tiers (optional) |
| Turn on Data Guard tracing on primary and standby |
| Tail Alert Logs (optional) on all instances |
| Create Guaranteed Restore Points (optional) |
| Verify that the primary database can be switched to the standby role |
| If The Primary is a RAC, then shutdown all secondary primary instances |
| Switchover the primary to a standby database |
| Verify the standby has received the end-of-redo (EOR) log(s) |
| Verify that the standby database can be switched to the primary role |
| Switchover the standby database to a primary |
| Open the new primary database |
| Correct any tempfile mismatch |
| Restart the new standby |
| Contingency or Fallback |
| IV. Post-Switchover Steps |
| Set Trace to Prior Value |
| Reset Jobs |
| Drop any Switchover Guaranteed Restore Points |
| References |
APPLIES TO:
Oracle Database Cloud Service - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Oracle Database - Enterprise Edition - Version 11.2.0.1 and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform.
GOAL
Perform trouble free Data Guard switchover. Note: for Data Guard switchover using the Broker please refer to Note 1305019.1 - "11.2 Data Guard Physical Standby Switchover Best Practices using the Broker"
Would you like to explore this Topic further with other Oracle Customers, Oracle Employees and Industry Experts ??
You can discuss this Note, show your Experiences or ask Questions about it directly right at the Bottom of this Note in the Discussion Thread about this Document.
If you want to discover Discussions about other Articles and Subjects or even post new Discussions you can access the My Oracle Support Community Page for High Availability Data Guard
SOLUTION
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Primary Database: DB_NAME: SFO
Standby Database: DB_UNIQUE_NAME: NYC
**************
I. Prerequisites / Preparation
Apply Latest Patch Bundle.
Review Note 785351.1 "11gR2 Upgrade Companion" Make sure to check the “Patches Recommended” tab. See Note 756671.1 for the latest available patches or patchset updates.
Review Primary Database Initialization Parameters
Further ensure 'compatible' is set correctly and to the same Value on the Primary and Standby Site.
Verify the Setup
Understand and Test Fallback Options
II. Pre-Switchover Checks
These steps should be completed before the switchover planned maintenance window begins. Our recommendation is that these are done a couple days in advance.
Verify Managed Recovery is running on the standby
The following query at the standby verifies that managed recovery is running:
The following query at the Primary verifies that recovery is running with “REAL TIME APPLY” option. In the example below, LOG_ARCHIVE_DEST_2 is established to ship redo to the target standby (dest_id=2):
If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:
Note: If you previously defined a delay for this standby the delay is ignored when you start real time apply For more information see Section 3.2.7 Verify the Physical Standby Database Is Performing Properly
Verify Sufficient Number of Archiver Processes
Ensure Online Redo Log Files on the Target Physical Standby have been cleared
Online redo logs on the target physical standby need to be cleared before that standby database can become a primary database. Although this will automatically happen as part of the SWITCHOVER TO PRIMARY command, it is recommended that the logs are cleared prior to the switchover. Setting the LOG_FILE_NAME_CONVERT parameter at the physical standby will cause the online redo logs to be automatically cleared when managed recovery is started on the standby. If your databases are using Oracle Managed Files (OMF) or you have already set the parameter LOG_FILE_NAME_CONVERT you can skip this step as the online log files will always be cleared automatically. Clearing online redo logs as part of the SWITCHOVER TO PRIMARY command can make the switchover command susceptible to termination by another process that is waiting on access to the CONTROLFILE. The CONTROLFILE waiter will attempt to kill the switchover after a timeout is 15 minutes. Oracle recommends setting LOG_FILE_NAME_CONVERT to automatically clear online redo logs on the physical standby database. In the event the primary database and the physical standby database have the exact same directory path to the online redo logs, it is acceptable to set LOG_FILE_NAME_CONVERT such that the entry pairs have the same value. As an example, if the online redo logs are stored in /oradata/order_db/redo for both the primary and physical standby databases on their respective servers, you can set the parameter value as LOG_FILE_NAME_CONVERT=’/oradata/order_db/redo/’,’/oradata/order_db/redo/’ This will initiate automatic clearing of the online redo logs on the physical standby database when managed recovery is started. Since the LOG_FILE_NAME_CONVERT parameter is not dynamic you must restart the standby database for the property change to take affect. If you have not set your environment to automatically clear the online redo logs and you do not want to restart the standby database, you should manually clear them at some point prior to the switchover. This can be done at any time. On the target physical standby run the following query to determine if the online redo logs have not been cleared:
If the above query returns rows, on the target physical standby stop Redo Apply, issue the following statement for each GROUP# returned and restart Redo Apply:
Please note that later when you do the actual switchover if it is terminated by a CONTROLFILE waiter timeout, just re-issue the SWITCHOVER TO PRIMARY command until it completes successfully. You should monitor your alert log to ensure your online redo logs are being cleared and you are not experiencing some other issue.
Verify there are no large Gaps
Identify the current sequence number for each thread on the primary database
Verify the target physical standby database has applied up to, but not including the logs from the primary query. On the standby the following query should be within 1 or 2 of the primary query result.
If large gaps exist (more than 3 logs) then see Section 6.4.3 Redo Gap Detection and Resolution.
Verify Primary and Standby tempfiles match and all datafiles are ONLINE
If the queries do not match then you can correct the mismatch now or immediately after the open of the new primary database. Prior to switchover, on the target standby, verify that all datafiles necessary for updates after role transition to primary are ONLINE. On the target standby:
If there are any OFFLINE datafiles, and these are needed after switchover, bring them ONLINE:
III. Switchover
These steps are completed as part of the switchover process on the day of the planned outage.
Cancel any apply delay for the target standby
Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database. If there is a delay then on the target standby database execute the following command.
Clear Potential Blocking Parameters & Jobs
Capture current job state on the primary:
Depending on what the running job is, be ready to terminate the job if necessary.
Note: Job candidates to be disabled among others: oracle text sync and optimizer, RMAN backups, application garbage collectors, application background agents. Block further job submission
Disable any jobs that may interfere.
Shutdown all mid-tiers (optional)
This can be done in parallel to the switchover.
Turn on Data Guard tracing on primary and standby
Tracing is turned on to have diagnostic information available in case any issues arise. Turning on tracing does not have any noticeable impact on switchover time but does require space for the trace output. Capture the current value on both the primary and the target physical standby databases
Set Data Guard trace level to 8191 on both the primary and the target physical standby databases
Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.
Tail Alert Logs (optional) on all instances
Locate alert logs by showing database parameter background_dump_dest
Tail the alert logs > tail –f <background_dump_dest location>/alert*
Create Guaranteed Restore Points (optional)
The standard switchover fallback options should suffice for successfully backing out of a switchover. However, if you want an additional fallback option then you can create a guaranteed restore point on the primary and standby database participating in the switchover. On the standby Stop the apply process
Create a guaranteed restore point
Start the apply process
On the primary Create a guaranteed restore point
Note: If a guaranteed restore points are created, make sure they are dropped post-switchover!
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:
A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command) 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 mis-configured or is not functioning properly. See Appendix A.4 Problems Switching Over to a Physical Standby Database
If The Primary is a RAC, then shutdown all secondary primary instances
A normal or immediate shutdown can be done, but to expedite the shutdown issue a SHUTDOWN ABORT on secondary RAC instances on the primary cluster only leaving one Primary instance up. Wait until the remaining Primary instance has completed cluster reconfiguration (and performed recovery if you chose to abort the secondary instances) before continuing.
Switchover the primary to a standby database
If an ORA-16139 error is encountered, as long as V$DATABASE.DATABASE_ROLE=’PHYSICAL STANDBY’, then you can proceed. A common case where this can occur is when there are a large number of data files. Once managed recovery is started on the new standby, the database will recover. If the role was not changed then you need to cancel the switchover and review the alert logs and trace files further.
Verify the standby has received the end-of-redo (EOR) log(s)
In the primary alert log you will see messages like these:
And correspondingly in the standby alert log file you should see messages like these:
In versions prior to Oracle Database 11g Release 2, the MRP (Redo Apply coordinator) would stop automatically after processing the End-of-Redo marker. With Oracle Database 11g Release 2, it no longer stops leaving all bystander standby databases still ready to apply redo from the new primary database without having to be restarted. The MRP process will be shut down automatically by the switchover command when executed at the target standby database.
Verify that the standby database can be switched to the primary role
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database:
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.
Switchover the standby database to a primary
In the standby alert log file you should see messages like these:
Open the new primary database
Note: There will be an increase in I/O activity while the new primary’s standby redo logs are cleared.
Correct any tempfile mismatch
If there was a tempfile that was not corrected during the pre-switchover check, then correct it now on the new primary.
Restart the new standby
If the new standby database (former primary database) was not shutdown since switching it to standby, bring it to the mount state and start managed recovery. This can be done in parallel to the new primary open.
Note: If you use IMMEDIATE, an ABORT will be performed anyway as of 11.2.0.2 and you would see the following in the alert log:
Performing implicit shutdown abort due to switchover to physical standby Shutting down instance (abort) License high water mark = 15 USER (ospid: 14665): terminating the instance Instance terminated by USER, pid = 14665
SQL> STARTUP MOUNT; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;Note: If you were using a delay for your standby then you would restart the apply without real time apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;Finally, if the database is a RAC, then start all secondary instances on the new standby.
Contingency or Fallback
See Appendix A.4.5 Roll Back After Unsuccessful Switchover and Start Over in the Data Guard Concepts and Administration manual.
IV. Post-Switchover Steps
Set Trace to Prior Value
For each instance on the Primary and Standby:
SQL> ALTER SYSTEM SET log_archive_trace=<prior value>;
Reset Jobs
Set the job queue processes to its original value on the new standby.
SQL> ALTER SYSTEM SET job_queue_processes=<value saved> scope=both sid=’*’Enable any jobs that were disabled.
SQL> EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name captured>);
Drop any Switchover Guaranteed Restore Points
On all databases where a Guaranteed Restore point was created
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;
编辑推荐：
下一篇：相关推荐
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 【SQL】Oracle SQL处理的流程
【SQL】Oracle SQL处理的流程
26-03-03- 【SQL】Oracle SQL共享池检查
【SQL】Oracle SQL共享池检查
26-03-03- Oracle 21C下载和安装
Oracle 21C下载和安装
26-03-03- oracle11g安装 单实例 系统centos7
oracle11g安装 单实例 系统centos7
26-03-03- Oracle 19c- 19.8应用32242453补丁
Oracle 19c- 19.8应用32242453补丁
26-03-03- 延迟块清除导致rac节点传输undo header块
延迟块清除导致rac节点传输undo header块
26-03-03- 怎么给多个视频添加相同的滚动字幕内容呢
怎么给多个视频添加相同的滚动字幕内容呢
26-03-03- 有什么简单快速获取天猫上商品详情图的方法吗？
有什么简单快速获取天猫上商品详情图的方法吗？
26-03-03- 商家如何制作互动小游戏活跃气氛 吸引粉丝？
商家如何制作互动小游戏活跃气氛 吸引粉丝？
26-03-03- kill session ORA-00031
kill session ORA-00031
26-03-03