參考文件: /> 以下對文件中的例項進行說明: 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進行升級後的效能測試.

11G透過物理standby進行滾動升級例項說明及注意

相關文章