Logical Standby: SQL Apply - Oracle 11g

While the physical standby applies redo and archivelog blocks to the disaster recovery database, the logical standby database mines the redo log entries and reexecutes the SQL against the target database. Logical standby databases provide these key benefits:

  • Reports are near real time since SQL Apply can occur while the database is up and running.
  • Reduces the workload on primary database because the logical standby database is updated in almost real time, and reports can be offloaded to the logical standby.
  • Modification of the schema on the target to take advantage of reports. (For example, additional indexes and even bitmapped indexes can be utilized on the logical standby database.)

One restriction that prohibits a company’s acceptance of the logical standby is its datatype limitation. Oracle Database 11g logical standby adds datatype support for XMLType stored as CLOBs. The compatibility parameter must be set to 11.1.0. The logical standby still does not support the following:

  • Bfile
  • Collections (including varrays and nested tables)
  • Multimedia datatypes (including Spatial, Image, and Context)
  • ROWID and UROWID
  • User-defined types
  • LOBs stored as SecureFiles
  • XML stored as binary XML

Support for Transparent Data Encryption (TDE)

TDE supported on the primary database allows encrypted data protection. For Data Guard logical standby TDE support, both the primary and logical standby databases must have compatibility set to 11.1.0. TDE in the context of user-held keys or hardware security modules are not available in Oracle version 11.1 logical standby databases. There are numerous rules to consider when you replicate tables using TDE. First, the wallet created on the primary database must be accessible by the logical standby database to decipher encrypted redo logs. We recommend you copy the wallet file from the primary database to the logical standby database server. If you change the master key on the primary database, you need to recopy the wallet to the logical standby. You should not attempt to rekey the wallet at the logical standby site while logical standby is performing SQL Apply. Rekeying the wallet can cause the SQL Apply process to shut down on the standby site.

Commands associated with opening and closing a wallet are not replicated to the logical standby database. Unless the auto-open wallet is used, the wallet must be manually opened and closed on the logical standby database.

Note :The password for the wallet does not have to be the same. You can have different passwords to open the wallet on the primary site and on the logical standby database.

Encrypt Tables and Columns

If you do plan to rekey the table at the standby site, you should downgrade the guard setting to none and perform the rekey function. Encryption levels and the key at the table and column level on the standby database can be different from the primary database. For example, the Social Security column on the per_all_people_f HR table can be AES256 on the primary database, and the encryption algorithm on the logical standby can be set to AES192. Likewise, the encryption level can be set to none on the standby site also. To rekey the table on the logical standby database, you can use a rekey option command similar to this:

SQL> alter table docs rekey using '3DES168';

Table altered.

Encrypt Tablespaces

Oracle Database 11g Data Guard logical standby supports TDE with tablespace encryption. Similar to column-level TDE support, Oracle requires that you copy the wallet from the primary site to the disaster recovery host. Whenever the key from the primary site changes, you will need to manually copy the new key to the disaster recovery site.

Dynamically Set the Data Guard SQL Apply Parameters

Traditionally the DBMS_LOGSTDBY.APPLY_SET procedure was used to set Oracle database initialization parameters. The negative aspect of this procedure was that the SQL Apply engine had to be shut down. Oracle Database 11g lifts this restriction, and the apply_set procedure can set these parameters without stopping SQL Apply:

  • apply_servers
  • event_log_dest
  • log_auto_del_retention_target
  • log_auto_delete
  • max_events_recorded
  • max_servers
  • max_sga
  • prepare_servers
  • record_applied_ddl
  • record_skip_ddl
  • record_skip_errors
  • record_unsupported_operations

For example, you can now dynamically adjust the number of parallel servers used by SQL Apply to ten without bouncing the SQL Apply engine:

SQL> exec dbms_logstdby.apply_set ('MAX_SERVERS', 10);

PL/SQL procedure successfully completed.

In Oracle Database 11g, new columns, UNIT, SETTING, and DYNAMIC, are added to the DBA_LOGSTDBY_PARAMETERS view. This view provides the list of parameters used by the SQL Apply engine. The SETTING column shows parameters set as default or those modified by the DBAs. The value of system indicates that the parameter was not explicitly set by a DBA. The UNIT column displays the unit of value when applicable. The DYNAMIC column provides information on whether the SQL Apply parameter must be shut down to activate. By querying this view, you can see that the preserve_commit_order parameter is the only parameter that is not dynamically modifiable:

SQL> select *
2 from dba_logstdby_parameters
3* order by name
SQL> /
NAME VALUE UNIT SETTING DYNAMIC
---------------------------- ---------- ------------ ----------
APPLY_SERVERS 5 SYSTEM YES
EVENT_LOG_DEST DEST_EVENTS SYSTEMYES
_TABLE
LOG_AUTO_DELETE TRUE SYSTEM YES
LOG_AUTO_DEL_ 1440 MINUTE SYSTEM YES
RETENTION_TARGET
MAX_EVENTS_RECORDED 10000 SYSTEM YES
MAX_SERVERS 9 SYSTEM YES
MAX_SGA 30 MEGABY SYSTEMYES
PREPARE_SERVERS 1 SYSTEM YES
PRESERVE_COMMIT_ORDER TRUE SYSTEM NO
RECORD_APPLIED_DDL FALSE SYSTEM YES
RECORD_SKIP_DDL TRUE SYSTEM YES
RECORD_SKIP_ERRORS TRUE SYSTEM YES
RECORD_UNSUPPORTED_ FALSE SYSTEM YES
OPERATIONS

