ORACLE数据库一主多备DG环境(一对多)主备切换

ORACLE数据库一主多备DG环境(一对多)主备切换

Mar282016
作者：dbdream   发布：2016-03-28 18:14   分类：Oracle, oracle 10g, oracle 11g     抢沙发  
 

本文主要分享一对多(一主多备)的DG环境主备切换的案例，我们的一套生产环境，一主四备，其中3个备库承载着查询业务，还有一个DG没有任务业务，由于主库的存储性能不给力，公司采购了新的存储，这个DG就是为了切换到新存储而搭建的，数据量1.5TB。

本文是在测试环境操作，之前并没有切换过一对多的DG环境，而且网上基本没有关于一对多DG环境切换的资料，官方文档也只是说自己选择切换到哪个DG，也没有详细的介绍和案例。本文测试环境为一主两备，版本为11.2.0.4.0。

以下是主库的几个主要参数的配置信息：

01 NAME                                 TYPE        VALUE
02 ------------------------------------ ----------- ------------------------------
03 fal_client                           string
04 fal_server                           string
05 log_archive_config                   string      dg_config=(orac2,orastd1,orastd2)
06 log_archive_dest_1                   string      location=/u01/app/oracle/orada
07                                                  ta/arch valid_for=(all_logfile
08                                                  s,all_roles) db_unique_name=or
09                                                  ac2
10 log_archive_dest_2                   string      service=orastd1 lgwr async val
11                                                  id_for=(online_logfiles,primar
12                                                  y_role) db_unique_name=orastd1
13 log_archive_dest_3                   string      service=orastd2 lgwr async val
14                                                  id_for=(online_logfiles,primar
15                                                  y_role) db_unique_name=orastd2

以下是ORASTD1备库的主要参数设置：

01 NAME                                 TYPE        VALUE
02 ------------------------------------ ----------- ------------------------------
03 log_archive_config                   string      dg_config=(orac2,orastd1)
04 og_archive_dest_1                   string      location=/u01/app/oracle/orada
05                                                  ta/arch valid_for=(all_logfile
06                                                  s,all_roles) db_unique_name=or
07                                                  astd1
08 og_archive_dest_2                   string      service=orac2 lgwr async valid
09                                                  _for=(online_logfiles,primary_
10                                                  role) db_unique_name=orac2
11 fal_client                           string      ORASTD1
12 fal_server                           string      ORAC2

以下是ORASTD2备库的主要参数设置：

01 NAME                                 TYPE        VALUE
02 ------------------------------------ ----------- ------------------------------
03 fal_client                           string      ORASTD2
04 fal_server                           string      ORAC2
05 log_archive_config                   string      dg_config=(orac2,orastd2)
06 log_archive_dest_1                   string      location=/u01/app/oracle/orada
07                                                  ta/arch valid_for=(all_logfile
08                                                  s,all_roles) db_unique_name=or
09                                                  astd2
10 log_archive_dest_2                   string      service=orac2 lgwr async valid
11                                                  _for=(online_logfiles,primary_
12                                                  role) db_unique_name=orac2

在DG切换之前，一定要检查当前环境是否可以切换，我在做这个测试的时候，因为是刚刚搭建的测试环境，而且搭建时已经测试，所以在切换之前没有检查。我之前就做过类似的案例，差点死的很惨，这里简单说一嘴，那是航天系统的一套数据库，当时负责人找我去做切换测试，他们的DBA已经离职，离职前写了一个切换方案，我到客户机房的时候，客户给我看了下那个方案，很简单的方案，只有切换的命令，并没有检查的相关信息，我看完方案后，直接提出方案不完成，切换之前需要检查，因为我是第一次接触这个数据库，操作之前必须仔细检查，万一掉坑里怎么办？我在检查时发现，备库和主库之间差了半年多的延迟，而且备库并没有接收到这半年来的归档，检查发现，备库在半年之前，主机重启，并没人起备库的监听和数据库，导致主库的日志传不到备库，经检查发现备库少了将近三千多个归档，短时间很难追得上，还好那个数据库只有20个GB，我直接选择重新搭建备库了，然后顺利的进行了主备切换。

在切换时，在主库上是不可以选择我要切换到哪个备库的，这个选择是在备库上选择的，下面进行SWITCHOVER切换。

主库(ORAC2)：

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

切换后，这个数据库已经备关闭，启动数据库，这个数据库已经变为备库，启动MRP进程。

01 SQL> startup
02 ORACLE instance started.
03  
04 Total System Global Area  835104768 bytes
05 Fixed Size                  2257840 bytes
06 Variable Size             528485456 bytes
07 Database Buffers          301989888 bytes
08 Redo Buffers                2371584 bytes
09 Database mounted.
10 Database opened.
11  
12 SQL> alter database recover managed standby database using current logfile disconnect from session;
13  
14 Database altered.
15  
16 SQL> select open_mode from v$database;
17  
18 OPEN_MODE
19 --------------------
20 READ ONLY WITH APPLY
21  
22 SQL> select database_role,switchover_status from v$database;
23  
24 DATABASE_ROLE    SWITCHOVER_STATUS
25 ---------------- --------------------
26 PHYSICAL STANDBY TO PRIMARY

