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.
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…
- MariaDB Connector/J high availability features testing - May 4, 2017
- Enhanced password strength in MySQL with validate_password plugin - April 12, 2017
- Automatic extended statistics to improve performance - April 6, 2017
- OGG-03517 when moving to Unicode from any other character set - March 10, 2017
- Audit creation and deletion of users for SOX evidences - February 17, 2017
- PL/SQL performance tuning with bulk SQL/binding and parallelism - February 14, 2017