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

Creating Oracle 11g active standby database from physical standby database

Creating Oracle 11g active standby database from physical standby database

Here in this article we will look at implementing an active standby database that allows as to open the standby database in read only mode and the redo keeps on applying. We assume that we already have a physical standby configuration already in place as shown below.

Primary Database- DB11G

Standby Database- DB11GDG

Now you can bring up the standby database in read-only mode and continue to have the standby database updated from the primary. Here are the steps to achieve that:

1. Stop the managed recovery process on standby database:
In DB11GDG ( Standby database )

SQL > alter database recover managed standby database cancel;
Database altered.

2. Open the DB11GDG – standby database as read-only:
SQL > alter database open read only;
Database altered.

3. Restart the managed recovery process on the standby database:

SQL > alter database recover managed standby database using current logfile disconnect;

Now the standby database is being updated, but it is simultaneously open for read-only access—this is the essence of Oracle Active Data Guard.

4. To test the “active” part of Oracle Active Data Guard, create a table in the primary database:

In Primary database issue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

On Standby databaseissue the following query
SQL > select table_name from dba_tables where table_name = ‘GJ’;
no rows selected

In DB11G database – Primary database
SQL > create table GJ (col_id number(10));

5. After a few seconds, check the existence of the table in the standby database:
SQL > select table_name from dba_tables where table_name = ‘GJ’;

TABLE_NAME
——————————
GJ

The table will be propagated.

The standby database is open in read-only mode and it is applying the logs from the primary database. Voila… This feature enables you to run reports against it without sacrificing the ability to put the standby database into the primary role quickly.

6. To confirm the application of redo logs on the primary database, first switch the log file:
alter system switch logfile;

7. Now observe the alert log of the standby database. Use the automatic diagnostic repository command interpreter (ADRCI) tool, new in Oracle Database 11g:

$ adrci
show alert -tail –f

kcrrvslf: active RFS archival for log 6 thread 1 sequence 15684
RFS[7]: Successfully opened standby log 5: ‘/u01/oradata/DB11GDG/standby02.log’
Media Recovery Log /u01/oradata/DB11GDG/arch/DB11GDG/archivelog/2010_03_10/o1_mf_1_15694_4mk48s9y_.arc
Media Recovery Waiting for thread 1 sequence 15685 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15695 Reading mem 0
Mem# 0: /u01/oradata/DB11GDG/standby02.log

Conclusion

The physical standby database is a physical replica of the primary database, kept in sync by the application of redo log entries from the primary database. With Oracle Active Data Guard and real time apply, the log entries are applied in real time but the primary database does not wait for the redo application to complete, thereby protecting itself from failures or slowdowns in the shipment of log information from the primary to the standby database. Using the Oracle Active Data Guard option, you can open the physical standby database for read-only operations while the managed recovery process is going on. You can offload all reporting applications and the Oracle RMAN backup to the standby database, easing the load on the primary database considerably. Because the standby database is being recovered continuously with real time apply, the standby database can be activated and used immediately in case the primary database fails. This combination of features makes the investment in Oracle Active Data Guard very worthwhile. Credit Arjun, Arup Nanda

March 14, 2010 Posted by | oracle | 8 Comments