将ORAC2数据库的FAL修为为ORASTD1。

1 SQL> alter system set fal_server=ORASTD1; 
2  
3 System altered.

主库切换后，两个备库均变为TO PRIMARY状态，这时就要选择切换哪个备库为主库了。

1 SQL> select database_role,switchover_status from v$database;
2  
3 DATABASE_ROLE    SWITCHOVER_STATUS
4 ---------------- --------------------
5 PHYSICAL STANDBY TO PRIMARY

这里选择ORASTD1这个备库切换为主库。

ORASTD1：

1 SQL> alter database commit to switchover to primary;
2  
3 Database altered.

切换后数据库变为MOUNT状态，打开数据库。

01 SQL> select open_mode from v$database;
02  
03 OPEN_MODE
04 --------------------
05 MOUNTED
06  
07 SQL> alter database open;
08  
09 Database altered.
10  
11 SQL> select database_role,switchover_status from v$database;
12  
13 DATABASE_ROLE    SWITCHOVER_STATUS
14 ---------------- --------------------
15 PRIMARY          NOT ALLOWED

因为之前ORASTD1这个备库的log_archive_config参数并没有添加ORASTD2这个备库，现在变成了主库，需要添加上所有的数据库。

1 SQL> alter system set log_archive_config='dg_config=(orac2,orastd1,orastd2)';
2  
3 System altered.

切换日志发现，日志并没有传到备库(ORAC2)。

1 SQL> alter system  switch logfile;
2  
3 System altered.

修改相关的log_archive_dest_state参数，日志可以成功发送到备库(ORAC2)，备库(ORAC2)也可以正常应用，备库(ORAC2)开始和主库(ORASTD1)同步数据。

1 SQL> alter system set log_archive_dest_state_2=enable;
2  
3 System altered.

因为ORASTD2这个数据库之前的主是ORAC2，现在已经变成了备库，需要把ORASTD2的主改成ORASTD1。因为ORASTD1并没有配ORASTD2的参数，需要把ORASTD2添加进来。

1 SQL> ALTER SYSTEM SET log_archive_dest_3='service=orastd2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orastd2';
2  
3 System altered.

将当前主库添加了归档到ORASTD2后，还需要将ORASTD2的远程归档信息改为ORASTD1。

ORASTD2:

1 SQL> ALTER SYSTEM SET log_archive_dest_2='service=orastd1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orastd1';
2  
3 System altered.

ORASTD2的FAL信息从ORAC2修改为ORASTD1。

1 SQL> alter system set fal_server=ORASTD1; 
2  
3 System altered.

此时整个DG切换就已经完成，ORASTD2数据库无需做其他操作，MRP进程也不需要重新启动，此时主库(ORASTD1)切换日志，会成功发送到所有的备库(ORAC2，ORASTD2)，两个备库会直接应用。

ORAC2：

