Oracle Database 11g: New Features in DataGuard

Active Data Guard

In Oracle Database 10g and below you could open the physical standby database for read-only activities, but only after stopping the recovery process.

In Oracle 11g, you can query the physical standby database in real time while applying the archived logs. This means standby continue to be in sync with primary but can use the standby for reporting.

Let us see the steps now..

First, cancel the managed standby recovery:

SQL> alter database recover managed standby database cancel;

Database altered.

Then, open the database as read only:

SQL> alter database open read only;

Database altered.

While the standby database is open in read-only mode, you can resume the managed recovery process.

SQL> alter database recover managed standby database disconnect;

Database altered.


Snapshot Standby

In Oracle Database 11g, physical standby database can be temporarily converted into an updateable one called Snapshot Standby Database.

In that mode, you can make changes to database. Once the test is complete, you can rollback the changes made for testing and convert the database into a standby undergoing the normal recovery. This is accomplished by creating a restore point in the database, using the Flashback database feature to flashback to that point and undo all the changes.

Steps:

Configure the flash recovery area, if it is not already done.

SQL> alter system set db_recovery_file_dest_size = 2G;

System altered.

SQL> alter system set db_recovery_file_dest= '+FRADG';

System altered.

Stop the recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

Convert this standby database to snapshot standby using command

SQL> alter database convert to snapshot standby;

Database altered.

Now recycle the database

SQL> shutdown immediate
...

SQL> startup

ORACLE instance started.

Database is now open for read/write operations

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

After your testing is completed, you would want to convert the snapshot standby database back to a regular physical standby database by following the steps below

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

SQL> startup mount

...
Database mounted.

SQL> alter database convert to physical standby;

Database altered.

Now shutdown, mount the database and start managed recovery.

SQL> shutdown

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.

Start the managed recovery process

SQL> alter database recover managed standby database disconnect;

Now the standby database is back in managed recovery mode. When the database was in snapshot standby mode, the archived logs from primary were not applied to it. They will be applied now.

In 10g this can be done by following steps .. DR failover test with flashback

Redo Compression

In Oracle Database 11g you can compress the redo that goes across to the standby server via SQL*Net using a parameter compression set to true. This works only for the logs shipped during the gap resolution. Here is the command you can use to enable compression.

alter system set log_archive_dest_2 = 'service=STDBYDB LGWR ASYNC
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=STDBYDB compression=enable'