This was first published on https://blog.dbi-services.com/dataguard-broker-properties-part-i-imported-parameters (2015-08-23)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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.
Broker property | Instance parameter |
---|---|
ArchiveLagTarget | ARCHIVE_LAG_TARGET |
DbFileNameConvert | DB_FILE_NAME_CONVERT |
LogArchiveFormat | LOG_ARCHIVE_FORMAT |
LogArchiveMaxProcesses | LOG_ARCHIVE_MAX_PROCESSES |
LogArchiveMinSucceedDest | LOG_ARCHIVE_MIN_SUCCEED_DEST |
LogArchiveTrace | LOG_ARCHIVE_TRACE |
LogFileNameConvert | LOG_FILE_NAME_CONVERT |
StandbyFileManagement | STANDBY_FILE_MANAGEMENT |
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.
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).
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 0Here 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.
Hello thank you for this very clear post, like all the others you provided. That’s right it’s important to understand this connection between dg broker and instance.