DataGuard broker properties – Part I: imported parameters

By Franck Pachot

.

When you are using the DataGuard Broker, you should always use the broker (with DGMGRL or OEM) to change the parameters that are managed, rather than changing them with ALTER SYSTEM. But do you know which parameters are concerned?

I’ve divided the parameters in two parts. All are set by the broker, but only some of them are read when you add a new database to the configuration.

Parameters imported

When you add a new database to the broker configuration, a few parameters are read from the instance in order to set the broker properties.

I’ll take an example.
I’ve a database with all default settings. Only db_file_name_convert and log_file_name_convert are set in spfile.
Here is the content of the primary spfile (db_unique_name=’DEMO11′):

*.db_file_name_convert='DEMO12','DEMO11'
*.log_file_name_convert='DEMO12','DEMO11'

and the standby spfile (db_unique_name=’DEMO12′):

*.db_file_name_convert='DEMO11','DEMO12'
*.log_file_name_convert='DEMO11','DEMO12'

Here is how I check the initialization parameters:

select name,isdefault,display_value from v$parameter 
 where translate(upper(name),'12','nnnnnnnnnn') in 
 ('ARCHIVE_LAG_TARGET','DB_FILE_NAME_CONVERT','LOG_ARCHIVE_FORMAT','LOG_ARCHIVE_MAX_PROCESSES','LOG_ARCHIVE_MIN_SUCCEED_DEST',
 'LOG_ARCHIVE_TRACE','LOG_FILE_NAME_CONVERT','STANDBY_FILE_MANAGEMENT','INSTANCE_NAME','LOCAL_LISTENER','DB_UNIQUE_NAME',
 'LOG_ARCHIVE_DEST_n','LOG_ARCHIVE_DEST_STATE_n') order by name;

Result in primary DEMO11:

NAME                           ISDEFAULT DISPLAY_VALUE
------------------------------ --------- --------------------------------------------------------------------------------
archive_lag_target             TRUE      0
db_file_name_convert           FALSE     DEMO12, DEMO11
db_unique_name                 TRUE      DEMO11
instance_name                  TRUE      DEMO11
local_listener                 TRUE
log_archive_dest_1             TRUE
log_archive_dest_2             TRUE
log_archive_dest_state_1       TRUE      enable
log_archive_dest_state_2       TRUE      enable
log_archive_format             TRUE      %t_%s_%r.dbf
log_archive_max_processes      TRUE      4
log_archive_min_succeed_dest   TRUE      1
log_archive_trace              TRUE      0
log_file_name_convert          FALSE     DEMO12, DEMO11
standby_file_management        TRUE      MANUAL

Result in standby DEMO12:

NAME                           ISDEFAULT DISPLAY_VALUE
------------------------------ --------- --------------------------------------------------------------------------------
archive_lag_target             TRUE      0
db_file_name_convert           FALSE     DEMO11, DEMO12
db_unique_name                 FALSE     DEMO12
instance_name                  TRUE      DEMO12
local_listener                 TRUE
log_archive_dest_1             TRUE
log_archive_dest_2             TRUE
log_archive_dest_state_1       TRUE      enable
log_archive_dest_state_2       TRUE      enable
log_archive_format             TRUE      %t_%s_%r.dbf
log_archive_max_processes      TRUE      4
log_archive_min_succeed_dest   TRUE      1
log_archive_trace              TRUE      0
log_file_name_convert          FALSE     DEMO11, DEMO12
standby_file_management        TRUE      MANUAL

Ok, now I’ll create my Data Guard Broker configuration and check the properties that have been imported from my instances configuration.

DGMGRL> CREATE CONFIGURATION demo11 AS PRIMARY DATABASE IS demo11 CONNECT IDENTIFIER IS '//vm111/DEMO11';
Configuration "demo11" created with primary database "demo11"
DGMGRL> ADD DATABASE demo12 AS CONNECT IDENTIFIER IS '//vm112/DEMO12';
Database "demo12" added
DGMGRL> ENABLE CONFIGURATION demo11;
Enabled.

Note that I’m using Easy Connect connection strings only on labs. In production, having a tnsnames.ora alias is the best practice.

Let’s check the properties of the standby:

DGMGRL> SHOW DATABASE demo12;
Database - demo12
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          36 minutes 13 seconds (computed 1 second ago)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    DEMO12
  Properties:
    DGConnectIdentifier             = '//vm112/DEMO12'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'DEMO11, DEMO12'
    LogFileNameConvert              = 'DEMO11, DEMO12'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM112)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DEMO12_DGMGRL)(INSTANCE_NAME=DEMO12)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

You can see that the properties from the table above have been set according to the initialization parameters (for example the file name converts that are not the defaults).

They have been imported and now they are managed by the broker: you must use the broker to change them. And you can see that the broker set all of them in the spfile even for those that were defaults. Here is what has been run on DEMO12 by the broker when enabling the configuration:

ALTER SYSTEM SET log_archive_config='dg_config=(demo11)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DEMO12';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='DEMO12';
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_file_name_convert='DEMO11','DEMO12' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='DEMO11','DEMO12' SCOPE=SPFILE;
ALTER SYSTEM SET fal_server='//vm111/DEMO11' SCOPE=BOTH;

You can see them in alert.log

You see that LOG_ARCHIVE_DEST_1 has been set as well, but it has not been read to get the value. We will see it in Part II. Only the parameters I’ve listed in the table above are read when adding a database to the configuration. This is very important to know if you remove and re-create the broker configuration.

Parameters read dynamically

There are others parameter that have been read and are not changed by the broker. The instance name comes from INSTANCE_NAME. And the StaticConnectIdentifier is built from LOCAL_LISTENER and DB_UNIQUE_NAME. If the parameters are defaults (not set manually) then the broker properties will adapt when those defaults are changed (when instance is started on another node of the cluster for example).

Parameters mismatch

What happens if you change manually one of the parameters I’ve listed above? No error, but inconsistent properties.
For example, I change the archive lag target in DEMO12:

SQL> alter system set archive_lag_target=60 scope=memory;
System altered.

The property is still at 0 for the broker:

DGMGRL> SHOW DATABASE VERBOSE demo12;
Database - demo12
...
  Instance(s):
    DEMO12
      Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
  Properties:
...
    ArchiveLagTarget                = '0'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
...

But I have a warning and I can check those inconsistent properties:

DGMGRL> SHOW DATABASE demo12 InconsistentProperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
          DEMO12     ArchiveLagTarget                   60                    0                    0

Here I can compare the instance parameters (memory and spfile) with broker property.

Have you noticed the InconsistentLogXptProps above? In next part we will see how LOG_ARCHIVE_DEST parameters are managed by Data guard Broker.