[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配置文档.