RAC dataguard切换演练实录

RAC dataguard切换演练实录

环境 primary是两个节点的rac  standby是两个节点rac

1.检查参数是否支持主备互切换

SQL> show parameter log_archive_dest_2
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
log_archive_dest_2                   string
service=STANDBYORCL valid_for=
(ALL_LOGFILES,ALL_ROLES) db_un
ique_name=orcldg
检查网络
SQL> !
[oracle@orclrac1 ~]$ tnsping standbyorcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-8鏈?-2014 13:24:56
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.0.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg)))
OK (0 msec)
[oracle@orclrac1 ~]$ exit
exit
SQL> !
停掉其他节点的时候只留一个
[oracle@orclrac1 ~]$ srvctl stop instance -d orcl -i orcl2
[oracle@orclrac1 ~]$ exit
exit
需要检查主数据库是否可以进行转换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
RESOLVABLE GAP
发现有日志没有传输到standby端，执行日志切换
SQL> alter system switch logfile;
System altered.
再次检查，发现主库可以切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO STANDBY
主库执行角色转换
SQL>  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
  2  ;
Database altered.
把主库启动到mount 状态
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> exit
Disconnected from 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
[oracle@orclrac1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on 鏄熸湡鍥?8鏈?7 13:30:14 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2.2448E+10 bytes
Fixed Size                  2263296 bytes
Variable Size            9126807296 bytes
Database Buffers         1.3288E+10 bytes
Redo Buffers               31277056 bytes
Database mounted

检查切换前的standby库
SQL> show parameter log_archive_dest_2

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
log_archive_dest_2                   string
service=primaryorcl valid_for=
(ALL_LOGFILES,ALL_ROLES) db_un
ique_name=orcl
SQL> !
[oracle@orclsty1 ~]$ tnsping primaryorcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 07-8鏈?-2014 13:24:34
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.19.0.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@orclsty1 ~]$ exit
exit
关闭standby库其他实例
[oracle@orclsty1 ~]$ srvctl stop instance -d orcl -i orcl2
[oracle@orclsty1 ~]$ exit
检查standby 库是否可以转换为主库发现为NOT ALLOWED有问题
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
NOT ALLOWED
直接执行切换报错
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
*
ERROR at line 1:
ORA-16139: 闇€瑕佷粙璐ㄦ仮澶?
可能原因是应为原来standby备库日志没有应用，执行应用
SQL> alter database recover managed standby database disconnect from session;
Database altered.
然后再次检查状态，执行切换为primay库
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
------------------------------------------------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
打开数据库并开启实时应用
SQL> alter database open;
Database altered.
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
测试在新主库建表，并查询数据
SQL> create table test(id number);
create table test(id number)
*
ERROR at line 1:
ORA-01109: 鏁版嵁搴撴湭鎵撳紑
SQL> alter database open; 
Database altered.
SQL>  create table test(id number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
在新standby库中查询，发现数据实时同步过来了，测试成功
SQL> select * from test;
        ID
----------
         1

修改集群的配置，并把启动节点的rac启动
新standby端：
[oracle@orclrac1 ~]$ export LANG=zh_CN.GBK 
[oracle@orclrac1 ~]$ srvctl config database -d orcl
数据库唯一名称: orcl
数据库名: orcl
Oracle 主目录: /oracle/oracle/app/oracle/product/11.2.0/dbhome_1
Oracle 用户: oracle
Spfile: +DATADG/orcl/spfileorcl.ora
域: 
启动选项: open
停止选项: immediate
数据库角色: PRIMARY
管理策略: AUTOMATIC
服务器池: orcl
数据库实例: orcl1,orcl2
磁盘组: DATADG,SYSTEMDG
装载点路径: 
服务: 
类型: RAC
数据库是管理员管理的
[oracle@orclrac1 ~]$ srvctl modify database -d orcl -n orcl -r PHYSICAL_STANDBY
[oracle@orclrac1 ~]$ srvctl config database -d orcl
数据库唯一名称: orcl
数据库名: orcl
Oracle 主目录: /oracle/oracle/app/oracle/product/11.2.0/dbhome_1
Oracle 用户: oracle
Spfile: +DATADG/orcl/spfileorcl.ora
域: 
启动选项: open
停止选项: immediate
数据库角色: PHYSICAL_STANDBY
管理策略: AUTOMATIC
服务器池: orcl
数据库实例: orcl1,orcl2
磁盘组: DATADG,SYSTEMDG
装载点路径: 
服务: 
类型: RAC
数据库是管理员管理的
[oracle@orclrac1 ~]$ 
[oracle@orclrac1 ~]$ 
[oracle@orclrac1 ~]$ srvctl stop database -d orcl
[oracle@orclrac1 ~]$ srvctl start database -d orcl
新primary端：
[oracle@orclsty1 ~]$ srvctl config database -d orcl
数据库唯一名称: orcl
数据库名: orcl
Oracle 主目录: /oracle/oracle/app/oracle/product/11.2.0/dbhome_1
Oracle 用户: oracle
Spfile: +DATADG/orcl/spfileorcl.ora
域: 
启动选项: open
停止选项: immediate
数据库角色: PHYSICAL_STANDBY
管理策略: AUTOMATIC
服务器池: orcl
数据库实例: orcl1,orcl2
磁盘组: DATADG,SYSTEMDG
装载点路径: 
服务: 
类型: RAC
数据库是管理员管理的
[oracle@orclsty1 ~]$ srvctl modify database -d orcl -n orcl -r primary
[oracle@orclsty1 ~]$ srvctl config database -d orcl
数据库唯一名称: orcl
数据库名: orcl
Oracle 主目录: /oracle/oracle/app/oracle/product/11.2.0/dbhome_1
Oracle 用户: oracle
Spfile: +DATADG/orcl/spfileorcl.ora
域: 
启动选项: open
停止选项: immediate
数据库角色: PRIMARY
管理策略: AUTOMATIC
服务器池: orcl
数据库实例: orcl1,orcl2
磁盘组: DATADG,SYSTEMDG
装载点路径: 
服务: 
类型: RAC
数据库是管理员管理的
再次打开新standby库的实时应用
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
遇到问题：
发现alert日志里报如下错误：
新standby端：
Thu Aug 07 14:04:24 2014
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
Thu Aug 07 14:05:24 2014
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
Thu Aug 07 14:06:24 2014
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
PING[ARC2]: Heartbeat failed to connect to standby 'STANDBYORCL'. Error is 16009.
新primary端：
Thu Aug 07 14:04:19 2014
RFS[13]: Assigned to RFS process 4642
RFS[13]: Database mount ID mismatch [0x526af93d:0x5269e114] (1382742333:1382670612)
RFS[13]: Client instance is standby database instead of primary
Thu Aug 07 14:05:19 2014
RFS[14]: Assigned to RFS process 4781
RFS[14]: Database mount ID mismatch [0x526af93d:0x5269e114] (1382742333:1382670612)
RFS[14]: Client instance is standby database instead of primary
Thu Aug 07 14:05:19 2014
RFS[15]: Assigned to RFS process 4783
RFS[15]: Database mount ID mismatch [0x526af93d:0x5269e114] (1382742333:1382670612)
RFS[15]: Client instance is standby database instead of primary
Thu Aug 07 14:06:19 2014
RFS[16]: Assigned to RFS process 4918
RFS[16]: Database mount ID mismatch [0x526af93d:0x5269e114] (1382742333:1382670612)
RFS[16]: Client instance is standby database instead of primary
Thu Aug 07 14:06:19 2014
原因是有由于log_archive_dest_2设置问题valid_for=(ALL_LOGFILES,ALL_ROLES)导致的
SQL> show parameter log_archive_dest_2
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
log_archive_dest_2                   string
service=STANDBYORCL valid_for=
(ALL_LOGFILES,ALL_ROLES) db_un
ique_name=orcldg
把新备份库的log_archive_dest_state_2设置为defer后者把VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)问题解决，报错日志不再产生
SQL> alter system set log_archive_dest_state_2=defer scope=both ;
至此rac datagurad切换完成