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