I had to setup a physical standby in a
RAC environment. The primary database is a two node RAC cluster but the standby
environment is a single instance database.
During setup and configuration of the
dataguard broker, we had the following error . ” ORA-16608: one or more sites
have warnings”.
In DGMGRL, the command line interface
of the broker you check the configuration status using “show configuration”,
the broker would give you the current setup at the end of the show
configuration output, the broker tries to query the current status from all
servers involved, this includes all instances in the RAC configuration on the
primary and also the physical standby.
DGMGRL> show configuration;
Configuration - ssodg
Protection Mode: MaxPerformance
Databases:
ssoprd - Primary database
Warning: ORA-16792:
configurable property value is inconsistent with database setting
ssodrp - Physical standby
database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
2. So the problem is for a init.ora
parameter on one (or both) RAC nodes. We need to drill down, so “show database
ssoprd” would tell us which database has this error and also show which
property (or init.ora parameter) it is complaining about.
DGMGRL> show database ssoprd;
Database - ssoprd
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ssoprd1
ssoprd2
Warning: ORA-16714: the
value of property StandbyFileManagement is inconsistent with the database
setting
Warning: ORA-16714: the
value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the
value of property LogArchiveMaxProcesses is inconsistent with the database
setting
Warning: ORA-16714: the
value of property LogArchiveMinSucceedDest is inconsistent with the database
setting
Warning: ORA-16714: the
value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16714: the
value of property LogArchiveFormat is inconsistent with the database setting
Database Status:
WARNING
3. So just one node in the RAC cluster
has a problem and that is ssoprd2. I logged into that node and did a “show
parameter standby_file_management” and it showed a value of AUTO. Back in
DGMGRL I did “show database verbose ssoprd”. The verbose option shows you DG configured
properties for a db. The StandbyFileManagement property was also set to AUTO,
so what is the problem. The command “SHOW DATABASE ssoprd
'InconsistentProperties';” gave me more info.
It showed me the 3 different places
where a property/parameter can be set MEMORY_VALUE, SPFILE_VALUE and BROKER_VALUE. As confirmed
earlier the database MEMORY_VALUE had and standby_file_management =AUTO and the
BROKER_VALUE had StandbyFileManagement=AUTO, the problem was that the
SPFILE_VALUE parameter for node ssoprd2 had a value of NULL.
4. Now why would one node in a RAC
cluster have a different SPFILE value when the SPFILE is kept in a shared
location on ASM? I looked in the $ORACLE_HOME/dbs directory of node ssoprd2 and
found a spfilessoprd2.ora file! Even though the contents was ‘*.SPFILE='+DG_DATA/ssoprd/spfilessoprd.ora'
which is the shared location it is still a non-shared spfile in the local
$ORACLE_HOME! Just to confirm I looked into $ORACLE_HOME/dbs on the other node
did not find a SPFILE there. So the solution was simply to shutdown node 2,
remove the spfile from $ORACLE_HOME/dbs and then to start the database on node
2. Note that each node had a initssoprd?.ora file in the $ORACLE_HOME with the
contents *.SPFILE='+DG_DATA/ssoprd/spfilessoprd.ora'
5. This fixed the issue, it was the DGB
not able to synchronize the value of these various parameters/properties
between the various instances in the cluster. In a RAC environment its a best
practice to maintain the SPFILE in a shared location such as ASM storage.
The SHOW CONFIGURATION command from the
DGMGRL returned error free.
