CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Sean Howe, Lead CommitDBA DBA

Insuring Active Data Guard with Managed Recovery is Operating

Sean Howe, CommitDBA Lead DBA

January 24, 2014


Problem Description

You’ve setup 11G Active Data Guard or inherited Data Guard from a previous DBA. Is Data Guard near real-time updates being applied to your DR site?

Solution

This document will guide you through verifying that your DR database is receiving near-immediate updates from: the source site. You may refer to Oracle Documentation for the initial setup of Active Data Guard at http://www.oracle.com/us/products/database/options/active-data-guard/overview/index.html

Why do you want to use Oracle’s 11G Active Data Guard? Active Data Guard allows you to open the database in read-only mode, while still receiving updates from the source system. Because of this, it allows you to use this server to offload read-only requests, such as reporting requirements.

Although many operations are allowed in read-only mode, the following operations are not.

  • Any Data Manipulation Language (DML) except for select statements
  • Any Data Definition Language (DDL)
  • Access of local sequences
  • DMLs on local temporary tables

First, you must determine if archivelog is enabled on the primary site. Archive logs are necessary in order to implement Data Guard.

On the primary site issue the following command:

select log_mode from v$database;

LOG_MODE
----------------
ARCHIVELOG

Another method to determine this is as follows:

standby

SQL> archive log list;

Database log mode: Archive Mode

Automatic archival: Enabled

Archive destination: USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence: 445

Next log sequence to archive: 0

Current log sequence: 447


primary

SQL> archive log list;

Database log mode: Archive Mode

Automatic archival: Enabled

Archive destination: USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence: 445

Next log sequence to archive: 447

Current log sequence: 447

If Data Guard is setup, but managed recovery is not enabled, you will still receive updates upon archive log switch. Unfortunately, log switched only occur when the existing redo log is full or when the “alter system switch logfile” is initiated.

SQL> select count(*) From rossassured.temps;

COUNT(*)

----------

64

SQL> insert into rossassured.temps select * from rossassured.temps;

64 rows created.

SQL> commit;

SQL> select count(*) from rossassured.temps;

COUNT(*)

----------

128

On Standby

SQL> select count(*) from rossassured.temps;

COUNT(*)

----------

64

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

Database altered.

SQL> select count(*) from rossassured.temps;

COUNT(*)

----------

128

In the alert log you will see the following after initiating managed recovery:

MRP0: Background Managed Standby Recovery process started (FASTREAD); started logmerger process Thu Nov 29 11:57:26 2012

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 8 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

You can see that Managed Recovery is running by issuing the following command:

SQL> select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby;

PROCESS
-------------
ARCH
ARCH
ARCH
ARCH
RFS
RFS
RFS
RFS
MRP0
STATUS
---------------
CLOSING
CLOSING
CONNECTED
CONNECTED
IDLE
IDLE
IDLE
IDLE
APPLYING LOG
SEQUENCE#
--------------
442
446
0
0
0
0
0
448
448
BLOCK#
-------------
63488
497664
0
0
0
0
0
11194
11194
BLOCKS
-------------
209
511
0
0
0
0
0
1
512000
DELAY_MINS
-------------
0
0
0
0
0
0
0
0
0
9 rows selected.

Summary

Active Data Guard with Managed recovery is Oracle’s solution for viewing data in a Physical Standby database while the database is open, and changes are continuing to be applied. This instance can serve not only as a DR site, but also alleviate crucial i/o and system resources from the production database.

Sean Howe, Sr. Database Specialist

For additional fact gathering on CommitDBA or Foglight, please contact us at 800.734.9304.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today