通过Oracle 逻辑DG 实现数据库滚动升级
系统环境：
操作系统：RedHat EL5
Oracle： Oracle 10gR2
对于Oracle 数据库的升级，一般需要长时间shutdown database ；升级的时间会因为数据库业务和数据库数据量的不同，停库时间不同；从而给生产带来不必要的损失，但通过Oracle 的逻辑DG可以实现数据库的滚动升级，大大缩短了业务的停用时间（只需要DG的切换时间），对于7X24小时的业务环境来说非常合适。
本案例的思想是：先在逻辑备库上执行升级，升级完成后，备库switchover 成主库，然后主库switchover 备库，再进行升级，所需要的业务停顿时间，只是DG的切换时间。
一、升级前准备
1） 将物理DG升级到逻辑DG:（见我博客另外文章）
http://tiany.blog.51cto.com/513694/1384698
Oracle DataGuard 之--Physical DG转换Logical DG
二、备库升级
1）备库升级（本案例以CPU update为例）
http://tiany.blog.51cto.com/513694/846066
Oracle 10g 升级（10.2.0.1升级到10.2.0.4）
三、主备库Switchover
注意：对于升级完成的备库，以下参数不能修改（全部升级完成后再修改）
升级后的版本：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
如果修改后，在做switchover时会出现以下错误：
switchover 前准备：
查看主备库是否同步：
主库：
14:38:08 SYS@ test1>select count(*) from scott.emp1;
COUNT(*)
----------
33
Elapsed: 00:00:00.00
14:38:13 SYS@ test1>alter system switch logfile;
System altered.
Elapsed: 00:00:05.03
14:38:22 SYS@ test1>select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
TEST1 PRIMARY MAXIMUM PERFORMANCE TO STANDBY
Elapsed: 00:00:00.13
14:40:27 SYS@ test1>
备库：
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> show parameter comp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
SQL> select count(*) from scott.emp1;
COUNT(*)
----------
30
SQL> /
COUNT(*)
----------
33
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
SHDB LOGICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
备库切换前准备：
SQL> alter database prepare to switchover to primary;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
SHDB LOGICAL STANDBY MAXIMUM PERFORMANCE PREPARING SWITCHOVER
SQL>
主库切换前准备：
14:40:27 SYS@ test1>alter database prepare to switchover to logical standby;
Database altered.
Elapsed: 00:00:00.04
14:42:06 SYS@ test1>select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
TEST1 PRIMARY MAXIMUM PERFORMANCE PREPARING SWITCHOVER
Elapsed: 00:00:00.00
在备库做prepare switchover 后，主库状态：
14:42:11 SYS@ test1>/
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
TEST1 PRIMARY MAXIMUM PERFORMANCE TO LOGICAL STANDBY
Elapsed: 00:00:00.01
14:43:38 SYS@ test1>
主库切换：
14:43:38 SYS@ test1>alter database commit to switchover to logical standby;
Database altered.
Elapsed: 00:00:31.50
14:45:01 SYS@ test1>
14:45:01 SYS@ test1>select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
TEST1 LOGICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
切换过程主库告警日志：
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (test1)
Tue Apr 15 14:44:30 2014
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Disabling job queue processes.
LOGSTDBY: Enabling database guard to prevent new transactions.
Tue Apr 15 14:44:30 2014
Waiting for transactions in flight at scn 0x0000.0004ebee to complete
LNS1 started with pid=16, OS id=3161
Tue Apr 15 14:44:37 2014
Thread 1 advanced to log sequence 69
Current log# 3 seq# 69 mem# 0: /u01/app/oracle/oradata/test1/redo03a.log
Tue Apr 15 14:44:37 2014
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 68 for destination LOG_ARCHIVE_DEST_2
Tue Apr 15 14:44:37 2014
LOGSTDBY: Waiting for pending archivals to dest [2].
Tue Apr 15 14:44:37 2014
LNS: Standby redo logfile selected for thread 1 sequence 69 for destination LOG_ARCHIVE_DEST_2
LNS1 started with pid=16, OS id=3163
Tue Apr 15 14:44:43 2014
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Tue Apr 15 14:44:43 2014
Thread 1 advanced to log sequence 70
Current log# 1 seq# 70 mem# 0: /u01/app/oracle/oradata/test1/redo01a.log
Tue Apr 15 14:44:43 2014
ARCH: Standby redo logfile selected for thread 1 sequence 69 for destination LOG_ARCHIVE_DEST_2
LOGSTDBY: Verifying receipt of EOR logfile on log archive destination [2].
Tue Apr 15 14:44:43 2014
LOGSTDBY: Verified EOR logfile archival to dest [2].
Tue Apr 15 14:44:44 2014
LNS: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2
Tue Apr 15 14:44:55 2014
Thread 1 cannot allocate new log, sequence 71
Checkpoint not complete
Current log# 1 seq# 70 mem# 0: /u01/app/oracle/oradata/test1/redo01a.log
LNS1 started with pid=16, OS id=3166
Tue Apr 15 14:45:01 2014
Shutting down archive processes
Tue Apr 15 14:45:01 2014
Thread 1 advanced to log sequence 71
Current log# 2 seq# 71 mem# 0: /u01/app/oracle/oradata/test1/redo02a.log
Tue Apr 15 14:45:01 2014
LOGSTDBY: Switchover complete (test1)
Tue Apr 15 14:45:01 2014
Completed: alter database commit to switchover to logical standby
Tue Apr 15 14:45:02 2014
LNS: Standby redo logfile selected for thread 1 sequence 71 for destination LOG_ARCHIVE_DEST_2
Tue Apr 15 14:45:06 2014
ARCH shutting down
ARC3: Archival stopped
备库切换过程告警日志：
RFS[1]: Possible network disconnect with primary database
Tue Apr 15 14:44:37 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 3189
RFS[4]: Identified database type as ‘logical standby‘
Tue Apr 15 14:44:37 2014
RFS LogMiner: Client enabled and ready for notification
RFS[4]: Successfully opened standby log 5: ‘/u01/app/oracle/oradata/sh/std_redo05a.log‘
Tue Apr 15 14:44:37 2014
RFS LogMiner: Client enabled and ready for notification
Tue Apr 15 14:44:37 2014
LOGMINER: Archived logfile found, transition to mining logfile: /u01/arch_sh1arch_1_68_797856158.log
Tue Apr 15 14:44:37 2014
RFS LogMiner: Registered logfile [/u01/arch_sh1arch_1_68_797856158.log] to LogMiner session id [21]
Tue Apr 15 14:44:37 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 3191
RFS[5]: Identified database type as ‘logical standby‘
Tue Apr 15 14:44:37 2014
RFS LogMiner: Client enabled and ready for notification
RFS[5]: Successfully opened standby log 4: ‘/u01/app/oracle/oradata/sh/std_redo04a.log‘
RFS[5]: Possible network disconnect with primary database
Tue Apr 15 14:44:37 2014
LOGMINER: End mining logfile: /u01/arch_sh1arch_1_68_797856158.log
Tue Apr 15 14:44:37 2014
LOGMINER: Log Auto Delete - deleting: /u01/arch_sh1arch_1_67_797856158.log
Deleted file /u01/arch_sh1arch_1_67_797856158.log
Tue Apr 15 14:44:43 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 3193
RFS[6]: Identified database type as ‘logical standby‘
Tue Apr 15 14:44:43 2014
RFS LogMiner: Client enabled and ready for notification
RFS[6]: Successfully opened standby log 4: ‘/u01/app/oracle/oradata/sh/std_redo04a.log‘
Tue Apr 15 14:44:43 2014
RFS LogMiner: Client enabled and ready for notification
Tue Apr 15 14:44:43 2014
LOGMINER: Begin mining logfile for session 21 thread 1 sequence 69, /u01/app/oracle/oradata/sh/std_redo04a.log
Tue Apr 15 14:44:43 2014
LOGMINER: End mining logfile: /u01/app/oracle/oradata/sh/std_redo04a.log
Tue Apr 15 14:44:43 2014
RFS LogMiner: Registered logfile [/u01/arch_sh1arch_1_69_797856158.log] to LogMiner session id [21]
Tue Apr 15 14:44:43 2014
LOGSTDBY: Shutdown acknowledged
LOGSTDBY Analyzer process P003 pid=27 OS id=3153 stopped
LOGSTDBY Apply process P004 pid=28 OS id=3155 stopped
LOGSTDBY Apply process P005 pid=29 OS id=3157 stopped
LOGSTDBY Apply process P006 pid=30 OS id=3159 stopped
LOGSTDBY Apply process P007 pid=31 OS id=3161 stopped
LOGSTDBY Apply process P008 pid=32 OS id=3163 stopped
Tue Apr 15 14:44:44 2014
LOGMINER: Log Auto Delete - deleting: /u01/arch_sh1arch_1_68_797856158.log
Deleted file /u01/arch_sh1arch_1_68_797856158.log
Tue Apr 15 14:44:44 2014
LOGSTDBY status: ORA-16257: Switchover initiated stop apply successfully completed
Tue Apr 15 14:44:44 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[7]: Assigned to RFS process 3195
RFS[7]: Identified database type as ‘logical standby‘
Primary database is in MAXIMUM PERFORMANCE mode
Tue Apr 15 14:44:44 2014
RFS LogMiner: Client enabled and ready for notification
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Successfully opened standby log 4: ‘/u01/app/oracle/oradata/sh/std_redo04a.log‘
RFS[7]: Possible network disconnect with primary database
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[8]: Assigned to RFS process 3197
RFS[8]: Identified database type as ‘logical standby‘
Primary database is in MAXIMUM PERFORMANCE mode
Tue Apr 15 14:45:02 2014
RFS LogMiner: Client enabled and ready for notification
Primary database is in MAXIMUM PERFORMANCE mode
RFS[8]: Successfully opened standby log 5: ‘/u01/app/oracle/oradata/sh/std_redo05a.log‘
主库切换完成后，备库状态：
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
SHDB LOGICAL STANDBY MAXIMUM PERFORMANCE PREPARING SWITCHOVER
SQL> /
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
SHDB LOGICAL STANDBY MAXIMUM PERFORMANCE TO PRIMARY
SQL>
备库切换：
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
SHDB PRIMARY MAXIMUM PERFORMANCE SESSIONS ACTIVE
SQL>
备库切换过程告警日志：
ALTER DATABASE SWITCHOVER TO PRIMARY (sh)
Tue Apr 15 14:48:31 2014
RFS LogMiner: Client enabled and ready for notification
Tue Apr 15 14:48:31 2014
LOGMINER: WARNING: registered partial log file /u01/arch_sh1arch_1_70_797856158.log
Tue Apr 15 14:48:31 2014
RFS LogMiner: Registered logfile [/u01/arch_sh1arch_1_70_797856158.log] to LogMiner session id [21]
LOGSTDBY Event: Starting SCN of new stream from seeded lockdown [0x0000.00071f8b]
LOGSTDBY Event: Successful close of the current log stream:
LOGSTDBY Event: primary: [1174898526]
LOGSTDBY Event: first scn: [0x0000.00046d38]
LOGSTDBY Event: end scn: [0x0000.0004ebf8]
LOGSTDBY Event: processed scn: [0x0000.0004ebf9]
LNS1 started with pid=20, OS id=3209
Tue Apr 15 14:48:34 2014
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Tue Apr 15 14:48:34 2014
ARC0: STARTING ARCH PROCESSES
Tue Apr 15 14:48:34 2014
Thread 1 advanced to log sequence 30 (LGWR switch)
Current log# 3 seq# 30 mem# 0: /u01/app/oracle/oradata/sh/redo03a.log
Tue Apr 15 14:48:34 2014
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC3 started with pid=20, OS id=3211
Tue Apr 15 14:49:24 2014
ARCH: Standby redo logfile selected for thread 1 sequence 29 for destination LOG_ARCHIVE_DEST_2
Completed: alter database commit to switchover to primary
Tue Apr 15 14:49:28 2014
Starting background process CJQ0
CJQ0 started with pid=23, OS id=3215
主库日志：
RFS[4]: Assigned to RFS process 3177
RFS[4]: Identified database type as ‘logical standby‘
Tue Apr 15 14:48:34 2014
RFS LogMiner: Client enabled and ready for notification
Tue Apr 15 14:49:24 2014
RFS[4]: Successfully opened standby log 4: ‘/u01/app/oracle/oradata/test1/std_redo04a.log‘
Tue Apr 15 14:49:25 2014
RFS LogMiner: Client enabled and ready for notification
Tue Apr 15 14:49:29 2014
RFS LogMiner: Registered logfile [/disk1/arch_test1/arch_1_29_844857742.log] to LogMiner session id [21]
四、switchover 成功后，升级原主库：
原主库：
14:47:27 SYS@ test1>select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
TEST1 LOGICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
Elapsed: 00:00:00.00
数据库版本：
14:54:26 SYS@ test1>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
原备库数据库版本：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
@原主库的升级，本案例就不在重复讲述，切换成功后，滚动升级应该到此已成功！
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1395830