AI 助理
备案控制台
开发者社区 数据库 文章 正文

RAC同单实例物理备库的switchover

2017-11-21 1137
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

 前面搭建好了rac同单实例数据库data guard的maa环境，为了方便切换操作，这里配置使用data guard broker进行操作！rac环境下，需要将data guard broker的配置文件存放到共享存储上！

一：修改data guard broker的配置文件参数，启动dmon进程

[oracle@rac1 ~]$ sqlplus sys/123456@rac as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 14 21:11:34 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter dg_broker_config;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
dg_broker_config_file1               string
+DATA/rac/dgbroker/dg_config_f
ile1.dat
dg_broker_config_file2               string
+FRA/rac/dgbroker/dg_config_fi
le2.dat

SQL> alter system set dg_broker_start=true;
System altered.

SQL> conn sys/123456@orcl as sysdba
Connected.

SQL> show parameter dg_broker_config;

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
dg_broker_config_file1               string
/u01/app/oracle/product/10.2.0
/db1/dbs/dg_config_file1.dat
dg_broker_config_file2               string
/u01/app/oracle/product/10.2.0
/db1/dbs/dg_config_file2.dat

SQL> alter system set dg_broker_start=true;
System altered.

二：使用srvctl命令修改rac数据库的启动选项，如果备库也是rac环境，同样需要相应的修改
[oracle@rac1 ~]$ srvctl modify database -d rac -s open -r primary -o $ORACLE_HOME
[oracle@rac1 ~]$ srvctl stop database -d rac -o immediate
[oracle@rac1 ~]$ srvctl start database -d rac

三：创建dg broker配置文件

[oracle@server49 ~]$ dgmgrl sys/123456@rac 
DGMGRL for Linux: Version 10.2.0.5.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create configuration maa as primary database is rac connect identifier is rac;
Configuration "maa" created with primary database "rac"

DGMGRL> add database orcl as connect identifier is orcl maintained as physical;
Database "orcl" added

DGMGRL> show configuration;

Configuration
  Name:                maa
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    rac  - Primary database
    orcl - Physical standby database

Current status for "maa":
DISABLED

DGMGRL> show database verbose rac;

Database
  Name:            rac
  Role:            PRIMARY
  Enabled:         NO
  Intended State:  ONLINE
  Instance(s):
    rac2
    rac1

  Properties:
    InitialConnectIdentifier        = 'rac'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '5'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/rac, /u01/app/oracle/oradata/orcl'
    LogFileNameConvert              = '+FLASH/rac, /u01/app/oracle/flash_recover_area/orcl, +DATA/rac,

