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