Oracle DataGuard主备切换之自动切换

今天主要介绍使用DG_broker工具管理切换DG主备。

1、设置primary和standby启动时参数文件为spfile

1. SQL>  show parameter spfile;
   

2、设置DG_BROKER_START为TRUE

将主备库的dg_broker_start的VALUE设置为true。

1. SQL>alter system set dg_broker_start=true scope=both;
   
2. SQL>! ps -ef|grep dmon
   
3. SQL>show parameter dg_broker_start；
   
4. SQL>SELECT d.DBID,
   
5. d.DB_UNIQUE_NAME,
   
6. d.FORCE_LOGGING,
   
7. d.FLASHBACK_ON,
   
8. d.FS_FAILOVER_STATUS,
   
9. d.FS_FAILOVER_CURRENT_TARGET,
   
10. d.FS_FAILOVER_THRESHOLD,
    
11. d.FS_FAILOVER_OBSERVER_PRESENT,
    
12. d.FS_FAILOVER_OBSERVER_HOST
    
13. FROM v$database d;
    

3、配置监听

需要在listener.ora静态添加一个service_name为db_unique_name_DGMGRL.db_domain的注册。这个service_name会在DGMGRL重启数据库的时候用到。通过DGMGRL重启数据库时DMON进程会先将数据库关闭，然后DGMGRL用此service_name通过listener连接到数据库，发出启动命令。tnsnames.ora主备库保持一致。

主库：

