11g dgbroker测试
adg已配置完成。
首先主备库上设置dg_broker_start
alter system set dg_broker_start=true;
主库dgmgrl登录
[oracle@oracletest1 ~]$ dgmgrl / DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration; ORA-16532: Data Guard broker configuration does not exist Configuration details cannot be determined by DGMGRL DGMGRL> DGMGRL> CREATE CONFIGURATION adgbroker AS PRIMARY DATABASE IS orcldbtest connect identifier is orcldbtest; Configuration "adgbroker" created with primary database "orcldbtest" DGMGRL> show configuration; Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtest - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> add database orcldbtestdg as connect identifier is orcldbtestdg maintained as physical > ; Database "orcldbtestdg" added DGMGRL> show configuration; Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtest - Primary database orcldbtestdg - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> show configuration verbose Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtest - Primary database orcldbtestdg - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: DISABLED DGMGRL> show database verbose orcldbtestdg Database - orcldbtestdg Role: PHYSICAL STANDBY Intended State: OFFLINE Transport Lag: (unknown) Apply Lag: (unknown) Apply Rate: (unknown) Real Time Query: OFF Instance(s): orcldbtest Properties: DGConnectIdentifier = 'orcldbtestdg' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'ENABLE' 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)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'orcldbtest' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcldbtestdg_DGMGRL)(INSTANCE_NAME=orcldbtest)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/oradata/arch' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'arc_%t_%s_%r.arc' TopWaitEvents = '(monitor)' Database Status: DISABLED DGMGRL> show database verbose orcldbtest Database - orcldbtest Role: PRIMARY Intended State: OFFLINE Instance(s): orcldbtest Properties: DGConnectIdentifier = 'orcldbtest' ObserverConnectIdentifier = '' LogXptMode = 'SYNC' 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)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'orcldbtest' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcldbtest_DGMGRL)(INSTANCE_NAME=orcldbtest)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/oradata/backup' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: DISABLED DGMGRL> enable configuration; Enabled. 主库alert日志 Mon Jun 13 13:55:26 2022 RSM0 started with pid=36, OS id=5787 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='orcldbtest'; ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='orcldbtest'; 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 ARCHIVE LOG Mon Jun 13 13:55:31 2022 LGWR: Standby redo logfile selected to archive thread 1 sequence 65 LGWR: Standby redo logfile selected for thread 1 sequence 65 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 65 (LGWR switch) Current log# 1 seq# 65 mem# 0: /oradata/ORCLDBTEST/onlinelog/redlog01.log Mon Jun 13 13:55:31 2022 Archived Log entry 106 added for thread 1 sequence 64 ID 0xbade385e dest 1: 备库alert日志 Mon Jun 13 13:55:25 2022 NSV0 started with pid=35, OS id=19206 Mon Jun 13 13:55:30 2022 RSM0 started with pid=37, OS id=19214 ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='orcldbtest'; ALTER SYSTEM SET log_archive_format='arc_%t_%s_%r.arc' SCOPE=SPFILE SID='orcldbtest'; 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 fal_server='orcldbtest' SCOPE=BOTH; Mon Jun 13 13:55:34 2022 Standby controlfile consistent with primary RFS[5]: Selected log 9 for thread 1 sequence 65 dbid -1202223572 branch 1048776084 Mon Jun 13 13:55:34 2022 Media Recovery Waiting for thread 1 sequence 65 (in transit) Recovery of Online Redo Log: Thread 1 Group 9 Seq 65 Reading mem 0 Mem# 0: /oradata/ORCLDBTESTDG/onlinelog/st02.log Mon Jun 13 13:55:35 2022 Archived Log entry 37 added for thread 1 sequence 64 ID 0xbade385e dest 1:
DGMGRL> show configuration verbose Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtest - Primary database orcldbtestdg - Physical standby database Properties: FastStartFailoverThreshold = '60' OperationTimeout = '30' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database orcldbtest Database - orcldbtest Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcldbtest Database Status: SUCCESS DGMGRL> show database orcldbtestdg Database - orcldbtestdg Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Apply Rate: 532.00 KByte/s Real Time Query: ON Instance(s): orcldbtest Database Status: SUCCESS
dgbroker还是有些需要注意的地方。
1、主备节点/etc/hosts文件需要配置，否则在switchover时会出错。
2、在进行switchover时一定要用 dgmgrl sys/xxxxxx@orcldbtest 方式登录。如果dgmgrl / 的方式，肯定会收到以下错误。
DGMGRL> switchover to orcldbtest; Performing switchover NOW, please wait... New primary database "orcldbtest" is opening... Operation requires startup of instance "orcldbtest" on database "orcldbtestdg" Starting instance "orcldbtest"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "orcldbtest" of database "orcldbtestdg"
3、switchover时会将原主库先关闭然后再启动，此时没有配置用于dgmgrl的监听，dgbroker无法启动原主库。
[oracle@oracletest2 ~]$ dgmgrl sys/oracle@orcldbtest DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtestdg - Primary database orcldbtest - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to orcldbtest; Performing switchover NOW, please wait... New primary database "orcldbtest" is opening... Operation requires startup of instance "orcldbtest" on database "orcldbtestdg" Starting instance "orcldbtest"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "orcldbtest" of database "orcldbtestdg"
以下配置中加粗部分是新增部分
主库监听配置
[oracle@oracletest1 admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldbtest) (ORACLE_HOME = /opt/11g/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcldbtest) ) (SID_DESC = (GLOBAL_DBNAME = orcldbtest_dgmgrl) (ORACLE_HOME = /opt/11g/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcldbtest) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.20)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) [oracle@oracletest1 admin]$ cat tnsnames.ora orcldbtest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) ) orcldbtestdg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.21)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) ) orcldbtestdg_dgmgrl= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.21)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) )
orcldbtest_dgmgrl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldbtest)
)
)
主库监听状态
[oracle@oracletest1 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUN-2022 09:06:18 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.224.20)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 14-JUN-2022 09:02:11 Uptime 0 days 0 hr. 4 min. 7 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/11g/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /opt/11g/oracle/diag/tnslsnr/oracletest1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.224.20)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcldbtest" has 2 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestXDB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtest_DGB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtest_dgmgrl" has 1 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
备库监听配置
[oracle@oracletest2 admin]$ cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldbtestdg) (ORACLE_HOME = /opt/11g/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcldbtest) ) (SID_DESC = (GLOBAL_DBNAME = orcldbtestdg_dgmgrl) (ORACLE_HOME = /opt/11g/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcldbtest) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.21)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) [oracle@oracletest2 admin]$ cat tnsnames.ora orcldbtest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) ) orcldbtestdg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.21)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) ) orcldbtest_dgmgrl= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldbtest) ) )
orcldbtestdg_dgmgrl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.224.21)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldbtest)
)
)
备库监听状态
[oracle@oracletest2 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUN-2022 09:27:47 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.224.21)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 14-JUN-2022 09:04:35 Uptime 0 days 0 hr. 23 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/11g/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /opt/11g/oracle/diag/tnslsnr/oracletest2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.224.21)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcldbtest" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestXDB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg" has 2 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg_DGB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg_dgmgrl" has 1 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully[oracle@oracletest2 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUN-2022 09:27:47 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.224.21)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 14-JUN-2022 09:04:35 Uptime 0 days 0 hr. 23 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/11g/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /opt/11g/oracle/diag/tnslsnr/oracletest2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.224.21)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcldbtest" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestXDB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg" has 2 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg_DGB" has 1 instance(s). Instance "orcldbtest", status READY, has 1 handler(s) for this service... Service "orcldbtestdg_dgmgrl" has 1 instance(s). Instance "orcldbtest", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
现在再来进行switchover，只需要一条命令。
[oracle@oracletest2 ~]$ dgmgrl sys/oracle@orcldbtest DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> show configuration Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtestdg - Primary database orcldbtest - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to orcldbtest Performing switchover NOW, please wait... New primary database "orcldbtest" is opening... Operation requires startup of instance "orcldbtest" on database "orcldbtestdg" Starting instance "orcldbtest"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "orcldbtest" DGMGRL> show configuration Configuration - adgbroker Protection Mode: MaxAvailability Databases: orcldbtest - Primary database orcldbtestdg - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to orcldbtestdg Performing switchover NOW, please wait... Operation requires a connection to instance "orcldbtest" on database "orcldbtestdg" Connecting to instance "orcldbtest"... Connected. New primary database "orcldbtestdg" is opening... Operation requires startup of instance "orcldbtest" on database "orcldbtest" Starting instance "orcldbtest"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "orcldbtestdg"
后续可以配置FSFO自动故障转移，参考以下链接：
