Oracle DG之--通过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

  注意:对于升级完成的备库,以下参数不能修改(全部升级完成后再修改)

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

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


(注: 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,如需转载请自行联系原作者
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
4天前
|
存储 SQL 多模数据库
多模数据库Lindorm再升级:对接Dataphin,打通数据治理“最后一公里”
Lindorm通过与Dataphin的深度整合,进一步解决了数据集成和数据治理的问题,为企业提供更加高效和更具性价比的方案。
多模数据库Lindorm再升级:对接Dataphin,打通数据治理“最后一公里”
|
9天前
|
Oracle 关系型数据库 数据库
实时计算 Flink版产品使用问题之连接到Oracle数据库但无法读取到数据,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3天前
|
Oracle Java 关系型数据库
[学习笔记] 在Eclipse中使用Hibernate,并创建第一个Demo工程,数据库为Oracle XE
[学习笔记] 在Eclipse中使用Hibernate,并创建第一个Demo工程,数据库为Oracle XE
15 0
|
4天前
|
Oracle 关系型数据库 数据库
Oracle 11G数据库安装步骤及截图操作2
Oracle 11G数据库安装步骤及截图操作
8 0
|
4天前
|
Oracle 安全 关系型数据库
Oracle 11G数据库安装步骤及截图操作1
Oracle 11G数据库安装步骤及截图操作
15 0
|
8天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之在进行Oracle数据库的全量同步时,只同步了一条数据而源表实际上包含多条数据,是什么原因
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2天前
|
存储 关系型数据库 MySQL
|
2天前
|
SQL 关系型数据库 MySQL
|
2天前
|
存储 关系型数据库 MySQL
|
3天前
|
存储 关系型数据库 MySQL
关系型数据库mysql日志和临时文件
【6月更文挑战第15天】
22 4

热门文章

最新文章

推荐镜像

更多