DataGuard Broker And its Benefits

The Oracle Data Guard broker  is a distributed  management  framework that automates and centralizes  the creation, maintenance, and   monitoring  of  Data Guard configurations. The  following  describes  some of the operations the broker automates and simplifies 

I.) Adding  additional  new  or  existing (physical, snapshot, logical, RAC or non-RAC)  standby databases to  an existing Data Guard configuration, for a total of one primary database, and from 1 to 30 standby databases(in Oracle 11g) in the same configuration.

II.) Managing  an entire Data Guard configuration, including  all databases, redo transport services, and log apply services, through a client connection to any database in the configuration.

III.) Managing the protection mode for the broker configuration.

IV.) Invoking switchover or failover with a single command to initiate and control complex role changes across all databases in the configuration.

V.) Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.

VI.) Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the redo apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing, and performance tools.

Oracle Data Guard Broker Diagram :  The below diagram will help us to understand Data Guard Broker.

We can perform all management operations locally or remotely through the broker's easy-to-use interfaces: the Data Guard management pages in Oracle Enterprise Manager, which is the broker's graphical user interface (GUI), and the Data Guard command-line interface called DGMGRL.

Benefits of  Data Guard Broker : 

The broker's interfaces improve usability and centralize management and monitoring of the Data Guard configuration.The following benefits are : 

1.) Disaster protection :  By  automating  many of the manual tasks required to configure and monitor  a Data  Guard configuration, the broker enhances  the  high  availability,  data  protection, and  disaster protection capabilities that are inherent in Oracle Data Guard. Access is possible through a client to any system  in the  Data Guard  configuration, eliminating  any  single  point  o  failure. If  the  primary  database fails, the  broker automates  the  process  for  any  one of the standby databases to replace the primary database  and  take  over  production  processing. The  database  availability  that  Data Guard provides makes it easier to protect our data.

2.) Higher availability and scalability :  While  Oracle  Data Guard broker  enhances  disaster  protection by  maintaining  transitionally  consistent  copies  of  the  primary database,  Data Guard,  configured  with Oracle  high  availability solutions such as Oracle Real Application Clusters (RAC) databases.

3.) Automated creation of a Data Guard configuration :  The broker  helps us  to  logically  define  and create  a Data  Guard  configuration  consisting  of  a  primary  database  and  (physical  or  logical, snapshot, R AC or non-RAC)  standby  databases.  The  broker  automatically  communicates  between  the databases  in  a Data Guard configuration using Oracle Net Services. The databases can be local or remote, connected by a LAN or geographically dispersed over a WAN.

4.) Easy configuration of additional standby databases :  After  we  create  a  Data Guard  configuration consisting  of  a  primary  and  a  standby  database,  we can  add  up  to  eight  new  or  existing,  physical, snapshot, or logical standby databases to each Data Guard configuration. Oracle Enterprise Manager provides an Add Standby Database wizard to guide us through the process of adding more databases. 

5.) Simplified, centralized, and extended management : We can issue commands to manage many aspects of the broker configuration. These include:

I.> Simplify the management of all components of the configuration, including the primary and standby databases, redo transport services, and log apply services.

II.>  Coordinate  database  state transitions  and  update  database  properties  dynamically  with  the broker recording the changes in a broker  configuration  file  that  includes  profiles  of  all  the  databases  in  the configuration. The broker  propagates  the  changes  to  all  databases  in  the configuration and their server parameter files.

6.) Simplified switchover and failover operations : The  broker  simplifies  switchovers  and  failovers  by allowing  us  to  invoke  them  using  a  single  key  click  in  Oracle  Enterprise  Manager  or  a  single command  at  the  DGMGRL  command-line interface. Fast-start  failover  can  be configured  to occur  with no data loss or with a configurable amount of data loss.

7.) Built-in monitoring and alert and control mechanisms :  The  broker  provides  built-in  validation that  monitors  the  health  of  all  of  the  databases  in  the configuration. From  any  system  in  the configuration connected to  any   database, we  can capture  diagnostic  information  and detect obvious and subtle  problems  quickly  with  centralized  monitoring,  testing,  and  performance  tools. 

8.) Transparent to application :  Use  of  the broker  is  possible  for  any  database  because  the  broker works  transparently  with  applications;  no  application  code  changes  are  required  to  accommodate a configuration that we manage with the broker.

Relationship of Objects Managed by the Data Guard Broker :

Reference : http://download.oracle.com/docs/cd/B13789_01/server.101/b10822/concepts.htm




Enjoy      :-) 

ORA-16789: Standby Redo Logs Not Configured

This is very common error which occur during the switchover the standby database in Dataguard Broker. In my case when i switchover to standby the error occur as below : 

C:\>dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxxx@noida
Connected.

DGMGRL>  switchover to 'red';
Performing switchover NOW, please wait...
Error: ORA-16789: standby redo logs not configured
Failed.
Unable to switchover, primary database is still "noida"

This error generally occur because the Standby redo logs were not configured for the database.

Therefore,Standby redo logs are required when the redo transport mode is set to SYNC or ASYNC . Hence to solve this we have add standby redolog files on primary database . Below the command to this issue. 

On Primary Database: 

SQL> ALTER DATABASE add standby  LOGFILE GROUP 6  'C:\APP\NEERAJS\ORADATA\NOIDA\REDO06.LOG' size 50m ;
Database altered.

Now it's is not giving any further error .


Enjoy         :-) 

