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

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