RAC dataguard切换演练实录

蔚落 2022-08-10 17:38 149阅读 0赞

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切换完成