13 rows selected.

Support for VPD and FGA

Oracle Database 11g logical standby provides support for row-level security (VPD) and finegrained auditing(FGA) by replicating the DBMS_RLS and DBMS_FGA packages. Now security implementations from the primary database server replicate to the logical standby database. This will automatically enable by default if the logical standby database is created from an Oracle Database 11g database. Databases upgraded to Oracle version 11.1 will need to have this feature enabled manually. When the DBMS_RLS and DBMS_FGA procedures are executed on the primary database, additional information is captured in the redo logs, which allows the logical standby to rebuild the procedural call. Both the primary and logical standby must have the compatibility set to 11.1 to have security enabled with VPD and FGA.

DBMS_SCHEDULER Support

Oracle Database 11g Data Guard logical standby now provides support for DBMS_SCHEDULER. The scheduler is capable of running jobs on both the primary and logical standby databases. Using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure, you can set the database_role attribute to be the primary database or logical standby. By setting this attribute, you specify that the jobs can run only when operating in that particular database role.

A job created on the primary database will, by default, run only in the primary database role. Let’s take an example of a job that already exists in the database called EDBA_UPDATE and change the database role attribute to logical standby using the set_attribute procedure:

SQL> BEGIN
2 DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'EDBA_UPDATE',
3 attribute => 'database_role',
4 value =>'LOGICAL STANDBY');
5 END;
6 /

PL/SQL procedure successfully completed.

Once you change the database role for the job, you can query the DBA_SCHEDULER_JOB view to confirm that the EDBA_UPDATE is targeted to run on the logical standby, as depicted here:

SQL> select job_name, database_role from dba_scheduler_job_roles;

JOB_NAME DATABASE_ROLE
------------------------------ ----------------
XMLDB_NFS_CLEANUP_JOB PRIMARY
FGR$AUTOPURGE_JOB PRIMARY
BSLN_MAINTAIN_STATS_JOB PRIMARY
DRA_REEVALUATE_OPEN_FAILURES PRIMARY
HM_CREATE_OFFLINE_DICTIONARY PRIMARY
ORA$AUTOTASK_CLEAN PRIMARY
PURGE_LOG PRIMARY
MGMT_STATS_CONFIG_JOBPRIMARY
MGMT_CONFIG_JOB PRIMARY
RLM$SCHDNEGACTION PRIMARY
RLM$EVTCLEANUP PRIMARY
EDBA_UPDATE LOGICAL STANDBY <--

The DBMS_SCHEDULER package automatically establishes the switching of roles for switchover or failover conditions. Since the scheduler log replicates to the logical standby, log history is available after a failover or switchover condition.

Logical Standby Archivelog Storage

Oracle Database 11g logical standby allows the flash recovery area to be used as the archivelog destination for the log_archive_dest_n parameter. You can modify this parameter using the alter system command, as shown here:

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

By default, the logical standby architecture automatically deletes an archivelog once it is applied. Now in Oracle Database11g, this is an option that can be controlled using the log_ auto_delete initialization parameter. The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. This parameter is set to 1,440 minutes, or 24 hours, by default. For archivelog retention to be effective, the log_auto_delete parameter must be set to true, and the flash recovery area must not be used to store the archivelogs. Here’s an example of setting log_auto_del_retention_target to two days and log_auto_delete to true:

SQL> exec dbms_logstdby.apply_set('LOG_AUTO_DELETE', 'TRUE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DEL_RETENTION_TARGET', 2880);

PL/SQL procedure successfully completed.

SQL Apply Event Messages

Prior to Oracle Database 11g, SQL Apply events were written to the alert log and the SYSTEM. LOGSTDBY$EVENTS table. Oracle Database 11g introduces the new event_log_dest parameter that dictates whether the SQL Apply engine will write out specified events to the SYSTEM. LOGSTDBY$EVENTS table and/or to the alert log. You can modify this parameter using the DBMS_ LOGSTDBY.APPLY_SET procedure to set a specified value. This value can also be unset using the DBMS_LOGSTDBY.APPLY_UNSET procedure.

The event_log_dest parameter has two valid values: dest_all and dest_events_table. By default, this value is set to dest_events_table, which specifies that the events will be recorded only to the LOGSTDBY$EVENTS table. By setting this parameter to dest_all, as in the following example code, SQL Apply will record events to both the alert log and the LOGSTDBY$EVENTS table:

SQL> exec dbms_logstdby.apply_set('EVENT_LOG_DEST', 'DEST_ALL');

PL/SQL procedure successfully completed.

You can view the contents of the LOGSTDBY$EVENTS view as shown here to view error and informational messages posted by SQL Apply:

1 select event_time, error
2 from system.logstdby$events
3* where error like 'APPLY_SET%'
SQL> /

EVENT_TIME ERROR

----------------------------- --------------------------------------------

14-AUG-07 09.29.56.536531 PM APPLY_SET: MAX_EVENTS_RECORDED changed to 200
14-AUG-07 09.32.09.984015 PM APLY_SET: MAX_SERVERS changed to 10
14-AUG-07 09.43.18.058327 PM APPLY_SET:EVENT_LOG_DEST changed to DEST_ALL

This parameter influences the behavior of other parameters such as the record_skip_errors, record_skip_ddl, record_applied_ddl, and record_unsupported_operations parameters.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics