Posts Tagged Data Guard

Active Data Guard – what does it mean?

There are misconceptions and half-truths about that term that I see time after time again in forums, postings and comments.

Some people think that Active Data Guard is a fancy marketing term for Standby Databases in Oracle. Wrong, that is just plain Data Guard :-)

Most people think that Active Data Guard means that a Physical Standby Database can be used for queries while it is still applying redo. Not the whole truth, because that is just one featureReal-Time Query – which is included in the Active Data Guard option.

Active Data Guard is an option, coming with an extra charge. Active is supposed to indicate that you can use the standby database for production usage – it is not just waiting for the primary database to fail.

In 11g, Active Data Guard includes three features:

  • Real-Time Query
  • Automatic Block Media Recovery
  • Block Change Tracking on the physical standby

In 12c, Active Data Guard got even more enhanced and includes now the features:

  • Real-time Query
  • Automatic Block Media Recovery
  • Block Change Tracking on the physical standby
  • Far Sync
  • Real-Time Cascade
  • Global Data Services
  • Application Continuity
  • Rolling Upgrade using DBMS_ROLLING

The bad news is that many of the 12c Data Guard New Features require Active Data Guard

About these ads

,

Leave a comment

Parameter to prevent license violation with Active Data Guard

Although Real-Time Query is a great feature, it requires the Active Data Guard option to be licensed AND it is very easy to turn it on. That has been a concern for some customers I encountered. Now I realized that we have an undocumented parameter to prevent exactly that. Thanks to Marc, who mentioned the MOS Note 1436313.1 in a recent comment!

I am here connected to a Physical Standby database, running on 11.2.0.1 and don’t want to use Real-Time Query. The startup command would normally trigger the database to open READ ONLY, together with a Data Guard Broker configuration, Real-Time Query would be started. Not any more:

SQL> alter system set "_query_on_physical"=false scope=spfile;                             

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size		    2214936 bytes
Variable Size		  314573800 bytes
Database Buffers	  201326592 bytes
Redo Buffers		    3821568 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> host oerr ora 16669
16669, 00000, "instance cannot be opened because the Active Data Guard option is disabled"
// *Cause:  The attempt to open the instance failed because the Active Data
//          Guard option was not enabled and Redo Apply was either running
//          or was about to be started by the Data Guard broker. 
// *Action: Stop Redo Apply or set the database state to APPLY-OFF and then 
//          open the database.

It is still possible to open the Physical Standby READ ONLY when the MRP background process is stopped:

DGMGRL> edit database physt set state=apply-off;
Succeeded.
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Only drawback is that it is an undocumented parameter, so you should confirm with Oracle Support that it is okay to use it in your case.
Conclusion: There is an easy way to prevent license violation by accident with Real-Time Query. Don’t believe it, ask Oracle Support :-)

,

9 Comments

How to add a Logical Standby to an existing #Oracle Data Guard Configuration

The proper way to add a Logical Standby database when the to be converted Physical Standby is already part of a 12c Data Guard configuration is a bit tricky. This is how my configuration looks initially:

DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
  prima - Primary database
    sbdb  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now I want to convert sbdb into a Logical Standby database and have the Data Guard Broker manage it. Redo Apply needs to be stopped on the Physical Standby now:

DGMGRL> edit database sbdb set state=apply-off;
Succeeded.
DGMGRL> show database sbdb;

Database - sbdb

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-OFF
  Transport Lag:     0 seconds (computed 0 seconds ago)
  Apply Lag:         0 seconds (computed 0 seconds ago)
  Apply Rate:        (unknown)
  Real Time Query:   OFF
  Instance(s):
    sbdb

Database Status:
SUCCESS

Important point here is that the Redo Transport is not stopped, just the Apply. Now I create the Logical Standby Dictionary on the Primary – no mistake possible here because the Standby is not opened READ WRITE. After that, I convert sbdb into a Logical Standby database. Unlike a Physical Standby database which has always the same DB_NAME as the Primary, it gets its own database name:

SQL> exec dbms_logstdby.build

PL/SQL procedure successfully completed.

SQL> connect sys/oracle@sbdb as sysdba
Connected.
SQL> alter database recover to logical standby sbdb;

Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             364907944 bytes
Database Buffers          146800640 bytes
Redo Buffers                7938048 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
SBDB      1219633322

Now I encounter an expected problem when I try to remove sbdb from the configuration that can be resolved easy:

DGMGRL> remove database sbdb;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.
DGMGRL> edit configuration set protection mode as maxperformance;
Succeeded.
DGMGRL> remove database sbdb;
Removed database "sbdb" from the configuration

The next problem took me some more time to resolve.

DGMGRL> add database sbdb as connect identifier is sbdb;
Error: ORA-16803: unable to query a database table or fixed view

Failed.

The solution here is (for me at least) a bit counter-intuitive: Logical Standby Apply needs to be running. That is odd in so far as the Broker way to do that is not yet possible. Instead (on the Standby):

SQL> alter database start logical standby apply immediate;

Database altered.

Now the new Logical Standby can be added:

DGMGRL> add database sbdb as connect identifier is sbdb;
Database "sbdb" added
DGMGRL> enable database sbdb;
Enabled.

The previous remove also removed the database property logxptmode, which was sync. Therefore:

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.
DGMGRL> edit database sbdb set property logxptmode=sync;
Property "logxptmode" updated
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Databases:
  prima - Primary database
    sbdb  - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Succeeded in the end. Hope you find it useful when you ever encounter this task yourself. As always: Don’t believe it, test it! :-)

5 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,430 other followers

%d bloggers like this: