11g Dynamic Setting of Oracle Data Guard Oracle 11g New Features Tips by Burleson Consulting June 29, 2008

Oracle 11g SQL New Features Tips

To improve manageability of a logical standby it is now possible to configure specific SQL Apply parameters without requiring SQL Apply to be restarted. 

The DBMS_LOGSTDBY.APPLY_SET package is used to control just about everything in a dynamic fashion.

One common use is to control the amount of SGA available to SQL Apply.  By default SQL Apply may use one quarter of the shared pool.  To dynamically alter this:

execute dbms_logstdby.apply_set('max_sga', 20);

The DBMS_LOGSTDBY.APPLY_UNSET package can be used by the Remote DBA to reset parameters back to their defaults.

Compression

Data Guard ships REDO to the standby system, either synchronously or asynchronously, as quickly as transactions are committed using either the Log Writer process or ARCH transport.  Oracle 11g is able to compress these logs being sent over to the standby server there by improving performance.  This is breakthrough watershed functionality.  To enable compression the following parameter can be changed:

log_archive_dest_x='service=bei sync compression=enable'
log_archive_dest_state_x=enable

Corruptions

Oracle 11g also adds a new feature which prevents corruptions, or lost writes, from the master database to propagate over to the standby database.  The storage industry seldom speaks of these lost writes.  While it rarely happens it causes many headaches for the Remote DBA (especially if they don't have good backups).  These "lost writes" occur when a storage subsystem acknowledges the completion of a block write in the database, when in fact the write did not occur in the persistent storage. 11g can now detect these lost writes.

Security & Auditing Improvements

Maintaining security and auditing in a replicated environment is now easier with Oracle.  Oracle 11g adds new features to the database to enable Logical Standby to automatically replicate Virtual Private Database (aka Row Level Security) and Fined Grained Auditing policies from the primary DB to the standby DB. 

For the replication of VPD and FGA to work both the primary and standby need to be running a compatibility setting of 11.1:

SQL> alter system set compatible='11.1.0' scope=spfile;

When a VPD procedure is performed against and object on the primary, additional data is captured in the redo.  This redo is then shipped to the Logical Standby where is it reconstructed and executed, thus saving the Remote DBA from having to manually perform these steps.

Redo Transport Authentication

In previous versions the Remote DBA was required to keep an up to date password file on each physical and standby database.  Anytime a user with the SYSRemote DBA or SYSOPER changed their password, the Remote DBA would have to update each physical and standby database. 

Oracle 11g gives administrators the option of using SSL authentication for REDO transport.  In order to use the new SSL option for REDO transport the databases must be members of the same (OID) enterprise domain.  The Remote DBA must enable current user database links between the databases within the common enterprise domain in OID.  Additionally the LOG_ARCHIVE_DEST_n, FAL_SERVER (fetch archive log), and FAL_CLIENT initialization parameters need to use Oracle Net connection descriptors configured for SSL and an Oracle wallet needs setup.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr. You can buy it direct from the publisher for 30% off.