01 Completed: alter database recover managed standby database using current logfile disconnect from session
02 Clearing online redo logfile 3 complete
03 Media Recovery Waiting for thread 1 sequence 31
04  
05 Mon Jan 25 17:49:54 2016
06 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/oradata/arch
07 RFS[1]: Assigned to RFS process 17703
08 RFS[1]: Opened log for thread 1 sequence 32 dbid 1800997619 branch 901820147
09 Archived Log entry 24 added for thread 1 sequence 32 rlc 901820147 ID 0x6b5ca155 dest 2:
10 RFS[1]: Opened log for thread 1 sequence 33 dbid 1800997619 branch 901820147
11 Archived Log entry 25 added for thread 1 sequence 33 rlc 901820147 ID 0x6b5ca155 dest 2:
12 Mon Jan 25 17:49:54 2016
13 RFS[2]: Assigned to RFS process 17705
14 RFS[2]: Opened log for thread 1 sequence 31 dbid 1800997619 branch 901820147
15 RFS[1]: Opened log for thread 1 sequence 34 dbid 1800997619 branch 901820147
16 Archived Log entry 26 added for thread 1 sequence 34 rlc 901820147 ID 0x6b5ca155 dest 2:
17 Archived Log entry 27 added for thread 1 sequence 31 rlc 901820147 ID 0x6b5ca155 dest 2:
18 RFS[2]: Selected log 10 for thread 1 sequence 35 dbid 1800997619 branch 901820147
19 Mon Jan 25 17:49:54 2016
20 Archived Log entry 28 added for thread 1 sequence 35 ID 0x6b5ca155 dest 1:
21 Mon Jan 25 17:49:55 2016
22 Primary database is in MAXIMUM PERFORMANCE mode
23 RFS[3]: Assigned to RFS process 17707
24 RFS[3]: Selected log 10 for thread 1 sequence 36 dbid 1800997619 branch 901820147
25 Mon Jan 25 17:49:55 2016
26 Media Recovery Log /u01/app/oracle/oradata/arch/1_31_901820147.dbf
27 Media Recovery Log /u01/app/oracle/oradata/arch/1_32_901820147.dbf
28 Media Recovery Log /u01/app/oracle/oradata/arch/1_33_901820147.dbf
29 Media Recovery Log /u01/app/oracle/oradata/arch/1_34_901820147.dbf
30 Media Recovery Log /u01/app/oracle/oradata/arch/1_35_901820147.dbf
31 Media Recovery Waiting for thread 1 sequence 36 (in transit)
32 Recovery of Online Redo Log: Thread 1 Group 10 Seq 36 Reading mem 0
33   Mem# 0: /u01/app/oracle/oradata/orac2/st01.log
34 Mon Jan 25 17:50:19 2016
35 Expanded controlfile section 11 from 28 to 280 records
36 Requested to grow by 252 records; added 9 blocks of records
37 Archived Log entry 29 added for thread 1 sequence 36 ID 0x6b5ca155 dest 1:
38 Mon Jan 25 17:50:19 2016
39 Primary database is in MAXIMUM PERFORMANCE mode
40 Mon Jan 25 17:50:19 2016
41 Media Recovery Waiting for thread 1 sequence 37
42 RFS[4]: Assigned to RFS process 17713
43 RFS[4]: Selected log 10 for thread 1 sequence 37 dbid 1800997619 branch 901820147
44 Recovery of Online Redo Log: Thread 1 Group 10 Seq 37 Reading mem 0
45   Mem# 0: /u01/app/oracle/oradata/orac2/st01.log
46 Mon Jan 25 17:51:03 2016
47 ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
48 Mon Jan 25 17:53:46 2016
49 Archived Log entry 30 added for thread 1 sequence 37 ID 0x6b5ca155 dest 1:
50 Mon Jan 25 17:53:46 2016
51 RFS[4]: Selected log 10 for thread 1 sequence 38 dbid 1800997619 branch 901820147
52 Mon Jan 25 17:53:46 2016
53 Media Recovery Waiting for thread 1 sequence 38 (in transit)
54 Recovery of Online Redo Log: Thread 1 Group 10 Seq 38 Reading mem 0
55   Mem# 0: /u01/app/oracle/oradata/orac2/st01.log
56 RFS[4]: Selected log 11 for thread 1 sequence 39 dbid 1800997619 branch 901820147
57 Mon Jan 25 17:53:49 2016
58 Archived Log entry 31 added for thread 1 sequence 38 ID 0x6b5ca155 dest 1:
59 Media Recovery Waiting for thread 1 sequence 39 (in transit)
60 Recovery of Online Redo Log: Thread 1 Group 11 Seq 39 Reading mem 0
61   Mem# 0: /u01/app/oracle/oradata/orac2/st02.log

ORASTD2:

01 Media Recovery Log /u01/app/oracle/oradata/arch/1_31_901820147.dbf
02 Media Recovery Log /u01/app/oracle/oradata/arch/1_32_901820147.dbf
03 Media Recovery Log /u01/app/oracle/oradata/arch/1_33_901820147.dbf
04 Media Recovery Log /u01/app/oracle/oradata/arch/1_34_901820147.dbf
05 Media Recovery Log /u01/app/oracle/oradata/arch/1_35_901820147.dbf
06 Media Recovery Log /u01/app/oracle/oradata/arch/1_36_901820147.dbf
07 Media Recovery Log /u01/app/oracle/oradata/arch/1_37_901820147.dbf
08 Media Recovery Waiting for thread 1 sequence 38
09 RFS[6]: Opened log for thread 1 sequence 38 dbid 1800997619 branch 901820147
10 Archived Log entry 14 added for thread 1 sequence 38 rlc 901820147 ID 0x6b5ca155 dest 3:
11 Mon Jan 25 23:30:17 2016
12 Primary database is in MAXIMUM PERFORMANCE mode
13 RFS[8]: Assigned to RFS process 14397
14 RFS[8]: No standby redo logfiles created for thread 1
15 RFS[8]: Opened log for thread 1 sequence 39 dbid 1800997619 branch 901820147
16 Media Recovery Log /u01/app/oracle/oradata/arch/1_38_901820147.dbf
17 Media Recovery Waiting for thread 1 sequence 39 (in transit)

有些参数可以提前设置好，这样可以减少切换的时间，比如，选择切换ORASTD1数据库，那么可以先停掉ORASTD2数据库，并修改相关的参数，从ORAC2切换到ORASTD1后，直接起ORASTD2就可以了。

posted @ 2021-07-24 08:02  耀阳居士  阅读(637)  评论(0)  编辑  收藏  举报