Active Data Guard and redo compression

Preamble

Before going further it’s worth to mention that both features required the licensing of an Enterprise option (Active Data Guard and Advanced Compression for Redo Compression). As usual with Oracle Corporation no warning will be displayed and no license file to install…

Active Data Guard allow you to read only query your physical standby for reporting purpose.

Redo compression, as its name stand for, allow you to compress redo to optimize your network and speed up redo transfer to your standby databases (with obviously small CPU overhead). If your sites are far from each others the main bottleneck is most probably the network and not the small amount of CPU that this option will use…

Active Data Guard

When you try to select something on your physical standby you hit below error message:

SQL> SELECT * FROM yjaquier.test;
SELECT * FROM yjaquier.test
                       *
ERROR AT line 1:
ORA-01219: DATABASE NOT OPEN: queries allowed ON fixed tables/views only

And in a way it sounds normal as the database is open in MOUNT state…

If you check at Data Guard Broker (DGB) level:

DGMGRL> show configuration
 
Configuration - DGConfig1
 
  Protection Mode: MaxPerformance
  Databases:
    rac1 - Primary database
    rac2 - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> show database rac2
 
Database - rac2
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac2
 
Database Status:
SUCCESS

To activate Active Data Guard, open your standby database in READ ONLY (no DGB command to do it):

SQL> ALTER DATABASE OPEN read only;
 
DATABASE altered.

Check status in DGB:

DGMGRL> show database rac2
 
Database - rac2
 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    rac2
 
Database Status:
SUCCESS

Now you can issue query on your physical standby:

SQL> SELECT * FROM yjaquier.test;
 
       VAL DESCR
---------- --------------------
         1 One
         2 Two
         3 Three

Obviously updating is still forbidden, to do such things you have to move to logical standby:

SQL> INSERT INTO yjaquier.test VALUES(4,'Four');
INSERT INTO yjaquier.test VALUES(4,'Four')
                     *
ERROR AT line 1:
ORA-16000: DATABASE OPEN FOR read-only ACCESS

To revert back to original state, shutdown your standby database and open it in MOUNT state.

Redo Compression

To activate redo compression there is only one online parameter to modify:

DGMGRL> edit database rac2 set property RedoCompression='enable';
Property "redocompression" updated
DGMGRL> show database verbose rac2 redocompression;
  RedoCompression = 'enable'

I suppose that for a switchover situation the parameter must also be activated on the primary instance.

And that’s it ! Nothing more to do except checking you bought advanced compression Enterprise option that is fortunately bringing many other added value…

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

IT World