Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Creating Oracle Data Guard in 11g R2 using Data Guard Broker with DGMGRL

Creating Oracle Data Guard in 11g R2 using Data Guard Broker with DGMGRL

In this post we will look at the creation of a physical standby database and configuring the physical standby database for management and administration with Data guard broker’s utility DGMGRL in oracle 11g R2.

  1. Setup on the primary database to support the role of a primary database. Follow the steps below.

  1. Creating the physical standby database
  2. Create an Oracle Net service name for the physical standby database in tnsnames.ora

  1. Configure an entry for the standby database in listener.ora

  1. Reload the listener on the DR server and prepare the DR site with the oracle password file, new init.ora file and directories for the standby database.

Prepare for the standby creation from the primary database. In order to duplicate the primary database to the standby location we will start the standby database in nomount state and will use RMAN. We invoke the RMAN from the primary site and connect to the primary database as a target and connect to the standby instance as an auxiliary database.

In oracle 11g R2 we will run the script below for the creation of the standby database. Note that from Oracle 11gR2 we can create a standby database either

  • With connection to the primary as a target and to the standby database as an auxiliary without an existing backup of the primary database.
  • Without connection to the primary database with existing backup of the primary database and with connection to the standby database.

In the RMAN script we also will specify some of the standby database init parameters.

Once the standby database is created verify that the redo transport is operational and redo from the primary is received on the standby database and it is archived. The sequence# from the standby database must match the sequence# from the standby database, i.e. there should not be gaps in the sequence#.

To setup and configure Data Guard Broker we need to start the Data Guard Broker processes. In order to start Data Guard Broker processes set the DG_BROKER_START init parameter to TRUE.

  • Data Guard Monitor (DMON)
  • Broker Resource Manager (RSM)
  • Data Guard Net Server (NVSn)
  • DRCn

Edit the listener.ora on both nodes to add a static entry for DGMGRL. This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

After creating the physical standby database create the configuration. After that, add the standby database to the already created broker configuration. Note the difference from 10g where the syntax is add database órclstby’ as connect identifier is orclstby maintained as physical’. In Oracle 11g R2 ‘maintained as physical ‘is not required to be specified. As the created configuration is not enabled by default when created it is required to be enabled explicitly. Enabling the configuration let the Broker start everything up. At the end we will verify the configuration.

Let’s see how to read data from the physical standby database using Active Data Guard option. We will setup an Active Data Guard Mode where the physical standby database is open in read-only mode while the redo still keep on being applied while the physical standby database is opened in read-only mode. We will show how in case of an Active Data Guard while the standby is open in read-only mode the transactions from the primary database get applied on the standby database. All we need to do is to stop the application of redo by disabling the managed recovery process. Then we will open the database in read-only mode. At the end we will resume the application of the redo by enabling and resuming the managed recovery process.

Let’s enable the flashback on the primary and the standby databases as this is a prerequisite for setting fast start failover feature (FSFO). After that we will enable the fast start failover feature.

At the end we will verify the configuration with the commands show below.

Last but not least we will look at how to implement a switchover to the standby database. After the completion of the switchover we will check and verify the changes in the configuration. At the end we will perform a switchover to the original configuration.

Summary

We created a physical standby database using RMAN new feature available in 11g R2. We configure Data Guard Broker to manage and administer the Data Guard configuration.

March 14, 2010 - Posted by | oracle

6 Comments »

  1. In the RAM script we also will specify some of the standby database init parameters

    should be?

    In the RMAN script we also will specify some of the standby database init parameters

    Comment by oracleman consulting | January 13, 2012 | Reply

    • Hi,

      Fixed it.

      Regards,

      Comment by gjilevski | January 13, 2012 | Reply

  2. [...] von Guenadi Jilevski Share this:TwitterFacebookGefällt mir:Gefällt mirSei der Erste dem dies gefällt. Dieser [...]

    Pingback by Erstellung Oracle Data Guard in 11g R2 mt Data Guard Broker und DGMGRL | Peter`s DBA Blog | September 24, 2012 | Reply

  3. Nice Document, but i want to know if we create any table on Primary DB, is that table created on Standby DB.
    Please share your comments on my email mueen_aq@yahoo.com

    Comment by Mueen Alam | May 6, 2013 | Reply

    • Hi,

      If this is a physical standby configuration the table is recreated on the standby. If this is logical standby configuration it depends on the logical standby configurqtion.

      Best Regards,

      Guenadi Jilevski

      Comment by gjilevski | May 30, 2013 | Reply

  4. Hi I am getting below error when ran the RMAN scripts. After that Auxiliary DB became shutdown!

    Can you please help me.

    sql statement: alter system set log_archive_dest_1 = ”service=ABPRODDB ASYNC
    valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE)db_unique_name=ABPRODDB” comment= ”” s
    cope=spfile

    Oracle instance shut down

    released channel: prmy1
    released channel: prmy2
    released channel: prmy3
    released channel: prmy4
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 08/26/2013 00:50:36
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-04006: error from auxiliary database: ORA-28547: connection to server faile
    d, probable Oracle Net admin error

    RMAN> **end-of-file**

    Comment by Khandaker Anwar | August 26, 2013 | Reply


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

Follow

Get every new post delivered to your Inbox.

Join 782 other followers

%d bloggers like this: