Oracle 11g 一主多备切换方案


Oracle 11g 一主多备切换方案

  最近公司需要把核心两节点RAC从虚拟化环境迁移至物理机,考虑停机时间要求比较短并且还有两个备库存在,决定使用RAC到RAC的备库使用switch over的方式进行迁移。原RAC主库还存在一个单实例的物理standby只读库和逻辑standby报表库,切换后需要对这两个库进行处理,可以接收新的RAC主库的redo。此方案和使用RMAN全备进行异机恢复的迁移方式对比,优点是停机时间短,并且迁移后无需重新配置原来的两个单实例备库。

一、 环境信息

当前一主多备架构数据库环境信息如下:

Oracle 11g 一主多备切换方案

二、 一主多备架构下主备切换步骤

  需要把当前RAC主库切换成备库,把RAC备库切换成新的RAC主库,其余两个单实例备库指向新的RAC主库,具体操作步骤如下:

2.1 检查当前RAC主库是否可以被切换成备用角色

[oracle@ngpdb01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 09:50:57 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

TO STANDBY或者SESSIONS ACTIVE状态下,主库可以切换成备库角色

2.2 原RAC主库启动switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

2.3 shutdown原RAC主库并且启动到mount状态

[oracle@ngpdb01 trace]$ srvctl stop database -d ngpdb
PRCC-1016 : ngpdb was already stopped
[oracle@ngpdb01 trace]$ srvctl start database -d ngpdb -o mount

2.4 查询switchover目标RAC备库是否准备好切换成为新的主库

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

TO PRIMARY或者SESSIONS ACTIVE 状态说明备库已经准备好切换成主库角色

2.5 切换目标RAC物理备库成为主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

2.6 打开新的RAC主库

节点1执行:

[oracle@ngpdb03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:10:14 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

节点2执行:

[oracle@ngpdb04 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:10:41 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

2.7 在新的RAC物理备库上执行redo apply

[oracle@ngpdb01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 7 10:13:30 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

2.8 在新的RAC主库上配置crmngpsd和ngppdg的远程归档路径

alter system set log_archive_dest_2='SERVICE=ngppdg lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ngppdg'  sid='*';

alter system set log_archive_dest_3='SERVICE=crmngpsd lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crmngpsd' sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE sid='*';

2.9 修改crmngpsd和ngppdg的FAL_SERVER指向新的RAC主库

SQL> alter system set fal_server='ngpdbracdg';

三、 主备切换后验证所有备库同步状态

3.1模拟 rac主库进行日志切换,查看各个备库是否能正常接收日志以及日志应用状态

RAC主库:

SQL> alter system archive log current;

System altered.

RAC备库:

Fri Aug 21 10:12:48 2020
RFS[2]: Selected log 11 for thread 1 sequence 466 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:50 2020
Media Recovery Waiting for thread 1 sequence 466 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 466 Reading mem 0
Mem# 0: +NGPDATA01/ngpdb/onlinelog/group_11.811.1047655443
Fri Aug 21 10:12:51 2020
RFS[4]: Selected log 13 for thread 2 sequence 765 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:53 2020
Archived Log entry 275114 added for thread 2 sequence 764 ID 0xf9cbfdcf dest 1:
Fri Aug 21 10:12:54 2020
Archived Log entry 275115 added for thread 1 sequence 465 ID 0xf9cbfdcf dest 1:
Media Recovery Waiting for thread 2 sequence 765 (in transit)
Recovery of Online Redo Log: Thread 2 Group 13 Seq 765 Reading mem 0
Mem# 0: +NGPDATA01/ngpdb/onlinelog/group_13.803.1047655395

ngppdg:

Fri Aug 21 10:12:49 2020
RFS[19]: Selected log 12 for thread 1 sequence 466 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:51 2020
Archived Log entry 155 added for thread 1 sequence 465 ID 0xf9cbfdcf dest 1:
Fri Aug 21 10:12:51 2020
Media Recovery Waiting for thread 1 sequence 466 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 466 Reading mem 0
Mem# 0: /oradata/ngppdg/group_12.802.1047655393
Fri Aug 21 10:12:52 2020
RFS[20]: Selected log 14 for thread 2 sequence 765 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:52 2020
Archived Log entry 156 added for thread 2 sequence 764 ID 0xf9cbfdcf dest 1:
Fri Aug 21 10:13:02 2020
Media Recovery Waiting for thread 2 sequence 765 (in transit)
Recovery of Online Redo Log: Thread 2 Group 14 Seq 765 Reading mem 0
Mem# 0: /oradata/ngppdg/group_14.804.1047655395

crmngpsd:

Fri Aug 21 10:12:49 2020
RFS[13]: Selected log 11 for thread 1 sequence 466 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:52 2020
RFS[14]: Selected log 14 for thread 2 sequence 765 dbid -216519938 branch 1046858879
Fri Aug 21 10:12:53 2020
RFS LogMiner: Registered logfile [/oradata/crmngpsd/archivelog_stdby/1_465_1046858879.dbf] to LogMiner session id [2]
Fri Aug 21 10:12:53 2020
RFS LogMiner: Registered logfile [/oradata/crmngpsd/archivelog_stdby/2_764_1046858879.dbf] to LogMiner session id [2]
Fri Aug 21 10:14:13 2020
LSP0: warning -- apply server 4, sid 21 waiting for event (since 448 seconds):

三个备库都正常接受日志并进行日志应用。

3.2 查看备库状态

RAC备库:

[oracle@ngpdb01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 21 10:21:43 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set linesize 500 pagesize 10000
SQL> select INST_ID,PROCESS,PID,STATUS,CLIENT_PID from gv$managed_standby;

INST_ID PROCESS PID STATUS CLIENT_PID
---------- --------- ---------- ------------ ----------------------------------------
1 ARCH 21437 CLOSING 21437
1 ARCH 21440 CLOSING 21440
1 ARCH 21442 CLOSING 21442
1 ARCH 21444 CLOSING 21444
1 RFS 21555 IDLE 4687
1 RFS 21499 IDLE 4691
1 RFS 21497 IDLE 14190
1 RFS 21505 IDLE 14856
1 RFS 21507 IDLE 4966
1 MRP0 22035 APPLYING_LOG N/A
1 RFS 21574 IDLE 14860
2 ARCH 25586 CLOSING 25586
2 ARCH 25589 CLOSING 25589
2 ARCH 25591 CONNECTED 25591
2 ARCH 25593 CLOSING 25593
2 RFS 25730 IDLE 4684
2 RFS 25769 IDLE 14862

17 rows selected.

ngppdg:

SQL> col client_pid format a10
SQL> col process format a10
SQL> col status format a10
SQL> set linesize 500 pagesize 10000
SQL> select INST_ID,PROCESS,PID,STATUS,CLIENT_PID from gv$managed_standby;

INST_ID PROCESS PID STATUS CLIENT_PID
---------- ---------- ---------- ---------- ----------
1 ARCH 29541 CLOSING 29541
1 ARCH 29543 CLOSING 29543
1 ARCH 29545 CONNECTED 29545
1 ARCH 29547 CLOSING 29547
1 RFS 13359 IDLE 4684
1 RFS 13363 IDLE 14856
1 RFS 13372 IDLE 5069
1 RFS 13370 IDLE 14337
1 RFS 13367 IDLE 4691
1 RFS 13386 IDLE 21444
1 RFS 13388 IDLE 21437
1 RFS 13390 IDLE 21442
1 MRP0 11897 APPLYING_L N/A
OG

1 RFS 13392 IDLE 4687
1 RFS 13403 IDLE 14860

15 rows selected.

crmngpsd:

SQL> col spid format a10
SQL> col type format a15
SQL> set linesize 500 pagesize 10000
SQL> SELECT SID, SERIAL#, SPID, TYPE FROM V$LOGSTDBY_PROCESS;

SID SERIAL# SPID TYPE
---------- ---------- ---------- ---------------
770 5 2201 COORDINATOR
23 11 2266 ANALYZER
775 11 2268 APPLIER
1534 11 2270 APPLIER
2291 11 2272 APPLIER
21 11 2274 APPLIER
777 11 2276 APPLIER
18 3 2215 READER
762 61 2217 BUILDER
1529 29 2219 PREPARER

10 rows selected.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN FROM V$LOGSTDBY_PROGRESS;
Session altered.

SQL>

APPLIED_TIME APPLIED_SCN MINING_TIME MINING_SCN
----------------------------- ----------- ----------------------------- ----------
21-AUG-2020 09:29:24 3.5747E+10 21-AUG-2020 09:29:26 3.5747E+10

SQL> col state format a10
SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID STATE
---------- ----------
2 APPLYING

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

Oracle 11g 一主多备切换方案

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » Oracle 11g 一主多备切换方案
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