Oracle RAC 主备切换实验（主库双节点RAC到单节点备库）

深碍√TFBOYSˉ_ 2023-06-27 03:08 56阅读 0赞

本实验环境如下：  
主库Oracle RAC双节点  
node1 192.168.169.111  
node2 192.168.169.222

备库为Oracle单节点  
192.168.169.123

## 操作步骤 ##

1、主库修改相关参数 fal\_client、fal\_server：

SQL> show parameter spfile;
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    spfile				     string	 +DATA/devdb/spfiledevdb.ora
    SQL> show parameter fal
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    fal_client			     string
    fal_server			     string
    SQL> alter system set fal_client='devdb1' sid='devdb1';
    
    System altered.
    
    SQL> alter system set fal_client='devdb2' sid='devdb2';
    
    System altered.
    
    SQL> alter system set fal_server='phydb' sid='*';
    
    System altered.
    
    SQL> show parameter fal
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    fal_client			     string	 devdb1
    fal_server			     string	 phydb

节点2上的

SQL> show parameter fal
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    fal_client			     string	 devdb2
    fal_server			     string	 phydb

2、主库创建 standby logfile：  
创建之前，主库是没有standby logfile记录

SQL> set line 200
    SQL> col member for a60
    SQL> select * from v$logfile;
    
        GROUP# STATUS  TYPE    MEMBER							IS_
    ---------- ------- ------- ------------------------------------------------------------ ---
    	 2	   ONLINE  +DATA/devdb/onlinelog/group_2.262.1027260437 		NO
    	 2	   ONLINE  +FLASH/devdb/onlinelog/group_2.258.1027260441		YES
    	 1	   ONLINE  +DATA/devdb/onlinelog/group_1.261.1027260435 		NO
    	 1	   ONLINE  +FLASH/devdb/onlinelog/group_1.257.1027260435		YES
    	 3	   ONLINE  +DATA/devdb/onlinelog/group_3.265.1027260897 		NO
    	 3	   ONLINE  +FLASH/devdb/onlinelog/group_3.259.1027260897		YES
    	 4	   ONLINE  +DATA/devdb/onlinelog/group_4.266.1027260901 		NO
    	 4	   ONLINE  +FLASH/devdb/onlinelog/group_4.260.1027260903		YES
    
    8 rows selected.
    
    SQL> select * from v$standby_log;
    
    no rows selected

主库进行创建standby logfile

SQL> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m;
    
    Database altered.
    
    SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;
    
    Database altered.
    
    SQL> select * from v$logfile;
    
        GROUP# STATUS  TYPE    MEMBER							IS_
    ---------- ------- ------- ------------------------------------------------------------ ---
    	 2	   ONLINE  +DATA/devdb/onlinelog/group_2.262.1027260437 		NO
    	 2	   ONLINE  +FLASH/devdb/onlinelog/group_2.258.1027260441		YES
    	 1	   ONLINE  +DATA/devdb/onlinelog/group_1.261.1027260435 		NO
    	 1	   ONLINE  +FLASH/devdb/onlinelog/group_1.257.1027260435		YES
    	 3	   ONLINE  +DATA/devdb/onlinelog/group_3.265.1027260897 		NO
    	 3	   ONLINE  +FLASH/devdb/onlinelog/group_3.259.1027260897		YES
    	 4	   ONLINE  +DATA/devdb/onlinelog/group_4.266.1027260901 		NO
    	 4	   ONLINE  +FLASH/devdb/onlinelog/group_4.260.1027260903		YES
    	 5	   STANDBY +DATA/devdb/onlinelog/group_5.270.1028635077 		NO
    	 5	   STANDBY +FLASH/devdb/onlinelog/group_5.301.1028635081		YES
    	 6	   STANDBY +DATA/devdb/onlinelog/group_6.271.1028635083 		NO
    
        GROUP# STATUS  TYPE    MEMBER							IS_
    ---------- ------- ------- ------------------------------------------------------------ ---
    	 6	   STANDBY +FLASH/devdb/onlinelog/group_6.302.1028635089		YES
    	 7	   STANDBY +DATA/devdb/onlinelog/group_7.272.1028635091 		NO
    	 7	   STANDBY +FLASH/devdb/onlinelog/group_7.303.1028635095		YES
    	 8	   STANDBY +DATA/devdb/onlinelog/group_8.273.1028635133 		NO
    	 8	   STANDBY +FLASH/devdb/onlinelog/group_8.304.1028635137		YES
    	 9	   STANDBY +DATA/devdb/onlinelog/group_9.274.1028635139 		NO
    	 9	   STANDBY +FLASH/devdb/onlinelog/group_9.305.1028635141		YES
    	10	   STANDBY +DATA/devdb/onlinelog/group_10.275.1028635143		NO
    	10	   STANDBY +FLASH/devdb/onlinelog/group_10.306.1028635145		YES
    
    20 rows selected.
    SQL> select * from v$standby_log;
    
        GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIME	    NEXT_CHANGE# NEXT_TIME
    ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
    LAST_CHANGE# LAST_TIME
    ------------ -------------------
    	 5 UNASSIGNED					     1		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    	 6 UNASSIGNED					     1		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    	 7 UNASSIGNED					     1		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    
        GROUP# DBID 				       THREAD#	SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS	  FIRST_CHANGE# FIRST_TIME	    NEXT_CHANGE# NEXT_TIME
    ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
    LAST_CHANGE# LAST_TIME
    ------------ -------------------
    	 8 UNASSIGNED					     2		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    	 9 UNASSIGNED					     2		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    	10 UNASSIGNED					     2		0   52428800	    512        512 YES UNASSIGNED	      0              0
    	   0
    
    
    6 rows selected.

