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

參考文件:
http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

以下對文件中的例項進行說明:
PROD:primary
PSTBY:物理standby
LSTBY:邏輯standby

源主庫當前角色	操作及說明	源從庫當前角色	操作及說明
PREPARATION STEPS(準備項)
PROD	DGMGRL> disable configuration; 如果有配置dg broker,停掉	PSTBY
PROD	ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH; 如果有配置dg broker,停掉	PSTBY	ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
PROD	exec dbms_capture_adm.stop_capture (capture_name =>'STRMADMIN_CAPTURE'); 如果有用oracle stream,停掉	PSTBY
PROD	ALTER DATABASE ADD STANDBY LOGFILE (’+PROD’) SIZE 50M; … 新增standby logfile	PSTBY	ALTER DATABASE ADD STANDBY LOGFILE (’+PSTBY’) SIZE 50M; …
PROD	SELECT PROTECTION_MODE FROM V$DATABASE; 確定dg保護模式,必須為最高可用或最大效能.	PSTBY	SELECT PROTECTION_MODE FROM V$DATABASE;
PROD	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; 修改主庫日誌傳輸	PSTBY	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;
PROD	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引數	PSTBY	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;
PROD	ALTER SYSTEM SET LOG_FILE_NAME_CONVERT= ’+PSTBY/’, ’+PROD/’ SCOPE=SPFILE; 日誌轉換	PSTBY	ALTER SYSTEM SET LOG_FILE_NAME_CONVERT= ’+PROD/’, ’+PSTBY/’ SCOPE=SPFILE;
PROD	SHUTDOWN IMMEDIATE (In an Oracle RAC environment for all instances) STARTUP MOUNT ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN; 開啟閃回資料庫.如果只是測試,可以不開	PSTBY	Optional: SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE FLASHBACK ON;
PROD	CREATE RESTORE POINT PRE_UPGRADE_1 GUARANTEE FLASHBACK DATABASE; 建還原點,這個是必須的	PSTBY	Optional: CREATE RESTORE POINT PRE_UPGRADE_2 GUARANTEE FLASHBACK DATABASE; RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
CONVERSION TO LOGICAL STANDBY STEPS物理standby轉換為邏輯standby
PROD		PSTBY	ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 取消日誌應用
PROD	EXECUTE DBMS_LOGSTDBY.BUILD; 建立logminer字典	PSTBY
PROD		PSTBY	If Oracle RAC then must be mounted exclusive. ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT 如果是rac,CLUSTER_DATABASE設為false
PROD		PSTBY	ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY; 從庫轉換為臨時邏輯standby
PROD		PSTBY	If Oracle RAC then reset CLUSTER_DATABASE. ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT 如果是rac,CLUSTER_DATABASE設為true
PROD		PSTBY	ALTER DATABASE OPEN;
PROD		PSTBY	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
PROD		PSTBY	EXECUTE DBMS_LOGSTDBY.APPLY_SET( 'LOG_AUTO_DELETE', 'FALSE'); 禁用自動歸檔日誌自動刪除
PROD		PSTBY	Wait for this query to display “IDLE”: SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE; 等待邏輯standby狀態為idle
CREATE AN ARCHIVED REDO LOG REPOSITORY建重做日誌儲存庫(可選)
PERFORM PATCH APPLY OR UPGRADE STEPS升級從庫
PROD	ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=MEMORY; 停止日誌傳輸	LSTBY	ALTER DATABASE STOP LOGICAL STANDBY APPLY; 停止邏輯standby sql apply
PROD		LSTBY	CREATE RESTORE POINT PRE_UPGRADE_3 GUARANTEE FLASHBACK DATABASE; SHUTDOWN IMMEDIATE 建還原點,用於失敗回退.測試可不用
PROD		PSTBY	RMAN> CATALOG START WITH '+PSTBY/ALOGREP/ARCHIVELOG/’; 與重做日誌儲存庫相關
PROD	ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=MEMORY; 開戶日誌傳輸	LSTBY	STARTUP
PROD		LSTBY	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
CREATE AN ARCHIVED REDO LOG REPOSITORY建重做日誌儲存庫(可選)
SWITCHOVER(主從切換)
PROD	Wait for this query to display “TO STANDBY” or proceed to switchover with session disconnect SELECT SWITCHOVER_STATUS FROM V$DATABASE; 檢視switchover_status的狀態	LSTBY->PROD
PROD	ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; 源主庫切換到邏輯standby	LSTBY->PROD
PROD		LSTBY->PROD	Wait for this query to display “TO PRIMARY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE;
PROD		LSTBY->PROD	ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY; 源從庫切換為從庫
PROD		LSTBY->PROD	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,開啟之前停用的
RETRANSFORMATION INTO A PHYSICAL STANDBY DATABASE邏輯standby回到物理standby
LSTBY->PSTBY		PROD	ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=MEMORY; 停用日誌傳輸
LSTBY->PSTBY	SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE_1; 通過還原點,閃回到還原點.	PROD
LSTBY->PSTBY	SHUTDOWN IMMEDIATE;	PROD
LSTBY->PSTBY	Switch to the new ORACLE_HOME 切換到軟體升級後的ORACLE_HOM	PROD
LSTBY->PSTBY	STARTUP MOUNT; ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 切換邏輯standby為物理standby	PROD
PSTBY	SHUTDOWN IMMEDIATE STARTUP MOUNT;	PROD
PSTBY	ALTER SYSTEM SET COMPATIBLE=’...’ SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT; 修改相容性引數	PROD
PSTBY	RMAN> CATALOG START WITH '+PROD/ALOGREP/ARCHIVELOG/’; 與重做日誌儲存庫相關	PROD
PSTBY	RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 開啟物理standby實時應用	PROD	ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 開啟日誌傳輸
SWITCHBACK STEPS(主從切回)
PSTBY->PROD		PROD->PSTBY	Wait for this query to display “TO STANDBY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE;
PSTBY->PROD		PROD->PSTBY	ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN; 切換回物理standby
PSTBY->PROD	Wait for this query to display “TO PRIMARY” or proceed to switchover with session disconnect: SELECT SWITCHOVER_STATUS FROM V$DATABASE;	PROD->PSTBY
PSTBY->PROD	ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 切換回主庫	PROD->PSTBY
PSTBY->PROD	ALTER DATABASE OPEN;	PROD->PSTBY	SHUTDOWN IMMEDIATE; STARTUP MOUNT;
PSTBY->PROD		PROD->PSTBY	RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 開啟物理standby 實時應用
PROD	SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE =’YES’;	PSTBY	SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE =’YES’;
PROD	DROP RESTORE POINT PRE_UPGRADE_1; 刪除之前建的還原點	PSTBY	DROP RESTORE POINT <name>;
PROD	ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; 開啟之前關閉的dg broker	PSTBY	ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
PROD	DGMGRL> enable configuration;

注意:
1.轉換為邏輯standby時,要關注不被支援的資料型別,通過dba_logstdby_unsupported檢視.
2.這種物理standby藉助臨時邏輯standby滾動升級,源從庫升級軟體和資料庫,源主庫升級軟體.源主庫的資料庫升級是通過物理standby以日誌的方式傳過去的.而正常的邏輯standby滾動升級是主從都要進行軟體和資料庫升級的.
3.在測試環境,主庫的還原點是必須的,就是通過這個還原點把邏輯standby返回到物理standby的.
4.滾動升級還要考慮主從切換時,客戶端的連線方案.
5.源從庫升級後,考慮通過database replay,sql performance analyzer,sql plan management進行升級後的效能測試.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1803392/，如需轉載，請註明出處，否則將追究法律責任。