An issue of Oracle dataguard Broker

I have been studying how to build data guard with brokers recently and it has been quite smooth before. I suddenly found a strange problem over the past two days. I confirmed that the configuration steps were correct, however, the following error message is displayed during 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

 

From this error message, we can see that db_file_name_convert of the standby database sbdb has a problem, so I did the following operations and finally found out the problem, the red font section below, it was truncated.

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 = '000000'
Nettimeout = '000000'
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

 

In my initsbdb. ora, this is the case below. Obviously, the broker is inconsistent with my spfile, so an error is always reported.

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 found the following 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

After careful analysis, it is found that the value of V $ parameter is varchar2 (512). The value I set in pfile is longer than this, so that all the values in show and broker are truncated.

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

Important broker diagnostic commands:

Show database 'sbdb' statusreport;

Show database verbose sbdb;

Summary: the length of the parameter file value should not exceed 512; otherwise, problems may occur. Record it and give yourself a lessons learned handle.