再做 Oracle 11gR2 RAC+DG4

by Posted on

第3部分已完成RAC主库到dg备库的日志同步. 这部分将配置dg备库 应用同步过来的日志.

接着完成RAC和dg角色互换.

查看备库是否应用日志

SQL> select sequence# ,name ,applied from v$archived_log;

SEQUENCE# NAME APPLIED

———- ———————————————————————- ———

24 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_24.264.915256125 NO

22 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_22.262.915256125 NO

23 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_23.263.915256125 NO

26 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_26.265.915256129 NO

25 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_25.266.915256129 NO

27 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_27.267.915256129 NO

28 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_28.268.915256129 NO

29 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_29.269.915256129 NO

32 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_32.270.915256129 NO

30 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_30.272.915256129 NO

31 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_31.271.915256129 NO

applied属性为NO,dg备库还没有应用同步过来的重做日志.

SQL> alter database recover managed standby database using current logfile disconnect

from session;

Database altered.

再次执行select sequence# ,name ,applied from v$archived_log; 发现已经同步

SEQUENCE# NAME APPLIED

———- ———————————————————————- ———

24 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_24.264.915256125 YES

22 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_22.262.915256125 YES

23 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_23.263.915256125 YES

26 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_26.265.915256129 YES

25 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_25.266.915256129 YES

27 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_27.267.915256129 YES

28 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_28.268.915256129 YES

29 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_29.269.915256129 YES

32 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_32.270.915256129 YES

30 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_30.272.915256129 YES

31 +FLASH/phydb/archivelog/2016_06_23/thread_1_seq_31.271.915256129 YES

将dg从mount状态切换到open状态, 但在切换之前必须关闭dg应用这些日志.

SQL> alter database open;

Database altered.

查看dg状态

SQL > select dbid,name,switchover_status,db_unique_name,database_role,

open_mode,current_scn from v$database;

dg58

重新应用日志

SQL> alter database recover managed standby database using current logfile disconnect

from session;

Database altered.

测试RAC主库和dg备库的数据

node1和gd都执行select file_name,tablespace_name from dba_data_files 发现数据文件是一致的.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

———————————————————— ——————————

+DATA/devdb/datafile/users.259.914028331 USERS

+DATA/devdb/datafile/undotbs1.258.914028331 UNDOTBS1

+DATA/devdb/datafile/sysaux.257.914028331 SYSAUX

+DATA/devdb/datafile/system.256.914028329 SYSTEM

+DATA/devdb/datafile/example.264.914028635 EXAMPLE

+DATA/devdb/datafile/undotbs2.265.914028947 UNDOTBS2

6 rows selected.

RAC查看原表空间, 创建表空间, 查看表空间路径,验证表空间

SQL >create tablespace dg_tbs datafile size 5m;

SQL >show parameter db_create_file;

SQL> select file_name,tablespace_name from dba_data_files;

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

———————————————————— ——————————

+DATA/devdb/datafile/users.259.914028331 USERS

+DATA/devdb/datafile/undotbs1.258.914028331 UNDOTBS1

+DATA/devdb/datafile/sysaux.257.914028331 SYSAUX

+DATA/devdb/datafile/system.256.914028329 SYSTEM

+DATA/devdb/datafile/example.264.914028635 EXAMPLE

+DATA/devdb/datafile/undotbs2.265.914028947 UNDOTBS2

+DATA/devdb/datafile/dg_tbs.269.265.917493135 DG_TBS

7 rows selected.

dg备库执行 select file_name,tablespace_name from dba_data_files;

dg60

说明dg备库将表空件同步过来了.

RAC主库删除该表空间

SQL >drop tablespace dg_tbs including contents and datafiles;

Tablespace dropped

dg备库检查一下现有表空间

dg61

上图所示,当主库删除表空间后,备库对应的表空间也消失了.

RAC主库和dg备库的角色互换

RAC上执行

SQL > alter system set fal_client=’devdb’ sid=’devdb1’;

System altered.

SQL > alter system set fal_client=’devdb’ sid=’devdb2’;

System altered.

SQL > alter system set fal_server=’phydb’ sid=’*’;

System altered.

node1和node2执行

show parameter spfile;

dg62

dg63

RAC主库创建standby logfile;

在创建之前查看一下本地日志组

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————— —

2 ONLINE +DATA/phydb/onlinelog/group_2.270.915262415 NO

2 ONLINE +FLASH/phydb/onlinelog/group_2.289.915262423 YES

1 ONLINE +DATA/phydb/onlinelog/group_1.269.915262411 NO

1 ONLINE +FLASH/phydb/onlinelog/group_1.288.915262415 YES

3 ONLINE +DATA/phydb/onlinelog/group_3.271.915262423 NO

3 ONLINE +FLASH/phydb/onlinelog/group_3.290.915262429 YES

4 ONLINE +DATA/phydb/onlinelog/group_4.272.915262429 NO

4 ONLINE +FLASH/phydb/onlinelog/group_4.291.915262433 YES

可见本地日志组是1,2,3,4 ; 因此创建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.

RAC主库修改参数db_file_name_convert和 log_file_name_convert

参数作用是处理主库和备库磁盘结构不一致的问题,比如: 主库磁盘组data备库磁盘组datadg,

将来在data磁盘组创建表空间,

而备库得到这个表空间,表空间含有data磁盘组, 而备库没有data磁盘组, 因此需要这几个参数将旧磁盘组

data转换到新的磁盘组datadg.否则因为备库上没有data磁盘组而不能创建这个表空间.

SQL >alter system set db_file_name_convert=’+DATA/phydb/’ , ‘+DATA/devdb/’ sid=’*’ scope=spfile;

Database altered.

SQL >alter system set log_file_name_convert=’+DATA/phydb/’ , ‘+DATA/devdb/’ sid=’*’ scope=spfile;

Database altered.

下面进入主备切换环节

停掉RAC的其中一个节点,这里停止node2

SQL > shutdown immediate;

Datebase closed.

Datebase dismounted.

ORACLE instance shut down.

将node1切换成备库

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

Database altered.

查看node1状态

SQL > select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,

switchover_status from gv$database;

dg64

目前的情况 RAC和dg都是standby状态了

dg切换成主库

SQL > alter database commit to switchover to primary;

Database altered.

查看一下dg状态

SQL > select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,

switchover_status from gv$database;

dg65

停止node1和node2

SQL > shutdown immediate;

Datebase closed.

Datebase dismounted.

ORACLE instance shut down.

退出sqlplus ,切换到grid用户,然后停止实例devdb

node1和node2切换到mount状态

node1 -> srvctl start database -d devdb -o mount;

node1和node2切换到oracle用户,sqlplus

SQL > select dbid,name,current_scn,protection_mode,database_role,force_logging,open_mode,

switchover_status from gv$database;

发现node1和node2的switchover_status都是session active的; DATABASE_ROLE都变成了

PHYSICAL STANDBY

dg66

RAC备库应用日志

SQL > alter database recover managed standby database using current logfile disconnect

from session;

Database altered.

将dg主库从mount状态切换到open状态

SQL > alter database open;

Database altered.

此时dg主库 OPEN_MODE是READ WRITE, SWITCHOVER_STATUS是RESOLVABLE GAP

dg67

 

RAC主库的SWITCHOVER_STATUS状态改为READ ONLY

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;

RAC备库打开日志应用

SQL > alter database recover managed standby database using current logfile disconnect

from session;

Database altered.

最终状态确认

dg主库状态和rac备库状态

dg68

dg69

至此rac库和dg库角色切换操作成功.

oracle 11gR2 RAC+DG搭建测试完成.

Donate

Published by byron

Leave a Reply