Let the Data Guard Broker control LOG_ARCHIVE_* parameters!

When using the Data Guard Broker, you don’t need to set any LOG_ARCHIVE_* parameter for the databases that are part of your Data Guard configuration. The broker is doing that for you. Forget about what you may have heard about VALID_FOR – you don’t need that with the broker. Actually, setting any of the LOG_ARCHIVE_* parameters with an enabled broker configuration might even confuse the broker and lead to warning or error messages. Let’s look at a typical example about the redo log transport mode. There is a broker configuration enabled with one primary database prima and one physical standby physt. The broker config files are mirrored on each site and spfiles are in use that the broker (the DMON background process, to be precise) can access:

Data Guard Broker: OverviewWhen connecting to the broker, you should always connect to a DMON running on the primary site. The only exception from this rule is when you want to do a failover: That must be done connected to the standby site. I will now change the redo log transport mode to sync for the standby database. It helps when you think of the log transport mode as an attribute (respectively a property) of a certain database in your configuration, because that is how the broker sees it also.

 

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> edit database physt set property logxptmode=sync;
Property "logxptmode" updated

In this case, physt is a standby database that is receiving redo from primary database prima, which is why the LOG_ARCHIVE_DEST_2 parameter of that primary was changed accordingly:

[oracle@uhesse1 ~]$ sqlplus sys/oracle@prima as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 30 17:21:41 2014

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 and Data Mining options

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service="physt", LGWR SYNC AFF
						 IRM delay=0 optional compressi
						 on=disable max_failure=0 max_c
						 onnections=1 reopen=300 db_uni
						 que_name="physt" net_timeout=3
						 0, valid_for=(all_logfiles,pri
						 mary_role)

Configuration for physt

The mirrored broker configuration files on all involved database servers contain that logxptmode property now. There is no new entry in the spfile of physt required. The present configuration allows now to raise the protection mode:

DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.

The next broker command is done to support a switchover later on while keeping the higher protection mode:

DGMGRL> edit database prima set property logxptmode=sync;
Property "logxptmode" updated

Notice that this doesn’t lead to any spfile entry; only the broker config files store that new property. In case of a switchover, prima will then receive redo with sync.

Configuration for primaNow let’s do that switchover and see how the broker ensures automatically that the new primary physt will ship redo to prima:

 

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
    prima - Primary database
    physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to physt;
Performing switchover NOW, please wait...
New primary database "physt" is opening...
Operation requires shutdown of instance "prima" on database "prima"
Shutting down instance "prima"...
ORACLE instance shut down.
Operation requires startup of instance "prima" on database "prima"
Starting instance "prima"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "physt"

All I did was the switchover command, and without me specifying any LOG_ARCHIVE* parameter, the broker did it all like this picture shows:

Configuration after switchoverEspecially, now the spfile of the physt database got the new entry:

 

[oracle@uhesse2 ~]$ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 14 15:43:41 2014

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 and Data Mining options

SQL> show parameter log_archive_dest_2

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service="prima", LGWR SYNC AFF
						 IRM delay=0 optional compressi
						 on=disable max_failure=0 max_c
						 onnections=1 reopen=300 db_uni
						 que_name="prima" net_timeout=3
						 0, valid_for=(all_logfiles,pri
						 mary_role)

Not only is it not necessary to specify any of the LOG_ARCHIVE* parameters, it is actually a bad idea to do so. The guideline here is: Let the broker control them! Else it will at least complain about it with warning messages. So as an example what you should not do:

[oracle@uhesse1 ~]$ sqlplus sys/oracle@prima as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 14 15:57:11 2014

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 and Data Mining options

SQL> alter system set log_archive_trace=4096;

System altered.

Although that is the correct syntax, the broker now gets confused, because that parameter setting is not in line with what is in the broker config files. Accordingly that triggers a warning:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
    physt - Primary database
    prima - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database prima statusreport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
               prima    WARNING ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting

In order to resolve that inconsistency, I will do it also with a broker command – which is what I should have done instead of the alter system command in the first place:

DGMGRL> edit database prima set property LogArchiveTrace=4096;
Property "logarchivetrace" updated
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
    physt - Primary database
    prima - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Thanks to a question from Noons (I really appreciate comments!), let me add the complete list of initialization parameters that the broker is supposed to control. Most but not all is LOG_ARCHIVE*

LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
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