1. [oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
   
2. 
   
3. # listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
   
4. # Generated by Oracle configuration tools.
   
5. 
   
6. LISTENER =
   
7.  (DESCRIPTION_LIST =
   
8.    (DESCRIPTION =
   
9.      (ADDRESS_LIST =
   
10.        (ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
    
11.      )
    
12.    )
    
13.  )
    
14. 
    
15. SID_LIST_LISTENER =
    
16.  (SID_LIST =
    
17.    (SID_DESC =
    
18.      (GLOBAL_DBNAME=orcl11gdg)
    
19.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    
20.      (SID_NAME = orcl11g)
    
21.    )
    
22.    (SID_DESC =
    
23.      (GLOBAL_DBNAME = orcl11gdg_DGMGRL.lhr.com)
    
24.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    
25.      (SID_NAME = orcl11g)
    
26.    )
    
27.  )
    
28. 
    
29. ADR_BASE_LISTENER = /u01/app/oracle
    
30. LOGGING_LISTENER = OFF
    
31. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
    

1. [oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
   
2. 
   
3. tnsorcl11g =
   
4.  (DESCRIPTION =
   
5.    (ADDRESS_LIST =
   
6.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
   
7.    )
   
8.    (CONNECT_DATA =
   
9.      (SERVER = DEDICATED)
   
10.      (SERVICE_NAME = orcl11g)
    
11.    )
    
12.  )
    
13. 
    
14. tnsorcl11gstandby =
    
15.  (DESCRIPTION =
    
16.    (ADDRESS_LIST =
    
17.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
    
18.    )
    
19.    (CONNECT_DATA =
    
20.      (SERVER = DEDICATED)
    
21.      (SERVICE_NAME = orcl11g)
    
22.    )
    
23.  )
    
24. tns_orcl11g_DGMGRL =
    
25. 
    
26.  (DESCRIPTION =
    
27. 
    
28.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
    
29. 
    
30.    (CONNECT_DATA =
    
31. 
    
32.      (SERVER = DEDICATED)
    
33. 
    
34.      (SERVICE_NAME = orcl11g_DGMGRL.lhr.com)
    
35. 
    
36.    )
    
37. 
    
38.  )
    
39. tns_orcl11gstandby_DGMGRL =
    
40. 
    
41.  (DESCRIPTION =
    
42. 
    
43.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
    
44. 
    
45.    (CONNECT_DATA =
    
46. 
    
47.      (SERVER = DEDICATED)
    
48. 
    
49.      (SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)
    
50. 
    
51.    )
    

备库：

1. [oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
   
2. 
   
3. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
   
4. # Generated by Oracle configuration tools.
   
5. 
   
6. LISTENER =
   
7.  (DESCRIPTION_LIST =
   
8.    (DESCRIPTION =
   
9.      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   
10.      (ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
    
11.    )
    
12.  )
    
13. SID_LIST_LISTENER =
    
14.  (SID_LIST =
    
15.    (SID_DESC =
    
16.      (GLOBAL_DBNAME=orcl11g_DGMGRL.lhr.com)
    
17.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    
18.      (SID_NAME = orcl11g)
    
19.    )
    
20.  )
    
21. ADR_BASE_LISTENER = /u01/app/oracle
    

1. [oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
   
2. 
   
3.  (DESCRIPTION =
   
4.    (ADDRESS_LIST =
   
5.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
   
6.    )
   
7.    (CONNECT_DATA =
   
8.      (SERVICE_NAME = orcl11g)
   
9.    )
   
10.  )
    
11. tnsorcl11gstandby =
    
12.  (DESCRIPTION =
    
13.    (ADDRESS_LIST =
    
14.      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
    
15.    )
    
16.    (CONNECT_DATA =
    
17.      (SERVICE_NAME = orcl11g)
    
18.    )
    
19.  )
    
20. tns_orcl11g_DGMGRL =
    
21.  (DESCRIPTION =
    
22.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
    
23.    (CONNECT_DATA =
    
24.      (SERVER = DEDICATED)
    
25.      (SERVICE_NAME = orcl11g_DGMGRL.lhr.com)
    
26.    )
    
27.  )
    
28. tns_orcl11gstandby_DGMGRL =
    
29.  (DESCRIPTION =
    
30.    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
    
31.    (CONNECT_DATA =
    
32.      (SERVER = DEDICATED)
    
33.      (SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)
    
34.    )
    
35. 
    
36.  )
    

4、dgmgrl中配置broker并启用

1. [oracle@primarynode ~]$ dgmgrl
   
2. DGMGRL> connect sys/password@tns_orcl11gdg_DGMGRL
   
3. DGMGRL> show configuration
   

1. #如果之前有配置，请先移除
   
2. DGMGRL> REMOVE CONFIGURATION;
   
3. --查看参数文件的状态
   
4. DGMGRL> SHOW CONFIGURATION;
   
5. #创建新的参数文件CONFIGURATION：
   
6. DGMGRL> create configuration 'fsf_orcl11g_lhr' as primary database is 'orcl11gdg' connect identifier is tns_orcl11gstandby_DGMGRL;
   
7. --添加备库：
   
8. DGMGRL> add database 'orcl11g' as connect identifier is tns_orcl11g_DGMGRL maintained as physical;
   
9. --启用参数文件：
   
10. DGMGRL> ENABLE CONFIGURATION;
    
11. --查看参数文件的状态
    
12. DGMGRL> SHOW CONFIGURATION;
    
13. --启用备库
    
14. DGMGRL> ENABLE DATABASE 'orcl11g'
    

5、切换主备库

1. DGMGRL> switchover to orcl11g
   

6、验证主备是否切换成功

主备库都执行

1. SQL> set lines 999
   
2. SQL> select database_role,open_mode from v$database;
   

在上图中我们可以看到主备已经完成切换。当然也可以切换保护模式，大家自行测试，这里我再不演示。

重新调至最大可用模式：

1. DGMGRL> EDIT DATABASE 'orcl11g' SET PROPERTY 'LogXptMode'='ASYNC';
   
2. DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
   

遇到oracle DGMGRL ORA-16603报错的解决方法(DG Broker)

备库执行：

1. [oracle@primarynode dbs]$cd $ORACLE_HOME/dbs
   
2. [oracle@primarynode dbs]$rm -rf dr*
   
3. SQL> alter system set dg_broker_start=false;
   
4. SQL> alter system set dg_broker_start=true;
   
5. SQL> show parameter dg_broker
   

参考链接：

https://blog.csdn.net/tuning_optmization/article/details/79235281