本实验环境如下：
主库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
切换好之后最好测试下，比如主库创建一个表空间，看归档日志有没有正常传输，备库是否能感知到。
790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
