[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.


--//

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