[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

相關文章