Monday, February 06, 2012

How to setup DGMGRL, broker with example

Feb
24

Tue

Posted By ritzy on Tuesday, February 24, 2009
15854 Views 11 Comments

Before we start, remember that DG broker does not have the ability to create standby and is used for managing the dataguard configuration. Before proceeding with step-by-step instructions on how to set up DG broker, I would recommend you to get comfortable with the concepts first.

Physical Standby Concepts

DataGuard Broker Concepts

The following configuration was tested on RHEL4U2 64-bit with Oracle 10.1.0.5 database.

Pri db_unique_name    =    'TESTPRI'
DG db_unique_name    =    'TESTDG'
Configuration(any name) =    'DG_TEST'
sys password        =    'sys'
Pri conn stg        =    'TESTPRI'

1. Set up init parameters on primary to enable broker

Note: For RAC, ensure dg_broker_config_files are on shared storage and accessible to all the instances.

Note: Broker config files are named as dr1<<db_unique_name>>.dat and dr2<<db_unique_name>>.dat

SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true sid='*';
System altered.

2. Verify if DMON process has started on all the instances of primary. Example:

$ ps -ef|grep dmon|grep -v grep
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR1

$ ps -ef|grep dmon|grep -v grep
oracle 29723 1 0 08:53 ? 00:00:00 ora_dmon_TESTPRIR2

3. Set up init parameters on standby

SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true sid='*';
System altered.

4. GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>> in listener.ora on all instances of both primary and standby.

This is important otherwise you'll have TNS-12154 error during switchover operation.

Example:

SID_LIST_LISTENER_TESTPRI =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = TESTPRIR1)
      (GLOBAL_DBNAME = TESTPRI_DGMGRL)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
    )
)

5. DGMGRL Configuration
5.1Connect
DGMGRL> CONNECT sys/sys
Connected.

5.2Create Configuration
DGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;
Configuration "DG_TEST" created with primary database "TESTPRI".

5.3Verify configuration
DGMGRL> SHOW CONFIGURATION;
Configuration
Name:            DG_TEST
Enabled:         NO
Protection Mode: MaxPerformance
Databases:
    TESTPRI - Primary database

Current status for "DG_TEST":
DISABLED

5.4Verify database; if RAC verify if all instances are validated
DGMGRL> show database 'TESTPRI';
Database
Name:            TESTPRI
Role:            PRIMARY
Enabled:         NO
Intended State: ONLINE
Instance(s):
    TESTPRIR1
    TESTPRIR2

Current status for "TESTPRI":
DISABLED

5.5Add standby database to the configuration
DGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;
Database "TESTDG" added.

5.6Enable the broker
DGMGRL> ENABLE CONFIGURATION;
Enabled.

5.7Verfying again
DGMGRL> SHOW CONFIGURATION;
Configuration
Name:            DG_TEST
Enabled:         YES
Protection Mode: MaxPerformance
Databases:
    TESTPRI - Primary database
    TESTDG - Physical standby database

Current status for "DG_TEST":
SUCCESS

6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
          TESTPRIR2    WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
          TESTPRIR2    WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
      TESTPRIR2     ArchiveLagTarget                    0                                         0
      TESTPRIR2 LogArchiveMaxProcesses                    4                    2                    4

Example
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'

Fix
DGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;

or

SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.

DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'

More commands
DGMGRL> SHOW DATABASE VERBOSE 'dbname';
This will show all property values in detail

DGMGRL> HELP;
List of all broker commands with usage help

Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases

That's it my friends. You are all set to use broker now for managing your dataguard configuration. In my next blog, I'll talk about switchover using DG broker

11 Comments so far ...

Leave a Comment

Mail me on replies to my comment

Mail me on comments to this Post

.. Saturday, March 21, 2009 at 1:10 AM

Re: How to setup DGMGRL, broker with example
Awesome Dude! This article really made things easy & saved time and was remained awed for a sec thinking implementing DG Broker is it so easy ? Its pretty clear in each step....

Simple Q! do we need to use only spfile as read somewhere but i strongly beleive that shudnt be the case... can you clarify ...

Ritzy Saturday, March 21, 2009 at 5:53 AM

Re: How to setup DGMGRL, broker with example
Yes, you do need SPFILE if you wish to manage your datagaurd configuration using broker. It's a mandatory requirement otherwise you could face this error ORA-16797 which means SPFILE is missing in the Data Guard broker environment.

.. Saturday, March 21, 2009 at 8:16 AM

Re: How to setup DGMGRL, broker with example
hm.. Thx for clarifying!

.. Tuesday, March 31, 2009 at 11:29 PM

Re: How to setup DGMGRL, broker with example
Ritzy! could you suggest the remedy for this

DGMGRL> show instance test2
Two or more instances have the name "test2"

-

Ritzy Wednesday, April 01, 2009 at 12:33 AM

Re: How to setup DGMGRL, broker with example
This means your primary and standby instance name is same. Use this command:show instance verbose test2 on database pri_dbname ;show instance verbose test2 on database standby_dbname ;

Reetesh Jain Friday, October 30, 2009 at 3:46 PM

Re: How to setup DGMGRL, broker with example
I have a question, when we do switchover using dgmgrl, do we need to have on a particular box to do so? or switchover command can be used on any node (primary or standby).

Ritzy Friday, October 30, 2009 at 4:34 PM

Re: How to setup DGMGRL, broker with example
dgmgrl is just a command line utlity. Switchover using dgmgrl can be run from any box. In fact, you can run even from a box which is not part of primary-standby configuration as long as connectivity is there

Erika Friday, August 27, 2010 at 1:57 AM

Nice blog - just one note (which I just realized during "action") concerning the "edit database set state" for to stop the logshipping during maintenance on Standby... well, it worked just fine, but how to set it back to "TRANSPORT-ON" ? I found the following on another site:

DGMGRL> edit database 'pri_db' set state='TRANSPORT-ON'; ==> this makes sense, but gives error "ORA-16516 the current state is invalid for the attempted operation". I did at the end do "edit database 'pri_db' set state=online" which worked.

Question: Wouldn't it be enough to just do on both primary and standby "set property 'LogShipping'='OFF'" ???

Thanks & greetings from Belgium!

Ritzy Friday, August 27, 2010 at 7:50 AM

Defer log shipping
Thanks Erika for your response and I'm glad that you found this post useful.

Logshipping property would be used in case we just want to defer log shipping to one of the standby as stated in this post earlier.
SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

State would be needed in case you want to defer all standby databases.
DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases meaning it will just stop shipping logs to any of the standby sites.

The error you got suggests that the role database was in didn't allow that operation. You should verify that.

Vince L Monday, December 06, 2010 at 8:32 AM

I am still getting " Failed to connect to remote database snd_sapsnddg. Error is ORA-12154" when I try to add the standby database. I can tnsping the standby AND I currently have log files going over being processed by the standby. Could you post your sqlnet.ora, tnsnames.ora, and listener.ora files? Or give me a clue how to debug the 12154 message? Thanks!