今天主要介绍使用DG_broker工具管理切换DG主备。
1、设置primary和standby启动时参数文件为spfile
SQL> show parameter spfile;
2、设置DG_BROKER_START为TRUE
将主备库的dg_broker_start的VALUE设置为true。
SQL>alter system set dg_broker_start=true scope=both;
SQL>! ps -ef|grep dmon
SQL>show parameter dg_broker_start;
SQL>SELECT d.DBID,
d.DB_UNIQUE_NAME,
d.FORCE_LOGGING,
d.FLASHBACK_ON,
d.FS_FAILOVER_STATUS,
d.FS_FAILOVER_CURRENT_TARGET,
d.FS_FAILOVER_THRESHOLD,
d.FS_FAILOVER_OBSERVER_PRESENT,
d.FS_FAILOVER_OBSERVER_HOST
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主备库保持一致。
主库:
[oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbynode)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=orcl11gdg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl11g)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl11gdg_DGMGRL.lhr.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl11g)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
LOGGING_LISTENER = OFF
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[oracle@standbynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
tnsorcl11g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)
tnsorcl11gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)
tns_orcl11g_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g_DGMGRL.lhr.com)
)
)
tns_orcl11gstandby_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)
)
备库:
[oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = primarynode)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=orcl11g_DGMGRL.lhr.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl11g)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@primarynode ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl11g)
)
)
tnsorcl11gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl11g)
)
)
tns_orcl11g_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.249)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g_DGMGRL.lhr.com)
)
)
tns_orcl11gstandby_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.250)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11gdg_DGMGRL.lhr.com)
)
)
4、dgmgrl中配置broker并启用
[oracle@primarynode ~]$ dgmgrl
DGMGRL> connect sys/password@tns_orcl11gdg_DGMGRL
DGMGRL> show configuration
#如果之前有配置,请先移除
DGMGRL> REMOVE CONFIGURATION;
--查看参数文件的状态
DGMGRL> SHOW CONFIGURATION;
#创建新的参数文件CONFIGURATION:
DGMGRL> create configuration 'fsf_orcl11g_lhr' as primary database is 'orcl11gdg' connect identifier is tns_orcl11gstandby_DGMGRL;
--添加备库:
DGMGRL> add database 'orcl11g' as connect identifier is tns_orcl11g_DGMGRL maintained as physical;
--启用参数文件:
DGMGRL> ENABLE CONFIGURATION;
--查看参数文件的状态
DGMGRL> SHOW CONFIGURATION;
--启用备库
DGMGRL> ENABLE DATABASE 'orcl11g'
5、切换主备库
DGMGRL> switchover to orcl11g
6、验证主备是否切换成功
主备库都执行
SQL> set lines 999
SQL> select database_role,open_mode from v$database;
在上图中我们可以看到主备已经完成切换。当然也可以切换保护模式,大家自行测试,这里我再不演示。
重新调至最大可用模式:
DGMGRL> EDIT DATABASE 'orcl11g' SET PROPERTY 'LogXptMode'='ASYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
遇到oracle DGMGRL ORA-16603报错的解决方法(DG Broker)
备库执行:
[oracle@primarynode dbs]$cd $ORACLE_HOME/dbs
[oracle@primarynode dbs]$rm -rf dr*
SQL> alter system set dg_broker_start=false;
SQL> alter system set dg_broker_start=true;
SQL> show parameter dg_broker
参考链接:
https://blog.csdn.net/tuning_optmization/article/details/79235281