Clicky Web Analytics

Clicky

Feb
24
Tue
Posted By ritzy on Tuesday, February 24, 2009
52146 Views 13 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 ConceptsPhysical Standby Concepts

Dataguard Broker ConceptsDataGuard 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


 

Categories

Rants & Raves Minimize

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

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

  • Gravatar
    .. Saturday, March 21, 2009 at 8:16 AM
    Re: How to setup DGMGRL, broker with example
    hm.. Thx for clarifying!

  • Gravatar
    .. 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"

    -

    • Gravatar
      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 ;

  • Gravatar
    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).

    • Gravatar
      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

  • Gravatar
    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!

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

  • Gravatar
    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!

  • Gravatar
    Nehpal Singh Rathore Saturday, November 19, 2011 at 4:48 AM

    Really nice article, nice job

    • Gravatar
      bin Monday, August 20, 2012 at 5:14 AM

      Hi Ritzy,
      Thank you for your article. It helped me in configuring DGMGRL. I have a doubt. Do we need to statically register both the standby and primary DB SID's (i mean, SID_DESC under SID_LIST) on both the listener.ora files. I have done this. I have added both primary and standby global names with _DGMGRL in both the listener.ora files. Both the DB's are working fine. I would like to know whether it is required.
      Thanks in advance.

      Bin

      • Gravatar
        Ritzy Wednesday, August 22, 2012 at 10:19 PM

        Yes - so whenever we do switchover/switchback both configs are ready

  • oracleguy.wordpress.com Thursday, November 21, 2013 at 9:23 AM
    via pingback
    dg broker | Oracleguy's Blog

  • andrewfraserdba.com Tuesday, August 09, 2011 at 11:21 AM
    via pingback
    Oracle Data Guard Overview | Andrew Fraser DBA

  • Recommended Oracle DBA Books Minimize

         

    Tag Cloud Minimize


    Archive Posts Minimize
     
    Monthly
      Yearly

      Disclaimer:
      This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

      This posting has nothing to do with my present or past employer.