Oracle DG之--通过Oracle 逻辑DG 实现数据库滚动升级

绝地灬酷狼 2022-02-04 14:47 492阅读 0赞

**系统环境：**

**操作系统：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][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**

**注意：对于升级完成的备库，以下参数不能修改（全部升级完成后再修改）**

**[![wKioL1NM4N2hTKw5AACGZKXWnXg421.jpg][]][wKioL1NM4N2hTKw5AACGZKXWnXg421.jpg]**

**升级后的版本：**

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时会出现以下错误：**

[![wKioL1NM43zTjTbTAAQgGx0bdFM097.jpg][]][wKioL1NM43zTjTbTAAQgGx0bdFM097.jpg]

**switchover 前准备：**

查看主备库是否同步：

**主库：**

14:38:08 SYS@ test1>select count(\*) from scott.emp1;

COUNT(\*)

\----------

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(\*)

\----------

SQL> /

COUNT(\*)

\----------

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

**（注： alter database prepare to switchover cancel； 终止切换准备）**

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

@原主库的升级，本案例就不在重复讲述，切换成功后，滚动升级应该到此已成功！

本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1395830，如需转载请自行联系原作者

[http_tiany.blog.51cto.com_513694_1384698]: http://tiany.blog.51cto.com/513694/1384698
[wKioL1NM4N2hTKw5AACGZKXWnXg421.jpg]: /images/20220204/f11020450d7146b8955201e6f51ac156.png
[wKioL1NM43zTjTbTAAQgGx0bdFM097.jpg]: /images/20220204/8bebc7efb3514c6cb2221861c6f5a811.png