11G通過邏輯standby滾動升級例項說明及注意

參考文件:
http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-rollingupgradebestprac-1-132006.pdf

以下是對文件中例項說明:
    

Step	Primary	Standby	Notes
I. Prerequisites 先決條件
1	Optional: Turn flashback database on SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN;	Optional: Turn flashback database on SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE FLASHBACK ON; SQL> ALTER DATABASE OPEN;	If flashback is already on then this step can be skipped. This can also be optional if you just use restore points without flashback database. The database must be in a mount state to turn flashback on. 開啟閃回資料庫.如果只想使用還原點,可以不開閃回資料庫.
2		Create a guaranteed restore point SQL> CREATE RESTORE POINT PRE_UPGRADE GUARANTEE FLASHBACK DATABASE;	This will be used to flashback the standby site This does not require flashback database to be on, i.e. a guaranteed restore point can be created without turning flashback database on. Creating a guaranteed restore point without flashback database enabled requires a single instance to be mounted to create the initial guaranteed restore point. Note that guaranteed restore point is available beginning with Oracle Database 10g Release 2. If you are upgrading from Oracle Database 10g Release 1 – see the note in step 5, below. 建還原點.如果只是為了測試,不準備做回退,不建也可以.
3	Ensure any necessary patches are applied as detailed in the “Patch/Upgrade Preparation Best Practices” section of this paper	Ensure any necessary patches are applied as detailed in the “Patch /Upgrade Preparation Best Practices” section of this paper.	準備補丁升級包
II. Create a Temporary Archive redo log repository (optional) 建立重做日誌儲存庫(可選項)
4		Follow MetaLink Note 434164.1	Optionally, an archived redo log repository can be created with the same database version and COMPATIBLE setting as the primary so that redo is still received during the patch apply/upgrade. See MetaLink Note 434164.1 for setting that up. This will ensure that the recovery point objective can be met in the event of a primary site failure during this step. 建立與主資料庫相同的歸檔重做日誌儲存庫,以便在打補丁或升級資料庫的期間仍然可以接收重做資料.確保升級失敗時滿足還原點目標.可選項
III. Perform the Upgrade on the Standby(升級standby)
5		Create a guaranteed restore point SQL> CREATE RESTORE POINT PRE_LOGICAL_UPGRADE GUARANTEE FLASHBACK DATABASE;	Note that if this an 10.1.0.3 database or a later 10.1 release, then capture the current SCN as the flashback database point. SQL> SELECT CURRENT_SCN FROM V$DATABASE; 建還原點.如果只是為了測試,不準備做回退,不建也可以.
6		Shutdown the logical standby database
7		Apply 11.1.0.7 patchset to CRS and ASM
8		10g only Follow MetaLink note 300479.1 to implement the workaround for issue 5236922	This will ensure that there are no Enterprise Manager components installed on the logical standby
9		Install the new 11.1.0.7 Patch set out-of-place	For an out-of-place patchset apply the existing ORACLE_HOME is cloned using the Cloning procedure in Appendix E.
10		Stop Logical Standby Apply SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY ;	SQL Apply should not be running 停止logical standby sql apply
11		If not using DBUA then set cluster_database=false in preparation for upgrade SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;	If using the Database Upgrade Assistant (dbua). dbua is executed from the new ORACLE_HOME. dbua requires CLUSTER_DATABASE=TRUE for a RAC database and it takes care of the OCR updates for you. 如果不用DBUA進行升級,rac資料庫需要設定CLUSTER_DATABASE=FALSE
12		Upgrade the database	You can now choose either the Database Upgrade Assistant (DBUA) or the manual upgrade method with catupgrd.sql when executing the actual database upgrade on the logical standby. Using the DBUA is the recommended method. It takes care of all parameter changes and of any updates to the Oracle Cluster Registry (OCR) in the case of a RAC system. Refer to the “Oracle Database Upgrade Guide 11.1 ” [7] for complete upgrade instructions. If you did not use DBUA then remember to set cluster_database=true before bouncing the database after the upgrade. 升級資料庫
13		If a temporary log archive repository was used then register logs so they don?t have to be resent: SQL> ALTER DATABASE REGISTER LOGFILE ?…. Optionally, you can use the RMAN CATALOG command to catalog the archived redo log repository archivelog destination. e.g.: RMAN> CATALOG START WITH '+ASMDG/ALOGREP/ARCHIVELOG/?;	database after the upgrade. 13 If a temporary log archive repository was used then register logs so they don?t have to be resent: SQL> ALTER DATABASE REGISTER LOGFILE ?…. Optionally, you can use the RMAN CATALOG command to catalog the archived redo log repository archivelog destination. e.g.: RMAN> CATALOG START WITH '+ASMDG/ALOGREP/ARCHIVELOG/?; This is if an archive log repository was used to cover the exposure period. If a log archive repository was used then the log register commands can be generated with a script like the following: :::::::::::::: register_logs.sql :::::::::::::: REM The parameter is the SCN from the ?recover standby database command set head off feedback off lines 133 pages 0 verify off echo off spool register_logs_for_standby.sql select 'alter database register logfile ' || chr(39) || name || chr(39) || ';' from v$archived_log where first_change# >= &1 / spool off
14		Start Logical Standby Apply @ 11.1.0.7 SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;	重新開啟logical standby sql apply
15
16		Verify that the logical standby is running correctly	See 10.3 Monitoring a Logical Standby Database [5] 檢查logical standby的執行情況
IV. Switchover(主從切換)
17	Switchover to Logical Standby on current/original primary SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY If the query returns “SESSIONS ACTIVE’, then ensure it’s ok to shut them down. SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);		Make sure this is aligned with MAA Switchover best practices [4]. You cannot do an “ALTER DATABASE PREPARE TO SWITCHOVER” for this switchover due to the mixed versions. This is not supported. 檢查主庫switchover_status的狀態,準備切換.滾動升級不能象正常的邏輯standby一樣使用ALTER DATABASE PREPARE TO SWITCHOVER,因為主從版本不同.
18	10g Oracle RAC only $ srvctl stop instance –d chicago –i chicago2 SQL> ALTER DATABASE DISABLE THREAD 2; SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;		11g logical standby switchover does not require disabling threads or shutting down other instances in an Oracle RAC environment. 10gRAC需只留一個例項
19	SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;		“SWITCHOVER TO LOGICAL STANDBY;” cannot use the “WITH SESSION SHUTDOWN” clause so you need to manually shutdown sessions. 切換主庫到邏輯standby.
20		Ensure archive logs are received and applied during the primary switchover	確認日誌已經在從庫上應用完.
21		Defer Redo SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;	停止從庫的日誌傳輸
22		10g Oracle RAC only $ srvctl stop instance –d boston –i boston2 SQL> ALTER DATABASE DISABLE THREAD 2;	10gRAC需只留一個例項
23		Switchover to Primary on current logical standby SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;	If switchover_status does not change to ?TO PRIMARY? as required then the switchover can be backed out at this point, see Appendix C, Canceling a Logical Standby Switchover. You cannot do an “ALTER DATABASE PREPARE TO SWITCHOVER” for this switchover due to the mixed versions. 檢查主庫switchover_status的狀態,切換.滾動升級不能象正常的邏輯standby一樣使用ALTER DATABASE PREPARE TO SWITCHOVER,因為主從版本不同.
24		10g Oracle RAC only Start other nodes of the primary (original standby database) SQL> ALTER DATABASE ENABLE THREAD 2; $ srvctl start instance –d boston –i boston2	10gRAC需開啟其它例項
25	10g Oracle RAC only Start other nodes of the primary (original standby database) SQL> ALTER DATABASE ENABLE THREAD 2; $ srvctl start instance –d chicago –i chicago2		10gRAC開啟其它例項
V.Install the New ORACLE_HOME and Apply the Patchset on the Original Primary安裝升級包在源主庫
26	Install a separate ORACLE_HOME and patch it to 11.1.0.7		This must be done here if a temporary log archive repository will be used. 安裝升級軟體
27	10g only Follow MetaLink note 300479.1 to implement the workaround for issue 5236922		This will insure that there are no Enterprise Manager components installed on the new logical standby 10G注意
28	Apply 11.1.0.7 patchset to CRS and ASM
29	Install the 11.1.0.7 Patch set		For an out-of-place patchset apply the existing ORACLE_HOME is cloned using the Cloning procedure in Appendix E.
VI.Create a Temporary Archive redo log repository (optional) 建立重做日誌儲存庫(可選項)
30	Follow MetaLink Note 434164.1		Optionally, an archived redo log repository can be created with the same database version and COMPATIBLE setting as the primary so that redo is still received during the patch apply/upgrade. See MetaLink Note 434164.1 for setting that up. This will ensure that the recovery point objective can be met in the event of a primary site failure during this step. 建立與主資料庫相同的歸檔重做日誌儲存庫,以便在打補丁或升級資料庫的期間仍然可以接收重做資料.確保升級失敗時滿足還原點目標.
VII.Perform the Upgrade on the Former Primary(升級源主庫資料庫)
31	Create a guaranteed restore point SQL> CREATE RESTORE POINT PRE_LOGICAL_UPGRADE GUARANTEE FLASHBACK DATABASE;		Note that is this 10.1.0.3 or a later 10.1 release then capture the current SCN as the flashback database point. SQL> SELECT CURRENT_SCN FROM V$DATABASE; Note: if this is a multi-standby configuration that includes one or more physical standby databases, please see instructions in this paper for “Special Instructions for Multi-Standby Configurations” 建還原點.如果只是為了測試,不準備做回退,不建也可以.
32	If not using DBUA then set cluster_database=false in preparation for upgrade SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;		If using the Database Upgrade Assistant (dbua). dbua is executed from the new ORACLE_HOME. dbua requires CLUSTER_DATABASE+TRUE for a RAC database and it takes care of the OCR updates for you. 如果不用DBUA進行升級,rac資料庫需要設定CLUSTER_DATABASE=FALSE
33	Upgrade the database		You can now choose either the Database Upgrade Assistant (DBUA) or the manual upgrade method with catupgrd.sql when executing the actual database upgrade on the logical standby. Using the DBUA is the recommended method. It takes care of all parameter changes and of any updates to the Oracle Cluster Registry (OCR) in the case of a RAC system. Refer to the “Oracle Database Upgrade Guide 11.1 ” [7] for complete upgrade instructions. If you did not use DBUA then remember to set cluster_database=true before bouncing the database after the upgrade. 升級資料庫
34	If a temporary log archive repository was used then register logs so they don?t have to be resent: SQL> ALTER DATABASE REGISTER LOGFILE ?…. Optionally, you can use the RMAN CATALOG command to catalog the archived redo log repository archivelog destination. e.g.: RMAN> CATALOG START WITH '+ASMDG/ALOGREP/ARCHIVELOG/?;		This is if an archive log repository was used to cover the exposure period. If a log archive repository was used then the log register commands can be generated with a script like the following: :::::::::::::: register_logs.sql :::::::::::::: REM The parameter is the SCN from the ?recover standby database command set head off feedback off lines 133 pages 0 verify off echo off spool register_logs_for_standby.sql select 'alter database register logfile ' || chr(39) || name || chr(39) || ';' from v$archived_log where first_change# >= &1 / spool off
35	Start Logical Standby Apply @ 11.1.0.7 SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE NEW PRIMARY TO_BOSTON;		If the ?new primary? clause is not used then it will receive an “ORA-16100: not a valid Logical Standby database” error. 源主庫應用sql apply.這裡要注意,需要建一個到源備庫的db link,這裡的TO_BOSTON是一個db link 名字,建db link 的格式為:CREATE DATABASE LINK <link name> CONNECT TO SYSTEM IDENTIFIED BY <password> USING <tns alias of logical standby>;
36		Enable redo transfer SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;	開日誌傳輸
37	Verify that the logical standby is running correctly		See 10.3 Monitoring a Logical Standby Database [5] 檢查源主庫logical standby的執行情況
VIII.Switch back to the original config (optional)(源主從切回)
38		Switch back to the original configuration where boston will be the logical standby and chicago the primary: Check the status SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY If the query returns “SESSIONS ACTIVE’, then ensure it’s ok to shut them down. SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);	檢查主庫switchover_status的狀態,準備切換.
39	10g Oracle RAC only $ srvctl stop instance –d chicago –i chicago2 SQL> ALTER DATABASE DISABLE THREAD 2;	10g Oracle RAC only $ srvctl stop instance –d boston –i boston2 SQL> ALTER DATABASE DISABLE THREAD 2;	10gRAC需只留一個例項
40		SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;	If the PREPARE does not complete on the logical standby, you can cancel the switchover operation by issuing the following statements in order: 1. Cancel switchover on the primary database: SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL; 2. Cancel the switchover on the logical standby database SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL; 這裡就可以正常的進行邏輯standby的切換準備了
41	SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;		源主庫切換回主庫準備
42		Ensure the current primary is ready SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO LOGICAL STANDBY 1 row selected	檢視switchover_status
43	Ensure archive logs are received and applied during the primary switchover	SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;	“SWITCHOVER TO LOGICAL STANDBY;” cannot use the “WITH SESSION SHUTDOWN” clause so you need to manually shutdown sessions. 源從庫切換回從庫
44	Defer Redo SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;		源主庫停止日誌傳輸
45	Switchover to Primary on current logical standby SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;		源主庫切換回主庫
46	10g Oracle RAC only Start other nodes of the primary (original standby database) SQL> alter database enable thread 2; $ srvctl start instance –d chicago –i chicago2		10gRAC開啟其它例項
47		10g Oracle RAC only Start other nodes of the standby SQL> ALTER DATABASE ENABLE THREAD 2; $ srvctl start instance –d boston –i boston2	10gRAC開啟其它例項
48		SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;	從庫應用sql apply
IX.Raise the COMPATIBLE Parameter Setting(修改引數檔案)
49	Once test results are satisfactory then the COMPATIBLE parameter setting can be raised if there new features to be used.		Note that raising the COMPATIBLE setting eliminates any ability to downgrade.
50		ALTER SYSTEM SET compatible='11.1.0.7' SCOPE=SPFILE;	修改相容性引數
51	ALTER SYSTEM SET compatible='11.1.0.7' SCOPE=SPFILE;		修改相容性引數

                
注意:
1.在滾動升級之前需要禁用data guard broker,alter system set dg_broker_start=false;在滾動升級完成後可以重新啟用data guard broker,alter system set dg_broker_start=true;
2.滾動升級主從切回時,兩個庫版本一置,使用 ALTER DATABASE PREPARE, ALTER DATABASE COMMIT去切換.但在第一次主從切換時,由於兩個版本不同,只需要ALTER DATABASE COMMIT去切換,
並在升級完源主庫後應用sql apply時加上new primary dblink_name.
3.滾動升級還要考慮主從切換時,客戶端的連線方案.
4.源從庫升級後,考慮通過database replay,sql performance analyzer,sql plan management進行升級後的效能測試.

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