/u01/app/oracle/oradata/orcl'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName(*)
    SidName(*)
    LocalListenerAddress(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    LatestLog(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Current status for "rac":
DISABLED
四：启用dg broker配置,执行主备切换

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration
  Name:                maa
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    rac  - Primary database
    orcl - Physical standby database

Current status for "maa":
SUCCESS

DGMGRL> show database rac;

Database
  Name:            rac
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    rac2
    rac1

Current status for "rac":
SUCCESS

DGMGRL> show database orcl;

Database
  Name:            orcl
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    orcl

Current status for "orcl":
SUCCESS

DGMGRL> switchover to orcl;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "rac1" on database "rac"
Shutting down instance "rac1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rac1" on database "rac"
Starting instance "rac1"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl"
五：验证

DGMGRL> show configuration;

Configuration
  Name:                maa
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    rac  - Physical standby database
    orcl - Primary database

Current status for "maa":
SUCCESS
DGMGRL> show configuration;

 Configuration
  Name:                maa
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    rac  - Physical standby database
    orcl - Primary database

Current status for "maa":
SUCCESS

单实例数据库(原备库)日志信息如下：
[oracle@server49 dbs]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log 
Managed Standby Recovery Canceled (orcl)
Sat Jan 14 22:36:17 CST 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sat Jan 14 22:36:17 CST 2012
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Sat Jan 14 22:36:17 CST 2012
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 565100
Online log /u01/app/oracle/oradata/ORCL/onlinelog/group_1.307.772367339: Thread 1 Group 1 was previously

cleared
Online log /u01/app/oracle/oradata/ORCL/onlinelog/group_2.306.772367347: Thread 1 Group 2 was previously

cleared
Online log /u01/app/oracle/oradata/ORCL/onlinelog/group_3.305.772367577: Thread 2 Group 3 was previously

cleared
Online log /u01/app/oracle/oradata/ORCL/onlinelog/group_4.304.772367577: Thread 2 Group 4 was previously

cleared
Standby became primary SCN: 565098
Converting standby mount to primary mount.
Sat Jan 14 22:36:21 CST 2012
Switchover: Complete - Database mounted as primary (orcl)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN
Sat Jan 14 22:36:21 CST 2012
ARC3: STARTING ARCH PROCESSES
ARC5: Archival started
ARC3: STARTING ARCH PROCESSES COMPLETE
Sat Jan 14 22:36:36 CST 2012
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Sat Jan 14 22:36:38 CST 2012
alter database CLOSE NORMAL
ORA-1109 signalled during: alter database CLOSE NORMAL...

rac数据库(原主库)节点1日志信息如下：
[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/rac/bdump/alert_rac1.log
Reconfiguration complete
Sat Jan 14 22:39:18 CST 2012
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA/rac/standbylog
Sat Jan 14 22:39:18 CST 2012
ALTER SYSTEM SET log_archive_dest_2='location="+FRA/rac/standbylog"',' valid_for=

(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='rac1';
Using STANDBY_ARCHIVE_DEST parameter default value as +FRA/rac/standbylog
Sat Jan 14 22:39:18 CST 2012
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='rac1';
Sat Jan 14 22:39:18 CST 2012
ALTER SYSTEM SET standby_archive_dest='+FRA/rac/standbylog' SCOPE=BOTH SID='rac1';
Sat Jan 14 22:39:18 CST 2012
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rac1';
Sat Jan 14 22:39:18 CST 2012
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rac1';
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET standby_file_management='auto' SCOPE=BOTH SID='*';
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET log_archive_max_processes=5 SCOPE=BOTH SID='*';
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET db_file_name_convert='+DATA/rac','/u01/app/oracle/oradata/orcl' SCOPE=SPFILE;
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET

log_file_name_convert='+FLASH/rac','/u01/app/oracle/flash_recover_area/orcl','+DATA/rac','/u01/app/oracle/orada

ta/orcl' SCOPE=SPFILE;
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.49)

(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl_XPT.yang.com)(SERVER=dedicated)))' SCOPE=BOTH;
Sat Jan 14 22:39:19 CST 2012
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.yang.com)

(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=rac_XPT.yang.com)(INSTANCE_NAME=rac1)(SERVER=dedicated)))' SCOPE=BOTH;
Sat Jan 14 22:39:19 CST 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sat Jan 14 22:39:20 CST 2012
MRP0: Background Media Recovery cancelled with status 16037
Sat Jan 14 22:39:20 CST 2012
Errors in file /u01/app/oracle/admin/rac/bdump/rac1_mrp0_22837.trc:
ORA-16037: user requested cancel of managed recovery operation
Sat Jan 14 22:39:20 CST 2012
Managed Standby Recovery not using Real Time Apply
Sat Jan 14 22:39:20 CST 2012
Recovery interrupted!
Recovered data files to a consistent state at change 565576
Sat Jan 14 22:39:21 CST 2012
Errors in file /u01/app/oracle/admin/rac/bdump/rac1_mrp0_22837.trc:
ORA-16037: user requested cancel of managed recovery operation
Sat Jan 14 22:39:21 CST 2012
MRP0: Background Media Recovery process shutdown (rac1)
Sat Jan 14 22:39:21 CST 2012
Managed Standby Recovery Canceled (rac1)
Sat Jan 14 22:39:21 CST 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sat Jan 14 22:39:21 CST 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Sat Jan 14 22:39:21 CST 2012
Attempt to start background Managed Standby Recovery process (rac1)
MRP0 started with pid=36, OS id=23667
Sat Jan 14 22:39:21 CST 2012
MRP0: Background Managed Standby Recovery process started (rac1)
Sat Jan 14 22:39:26 CST 2012
Managed Standby Recovery starting Real Time Apply
Sat Jan 14 22:39:26 CST 2012
 parallel recovery started with 2 processes
Sat Jan 14 22:39:27 CST 2012
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Sat Jan 14 22:39:27 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 31 Reading mem 0
  Mem# 0: +FRA/rac/onlinelog/group_11.303.772371907
Sat Jan 14 22:39:27 CST 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT

