当前位置: 开发笔记 > 数据库 > 正文

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

作者：翁向军_943 | 来源：互联网 | 2018-07-08 15:38

通过Oracle逻辑DG实现数据库滚动升级系统环境：操作系统：RedHatEL5Oracle：Oracle10gR2对于Oracle数据库的升级，一般需要长时间shutdowndatabase；升级的时间会

三、主备库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(*)

----------

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

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” 博客，请务必保留此出处

推荐阅读

数据库
SharePoint BDC 集成挑战与解决方案

本文探讨了在SharePoint环境中使用BDC（Business Data Catalog）时遇到的问题及其解决策略，包括XML文件导入SSP后的不可见性问题以及与远程SQL Server 2005连接的难题。 ... [详细]

蜡笔小新 2024-11-25 05:32:41
数据库
2021年度精选：新手必知的数据分析工具指南

面对众多的数据分析工具，如何选择最适合自己的那一个？对于初学者而言，了解并掌握几种核心工具是快速入门的关键。本文将从数据处理的不同阶段出发，推荐三种广泛使用的数据分析工具。 ... [详细]

蜡笔小新 2024-11-23 19:47:22
数据库
Linux线程中私有数据的管理与保护

本文探讨了Linux环境下线程私有数据(Thread-Specific Data, TSD)的概念及其重要性，介绍了如何通过TSD技术避免多线程间全局变量冲突的问题，并提供了具体的实现方法和示例代码。 ... [详细]

蜡笔小新 2024-11-23 13:45:37
mysql
【MySQL】frm文件解析

官网说明：http:dev.mysql.comdocinternalsenfrm-file-format.htmlfrm是MySQL表结构定义文件，通常frm文件是不会损坏的，但是如果 ... [详细]

蜡笔小新 2024-11-23 10:29:06
sql
数据输入验证与控件绑定方法

本文提供了多种数据输入验证函数及控件绑定方法的实现代码，包括电话号码、数字、传真、邮政编码、电子邮件和网址的验证，以及报表绑定和自动编号等功能。 ... [详细]

蜡笔小新 2024-11-23 07:47:36
数据库
MySQL窗口函数详解与应用

本文介绍了MySQL窗口函数的基本概念、应用场景及常见函数的使用方法。窗口函数在处理复杂查询时非常有用，例如计算每个用户的订单排名、环比增长率、以及动态聚合等。 ... [详细]

蜡笔小新 2024-11-23 02:53:38
数据库
Hive中时间处理函数详解

本文详细介绍了Hive中用于处理日期和时间的各种函数，包括UNIX时间戳与日期之间的转换、日期提取以及日期计算等，帮助读者更好地理解和使用这些函数。 ... [详细]

蜡笔小新 2024-11-24 23:29:46
数据库
Hadoop集群搭建：实现SSH无密码登录

本文介绍了如何在CentOS 7 64位操作系统环境下配置Hadoop集群中的SSH无密码登录，包括环境准备、用户创建、密钥生成及配置等步骤。 ... [详细]

蜡笔小新 2024-11-24 19:47:28
数据库
深入解析Socket结构与实现

本文详细介绍了Socket在Linux内核中的实现机制，包括基本的Socket结构、协议操作集以及不同协议下的具体实现。通过这些内容，读者可以更好地理解Socket的工作原理。 ... [详细]

蜡笔小新 2024-11-24 12:00:27
数据库
解决Linux中wget无法解析主机的问题

本文介绍了如何通过修改/etc/resolv.conf文件来解决Linux系统中wget命令无法解析主机名的问题，通过添加Google的公共DNS服务器地址作为解决方案。 ... [详细]

蜡笔小新 2024-11-23 11:36:07
数据库
服务器系统架构性能评估指南

本文探讨了服务器系统架构的性能评估方法，包括性能评估的目的、步骤以及如何选择合适的度量标准。文章还介绍了几种常用的基准测试程序及其应用，并详细说明了Web服务器性能评估的关键指标与测试方法。 ... [详细]

蜡笔小新 2024-11-23 11:12:26
数据库
WebBenchmark：强大的Web API性能测试工具

本文介绍了一款名为WebBenchmark的Web API性能测试工具，该工具不仅支持HTTP和HTTPS服务的测试，还提供了丰富的功能来帮助开发者进行高效的性能评估。 ... [详细]

蜡笔小新 2024-11-23 05:24:11
数据库
从迷茫到收获：阿里腾讯实习Offer的求取之路

本文回顾了作者在求职阿里和腾讯实习生过程中，从最初的迷茫到最后成功获得Offer的心路历程。文中不仅分享了个人的面试经历，还提供了宝贵的面试准备建议和技巧。 ... [详细]

蜡笔小新 2024-11-22 19:32:32
数据库
春季职场跃迁指南：如何高效利用金三银四跳槽季

随着每年的‘金三银四’跳槽高峰期的到来，许多职场人士都开始考虑是否应该寻找新的职业机会。本文将探讨如何制定有效的职业规划、撰写吸引人的简历以及掌握面试技巧，助您在这关键时期成功实现职场跃迁。 ... [详细]

蜡笔小新 2024-11-22 19:18:22
数据库
CentOS 服务器自定义密码策略

随着Linux操作系统的广泛使用，确保用户账户及系统安全变得尤为重要。用户密码的复杂性直接关系到系统的整体安全性。本文将详细介绍如何在CentOS服务器上自定义密码规则，以增强系统的安全性。 ... [详细]

蜡笔小新 2024-11-22 19:15:42

翁向军_943

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章