,

  1. #1 by Noons on October 15, 2014 - 01:00

    Excellent post, Uwe. I’m having a bit of a tussle at work between some folks who want to do all by command line, while I much prefer to use DG Broker. But it’s not clear from the doco – or else I’m not reading it properly – what can be or should be controlled from it. Do you know of any other info on this that you could share?

  2. #2 by Uwe Hesse on October 15, 2014 - 09:34

    Noons, thank you for that question! It made me research it myself, which is what I wanted to do time and time again before but never really did 🙂
    Here is a list
    http://docs.oracle.com/database/121/DGBKR/dbpropref.htm#DGBKR810

    Most of the broker properties you see with SHOW DATABASE VERBOSE are related to LOG_ARCHIVE* parameters, though – therefore the headline

  3. #3 by Noons on October 15, 2014 - 10:54

    Ah-hah! Excellent! Thanks heaps.

  4. #4 by mundaoloco on December 4, 2014 - 14:46

    Uwe, for the first replication of active database I need to set these parameters.

    You are saying that I can use broker to actually create the physical standby? Or I’ll set these parameters just for the first replication and then the broker assumes any changes?

    Really great your site. really good one

  5. #5 by Uwe Hesse on December 4, 2014 - 18:25

    mundaoloco, you don’t have to set any LOG_ARCHIVE* parameter at all. You create the standby with RMAN first (dgmgrl cannot do that). Then you add it to the broker configuration and enable the configuration. During that, the broker sets all required LOG_ARCHIVE* parameters like I demonstrated here: https://uhesse.com/2013/07/08/the-data-guard-broker-why-it-is-recommended/

  6. #6 by Paul Dubar on August 24, 2015 - 07:50

    Hi Uwe,
    I had a workign standby, and did switchiver just fine….however trying to switchback failed.
    It appears that the broker configuration has gotten two entries for archive_log_dest values, which is evident when I stop and start the database resource
    PRCR-1079 : Failed to start resource ora.istsg03q_as01.db
    CRS-5017: The resource action “ora.istsg03q_as01.db start” encountered the following error:
    ORA-16033: parameter LOG_ARCHIVE_DEST_8 destination cannot be the same as parameter LOG_ARCHIVE_DEST_1 destination
    . For details refer to “(:CLSN00107:)” in “/app/oragrid/product/11.2.0.4/log/auq4274l/agent/crsd/oraagent_oracle//oraagent_oracle.log”.

    CRS-5017: The resource action “ora.istsg03q_as01.db start” encountered the following error:
    ORA-16033: parameter LOG_ARCHIVE_DEST_8 destination cannot be the same as parameter LOG_ARCHIVE_DEST_1 destination
    . For details refer to “(:CLSN00107:)” in “/app/oragrid/product/11.2.0.4/log/auq4272l/agent/crsd/oraagent_oracle//oraagent_oracle.log”.

    CRS-2674: Start of ‘ora.istsg03q_as01.db’ on ‘auq4274l’ failed
    CRS-2674: Start of ‘ora.istsg03q_as01.db’ on ‘auq4272l’ failed
    CRS-2632: There are no more servers to try to place resource ‘ora.istsg03q_as01.db’ on that would satisfy its placement policy

    If i create a pfile from the spfile, remove the offending entry and then recreate the spfile, on startup, the two entries get reinstated – is this the broker? It seems so, since if I start the instance with sqlplus pointing to pfile it starts wihtout error.

    So how then do I get rid of the offending duplicate entry on the standby that is in the broker config file? Did the broker create log_archive_dest_1 automatically on switchover (despite there already being an entry for log_archive_dest_8 – I think this entry omitted db_unique_name property in error, which could be the culprit resulting in the duplication).

  7. #7 by Max on August 30, 2016 - 22:33

    Hello –

    I know this is a old post, but how does one set “LOG_ARCHIVE_DEST_n” parameter thru dgmgrl ?

    there is no property called LOG_archive_dest for example like “edit database prima set property LogArchiveTrace=4096;”

  8. #8 by Uwe Hesse on September 1, 2016 - 10:24

    Max, the broker sets LOG_ARCHIVE_DEST_2 according to what you configure for the standby database implicitly. For example you configure LogXptMode=sync for database physt, this will be translated to LOG_ARCHIVE_DEST_2=’SERVICE=PHYST LGWR SYNC AFFIRM’ in the spfile of prima if prima is in primary role. LOG_ARCHIVE_DEST_n contains many parameters that are being specified as various different properties in the broker configuration.

  9. #9 by samjaz on January 19, 2017 - 03:42

    Hi Uwe thanks for sharing with us the article
    can you please explain to us the Binding impact on both the primary and standby and its correlation with the protection mode
    Cheers ,
    Sam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: