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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1803392/