Data Guard Command-Line Interface (DGMGRL)

In this article we are going to learn, Data Guard Command-Line Interface (DGMGRL).


Overview:

This article is a comprehensive guide for the use of Data Guard Command-Line Interface (DGMGRL). The Data Guard command-line interface (DGMGRL) enables us to manage a Data Guard broker configuration and its databases directly from the command line.
Data Guard Broker eliminates the need for the complex commands that are needed during role transitions – such as, switchover between a primary and a standby database.

Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official Data Guard Command-Line Interface Reference.

http://docs.oracle.com/cd/E11882_01/server.112/e17023/dgmgrl.htm 

Objective of the Article:

By the time we finish this article, we should be able to understand the following:

(1)How to utilize Data Guard Command Line Interface (DGMGRL).

(2)Performing switchover by DGMGRL utility.

Names and Parameter Values Used:

	Node1 (Primary)	Node2 (Standby)
Machine Name	DG1	DG2
IP Address	192.168.1.100	192.168.1.101
Oracle Database Version	11g Ent. R2(11.2.0.1)- 64 bit	11g Ent. R2(11.2.0.1)- 64 bit
DB Service Name	orcl	stdby
Oracle Software Owner	O/S User – oracle	O/S User – oracle

Prerequisites for Data Guard Broker Configuration:

We have to set initialization parameter DG_BROKER_START to a value of TRUE on both databases (primary and standby) so that Data Guard Broker background process (DMON) would start automatically whenever a Data Guard configuration was successfully implemented.

For setting "DG_BROKER_START" parameter to "TRUE", connect to the database and fire the below command (Do these from both the Nodes).
Do these from node 1:

[oracle@dg1 ~]export ORACLE_SID=orcl
[oracle@dg1 ~]sqlplus sys/sys@orcl as sysdba

SQL> select name , db_unique_name , database_role from v$database ;

SQL> show parameter dg_broker_start ;

If the output shows "FALSE", then we have to set it to "TRUE".

SQL> alter system set dg_broker_start = TRUE ;

Similarly do these activities from the second node (node2).

[oracle@dg2 ~]sqlplus sys/sys@stdby as sysdba

SQL> select name , db_unique_name , database_role from v$database ;

SQL> show parameter dg_broker_start ;

If the output shows "FALSE", then we have to set it to "TRUE".

SQL> alter system set dg_broker_start = TRUE ;

We must have SYSDBA privileges to use the Data Guard command-line interface. So do not include "AS SYSDBA" on the CONNECT command because SYSDBA is the default setting for this command.

 Data Guard Broker configuration:

Start the Data Guard Command-Line Interface:

From the primary node (node1), open the terminal and connect to "DGMGRL" as the "SYS" user. As shown below:

[oracle@dg1 ~]dgmgrl
Then we will get the dgmgrl prompt. For connecting to the database, type:
DGMGRL> connect sys
Then we will be asked for password, Enter the correct password, then we will get connected to the database.

Create Data Guard Broker configuration for the primary database:

For creating Data Guard Broker configuration for the primary database (orcl), we will use "CREATE CONFIGURATION" command to initialize a Data Guard Broker configuration named "DG_orcl".
For doing so, type the following inside the DGMGRL prompt:

DGMGRL> CREATE CONFIGURATION 'DG_orcl' AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl;

 For all the screenshots , please visit the link::: http://www.sensehaze.com/mydata/resources_section/dataguard/dgmgrl/index.php

For confirming its creation, type "SHOW CONFIGURATION" in the "DGMGRL" prompt.

DGMGRL> SHOW CONFIGURATION ;

Add standby database(s) to the Data Guard Broker configuration:

To add a standby database to the current configuration, we will use "ADD DATABASE" command.
For doing so, type the following inside the DGMGRL prompt:

DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS stdby;

For verifying the configuration, type "SHOW CONFIGURATION" in the "DGMGRL" prompt.

DGMGRL> SHOW CONFIGURATION ;

Enable the Data Guard Broker configuration:

For enabling Data Guard Broker configuration, issue the "ENABLE CONFIGURATION" command to activate the configuration.

Verify the successful activation of the primary and standby database(s) by using "SHOW DATABASE" command.

Perform a Switchover using Data Guard Broker:

Once all the above activities are completed successfully , we can perform siwtchover from DGMGRL prompt by issuing only one command "SWITCHOVER TO target_standby_database ;".
DGMGRL> SWITCHOVER to stdby ;

After execution of the above command, our primary database should be "stdby"(Node2) and our standby database should be "orcl"(Node1). Lets verify it by connecting to the databases and querying the view "V$DATABASE" on all the nodes.

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

To see whether logfiles are being transmitting or not, lets switch logfile. For doing logfile switch, type the below from primary database (node2):

SQL> alter system switch logfile;
SQL> archive log list ;

On the standby database (node1) check archive log list status.
SQL> archive log list ;

For all the screenshots , please visit the link::: http://www.sensehaze.com/mydata/resources_section/dataguard/dgmgrl/index.php

 Perform a Revertback to original Primary Database:

We can Perform a Revertback to original Primary Database from DGMGRL prompt by issuing only one command "SWITCHOVER TO target_database ;".
DGMGRL> SWITCHOVER to orcl ;

After execution of the above command, our primary database should be "orcl"(Node1) again and our standby database should be "stdby"(Node2). Lets verify it by connecting to the databases and querying the view "V$DATABASE" on all the nodes.

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

To see whether logfiles are being transmitting or not, lets switch logfile. For doing logfile switch, type the below from primary database (node1):

SQL> alter system switch logfile;
SQL> archive log list ;

On the standby database (node2) check archive log list status.
SQL> archive log list ;

Convert Standby Database:

We can convert a physical standby database to a snapshot standby database, or revert the snapshot standby database back to a physical standby database byusing DGMGRL utility.

We are going to convert "Physical Standby Database(Node2-stdby)" to "Snapshot Standby Database" by using "DGMGRL" utility.
DGMGRL> CONVERT DATABASE stdby TO SNAPSHOT STANDBY ;

 Similarly we can convert the snapshot standby database to physical standby database.

For all the screenshots , please visit the link::: http://www.sensehaze.com/mydata/resources_section/dataguard/dgmgrl/index.php