How to Drop/Rename Standby Redolog file in Oracle 11g

While performing the dataguard Broker, we need to drop the standby database while switchover the standby . As it seems an easy task but it is bit tricky . Below are the steps to drop the redolog file from standby database :

On Standby Database : 
SQL> select member,type from v$logfile;
MEMBER                                                                     TYPE                      
----------------------------------                                         -----------
D:\APP\STANDBY\ORADATA\REDO03.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO02.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO01.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO04.LOG      STANDBY    
D:\APP\STANDBY\ORADATA\REDO05.LOG      STANDBY

Here,we have to drop the two standby redolog file .

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Now to solve this issue we have cancel the managed recovery session and set  "standby_file_management"  to manual and drop the standby redolog file  as 

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

SQL> alter system set standby_file_management='MANUAL' ;
System altered.

SQL>alter database drop standby logfile group 4;
Database altered.

SQL>alter database drop standby logfile group 5;
Database altered.

If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as
SQL> alter database clear logfile group n;

Once the standby redologs are dropped then again back to recover the standby.

SQL>alter system set standby_file_management='AUTO' ;
System altered.

SQL> alter database recover managed standby database disconnect from session ;
Database altered.

More detail click here


Enjoy    :-) 

Switchover to Physical Standby Database in Oracle 11g

Once the standby database is configured and works fine then we can switchover to standby database for testing purpose to reduce the primary database downtime .Primary database may need down-time for many  reasons like OS upgradation, Hardwares upgradation  and for many other issues .

Whenever we switchover the primary database to standby database , there is no loss of data during the switchover. Once the maintainance of the primary database is over , then we can again switchover to standby database.

In this scenario , the Primary database is "NOIDA" and standby database is "RED". Here i will switchover the primary database to standby database i.e, from "noida" to "red". Before switching, we should check some prerequisites .

Step 1 :  Verify whether it is possible to perform a switchover 

On the current primary database, query the "switchover_status" column of the V$DATABASE fixed view on the primary database to verify it is possible to perform switchover.

SQL> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO STANDBY

The TO STANDBY value in the "switchover_status" column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the configuration is functioning correctly .  (for example, verify all  "log_archive_dest_n"  parameter values are specified correctly). If the value in the switchover_status column is SESSIONS ACTIVE or FAILED DESTINATION then click here .

Step  2  : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;

Step  3 : Switch the current online redo log file on primary database and verify that it has been appleid 

SQL>alter system switch logfile ;
System altered.

Step  4 : Connect with primary database and initiate the switchover 
C:\>sqlplus sys/xxxx@noida as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file,if necessary.
If we try to perform a switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

SQL>alter database commit to switchover to physical standby with session shutdown ;

The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

Step  5 : Shut down and restart the  primary instance(RED).
SQL>shutdown immediate; 

SQL> startup mount ;

Step  6 : Verify the switchover status in the v$database view.

After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status"  column of the v$database fixed view on the target standby database.

On old Primary database(noida)
SQL> select name,open_mode,db_unique_name from v$database;
NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------       -----------          --------------------         ----------------------
NOIDA     MOUNTED                noida                       TO PRIMARY

On old standby database (RED)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------         ------------         -----------------            ---------------------
NOIDA       MOUNTED               red                          TO PRIMARY

Step 8 : Switch the target physical standby database role to the primary role

We can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following sql statement on the physical standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area        263639040 bytes
Fixed Size                               1373964 bytes
Variable Size                           213911796 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  9  : Check the new primary database(RED) and switch logfile : 

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE

Note :  it's a good idea to perform a log switch on the primary .


SQL> alter system switch logfile;
System altered.

Step 10  :  Open new standby database(Noida) in read-write 

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status  from v$database;

NAME      OPEN_MODE    DB_UNIQUE_NAME     SWITCHOVER_STATUS
------      -------------      ------------------        -----------------------
NOIDA     READ ONLY        noida                        RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select  name,open_mode  from  v$database;
NAME              OPEN_MODE          
---------          ---------------------------------
NOIDA            READ ONLY WITH APPLY

The switchover_status column of v$database can have the following values:

Not Allowed :  Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases. 

Session Active : Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
Switchover Pending : This is a standby database and the primary database switchover request has been received but not processed.

Switchover Latent :  The switchover was in pending mode, but did not complete and went back to the primary database. 

To Primary :  This is a standby database, with no active sessions, that is allowed to switch over to a primary database. 

To Standby :  This is a primary database, with no active sessions, that is allowed to switch over to a standby database. 

Recovery Needed : This is a standby database that has not received the switchover request.

Enjoy   :-) 

ORA-10456: cannot open standby database; media recovery session may be in progress

Once while starting my standby database i found that database is not opening in normal mode. It throws the error-10456 :cannot standby database. 

On performing some R&D and googling comes to the conclusion that this error is generally occurs because a media recovery or RMAN session may have been in progress on a mounted instance of a standby database when an attempt was made to open the standby database. Hence to solve of this issue we have to cancel any conflicting recovery session and then open the standby database.

Here is the issue what i have experienced .

C:\>sqlplus sys/xxxx@red as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 3 12:47:32 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from hr.aa;
select * from hr.aa
                 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select name,open_mode from v$database;
NAME           OPEN_MODE
---------      --------------------
NOIDA          MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

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

SQL> alter database open;
Database altered.

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

Hence, finally we solve the issues.


Enjoy    :-)