Here I am going to discuss some of the common Data guard broker (DGMGRL) errors.
Following are the details of my example
Database Name/Primary/Standby: ORCLPROD
Primary DB Unique Name: ORCLPROD
Standby DB Unique Name: ORCLPRODSTY
Operating System (OS): HP UX Itanium 11.31
Database Version :11.2.0.3
1. ORA-16664: unable to receive the result from a database
Solution:
This is a very generic error. We should see the respective (standby) database DRC log. The DRC log are location in background_dump_dest (in 10g) or diagnostic_dest (in 11g). Common symptoms are password file issue , local_listener is not set properly or is NULL.
The error can be further debugged by setting an event
event = "16664 trace name errorstack forever, level 3"
When I setup the data guard broker using DGMGRL, the 'ORA-16664' error turned to"Warning: ORA-16809: multiple warnings detected for the database".
Extract from DRC log (drcORCLPROD.log) :
Site ORCLPRODSTY returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration ORCLPROD_primary Warning ORA-16607
Primary Database ORCLPROD Success ORA-00000
Physical Standby Database ORCLPRODSTY Error ORA-16664
08/01/2013 12:06:38
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration ORCLPROD_primary Warning ORA-16608
Primary Database ORCLPROD Success ORA-00000
Physical Standby Database ORCLPRODSTY Warning ORA-16809
08/01/2013 12:07:39
I have verified the ORA error using DGMGRL CLI as well.
DGMGRL> show configuration;
Configuration - ORCLPROD_primary
Protection Mode: MaxPerformance
Databases:
ORCLPROD - Primary database
ORCLPRODSTY - Physical standby database
Warning: ORA-16809: multiple warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
2. ORA-16714: the value of property XYZ is inconsistent with the database setting
Solution:
This is also a generic warning and XYZ refers to the different property settings for the database and Data Guard broker You can get additional details within DGMGRL CLI (as shown below) or by DRC logfile.
DGMGRL CLI:
DGMGRL> SHOW DATABASE 'ORCLPRODSTY';
Database - ORCLPRODSTY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 15 minutes 17 seconds
Real Time Query: OFF
Instance(s):
ORCLPROD
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 Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property
Database Status:
WARNING
DGMGRL> show database 'ORCLPRODSTY' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ORCLPROD ArchiveLagTarget 0 0
ORCLPROD LogArchiveMaxProcesses 4 4
ORCLPROD LogArchiveMinSucceedDest 1 1
ORCLPROD LogArchiveTrace 0 (missing) 0
ORCLPROD LogArchiveFormat ORCLPROD_%s_%t_%r.log (missing) ORCLPROD_%s_%t_%r.log
DRC Logfile:
08/01/2013 12:07:38
Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='ORCLPROD'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='ORCLPROD'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='ORCLPROD_%s_%t_%r.log', SPFILE='(missing)', DATABASE='ORCLPROD_%s_%t_%r.log'
08/01/2013 12:08:38
You can fix each warning as shown below:
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 1 12:21:32 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=0 SCOPE=BOTH;
System altered.
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=1 SCOPE=BOTH;
System altered.
As you fix parameter values one by one, it gets reflected in DGMGRL CLI. For example I fixed, 3 settings and DGMGRL output is updated.
DGMGRL> show database 'ORCLPRODSTY' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ORCLPROD LogArchiveTrace 0 (missing) 0
ORCLPROD LogArchiveFormat ORCLPROD_%s_%t_%r.log (missing) ORCLPROD_%s_%t_%r.log
DGMGRL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 scope=both;
To fix LOG_ARCHIVE_FORMAT warning, we need toc reate pfile, make the corresponding changes and bounce the database.
Once all warning/error are fixed DGMGL status becomes SUCCESS
DGMGRL> show configuration;
Configuration - ORCLPROD_primary
Protection Mode: MaxPerformance
Databases:
ORCLPROD - Primary database
ORCLPRODSTY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Following are the details of my example
Database Name/Primary/Standby: ORCLPROD
Primary DB Unique Name: ORCLPROD
Standby DB Unique Name: ORCLPRODSTY
Operating System (OS): HP UX Itanium 11.31
Database Version :11.2.0.3
1. ORA-16664: unable to receive the result from a database
Solution:
This is a very generic error. We should see the respective (standby) database DRC log. The DRC log are location in background_dump_dest (in 10g) or diagnostic_dest (in 11g). Common symptoms are password file issue , local_listener is not set properly or is NULL.
The error can be further debugged by setting an event
event = "16664 trace name errorstack forever, level 3"
When I setup the data guard broker using DGMGRL, the 'ORA-16664' error turned to"Warning: ORA-16809: multiple warnings detected for the database".
Extract from DRC log (drcORCLPROD.log) :
Site ORCLPRODSTY returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration ORCLPROD_primary Warning ORA-16607
Primary Database ORCLPROD Success ORA-00000
Physical Standby Database ORCLPRODSTY Error ORA-16664
08/01/2013 12:06:38
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration ORCLPROD_primary Warning ORA-16608
Primary Database ORCLPROD Success ORA-00000
Physical Standby Database ORCLPRODSTY Warning ORA-16809
08/01/2013 12:07:39
I have verified the ORA error using DGMGRL CLI as well.
DGMGRL> show configuration;
Configuration - ORCLPROD_primary
Protection Mode: MaxPerformance
Databases:
ORCLPROD - Primary database
ORCLPRODSTY - Physical standby database
Warning: ORA-16809: multiple warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
2. ORA-16714: the value of property XYZ is inconsistent with the database setting
Solution:
This is also a generic warning and XYZ refers to the different property settings for the database and Data Guard broker You can get additional details within DGMGRL CLI (as shown below) or by DRC logfile.
DGMGRL CLI:
DGMGRL> SHOW DATABASE 'ORCLPRODSTY';
Database - ORCLPRODSTY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 15 minutes 17 seconds
Real Time Query: OFF
Instance(s):
ORCLPROD
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 Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property
Database Status:
WARNING
DGMGRL> show database 'ORCLPRODSTY' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ORCLPROD ArchiveLagTarget 0 0
ORCLPROD LogArchiveMaxProcesses 4 4
ORCLPROD LogArchiveMinSucceedDest 1 1
ORCLPROD LogArchiveTrace 0 (missing) 0
ORCLPROD LogArchiveFormat ORCLPROD_%s_%t_%r.log (missing) ORCLPROD_%s_%t_%r.log
DRC Logfile:
08/01/2013 12:07:38
Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='ORCLPROD'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='ORCLPROD'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='ORCLPROD_%s_%t_%r.log', SPFILE='(missing)', DATABASE='ORCLPROD_%s_%t_%r.log'
08/01/2013 12:08:38
You can fix each warning as shown below:
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 1 12:21:32 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=0 SCOPE=BOTH;
System altered.
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST=1 SCOPE=BOTH;
System altered.
As you fix parameter values one by one, it gets reflected in DGMGRL CLI. For example I fixed, 3 settings and DGMGRL output is updated.
DGMGRL> show database 'ORCLPRODSTY' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ORCLPROD LogArchiveTrace 0 (missing) 0
ORCLPROD LogArchiveFormat ORCLPROD_%s_%t_%r.log (missing) ORCLPROD_%s_%t_%r.log
DGMGRL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_TRACE=0 scope=both;
To fix LOG_ARCHIVE_FORMAT warning, we need toc reate pfile, make the corresponding changes and bounce the database.
Once all warning/error are fixed DGMGL status becomes SUCCESS
DGMGRL> show configuration;
Configuration - ORCLPROD_primary
Protection Mode: MaxPerformance
Databases:
ORCLPROD - Primary database
ORCLPRODSTY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
No comments:
Post a Comment