Wednesday, September 5, 2012

ORA-16714 in a RAC environment

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.

DGMGRL> show configuration;Configuration - ssodg  Protection Mode: MaxPerformance  Databases:    ssoprd - Primary database    ssodrp - Physical standby database Fast-Start Failover: DISABLED Configuration Status:SUCCESS DGMGRL> show database ssoprd;Database - ssoprd  Role:            PRIMARY  Intended State:  TRANSPORT-ON  Instance(s):    ssoprd1    ssoprd2 Database Status:SUCCESS

No comments: