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。
以下是主库的几个主要参数的配置信息：
02 |
------------------------------------ ----------- ------------------------------ |
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
|
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备库的主要参数设置：
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
|
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备库的主要参数设置：
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
|
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; |
切换后，这个数据库已经备关闭，启动数据库，这个数据库已经变为备库，启动MRP进程。
02 |
ORACLE instance started. |
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 |
12 |
SQL> alter database recover managed standby database using current logfile disconnect from session; |
16 |
SQL> select open_mode from v$database; |
22 |
SQL> select database_role,switchover_status from v$database; |
24 |
DATABASE_ROLE SWITCHOVER_STATUS |
25 |
---------------- -------------------- |
26 |
PHYSICAL STANDBY TO PRIMARY |
将ORAC2数据库的FAL修为为ORASTD1。
1 |
SQL> alter system set fal_server=ORASTD1; |
主库切换后，两个备库均变为TO PRIMARY状态，这时就要选择切换哪个备库为主库了。
1 |
SQL> select database_role,switchover_status from v$database; |
3 |
DATABASE_ROLE SWITCHOVER_STATUS |
4 |
---------------- -------------------- |
5 |
PHYSICAL STANDBY TO PRIMARY |
这里选择ORASTD1这个备库切换为主库。
ORASTD1：
1 |
SQL> alter database commit to switchover to primary; |
切换后数据库变为MOUNT状态，打开数据库。
01 |
SQL> select open_mode from v$database; |
07 |
SQL> alter database open; |
11 |
SQL> select database_role,switchover_status from v$database; |
13 |
DATABASE_ROLE SWITCHOVER_STATUS |
14 |
---------------- -------------------- |
因为之前ORASTD1这个备库的log_archive_config参数并没有添加ORASTD2这个备库，现在变成了主库，需要添加上所有的数据库。
1 |
SQL> alter system set log_archive_config='dg_config=(orac2,orastd1,orastd2)'; |
切换日志发现，日志并没有传到备库(ORAC2)。
1 |
SQL> alter system switch logfile; |
修改相关的log_archive_dest_state参数，日志可以成功发送到备库(ORAC2)，备库(ORAC2)也可以正常应用，备库(ORAC2)开始和主库(ORASTD1)同步数据。
1 |
SQL> alter system set log_archive_dest_state_2=enable; |
因为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'; |
将当前主库添加了归档到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'; |
ORASTD2的FAL信息从ORAC2修改为ORASTD1。
1 |
SQL> alter system set fal_server=ORASTD1; |
此时整个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 |
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 |
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 |
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 |
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 |
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就可以了。