An Issue of Oracle DataGuard Broker

最近一直在研究用Broker搭建Data Guard,以前一直都很順利，這兩天突然發現一個奇怪的問題，自己確認配置的步驟是沒有問題的，但是show configuration的時候，就是老出如下錯誤提示： 

DGMGRL> show configuration;

Configuration
  Name:                dgmgrl_1
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    prod - Primary database
    sbdb - Physical standby database

Current status for "dgmgrl_1":
Warning: ORA-16608: one or more databases have warnings

 

DGMGRL> show database 'prod' statusreport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database 'sbdb' statusreport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *    WARNING ORA-16707: the value of the property DbFileNameConvert is invalid, valid values are pairs of file specifications
                SBDB    WARNING ORA-16714: the value of property DbFileNameConvert is inconsistent with the database setting

 

從這個錯誤提示可以看出，是standby 庫SBDB 的db_file_name_convert有問題,於是我做了如下的操作，終於發現問題的所在，下面紅色的字型部分，竟然被截斷了一部分。

DGMGRL> show database verbose SBDB

Database
  Name:            sbdb
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    SBDB

  Properties:
    InitialConnectIdentifier        = 'sbdb'
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '2'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD/Disk1, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk2, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk3,
/u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk4, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk5, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk6, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk7,
/u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk8'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD/Disk1, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk2, /u01/app/oracle/oradata/SBDB, /u01/app/oracle/oradata/PROD/Disk3, /u01/app/oracle/oradata/SBDB'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'even.oracle.com'
    SidName                         = 'SBDB'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1521))'
    StandbyArchiveLocation          = '/u01/app/oracle/bak/arch1'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "sbdb":
Warning: ORA-16809: multiple warnings detected for the database

 

我initSBDB.ora裡是下面這樣的，明顯的broker 裡和我SPFILE裡是不一致的，所以一直報錯誤。

db_file_name_convert=
'/u01/app/oracle/oradata/PROD/Disk1','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk2','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk3','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk4','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk5','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk6','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk7','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD/Disk8','/u01/app/oracle/oradata/SBDB',
'/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/SBDB'

 

查看metlink發現有如下bug：
Bug 9469176: BROKER DOESNOT SET DB_FILE_NAME_CONVERT (DBFILENAMECONVERT) CORRECTLY

DescriptionData Guard broker limit the pairs of strings in DbFileNameConvert and LogFileNameConvert to only 10. Exceeding that number, broker will not set them into the actual DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT
parameter

仔細分析發現v$parameter 的VALUE為VARCHAR2(512)，我在pfile中設定的值比這個長，導致show 和 broker中看到的都是被截斷的值

SQL> desc v$parameter
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(512)
 DISPLAY_VALUE                                      VARCHAR2(512)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER

Broker重要的診斷命令：

show database 'sbdb' statusreport;

show database verbose SBDB;

總結：參數檔案的值的長度最好不要超過512，否則會出現問題的。記錄下來，給自己一個Lessons learned的把。