ORACLE数据库一主多备DG环境(一对多)主备切换
Mar282016
本文主要分享一对多(一主多备)的DG环境主备切换的案例，我们的一套生产环境，一主四备，其中3个备库承载着查询业务，还有一个DG没有任务业务，由于主库的存储性能不给力，公司采购了新的存储，这个DG就是为了切换到新存储而搭建的，数据量1.5TB。
本文是在测试环境操作，之前并没有切换过一对多的DG环境，而且网上基本没有关于一对多DG环境切换的资料，官方文档也只是说自己选择切换到哪个DG，也没有详细的介绍和案例。本文测试环境为一主两备，版本为11.2.0.4.0。
以下是主库的几个主要参数的配置信息：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string
fal_server string
log_archive_config string dg_config=(orac2,orastd1,orastd2)
log_archive_dest_1 string location=/u01/app/oracle/orada
ta/arch valid_for=(all_logfile
s,all_roles) db_unique_name=or
ac2
log_archive_dest_2 string service=orastd1 lgwr async val
id_for=(online_logfiles,primar
y_role) db_unique_name=orastd1
log_archive_dest_3 string service=orastd2 lgwr async val
id_for=(online_logfiles,primar
y_role) db_unique_name=orastd2
以下是ORASTD1备库的主要参数设置：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(orac2,orastd1)
og_archive_dest_1 string location=/u01/app/oracle/orada
ta/arch valid_for=(all_logfile
s,all_roles) db_unique_name=or
astd1
og_archive_dest_2 string service=orac2 lgwr async valid
_for=(online_logfiles,primary_
role) db_unique_name=orac2
fal_client string ORASTD1
fal_server string ORAC2
以下是ORASTD2备库的主要参数设置：
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string ORASTD2
fal_server string ORAC2
log_archive_config string dg_config=(orac2,orastd2)
log_archive_dest_1 string location=/u01/app/oracle/orada
ta/arch valid_for=(all_logfile
s,all_roles) db_unique_name=or
astd2
log_archive_dest_2 string service=orac2 lgwr async valid
_for=(online_logfiles,primary_
role) db_unique_name=orac2
在DG切换之前，一定要检查当前环境是否可以切换，我在做这个测试的时候，因为是刚刚搭建的测试环境，而且搭建时已经测试，所以在切换之前没有检查。我之前就做过类似的案例，差点死的很惨，这里简单说一嘴，那是航天系统的一套数据库，当时负责人找我去做切换测试，他们的DBA已经离职，离职前写了一个切换方案，我到客户机房的时候，客户给我看了下那个方案，很简单的方案，只有切换的命令，并没有检查的相关信息，我看完方案后，直接提出方案不完成，切换之前需要检查，因为我是第一次接触这个数据库，操作之前必须仔细检查，万一掉坑里怎么办？我在检查时发现，备库和主库之间差了半年多的延迟，而且备库并没有接收到这半年来的归档，检查发现，备库在半年之前，主机重启，并没人起备库的监听和数据库，导致主库的日志传不到备库，经检查发现备库少了将近三千多个归档，短时间很难追得上，还好那个数据库只有20个GB，我直接选择重新搭建备库了，然后顺利的进行了主备切换。
在切换时，在主库上是不可以选择我要切换到哪个备库的，这个选择是在备库上选择的，下面进行SWITCHOVER切换。
主库(ORAC2)：
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
切换后，这个数据库已经备关闭，启动数据库，这个数据库已经变为备库，启动MRP进程。
SQL> startup ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2257840 bytes Variable Size 528485456 bytes Database Buffers 301989888 bytes Redo Buffers 2371584 bytes Database mounted. Database opened. SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY TO PRIMARY
将ORAC2数据库的FAL修为为ORASTD1。
SQL> alter system set fal_server=ORASTD1; System altered.
主库切换后，两个备库均变为TO PRIMARY状态，这时就要选择切换哪个备库为主库了。
SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PHYSICAL STANDBY TO PRIMARY
这里选择ORASTD1这个备库切换为主库。
ORASTD1：
SQL> alter database commit to switchover to primary; Database altered.
切换后数据库变为MOUNT状态，打开数据库。
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open; Database altered. SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS ---------------- -------------------- PRIMARY NOT ALLOWED
因为之前ORASTD1这个备库的log_archive_config参数并没有添加ORASTD2这个备库，现在变成了主库，需要添加上所有的数据库。
SQL> alter system set log_archive_config='dg_config=(orac2,orastd1,orastd2)'; System altered.
切换日志发现，日志并没有传到备库(ORAC2)。
SQL> alter system switch logfile; System altered.
修改相关的log_archive_dest_state参数，日志可以成功发送到备库(ORAC2)，备库(ORAC2)也可以正常应用，备库(ORAC2)开始和主库(ORASTD1)同步数据。
SQL> alter system set log_archive_dest_state_2=enable; System altered.
因为ORASTD2这个数据库之前的主是ORAC2，现在已经变成了备库，需要把ORASTD2的主改成ORASTD1。因为ORASTD1并没有配ORASTD2的参数，需要把ORASTD2添加进来。
SQL> ALTER SYSTEM SET log_archive_dest_3='service=orastd2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orastd2'; System altered.
将当前主库添加了归档到ORASTD2后，还需要将ORASTD2的远程归档信息改为ORASTD1。
ORASTD2:
SQL> ALTER SYSTEM SET log_archive_dest_2='service=orastd1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orastd1'; System altered.
ORASTD2的FAL信息从ORAC2修改为ORASTD1。
SQL> alter system set fal_server=ORASTD1; System altered.
此时整个DG切换就已经完成，ORASTD2数据库无需做其他操作，MRP进程也不需要重新启动，此时主库(ORASTD1)切换日志，会成功发送到所有的备库(ORAC2，ORASTD2)，两个备库会直接应用。
ORAC2：
Completed: alter database recover managed standby database using current logfile disconnect from session Clearing online redo logfile 3 complete Media Recovery Waiting for thread 1 sequence 31 Mon Jan 25 17:49:54 2016 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/oradata/arch RFS[1]: Assigned to RFS process 17703 RFS[1]: Opened log for thread 1 sequence 32 dbid 1800997619 branch 901820147 Archived Log entry 24 added for thread 1 sequence 32 rlc 901820147 ID 0x6b5ca155 dest 2: RFS[1]: Opened log for thread 1 sequence 33 dbid 1800997619 branch 901820147 Archived Log entry 25 added for thread 1 sequence 33 rlc 901820147 ID 0x6b5ca155 dest 2: Mon Jan 25 17:49:54 2016 RFS[2]: Assigned to RFS process 17705 RFS[2]: Opened log for thread 1 sequence 31 dbid 1800997619 branch 901820147 RFS[1]: Opened log for thread 1 sequence 34 dbid 1800997619 branch 901820147 Archived Log entry 26 added for thread 1 sequence 34 rlc 901820147 ID 0x6b5ca155 dest 2: Archived Log entry 27 added for thread 1 sequence 31 rlc 901820147 ID 0x6b5ca155 dest 2: RFS[2]: Selected log 10 for thread 1 sequence 35 dbid 1800997619 branch 901820147 Mon Jan 25 17:49:54 2016 Archived Log entry 28 added for thread 1 sequence 35 ID 0x6b5ca155 dest 1: Mon Jan 25 17:49:55 2016 Primary database is in MAXIMUM PERFORMANCE mode RFS[3]: Assigned to RFS process 17707 RFS[3]: Selected log 10 for thread 1 sequence 36 dbid 1800997619 branch 901820147 Mon Jan 25 17:49:55 2016 Media Recovery Log /u01/app/oracle/oradata/arch/1_31_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_32_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_33_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_34_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_35_901820147.dbf Media Recovery Waiting for thread 1 sequence 36 (in transit) Recovery of Online Redo Log: Thread 1 Group 10 Seq 36 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/orac2/st01.log Mon Jan 25 17:50:19 2016 Expanded controlfile section 11 from 28 to 280 records Requested to grow by 252 records; added 9 blocks of records Archived Log entry 29 added for thread 1 sequence 36 ID 0x6b5ca155 dest 1: Mon Jan 25 17:50:19 2016 Primary database is in MAXIMUM PERFORMANCE mode Mon Jan 25 17:50:19 2016 Media Recovery Waiting for thread 1 sequence 37 RFS[4]: Assigned to RFS process 17713 RFS[4]: Selected log 10 for thread 1 sequence 37 dbid 1800997619 branch 901820147 Recovery of Online Redo Log: Thread 1 Group 10 Seq 37 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/orac2/st01.log Mon Jan 25 17:51:03 2016 ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH; Mon Jan 25 17:53:46 2016 Archived Log entry 30 added for thread 1 sequence 37 ID 0x6b5ca155 dest 1: Mon Jan 25 17:53:46 2016 RFS[4]: Selected log 10 for thread 1 sequence 38 dbid 1800997619 branch 901820147 Mon Jan 25 17:53:46 2016 Media Recovery Waiting for thread 1 sequence 38 (in transit) Recovery of Online Redo Log: Thread 1 Group 10 Seq 38 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/orac2/st01.log RFS[4]: Selected log 11 for thread 1 sequence 39 dbid 1800997619 branch 901820147 Mon Jan 25 17:53:49 2016 Archived Log entry 31 added for thread 1 sequence 38 ID 0x6b5ca155 dest 1: Media Recovery Waiting for thread 1 sequence 39 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 39 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/orac2/st02.log
ORASTD2:
Media Recovery Log /u01/app/oracle/oradata/arch/1_31_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_32_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_33_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_34_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_35_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_36_901820147.dbf Media Recovery Log /u01/app/oracle/oradata/arch/1_37_901820147.dbf Media Recovery Waiting for thread 1 sequence 38 RFS[6]: Opened log for thread 1 sequence 38 dbid 1800997619 branch 901820147 Archived Log entry 14 added for thread 1 sequence 38 rlc 901820147 ID 0x6b5ca155 dest 3: Mon Jan 25 23:30:17 2016 Primary database is in MAXIMUM PERFORMANCE mode RFS[8]: Assigned to RFS process 14397 RFS[8]: No standby redo logfiles created for thread 1 RFS[8]: Opened log for thread 1 sequence 39 dbid 1800997619 branch 901820147 Media Recovery Log /u01/app/oracle/oradata/arch/1_38_901820147.dbf Media Recovery Waiting for thread 1 sequence 39 (in transit)
有些参数可以提前设置好，这样可以减少切换的时间，比如，选择切换ORASTD1数据库，那么可以先停掉ORASTD2数据库，并修改相关的参数，从ORAC2切换到ORASTD1后，直接起ORASTD2就可以了。
ORACLE数据库一主多备DG环境(一对多)主备切换：等您坐沙发呢！
发表评论