3、主 库 修 改 相 关 参 数 standby\_file\_management 、 db\_file\_name\_convert 、  
log\_file\_name\_convert：  
修改之前，查看下参数

SQL> show parameter standby_file
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    standby_file_management 	     string	 MANUAL
    SQL> show parameter db_file_name
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_name_convert		     string
    SQL> show parameter log_file
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    log_file_name_convert		     string

修改下这两个参数

SQL> alter system set db_file_name_convert='+DATA/phydb/','+DATA/devdb/' sid='*' scope=spfile;
    
    System altered.
    
    SQL> alter system set log_file_name_convert='+DATA/phydb/','+DATA/devdb/' sid='*' scope=spfile;
    
    System altered.

4、停止RAC node1节点

SQL> set line 300
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1533716 MAXIMUM PERFORMANCE  PRIMARY	       YES READ WRITE		TO STANDBY
     931990411 DEVDB	 1533729 MAXIMUM PERFORMANCE  PRIMARY	       YES READ WRITE		TO STANDBY
    
    SQL> shutdown immediate;

5、RAC node2切换原RAC主库到备库

如下是在node1没有完成执行完shutdown immediate的情况，查询的switchover\_status为RESOLVABLE GAP

SQL> show parameter instance_name
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    instance_name			     string	 devdb2
    SQL> set line 300
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535186 MAXIMUM PERFORMANCE  PRIMARY	       YES READ WRITE		RESOLVABLE GAP

说明在商量中，待完全关闭node1节点后，查询结果为TO STANDBY

SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535367 MAXIMUM PERFORMANCE  PRIMARY	       YES READ WRITE		TO STANDBY

执行如下语句

SQL> alter database commit to switchover to physical standby with session shutdown;
    
    Database altered.

此时，出现的情况为群龙无首的请求，没有主库。

6、切换原物理备库到主库角色

SQL> show parameter instance_name;
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    instance_name			     string	 phydb
    SQL> set line 300
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1484223 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY		SWITCHOVER PENDING

SQL> alter database commit to switchover to primary;
    alter database commit to switchover to primary
    *
    ERROR at line 1:
    ORA-16139: media recovery required
    
    
    SQL> ALTER  DATABASE RECOVER MANAGED STANDBY  DATABASE DISCONNECT FROM SESSION;
    
    Database altered.
    
    SQL> alter database commit to switchover to primary;
    
    Database altered.
    
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	       0 MAXIMUM PERFORMANCE  PRIMARY	       YES MOUNTED		NOT ALLOWED
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1536105 MAXIMUM PERFORMANCE  PRIMARY	       YES READ WRITE		FAILED DESTINATION

节点2上查看

SQL> show parameter instance_name;
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    instance_name			     string	 devdb2
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED		TO PRIMARY

把node1节点启动，查看其数据库角色也为PHYSICAL STANDBY。

7、两个节点启动到mount状态  
srvctl start database -d devdb -o mount

SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED		SESSIONS ACTIVE
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED		SESSIONS ACTIVE

8、原 RAC 主库启动 redo apply：

SQL> alter database recover managed standby database using current logfile disconnect from session;
    
    Database altered.

9、原RAC主库停止redo apply,并将 RAC 主库所有节点以 READ ONLY 打开

SQL> alter database recover managed standby database using current logfile disconnect from session;
    
    Database altered.
    
    SQL> alter database recover managed standby database cancel;
    
    Database altered.
    
    SQL> alter database open;      ##另外一个节点也执行打开数据库
    
    Database altered.
    
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY		SESSIONS ACTIVE
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY		SESSIONS ACTIVE
     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
    
    Database altered.
    
    SQL> select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;
    
          DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE		SWITCHOVER_STATUS
    ---------- --------- ----------- -------------------- ---------------- --- -------------------- --------------------
     931990411 DEVDB	 1535701 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY SESSIONS ACTIVE