Oracle Database 11g Release 2 Data Guard deployment using Oracle Grid Control 10.2.0.5

Oracle Database 11g Release 2 Data Guard deployment using Oracle Grid Control 10.2.0.5

This article describes the creation of a standby database using Oracle Database 11g Release 2 and the Oracle Grid Control on the same server, how to perform a switchover from primary to standby database and a switchover from the ex-standby to the original primary. We will look at Data Guard configuration verification using OEM Grid Control and how to reconfigure the Data Guard for example set redo transport to SYNCH and setting up an active Data Guard open for read only. The article assumes the following software components are present, installed and configured.

• Grid Control (10.2.0.5). An example installation can be found in previous posts.
  
• A primary database server, with Oracle Database 11g Release 2 software installed and an the existing database is present as a primary.
  
• In this article we look at creating a standby database with data guard on the same server for testing. To deploy the concept in real life the following prerequisites needs to be met.
  
  • All servers need to have an Oracle EM Grid Control agent install
    
  • All servers need to have the binaries (software only) for Oracle 11g R2 installed.
    

With this setup in place, the standby database is created and managed from Oracle Grid Control as follows.

• Standby Database Creation
  
• Switchover
  
• Data Guard Verification
  
• Data Guard Re-configuration
  

Standby Database Creation

As the Oracle 11g R2 databases are configured to listen to port 1522 we need to set up the local_listener initialization parameter on the existing Oracle 11g R2 database that will be designated for a primary database..

1. Set listener in $TNS_ADMIN/tnsnames.ora as follows.
   

LISTENER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = linuxoel54.gj.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = db11gr2)

)

)

1. The parameter local_listener is a static parameter. After setting the parameter please bounce the instance.
   

SQL>alter system set local_listener = ‘listener’ scope=spfile

SQL>shutdown immediate;

………………………………………………………………..

SQL>startup open;

SQL> show parameter local_listener

 

NAME TYPE VALUE

———————————— ———– ——————————

local_listener string listener

SQL>

LISTENER =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = linuxoel54.gj.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = db11gr2)

)

)

 

Navigate to the “Availability” tab of the primary database server (Targets > Databases > (Primary Database Name) > Availability). Click on the “Add Standby Database” link under the “Data Guard” section of the page.

 

 

On the resulting page, click on the “Add Standby Database” link.

 

Accept the “Create a new physical standby database” option by clicking the “Continue” button.

 

Accept the “Perform an online backup of the primary database” and “Use Recovery Manager (RMAN) to copy the files” options by clicking the “Next” button.

 

Enter the “Primary Host Credentials”, if they have not already been defaulted as a preferred credentials, then click the “Next” button.

 

Enter the name of the standby host and its host credentials, then click the “Next” button.

Since we use a single server for both primary and standby database we keep the $ORACLE_HOME and $TNS_ANMES as the same Oracle 11g R2 binaries are shared and accept the suggested Oracle Optimal Flexible Architecture directory structure, then click the “Next” button.

Enter a “Database Unique Name” and “Target Name” and select the “Use SYSDBA monitoring credentials” option, then click the “Next” button. Notice the “Use Data Guard Broker” option is checked.

If you are happy with the information on the review page, then click the “Finish” button.

The standby database creation process runs as an Enterprise Manager job. The job will be submitted after completion of several preliminary steps. Wait while the first section of processing completes.

Once the basic processing is complete, you are presented with the Data Guard “Setup and Manage” screen. Towards the bottom of the screen is a list of available standby databases, with the new standby database listed. The status of the new database is “Creation in process”. Clicking on this status link gives a breakdown of the current processing.

Expand the “Task: DBClone” node for a more detailed status of the operation.

The image below shows an example of the expanded progress.

When the standby database creation is complete, the “Setup and Manage” screen will display a status of “Normal” for the standby database.

The “Availability” tab now includes several new links in the “Data Guard” section.

Primary database:

SQL> select CONTROLFILE_TYPE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE from v$database;

 

CONTROL PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE

——- ——————– ——————– —————-

CURRENT MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

—————-

db11gr2

 

SQL>

Standby database:

SQL> select CONTROLFILE_TYPE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE from v$database;

 

CONTROL PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE

——- ——————– ——————– —————-

STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

—————-

Db11gr2s

 

SQL>

Switchover

To perform a switchover, navigate to the “Setup and Manage” screen, select the standby server you wish to become the new primary server, then click the “Switchover” button.

Accept the “Swap Monitoring Settings” and “Transfer Jobs” options by clicking the “OK” button.

Wait while the switchover processing takes place.

Once complete, the “Setup and Manage” screen shows the primary and standby roles have been reversed.

Lets perform a switchover to db11gr2 and have make db11gr2 a primary database. Select db11gr2 and press Switchover button. Select ‘Swap Monitoring Settings’ and ‘Transfer Jobs’ and press OK.

Data Guard Verification

Select ‘Verify configuration’

Wait for the OEM Grid Control to perform checks of the various databases settings.

Upon successful completion of the verification the detailed results are displayed. Press OK to continue.

We are displayed the screen below.

Data Guard re-configuration

Let’s look at the features of OEM Grid Control for reconfiguring Data Guard. Select the link under Data Guard status.

Select Redo Apply Services and ‘Apply on’ and ‘Enable Read real-time query’ and press OK.

Wait until the database property changes get applied.

Lets’ change the redo transport mode to SYNCH. Press Apply to continue.

Wait until configuration completes.

After completion we will notice that the changes have been implemented.

Conclusion

I this article we looked at how easy and cost effective is to setup a Data Guard with OEM Grid Control. We performed switchover to the standby database and from new primary database to the original database using OEM Grid Control. We also refreshed how to use the OEM Grid Control for Data Guard verification. We covered the additional reconfiguration of Data Guard by setting a SYNH redo log transport and setting an active standby database opened in read only mode while the redo keep on being applied.