[20221111]19c配置Data Guard Broker问题.txt

来源：这里教程网时间：2026-03-03 18:05:07 作者：

[20221111]19c配置Data Guard Broker问题.txt --//生产系统优化已经快完成,剩下配置Data Guard Broker,本来应该好好看看19c dgmgrl文档再操作,感觉问题不大, --//一边做一边学习,结果遇到一堆问题,自己整理工作笔记做一个记录: 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.配置dg broker前准备: --//主库 SYS@192.168.100.235:1521/orcl> show parameter broker PARAMETER_NAME TYPE VALUE ---------------------- -------- ------------------------------------------------------- connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1)) dg_broker_config_file1 string /u01/app/oracle/product/19/db_1/dbs/dr1orcl.dat dg_broker_config_file2 string /u01/app/oracle/product/19/db_1/dbs/dr2orcl.dat dg_broker_start boolean FALSE use_dedicated_broker boolean FALSE --//备库: SYS@192.168.100.237:1521/orcldg> show parameter broker PARAMETER_NAME TYPE VALUE ---------------------- ------- ------------------------------------------------------- connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1)) dg_broker_config_file1 string /u01/app/oracle/product/19/db_1/dbs/dr1orcldg.dat dg_broker_config_file2 string /u01/app/oracle/product/19/db_1/dbs/dr2orcldg.dat dg_broker_start boolean FALSE use_dedicated_broker boolean FALSE --//主,备库两边都要执行. SYS@192.168.100.235:1521/orcl> alter system set dg_broker_start=TRUE scope=both; System altered. SYS@192.168.100.237:1521/orcldg> alter system set dg_broker_start=TRUE scope=both; System altered. --//修改主库监听配置listener.ora,加入orcl_DGMGRL静态服务. $ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora # Generated by Oracle configuration tools SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/19/db_1) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = orcl_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/19/db_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.235)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) DIAG_ADR_ENABLED_LISTENER = OFF --//重启监听: --//$ lsnrctl stop; lsnrctl start --//alter system register; --//修改备库监听配置listener.ora,加入orcldg_DGMGRL静态服务.: $ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora # Generated by Oracle configuration tools SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldg) (ORACLE_HOME = /u01/app/oracle/product/19/db_1) (SID_NAME = orcldg) ) (SID_DESC = (GLOBAL_DBNAME = orcldg_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/19/db_1) (SID_NAME = orcldg) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.237)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) --//重启监听: --//$ lsnrctl stop; lsnrctl start --//alter system register; 3.使用dgmgrl配置: DGMGRL> create configuration lisdb as primary database is orcl connect identifier is orcl; Configuration "lisdb" created with primary database "orcl" DGMGRL> add database orcldg as connect identifier is orcldg maintained as physical; Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set --//上网查询设置先要清除log_archive_dest_2设置: SYS@192.168.100.237:1521/orcldg> alter system set log_archive_dest_2='' scope=both; System altered. --//注实际上仅仅需要清除备库的log_archive_dest_2设置. DGMGRL> add database orcldg as connect identifier is orcldg maintained as physical; Database "orcldg" added DGMGRL> enable configuration; Enabled. DGMGRL> show configuration verbose; Configuration - lisdb Protection Mode: MaxPerformance Members: orcl - Primary database orcldg - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'USER' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'orcl_CFG' Fast-Start Failover: Disabled Configuration Status: SUCCESS DGMGRL> show database verbose orcl ; Database - orcl Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcl Properties: DGConnectIdentifier = 'orcl' ObserverConnectIdentifier = '' FastStartFailoverTarget = '' PreferredObserverHosts = '' LogShipping = 'ON' RedoRoutes = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '0' LogArchiveMinSucceedDest = '0' DataGuardSyncLatency = '0' LogArchiveTrace = '0' LogArchiveFormat = '' DbFileNameConvert = '' LogFileNameConvert = '' ArchiveLocation = '' AlternateLocation = '' StandbyArchiveLocation = '' StandbyAlternateLocation = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' LogXptStatus = '(monitor)' SendQEntries = '(monitor)' RecvQEntries = '(monitor)' HostName = 'LIS-DB' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.235)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))' TopWaitEvents = '(monitor)' SidName = '(monitor)' Log file locations: Alert log : /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcl/orcl/trace/drcorcl.log Database Status: SUCCESS DGMGRL> show database verbose orcldg ; Database - orcldg Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 84.00 KByte/s Active Apply Rate: 469.00 KByte/s Maximum Apply Rate: 4.80 MByte/s Real Time Query: ON Instance(s): orcldg Properties: DGConnectIdentifier = 'orcldg' ObserverConnectIdentifier = '' FastStartFailoverTarget = '' PreferredObserverHosts = '' LogShipping = 'ON' RedoRoutes = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '0' LogArchiveMinSucceedDest = '0' DataGuardSyncLatency = '0' LogArchiveTrace = '0' LogArchiveFormat = '' DbFileNameConvert = '' LogFileNameConvert = '' ArchiveLocation = '' AlternateLocation = '' StandbyArchiveLocation = '' StandbyAlternateLocation = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' LogXptStatus = '(monitor)' SendQEntries = '(monitor)' RecvQEntries = '(monitor)' HostName = 'LISDG' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.237)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcldg_DGMGRL)(INSTANCE_NAME=orcldg)(SERVER=DEDICATED)))' TopWaitEvents = '(monitor)' SidName = '(monitor)' Log file locations: Alert log : /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/alert_orcldg.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/drcorcldg.log Database Status: SUCCESS --//注意执行validate database一定要以connect sys/XXXX连接执行,不然报ORA-01017: invalid username/password; logon denied DGMGRL> validate database 'orcl' Database Role: Primary database Ready for Switchover: Yes Flashback Database Status: orcl: Off Managed by Clusterware: orcl: NO Validating static connect identifier for the primary database orcl... The static connect identifier allows for a connection to database "orcl". DGMGRL> validate database 'orcldg' Database Role: Physical standby database Primary Database: orcl Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: orcl : Off orcldg: On Managed by Clusterware: orcl : NO orcldg: NO Validating static connect identifier for the primary database orcl... The static connect identifier allows for a connection to database "orcl". --//补充说明,validate database 'orcldg'时报如下问题: DGMGRL> validate database 'orcldg' Database Role: Physical standby database Primary Database: orcl Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: orcl : Off orcldg: On Managed by Clusterware: orcl : NO orcldg: NO Validating static connect identifier for the primary database orcl... The static connect identifier allows for a connection to database "orcl". Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (orcl) (orcldg) 1 6 3 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (orcldg) (orcl) 1 3 0 Insufficient SRLs Warning: standby redo logs not configured for thread 1 on orcl --//两边建立的redo logfile数量不等,standby logfile也是一样,另外注意一点,建立STANDBY LOGFILE要指定thread号. --//以上输出是修复后的情况. --//建立例子: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('/u02/app/oracle/oradata/orcl/datafile/ORCL/st_redo11.log') size 1024M; 4.补充说明: SYS@192.168.100.237:1521/orcldg> show parameter log_archive_dest_2 PARAMETER_NAME TYPE VALUE -------------------- ---------- -------- log_archive_dest_2 string --//备库的log_archive_dest_2没有赋值不用担心,切换时自动赋值.你也可以现在给其设置,不影响dgmgrl的使用. SYS@192.168.100.237:1521/orcldg> alter system set log_archive_dest_2='SERVICE=oracl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'; System altered. --//测试两边都清除的情况,重新配置: SYS@192.168.100.237:1521/orcldg> alter system set log_archive_dest_2='' scope=both; System altered. SYS@192.168.100.235:1521/orcl> alter system set log_archive_dest_2='' scope=both; System altered. DGMGRL> disable configuration; Disabled. DGMGRL> remove CONFIGURATION; Removed configuration DGMGRL> create configuration lisdb as primary database is orcl connect identifier is orcl; Configuration "lisdb" created with primary database "orcl" DGMGRL> add database orcldg as connect identifier is orcldg maintained as physical; Database "orcldg" added --//两边执行: SYS@192.168.100.235:1521/orcl> show parameter log_archive_dest_2 PARAMETER_NAME TYPE VALUE ------------------ ---------- -------------------------------------- log_archive_dest_2 string SYS@192.168.100.237:1521/orcldg> show parameter log_archive_dest_2 PARAMETER_NAME TYPE VALUE ------------------ ---------- --------------------------------------- log_archive_dest_2 string DGMGRL> enable configuration; Enabled. SYS@192.168.100.235:1521/orcl> show parameter log_archive_dest_2 PARAMETER_NAME TYPE VALUE ------------------ ---------- ---------------------------------------------------------------------------------------------------- log_archive_dest_2 string service="orcldg", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_un ique_name="orcldg" net_timeout=30, valid_for=(online_logfile,all_roles) --//自动给主库的log_archive_dest_2附上缺省值. SYS@192.168.100.237:1521/orcldg> show parameter log_archive_dest_2 PARAMETER_NAME TYPE VALUE ------------------ ---------- ---------------------------------------------------------------------------------------------------- log_archive_dest_2 string --//备库的log_archive_dest_2没有赋值. show configuration verbose show database verbose orcl show database verbose orcldg validate database orcl validate database orcldg --//执行如上命令没有任何问题. SYS@192.168.100.237:1521/orcldg> alter system set log_archive_dest_2='SERVICE=oracl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'; System altered. --//不过我担心切换时可能再次出现问题,建议还是不要设置. --//SYS@192.168.100.237:1521/orcldg> alter system set log_archive_dest_2='' scope=both; --//System altered. --//顺便贴上alert*.log设置log_archive_dest_2执行语句,注意里面的逗号,相当于赋了3个值. 2022-11-11T10:02:30.197067+08:00 ALTER SYSTEM SET log_archive_dest_2='service="orcldg"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldg" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH; SYS@192.168.100.235:1521/orcl> select * from V$SYSTEM_PARAMETER2 where name='log_archive_dest_2' 2 @pr ============================== NUM : 1866 NAME : log_archive_dest_2 TYPE : 2 VALUE : service="orcldg" DISPLAY_VALUE : service="orcldg" ISDEFAULT : FALSE ISSES_MODIFIABLE : TRUE ISSYS_MODIFIABLE : IMMEDIATE ISPDB_MODIFIABLE : FALSE ISINSTANCE_MODIFIABLE : TRUE ISMODIFIED : MODIFIED ISADJUSTED : FALSE ISDEPRECATED : FALSE ISBASIC : TRUE DESCRIPTION : archival destination #2 text string ORDINAL : 1 UPDATE_COMMENT : HASH : 993174268 CON_ID : 0 ============================== NUM : 1866 NAME : log_archive_dest_2 TYPE : 2 VALUE : ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldg" net_timeout=30 DISPLAY_VALUE : ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldg" net_timeout=30 ISDEFAULT : FALSE ISSES_MODIFIABLE : TRUE ISSYS_MODIFIABLE : IMMEDIATE ISPDB_MODIFIABLE : FALSE ISINSTANCE_MODIFIABLE : TRUE ISMODIFIED : MODIFIED ISADJUSTED : FALSE ISDEPRECATED : FALSE ISBASIC : TRUE DESCRIPTION : archival destination #2 text string ORDINAL : 2 UPDATE_COMMENT : HASH : 993174268 CON_ID : 0 ============================== NUM : 1866 NAME : log_archive_dest_2 TYPE : 2 VALUE : valid_for=(online_logfile,all_roles) DISPLAY_VALUE : valid_for=(online_logfile,all_roles) ISDEFAULT : FALSE ISSES_MODIFIABLE : TRUE ISSYS_MODIFIABLE : IMMEDIATE ISPDB_MODIFIABLE : FALSE ISINSTANCE_MODIFIABLE : TRUE ISMODIFIED : MODIFIED ISADJUSTED : FALSE ISDEPRECATED : FALSE ISBASIC : TRUE DESCRIPTION : archival destination #2 text string ORDINAL : 3 UPDATE_COMMENT : HASH : 993174268 CON_ID : 0 PL/SQL procedure successfully completed. --//也可以执行如下: SYS@192.168.100.235:1521/orcl> show spparameter log_archive_dest_2 SID NAME TYPE VALUE -------- ----------------------------- ---------- ---------------------------------------------------------------------------------------------------- * log_archive_dest_2 string ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldg" net_timeout=30 * log_archive_dest_2 string service="orcldg" * log_archive_dest_2 string valid_for=(online_logfile,all_roles)

编辑推荐：

返回列表

[20221111]19c配置Data Guard Broker问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推