LOGFILE
Sat Jan 14 22:39:48 CST 2012
Media Recovery Waiting for thread 1 sequence 32
Sat Jan 14 22:39:48 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[5]: Assigned to RFS process 23859
RFS[5]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Successfully opened standby log 11: '+FRA/rac/onlinelog/group_11.303.772371907'
NSV0 started with pid=46, OS id=23866
Sat Jan 14 22:39:56 CST 2012
Recovery of Online Redo Log: Thread 1 Group 11 Seq 32 Reading mem 0
  Mem# 0: +FRA/rac/onlinelog/group_11.303.772371907
Sat Jan 14 22:40:15 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 24003
RFS[6]: Identified database type as 'physical standby'


[oracle@server49 ~]$ sqlplus sys/123456@orcl as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 14 22:47:14 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME       OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------- ---------------- --------------------
RAC       orcl                 READ WRITE PRIMARY          SESSIONS ACTIVE

SQL> conn sys/123456@rac as sysdba
Connected.
SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database;

NAME      DB_UNIQUE_NAME       OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------- ---------------- --------------------
RAC       rac                  MOUNTED    PHYSICAL STANDBY SESSIONS ACTIVE
RAC       rac                  MOUNTED    PHYSICAL STANDBY SESSIONS ACTIVE


SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     31
Next log sequence to archive   32
Current log sequence           32
SQL> alter system switch logfile;
System altered.

SQL> select first_time,next_time,sequence#,thread#,applied from v$archived_log where thread#=1 and sequence# >

30;

FIRST_TIM NEXT_TIME  SEQUENCE#    THREAD# APP
--------- --------- ---------- ---------- ---
14-JAN-12 14-JAN-12         31          1 YES
14-JAN-12 14-JAN-12         32          1 YES

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/764582如需转载请自行联系原作者


ylw6006
文章标签：
关系型数据库
Oracle
数据库
SQL
关键词：
RAC实例
RAC switchover
RAC库switchover
RAC单实例
科技探索者
目录
相关文章
姚远OracleACE
|
Oracle 关系型数据库 数据库
一次解决RAC实例状态为“Stuck Archiver”的经历
一个客户反映Oracle数据库恢复后，有一个数据文件报错
姚远OracleACE
323 0
姚远OracleACE
|
Oracle 关系型数据库 数据库
RAC中，控制文件的快照文件必须能够被所有节点的数据实例访问到 ORA-00245
RMAN在使用控制文件备份的时候，备份开始点需要最新的检查点信息以及文件头信息。
姚远OracleACE
114 0
SRE团队技术小编-小凌
|
Oracle 关系型数据库 开发者
自建裸金属实例Oracle RAC上云
自建裸金属实例Oracle RAC上云
SRE团队技术小编-小凌
20412 0
自建裸金属实例Oracle RAC上云
最佳实践小文
|
存储 弹性计算 负载均衡
自建裸金属实例Oracle RAC上云最佳实践
本实践介绍自建裸金属实例搭建 Oracle RAC 架构迁 移上云，提供高并发，高吞吐，高安全等 特性，适用于金融，电力，电信，制造业 等传统客户的核心交易系统。
最佳实践小文
3463 0
自建裸金属实例Oracle RAC上云最佳实践
持续高温
Oracle RAC迁移到单实例DB
这篇文章记录Oracle RAC通过RMAN备份如何迁移到单机DB，文章中描述了几个容易出错的地方，如下：    ①RAC到单机redo文件位置处理    ②RAC多个UNDO处理    ③RAC到单机临时表空间处理下面开始文章正文。
持续高温
8269 0
jan1990
rac库数据文件创建在os本地的解决办法
按照影响程度大小，方案A需要down库，方案B需要offline tablespace，方案C只需offline datafile即可。综合来看，下面的Testcase_C_2的方法来的最简单。影响也最小。
jan1990
1244 0
技术小阿哥
|
Oracle Java 关系型数据库
Oracle 11g R2 RAC dbca新建实例报错
技术小阿哥
1601 0
余二五
|
SQL Oracle 关系型数据库
Oracle单实例数据库迁移到Oracle RAC 环境之(3)--主备库Switchover
余二五
1208 0
技术小甜
|
SQL 存储 监控
同一环境下新建Standby RAC库
技术小甜
1055 0
余二五
|
Oracle 关系型数据库
12c rac 实例无法启动之磁盘组空间耗尽
余二五
993 0