配置dataguard broker
配置Dataguard的前提是你已经将Dataguard搭建完成了。DG_broker的作用在于,它不但自动化了 DataGuard 配置的创建、维护和监视,并对这些操作进行统一管理。还可以通过 Broker 的专用命令行界面 (DGMGRL) 执行所有管理操作。Data Guard Broker 11g 还可以使用最大可用性或最大性能模式将 Data Guard 配置为在数据库出现故障时自动切换。
DG_broker其实就是dgmgrl工具,安装数据库软件或数据库管理客户端都可以获取到这个工具。
Data Guard Broker
- 使用最大可用性或最佳性能模式针对配置启用自动数据库故障切换。
- 启用可配置事件来触发对目标备用数据库的即时自动切换。
- 改善了对重做传输选项的支持,使管理员可以为重做传输服务指定连接描述。
- 消除在最大可用性和最佳性能保护模式间更换的数据库停机时间。
- 支持使用 Oracle 集群件和冷故障切换集群针对高可用性配置单一实例数据库。
大体分为五部
(1)Tnsname检查
(2)添加静态监听
(3)修改参数
(4)创建broker configuration
(5)添加备库到broker configuration
primary_tns 主库的tns连接名
standby_tns 备库的tns连接名
<Primary db_unique_name> 主库的唯一名
<Standby db_unique_name> 备库的唯一名
<ORACLE_SID> 主备库的SID
(1)Tnsname检查
[oracle@host1 admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
primary_tns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1.us.oracle.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <Primary db_unique_name>.us.oracle.com)
)
)
standby_tns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <Standby db_unique_name>.us.oracle.com)
)
)
[oracle@host2 admin]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
primary_tns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1.us.oracle.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <Primary db_unique_name>.us.oracle.com)
)
)
standby_tns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <Standby db_unique_name>.us.oracle.com)
)
)
(2)添加静态监听
[oracle@host1 admin]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <Primary db_unique_name>.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = <ORACLE_SID>)
)
(SID_DESC =
(GLOBAL_DBNAME = <Primary db_unique_name> _DGMGRL.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = <ORACLE_SID>)
)
)
[oracle@host2 admin]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <Standby db_unique_name>.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = <ORACLE_SID>)
)
(SID_DESC =
(GLOBAL_DBNAME = <Standby db_unique_name> _DGMGRL.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = <ORACLE_SID>)
)
)
如果监听中为出现新的静态监听,可reload一下监听
[oracle@host1 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 22-JUN-2017 13:21:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)))
The command completed successfully
(3)修改参数
SYS@<ORACLE_SID> > show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1<Primary db_unique_name>.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2<Primary db_unique_name>.dat
dg_broker_start boolean FALSE
SYS@<ORACLE_SID> > alter system set dg_broker_start=true;
System altered.
日志内容
Thu Jun 22 11:26:09 2017
ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;
Thu Jun 22 11:26:09 2017
DMON started with pid=23, OS id=3383
Starting Data Guard Broker (DMON)
Thu Jun 22 11:26:12 2017
INSV started with pid=24, OS id=3385
Thu Jun 22 11:26:16 2017
NSV0 started with pid=31, OS id=3387
Thu Jun 22 11:26:19 2017
RSM0 started with pid=35, OS id=3390
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';
(4)创建broker configuration
[oracle@host1 ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
--通过help命令可以查看都可以进行哪些操作(此不可不做)
DGMGRL> help
The following commands are available:
add Adds a standby database to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a database, or fast-start failover
edit Edits a configuration, database, or instance
enable Enables a configuration, a database, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration, database, or instance
show Displays information about a configuration, database, or instance
shutdown Shuts down a currently running Oracle database instance
sql Executes a SQL statement
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database
Use "help <command>" to see syntax for individual commands
--用帮助查如何去创建一个configuration(此不可不做)
DGMGRL> help create
Creates a broker configuration
Syntax:
CREATE CONFIGURATION <configuration name> AS
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
--创建一个configuration并添加主库到broker,此步只能在主库的机器上操作。如用其他机器,需要@primary_tns
--其中 <Primary db_unique_name> 为主库的唯一名db_unique_name,primary_tns为连接到<Primary db_unique_name>的Tnsname
DGMGRL> CREATE CONFIGURATION <configuration_name> AS PRIMARY DATABASE IS <Primary db_unique_name> CONNECT IDENTIFIER IS primary_tns;
Configuration "<configuration_name>" created with primary database "<Primary db_unique_name>"
--启用配置
DGMGRL> enable configuration
Enabled.
--显示配置
--可以看到主库已经添加进去并SUCCESS(此不可不做)
DGMGRL> show configuration
Configuration - <configuration_name>
Protection Mode: MaxPerformance
Databases:
<Primary db_unique_name> - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
(5)添加备库到broker configuration
--用帮助查看如何添加一个备库(此不可不做)
DGMGRL> help add
Adds a standby database to the broker configuration
Syntax:
ADD DATABASE <database name>
[AS CONNECT IDENTIFIER IS <connect identifier>]
[MAINTAINED AS {PHYSICAL|LOGICAL}];
--添加备库到配置
DGMGRL> ADD DATABASE <Standby db_unique_name> AS CONNECT IDENTIFIER IS standby_tns;
Database "<Standby db_unique_name>" added
--使备库可用
DGMGRL> enable database <Standby db_unique_name>
Enabled.
--此时可以看到备库已经添加到配置文件
DGMGRL> show configuration
Configuration - <configuration_name>
Protection Mode: MaxPerformance
Databases:
<Primary db_unique_name> - Primary database
<Standby db_unique_name> - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
主日志如下
Thu Jun 22 11:27:40 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Thu Jun 22 11:31:05 2017
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
ALTER SYSTEM SWITCH ALL LOGFILE start (<ORACLE_SID>)
Thu Jun 22 11:31:06 2017
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/redo02.log
ALTER SYSTEM SWITCH ALL LOGFILE complete (<ORACLE_SID>)
Thu Jun 22 11:31:07 2017
Archived Log entry 495 added for thread 1 sequence 10 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:31:18 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Thu Jun 22 11:31:47 2017
NSV1 started with pid=29, OS id=3417
Thu Jun 22 11:32:01 2017
ALTER SYSTEM SET log_archive_config='dg_config=(<Primary db_unique_name>,<Standby db_unique_name>)' SCOPE=BOTH;
Thu Jun 22 11:32:05 2017
ALTER SYSTEM SET log_archive_dest_2='service="standby_tns"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="<Standby db_unique_name>" net_timeout=30','valid_for=(all_logfiles,primary_role)' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Thu Jun 22 11:32:05 2017
NSA2 started with pid=36, OS id=3422
Thu Jun 22 11:32:08 2017
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/redo03.log
Archived Log entry 496 added for thread 1 sequence 11 ID 0x2a1b1542 dest 1:
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 12 for destination LOG_ARCHIVE_DEST_2
Thu Jun 22 11:32:49 2017
ARC3: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
备库日志如下
Thu Jun 22 11:31:06 2017
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
Thu Jun 22 11:31:07 2017
Media Recovery Waiting for thread 1 sequence 11
Thu Jun 22 11:31:07 2017
Archived Log entry 434 added for thread 1 sequence 10 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:32:01 2017
ALTER SYSTEM SET log_archive_config='dg_config=(<Standby db_unique_name>,<Primary db_unique_name>)' SCOPE=BOTH;
Thu Jun 22 11:32:01 2017
NSV0 started with pid=31, OS id=3502
Thu Jun 22 11:32:04 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='<ORACLE_SID>';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='<ORACLE_SID>';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_file_name_convert='<ORACLE_SID>','<ORACLE_SID>' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='primary_tns' SCOPE=BOTH;
Thu Jun 22 11:32:08 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process 3506
RFS[5]: Selected log 4 for thread 1 sequence 12 dbid 700188039 branch 947267305
Thu Jun 22 11:32:49 2017
RFS[6]: Assigned to RFS process 3511
RFS[6]: Selected log 5 for thread 1 sequence 11 dbid 700188039 branch 947267305
Thu Jun 22 11:32:49 2017
Archived Log entry 435 added for thread 1 sequence 11 ID 0x2a1b1542 dest 1:
Thu Jun 22 11:32:50 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/<Standby db_unique_name>/archivelog/2017_06_22/o1_mf_1_11_dnpgm1hp_.arc
Media Recovery Waiting for thread 1 sequence 12 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/<ORACLE_SID>/standby04.log
查看某个数据库的配置消息
DGMGRL> show database verbose <Primary db_unique_name>
Database - <Primary db_unique_name>
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
<ORACLE_SID>
Properties:
DGConnectIdentifier = 'primary_tns'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = '<ORACLE_SID>'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<Primary db_unique_name>_DGMGRL.us.oracle.com)(INSTANCE_NAME=<ORACLE_SID>)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> show database verbose <Standby db_unique_name>
Database - <Standby db_unique_name>
--Role:当前数据库的角色
--Intended State:APPLY-ON 在实时应用日志
--Transport Lag: 传输延迟的时间
--Apply Lag: 应用延迟的时间
--Real Time Query:实时查询
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
<ORACLE_SID>
Properties:
DGConnectIdentifier = 'standby_tns'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = '<ORACLE_SID>, <ORACLE_SID>'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = '<ORACLE_SID>'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<Standby db_unique_name>_DGMGRL.us.oracle.com)(INSTANCE_NAME=<ORACLE_SID>)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
broker的功能还是很强大的,如果不会使用可以敲入help查看。也可查看单独命令的帮助,例如:help create
--end--
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30820196/viewspace-2141111/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30820196/viewspace-2141111/