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

[20221111]19c配置Data Guard Broker问题2.txt --//前几天在19c上配置Data Guard Broker,遇到许多问题浪费不少时间,我还发现一些11g不同的细节. 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.问题: 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 --//注意下划线内容.出现ConfigurationWideServiceName    = 'orcl_CFG'.  $ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-NOV-2022 10:19:20 Copyright (c) 1991, 2019, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.235)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date                09-NOV-2022 10:40:55 Uptime                    1 days 23 hr. 38 min. 24 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/19/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/product/19/db_1/network/log/listener.log Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.235)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=LIS-DB)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "orcl" has 2 instance(s).   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...   Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s).   Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl_CFG" has 1 instance(s). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl_DGMGRL" has 1 instance(s).   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully --//自动配置了orcl_CFG的服务名. SYS@192.168.100.235:1521/orcl> show parameter  service PARAMETER_NAME TYPE   VALUE -------------- ------ ------- service_names  string orcl SYS@192.168.100.235:1521/orcl> select * from V$SERVICES where NETWORK_NAME='orcl_CFG'   2  @ pr ============================== SERVICE_ID                    : 4 NAME                          : orcl_CFG NAME_HASH                     : 1593985797 NETWORK_NAME                  : orcl_CFG CREATION_DATE                 : 2022-11-09 10:43:51 CREATION_DATE_HASH            : 3710754504 GOAL                          : NONE DTP                           : N AQ_HA_NOTIFICATION            : NO CLB_GOAL                      : LONG COMMIT_OUTCOME                : NO RETENTION_TIME                : 86400 REPLAY_INITIATION_TIMEOUT     : 300 SESSION_STATE_CONSISTENCY     : GLOBAL                        : NO PDB                           : SQL_TRANSLATION_PROFILE       : MAX_LAG_TIME                  : STOP_OPTION                   : NONE FAILOVER_RESTORE              : NONE DRAIN_TIMEOUT                 : 0 TABLE_FAMILY_ID               : 0 CON_ID                        : 0 PL/SQL procedure successfully completed. --//https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/oracle-data-guard-broker-properties.html#GUID-EA9AA7B3-36F4-4D45-BD55-30D6A71210DC 8.1.3 ConfigurationWideServiceName The ConfigurationWideServiceName configuration property is used to change the name of the configuration-wide service. 配置范围服务名称配置属性用于更改配置范围服务的名称。 The broker publishes a service on each member of a configuration with a unified service name. The default service name of this configuration-wide service is primarydbname_CFG, where the primary database name is appended with a suffix of _CFG. The service name does not change after a role transition. 代理会对具有统一服务名的配置中的每个成员发布服务。此配置范围服务的默认服务名称是primarydbname_CFG，其中主数据库名称附加 一个后缀_CFG。在角色转换后，服务名称不会发生更改。 --//因为这个是启用dg broker写入service$表中的,这样备库也应该存在一个类似的名字. $ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 11-NOV-2022 10:30:53 Copyright (c) 1991, 2019, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.237)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date                09-NOV-2022 10:42:22 Uptime                    1 days 23 hr. 48 min. 31 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/19/db_1/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/LISDG/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.237)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=LISDG)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcldg/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "orcl_CFG" has 1 instance(s).   Instance "orcldg", status READY, has 1 handler(s) for this service... Service "orcldg" has 2 instance(s).   Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service...   Instance "orcldg", status READY, has 1 handler(s) for this service... Service "orcldg_DGMGRL" has 1 instance(s).   Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully --//也就是建立dg broker后,可以通过primarydbname_CFG名字连接主库以及备库. $ rlsql -s -l sys/XXXXX@192.168.100.237:1521/orcl_cfg as sysdba <<< 'select sysdate from dual ;' SYSDATE ------------------- 2022-11-11 10:33:29 $ rlsql -s -l sys/XXXXX@192.168.100.235:1521/orcl_cfg as sysdba <<< 'select sysdate from dual ;' SYSDATE ------------------- 2022-11-11 10:33:35 --//有时间还是认真看看19c以上版本的DGMGRL配置文档.