oracle 10g Data Guard Broker的一个Bug

最近一直在研究用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 DOES NOT 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的把。