参考文档: http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-transientlogicalrollingu-1-131927.pdf 以下对文档中的实例进行说明: PROD:primary PSTBY:物理standby LSTBY:逻辑standby <table style="width:70%;" class="" border="1" bordercolor="#000000" cellpadding="0" cellspacing="0"> <tbody> <tr> <td align="center" height="34" width="138"> 源主库当前角色 </td> <td align="center" width="352"> 操作及说明 </td> <td align="center" width="132"> 源从库当前角色 </td> <td align="center" width="455"> 操作及说明 </td> </tr> <tr align="center"> <td colspan="4" height="24"> PREPARATION STEPS(准备项) </td> </tr> <tr> <td height="41"> PROD </td> <td width="352"> DGMGRL> disable configuration; 如果有配置dg broker,停掉 </td> <td> PSTBY </td> <td> </td> </tr> <tr> <td height="54"> PROD </td> <td width="352"> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH; 如果有配置dg broker,停掉 </td> <td> PSTBY </td> <td width="455"> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH; </td> </tr> <tr> <td height="72"> PROD </td> <td width="352"> exec dbms_capture_adm.stop_capture (capture_name =>'STRMADMIN_CAPTURE'); 如果有用oracle stream,停掉 </td> <td> PSTBY </td> <td> </td> </tr> <tr> <td height="72"> PROD </td> <td width="352"> ALTER DATABASE ADD STANDBY LOGFILE (’+PROD’) SIZE 50M; … 添加standby logfile </td> <td> PSTBY </td> <td width="455"> ALTER DATABASE ADD STANDBY LOGFILE (’+PSTBY’) SIZE 50M; … </td> </tr> <tr> <td height="54"> PROD </td> <td width="352"> SELECT PROTECTION_MODE FROM V$DATABASE; 确定dg保护模式,必须为最高可用或最大性能. </td> <td> PSTBY </td> <td width="455"> SELECT PROTECTION_MODE FROM V$DATABASE; </td> </tr> <tr> <td height="540"> PROD </td> <td width="352"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=' LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=pstby LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL REOPEN=15 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=pstby' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_3= 'LOCATION=USE_DB_RECOVERY_FILE_DES T VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=prod' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH; 修改主库日志传输 </td> <td> PSTBY </td> <td width="455"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=' LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=pstby' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=prod LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL REOPEN=15 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=prod' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_3= 'LOCATION=USE_DB_RECOVERY_FILE_DES T VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=pstby' SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH; </td> </tr> <tr> <td height="198"> PROD </td> <td width="352"> ALTER SYSTEM SET FAL_SERVER=’pstby’ SCOPE=BOTH; ALTER SYSTEM SET FAL_CLIENT=’prod’ SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’dg_config= (prod,pstby)’ SCOPE=BOTH; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’ SCOPE=BOTH; 配置归档standby参数 </td> <td> PSTBY </td> <td width="455"> ALTER SYSTEM SET FAL_SERVER=’prod’ SCOPE=BOTH; ALTER SYSTEM SET FAL_CLIENT=’pstby’ SCOPE=BOTH; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’dg_config= (prod,pstby)’ SCOPE=BOTH; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=’AUTO’ SCOPE=BOTH; </td> </tr> <tr> <td height="72"> PROD </td> <td width="352"> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT= ’+PSTBY/’, ’+PROD/’ SCOPE=SPFILE; 日志转换 </td> <td> PSTBY </td> <td width="455"> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT= ’+PROD/’, ’+PSTBY/’ SCOPE=SPFILE; </td> </tr> <tr> <td height="126"> PROD </td> <td width="352"> SHUTDOWN IMMEDIATE (In an Oracle RAC environment for all instances) STARTUP MOUNT ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN; 开启闪回数据库.如果只是测试,可以不开 </td> <td> PSTBY </td> <td width="455"> Optional: SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE FLASHBACK ON; </td> </tr> <tr> <td height="90"> PROD </td> <td width="352"> CREATE RESTORE POINT PRE_UPGRADE_1 GUARANTEE FLASHBACK DATABASE; 建还原点,这个是必须的 </td> <td> PSTBY </td> <td width="455"> Optional: CREATE RESTORE POINT PRE_UPGRADE_2 GUARANTEE FLASHBACK DATABASE; RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; </td> </tr> <tr align="center"> <td colspan="4" height="18"> CONVERSION TO LOGICAL STANDBY STEPS物理standby转换为逻辑standby </td> </tr> <tr> <td height="54"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 取消日志应用 </td> </tr> <tr> <td height="66"> PROD </td> <td width="352"> EXECUTE DBMS_LOGSTDBY.BUILD; 建立logminer字典 </td> <td> PSTBY </td> <td> </td> </tr> <tr> <td height="142"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> If Oracle RAC then must be mounted exclusive. ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT 如果是rac,CLUSTER_DATABASE设为false </td> </tr> <tr> <td height="68"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY; 从库转换为临时逻辑standby </td> </tr> <tr> <td height="126"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> If Oracle RAC then reset CLUSTER_DATABASE. ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT 如果是rac,CLUSTER_DATABASE设为true </td> </tr> <tr> <td height="18"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> ALTER DATABASE OPEN; </td> </tr> <tr> <td height="158"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> Note: If using EDS, skip this step. Do not start SQL Apply until after the logical standby upgrade and after the steps for “Configuring EDS Logging Table Triggers” in Appendix B are run. ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; 逻辑standby应用sql apply </td> </tr> <tr> <td height="59"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> EXECUTE DBMS_LOGSTDBY.APPLY_SET( 'LOG_AUTO_DELETE', 'FALSE'); 禁用自动归档日志自动删除 </td> </tr> <tr> <td height="84"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> Wait for this query to display “IDLE”: SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE; 等待逻辑standby状态为idle </td> </tr> <tr align="center"> <td colspan="4" height="18"> CREATE AN ARCHIVED REDO LOG REPOSITORY建重做日志存储库(可选) </td> </tr> <tr align="center"> <td colspan="4" height="18"> PERFORM PATCH APPLY OR UPGRADE STEPS升级从库 </td> </tr> <tr> <td height="72"> PROD </td> <td width="352"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=MEMORY; 停止日志传输 </td> <td> LSTBY </td> <td width="455"> ALTER DATABASE STOP LOGICAL STANDBY APPLY; 停止逻辑standby sql apply </td> </tr> <tr> <td height="88"> PROD </td> <td> </td> <td> LSTBY </td> <td width="455"> CREATE RESTORE POINT PRE_UPGRADE_3 GUARANTEE FLASHBACK DATABASE; SHUTDOWN IMMEDIATE 建还原点,用于失败回退.测试可不用 </td> </tr> <tr> <td height="54"> PROD </td> <td> </td> <td> PSTBY </td> <td width="455"> RMAN> CATALOG START WITH '+PSTBY/ALOGREP/ARCHIVELOG/’; 与重做日志存储库相关 </td> </tr> <tr> <td height="77"> PROD </td> <td width="352"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=MEMORY; 开户日志传输 </td> <td> LSTBY </td> <td width="455"> STARTUP </td> </tr> <tr> <td height="127"> PROD </td> <td> </td> <td> LSTBY </td> <td width="455"> Note: If using EDS, perform the steps in “Configuring EDS Logging Table Triggers” in Appendix B before starting SQL Apply. ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; 开启逻辑standby sql apply </td> </tr> <tr align="center"> <td colspan="4" height="18"> CREATE AN ARCHIVED REDO LOG REPOSITORY建重做日志存储库(可选) </td> </tr> <tr align="center"> <td colspan="4" height="18"> SWITCHOVER(主从切换) </td> </tr> <tr> <td height="116"> PROD </td> <td width="352"> Wait for this query to display “TO STANDBY” or proceed to switchover with session disconnect SELECT SWITCHOVER_STATUS FROM V$DATABASE; 查看switchover_status的状态 </td> <td> LSTBY->PROD </td> <td> </td> </tr> <tr> <td height="64"> PROD </td> <td width="352"> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; 源主库切换到逻辑standby </td> <td> LSTBY->PROD </td> <td> </td> </tr> <tr> <td height="80"> PROD </td> <td> </td> <td> LSTBY->PROD </td> <td width="455"> Wait for this query to display “TO PRIMARY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE; </td> </tr> <tr> <td height="54"> PROD </td> <td> </td> <td> LSTBY->PROD </td> <td width="455"> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY; 源从库切换为从库 </td> </tr> <tr> <td height="167"> PROD </td> <td> </td> <td> LSTBY->PROD </td> <td width="455"> If using Oracle Streams then start Streams capture. Note capture will go back in time and mine the redo logs that got generated while it was down. exec dbms_capture_adm.start_capture ('STRMADMIN_CAPTURE'); 如果有oracle stream,开启之前停用的 </td> </tr> <tr align="center"> <td colspan="4" height="18"> RETRANSFORMATION INTO A PHYSICAL STANDBY DATABASE逻辑standby回到物理standby </td> </tr> <tr> <td height="72"> LSTBY->PSTBY </td> <td> </td> <td> PROD </td> <td width="455"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=MEMORY; 停用日志传输 </td> </tr> <tr> <td height="90"> LSTBY->PSTBY </td> <td width="352"> SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE_1; 通过还原点,闪回到还原点. </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="18"> LSTBY->PSTBY </td> <td> SHUTDOWN IMMEDIATE; </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="40"> LSTBY->PSTBY </td> <td width="352"> Switch to the new ORACLE_HOME 切换到软件升级后的ORACLE_HOM </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="72"> LSTBY->PSTBY </td> <td width="352"> STARTUP MOUNT; ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 切换逻辑standby为物理standby </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="36"> PSTBY </td> <td width="352"> SHUTDOWN IMMEDIATE STARTUP MOUNT; </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="89"> PSTBY </td> <td width="352"> ALTER SYSTEM SET COMPATIBLE=’...’ SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT; 修改兼容性参数 </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="46"> PSTBY </td> <td width="352"> RMAN> CATALOG START WITH '+PROD/ALOGREP/ARCHIVELOG/’; 与重做日志存储库相关 </td> <td> PROD </td> <td> </td> </tr> <tr> <td height="61"> PSTBY </td> <td width="352"> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 开启物理standby实时应用 </td> <td> PROD </td> <td width="455"> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 开启日志传输 </td> </tr> <tr align="center"> <td colspan="4" height="18"> SWITCHBACK STEPS(主从切回) </td> </tr> <tr> <td height="75"> PSTBY->PROD </td> <td> </td> <td> PROD->PSTBY </td> <td width="455"> Wait for this query to display “TO STANDBY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE; </td> </tr> <tr> <td height="70"> PSTBY->PROD </td> <td> </td> <td> PROD->PSTBY </td> <td width="455"> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; 切换回物理standby </td> </tr> <tr> <td height="90"> PSTBY->PROD </td> <td width="352"> Wait for this query to display “TO PRIMARY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE; </td> <td> PROD->PSTBY </td> <td> </td> </tr> <tr> <td height="54"> PSTBY->PROD </td> <td width="352"> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 切换回主库 </td> <td> PROD->PSTBY </td> <td> </td> </tr> <tr> <td height="44"> PSTBY->PROD </td> <td width="352"> ALTER DATABASE OPEN; </td> <td> PROD->PSTBY </td> <td width="455"> SHUTDOWN IMMEDIATE; STARTUP MOUNT; </td> </tr> <tr> <td height="51"> PSTBY->PROD </td> <td> </td> <td> PROD->PSTBY </td> <td width="455"> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 开启物理standby 实时应用 </td> </tr> <tr> <td height="54"> PROD </td> <td width="352"> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE =’YES’; </td> <td> PSTBY </td> <td width="455"> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE =’YES’; </td> </tr> <tr> <td height="45"> PROD </td> <td width="352"> DROP RESTORE POINT PRE_UPGRADE_1; 删除之前建的还原点 </td> <td> PSTBY </td> <td width="455"> DROP RESTORE POINT <name>; </td> </tr> <tr> <td height="49"> PROD </td> <td width="352"> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; 开启之前关闭的dg broker </td> <td> PSTBY </td> <td width="455"> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; </td> </tr> <tr> <td height="18"> PROD </td> <td width="352"> DGMGRL> enable configuration; </td> <td> </td> <td> </td> </tr> </tbody> </table> 注意: 1.转换为逻辑standby时,要关注不被支持的数据类型,通过dba_logstdby_unsupported视图. 2.这种物理standby借助临时逻辑standby滚动升级,源从库升级软件和数据库,源主库升级软件.源主库的数据库升级是通过物理standby以日志的方式传过去的.而正常的逻辑standby滚动升级是主从都要进行软件和数据库升级的. 3.在测试环境,主库的还原点是必须的,就是通过这个还原点把逻辑standby返回到物理standby的. 4.滚动升级还要考虑主从切换时,客户端的连接方案. 5.源从库升级后,考虑通过database replay,sql performance analyzer,sql plan management进行升级后的性能测试.

ITPub博客

11G通过物理standby进行滚动升级实例说明及注意

最新文章