oracle rac 切换演练,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切换完成