oracle 10GR2配置data guard broker快速参考:
Data Guard broker是建立在Data Guard基础上的一个对Data Guard配置，集中管理操作的一个平台。
这里借设data guard已经在运行了，希望通过配置data guard broker后，可以用DGMGRL管理切换等操作
环境介绍：
OS:RHEL 5.4 64bit
数据库版本:oracle 10.2.0.5
一个主库和一个物理备库位于同一台机器
主库sid:kgbdb5 备库sid:kgbdb5dg
OS:RHEL 5.4 64bit
数据库版本:oracle 10.2.0.5
一个主库和一个物理备库位于同一台机器
主库sid:kgbdb5 备库sid:kgbdb5dg
作者：george.ma blog:http://blog.chinaunix.net/u/12521/
配置过程：
1.备库和主库都要起用spfile，如何起用spfile就不用介绍了吧
2.设置local_listener，实例动态注册
如果你使用的不是默认的1521端口，请通过配置LOCAL_LISTENER参数，确保实例可以在监听上动态注册service
如我的备库监听上用的是1522端口，配置如下：
SQL> show parameter local_listener
1.备库和主库都要起用spfile，如何起用spfile就不用介绍了吧
2.设置local_listener，实例动态注册
如果你使用的不是默认的1521端口，请通过配置LOCAL_LISTENER参数，确保实例可以在监听上动态注册service
如我的备库监听上用的是1522端口，配置如下：
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_STD
在tnsname.ora包括了LISTENER_STD的信息
LISTENER_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
当然也可以直接用alter system set local_listener='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522)))指定，这样可以不用在tnsnames.ora文件中配置listener_std的信息
3. listener.ora监听文件配置
DGMGRL执行某些操作时，需要重启数据库，为了保证数据库实例关闭后，DGMGRL还能连接实例去起库，所以要添加静态注册.这里GLOBAL_DBNAME必须指定为db_unique_name_DGMGRL.db_domain
举例:
LISTENER = (DESCRIPTION = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
在这次的实际环境中，listener.ora文件如下：
#cat listener.ora
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_STD
在tnsname.ora包括了LISTENER_STD的信息
LISTENER_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
当然也可以直接用alter system set local_listener='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522)))指定，这样可以不用在tnsnames.ora文件中配置listener_std的信息
3. listener.ora监听文件配置
DGMGRL执行某些操作时，需要重启数据库，为了保证数据库实例关闭后，DGMGRL还能连接实例去起库，所以要添加静态注册.这里GLOBAL_DBNAME必须指定为db_unique_name_DGMGRL.db_domain
举例:
LISTENER = (DESCRIPTION = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
在这次的实际环境中，listener.ora文件如下：
#cat listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5)
(GLOBAL_DBNAME=kgbdb5_DGMGRL)
)
)
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5)
(GLOBAL_DBNAME=kgbdb5_DGMGRL)
)
)
CONNECT_TIMEOUT_LISTENER = 0
#以上为主库监听，其中GLOBAL_DBNAME=kgbdb5_DGMGRL格式为GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因为实际环境中db_domain为空
#以上为主库监听，其中GLOBAL_DBNAME=kgbdb5_DGMGRL格式为GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因为实际环境中db_domain为空
LISTENER_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
SID_LIST_LISTENER_STD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5dg)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5dg)
(GLOBAL_DBNAME=kgbdb5dg_DGMGRL)
)
)
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5dg)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/productl/1020)
(SID_NAME = kgbdb5dg)
(GLOBAL_DBNAME=kgbdb5dg_DGMGRL)
)
)
CONNECT_TIMEOUT_LISTENER_STD = 0
#以上为备库监听，其中GLOBAL_DBNAME=kgbdb5dg_DGMGRL格式为GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因为实际环境中db_domain为空
#以上为备库监听，其中GLOBAL_DBNAME=kgbdb5dg_DGMGRL格式为GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因为实际环境中db_domain为空
作者：george.ma blog:http://blog.chinaunix.net/u/12521/
4.tnsnames.ora配置
这个文件配置没有很严格的要求，只是在DGMGRL>里添加configuration里要用到这里的连接串去连接数据库，实际环境如下：
$ cat tnsnames.ora
kgbdb5_pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kgbdb5)
)
)
这个文件配置没有很严格的要求，只是在DGMGRL>里添加configuration里要用到这里的连接串去连接数据库，实际环境如下：
$ cat tnsnames.ora
kgbdb5_pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kgbdb5)
)
)
kgbdb5_dg1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kgbdb5dg)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = kgbdb5dg)
)
)
LISTENER_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost1)(PORT = 1522))
)
5.重起监听，观察监听配置是否正确
[oracle@kgbdb5 admin]$ lsnrctl status
[oracle@kgbdb5 admin]$ lsnrctl status
如果配置正确，应该看到<db_unique_name>_DGMGRL和<db_unique_name>_XPT这样的service，等数据库参数dg_broker_start=true设置后，还会有<db_unique_name>_DGR这样的service
如：
如：
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 19-SEP-2010 13:58:07
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 19-SEP-2010 11:51:20
Uptime 0 days 2 hr. 6 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/productl/1020/network/admin/listener.ora
Listener Log File /u01/oracle/productl/1020/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1)(PORT=1521)))
Services Summary...
Service "kgbdb5" has 2 instance(s).
Instance "kgbdb5", status UNKNOWN, has 1 handler(s) for this service...
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "kgbdb5_DGB" has 1 instance(s).
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "kgbdb5_DGMGRL" has 1 instance(s).
Instance "kgbdb5", status UNKNOWN, has 1 handler(s) for this service...
Service "kgbdb5_XPT" has 1 instance(s).
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "kgbdb5", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@kgbdb5 admin]$ lsnrctl status listener_std
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 19-SEP-2010 11:51:20
Uptime 0 days 2 hr. 6 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/productl/1020/network/admin/listener.ora
Listener Log File /u01/oracle/productl/1020/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1)(PORT=1521)))
Services Summary...
Service "kgbdb5" has 2 instance(s).
Instance "kgbdb5", status UNKNOWN, has 1 handler(s) for this service...
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "kgbdb5_DGB" has 1 instance(s).
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "kgbdb5_DGMGRL" has 1 instance(s).
Instance "kgbdb5", status UNKNOWN, has 1 handler(s) for this service...
Service "kgbdb5_XPT" has 1 instance(s).
Instance "kgbdb5", status READY, has 2 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "kgbdb5", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@kgbdb5 admin]$ lsnrctl status listener_std
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 19-SEP-2010 13:58:26
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener_std
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 19-SEP-2010 11:50:22
Uptime 0 days 2 hr. 8 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/productl/1020/network/admin/listener.ora
Listener Log File /u01/oracle/productl/1020/network/log/listener_std.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1)(PORT=1522)))
Services Summary...
Service "kgbdb5dg" has 2 instance(s).
Instance "kgbdb5dg", status UNKNOWN, has 1 handler(s) for this service...
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
Service "kgbdb5dg_DGB" has 1 instance(s).
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
Service "kgbdb5dg_DGMGRL" has 1 instance(s).
Instance "kgbdb5dg", status UNKNOWN, has 1 handler(s) for this service...
Service "kgbdb5dg_XPT" has 1 instance(s).
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
The command completed successfully
6.设置dg_broker_start为TRUE
a.在主备库上分别执行：alter system set dg_broker_start=true scope=both;
b.然后查看下结果，先看进程是否起来了，可以从alert.log里看到数据库多出几个进程
Starting Data Guard Broker (DMON)
NSV0 started with pid=16, OS id=10685
INSV started with pid=21, OS id=10776
c.可以看到主备库的dmon进程都起来了，接下来看看service name _DGB有没有被正确的注册上
lsnrctl service <listener_name>
STATUS of the LISTENER
------------------------
Alias listener_std
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 19-SEP-2010 11:50:22
Uptime 0 days 2 hr. 8 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/productl/1020/network/admin/listener.ora
Listener Log File /u01/oracle/productl/1020/network/log/listener_std.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1)(PORT=1522)))
Services Summary...
Service "kgbdb5dg" has 2 instance(s).
Instance "kgbdb5dg", status UNKNOWN, has 1 handler(s) for this service...
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
Service "kgbdb5dg_DGB" has 1 instance(s).
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
Service "kgbdb5dg_DGMGRL" has 1 instance(s).
Instance "kgbdb5dg", status UNKNOWN, has 1 handler(s) for this service...
Service "kgbdb5dg_XPT" has 1 instance(s).
Instance "kgbdb5dg", status READY, has 1 handler(s) for this service...
The command completed successfully
6.设置dg_broker_start为TRUE
a.在主备库上分别执行：alter system set dg_broker_start=true scope=both;
b.然后查看下结果，先看进程是否起来了，可以从alert.log里看到数据库多出几个进程
Starting Data Guard Broker (DMON)
NSV0 started with pid=16, OS id=10685
INSV started with pid=21, OS id=10776
c.可以看到主备库的dmon进程都起来了，接下来看看service name _DGB有没有被正确的注册上
lsnrctl service <listener_name>
7.配置broker
首先使用DGMGRL连接到primary机器上
[oracle@kgbdb5 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.5.0 - 64bit Production
首先使用DGMGRL连接到primary机器上
[oracle@kgbdb5 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.5.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/abc123@kgbdb5_pri
Connected.
DGMGRL> help create
DGMGRL> connect sys/abc123@kgbdb5_pri
Connected.
DGMGRL> help create
Create a broker configuration
Syntax:
CREATE CONFIGURATION <configuration name> AS
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
DGMGRL>
DGMGRL> create configuration kgbdb5_config as primary database is kgbdb5 connect identifier is kgbdg5_pri; #创建configuration，这里指定主库信息
DGMGRL> add database kgbdb5dg as connect identifier is kgbdb5_dg1 maintained as physical; #给configuration增加备库
DGMGRL> show configuration #显示configuration
DGMGRL> create configuration kgbdb5_config as primary database is kgbdb5 connect identifier is kgbdg5_pri; #创建configuration，这里指定主库信息
DGMGRL> add database kgbdb5dg as connect identifier is kgbdb5_dg1 maintained as physical; #给configuration增加备库
DGMGRL> show configuration #显示configuration
Configuration
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Primary database
kgbdb5dg - Physical standby database
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Primary database
kgbdb5dg - Physical standby database
Current status for "test":
SUCCESS
SUCCESS
DGMGRL>
DGMGRL> enable configuration #起用configuration，一个configuration配置完后，要用enable configuration使其生效才可使用，默认为disable
Enabled.
DGMGRL> enable configuration #起用configuration，一个configuration配置完后，要用enable configuration使其生效才可使用，默认为disable
Enabled.
8.用DGMGRL切换主备库
作者：george.ma blog:http://blog.chinaunix.net/u/12521/
DGMGRL> show configuration
DGMGRL> show configuration
Configuration
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Primary database #这里显示现在主库为kgbdb5
kgbdb5dg - Physical standby database
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Primary database #这里显示现在主库为kgbdb5
kgbdb5dg - Physical standby database
Current status for "test":
SUCCESS
SUCCESS
DGMGRL>
DGMGRL> switchover to kgbdb5dg #发生切换命令
Performing switchover NOW, please wait...
Operation requires shutdown of instance "kgbdb5" on database "kgbdb5"
Shutting down instance "kgbdb5"...
ORA-01109: database not open
DGMGRL> switchover to kgbdb5dg #发生切换命令
Performing switchover NOW, please wait...
Operation requires shutdown of instance "kgbdb5" on database "kgbdb5"
Shutting down instance "kgbdb5"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "kgbdb5dg" on database "kgbdb5dg"
Shutting down instance "kgbdb5dg"...
ORA-01109: database not open
ORACLE instance shut down.
Operation requires shutdown of instance "kgbdb5dg" on database "kgbdb5dg"
Shutting down instance "kgbdb5dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "kgbdb5" on database "kgbdb5"
Starting instance "kgbdb5"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "kgbdb5dg" on database "kgbdb5dg"
Starting instance "kgbdb5dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "kgbdb5dg" #显示切换完成
DGMGRL>
DGMGRL> show configuration
ORACLE instance shut down.
Operation requires startup of instance "kgbdb5" on database "kgbdb5"
Starting instance "kgbdb5"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "kgbdb5dg" on database "kgbdb5dg"
Starting instance "kgbdb5dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "kgbdb5dg" #显示切换完成
DGMGRL>
DGMGRL> show configuration
Configuration
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Physical standby database #这里kdbdb5已经变成备库
kgbdb5dg - Primary database
Name: test
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
kgbdb5 - Physical standby database #这里kdbdb5已经变成备库
kgbdb5dg - Primary database
Current status for "test":
SUCCESS
SUCCESS
DGMGRL>
发生switchover to切换命令后，注意观察主备库的alert.log信息，正常情况下经过停止启动后就可完成主备库切换，如果发现数据库停止后并没有启动，DGMGRL>显示manual信息，你可以把主备库都用starup mount起到mount状态后，DGMGRL会继续完成切换
9.碰到的几个错误：
a.在DGMGRL做切换时报ORA-12521错误，数据库shutdown后就没有起来，需要手工去做startup mount操作
解决：如果出现ORA-12521或ORA-12514错误, 说明监听配置中未含GLOBAL_DBNAME=<db_unique_name>_DGMGRL.<db_domain>项，或者配置不正确，请仔细检查listener.ora文件
b.DGMGRL做切换时报ORA-01031: insufficient privileges
主备库建库时关键字都为小写. 而在"主备库新增静态监听"时, 配置的静态监听服务<db_unique_name>_DGMGRL.<db_domain>时却设置成了大写, 实际上此处<db_unique_name>和<db_domain>大小写需和数据库的初始化参数一致才行
a.在DGMGRL做切换时报ORA-12521错误，数据库shutdown后就没有起来，需要手工去做startup mount操作
解决：如果出现ORA-12521或ORA-12514错误, 说明监听配置中未含GLOBAL_DBNAME=<db_unique_name>_DGMGRL.<db_domain>项，或者配置不正确，请仔细检查listener.ora文件
b.DGMGRL做切换时报ORA-01031: insufficient privileges
主备库建库时关键字都为小写. 而在"主备库新增静态监听"时, 配置的静态监听服务<db_unique_name>_DGMGRL.<db_domain>时却设置成了大写, 实际上此处<db_unique_name>和<db_domain>大小写需和数据库的初始化参数一致才行
作者：george.ma blog:http://blog.chinaunix.net/u/12521/