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.
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.
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.