Synchronous Capture - Oracle 11g

Oracle Database 11g pioneers another mechanism to perform data capture at the source database called synchronous capture. Synchronous capture utilizes an internal mechanism to capture deltas to tables immediately as transaction executes.

Synchronous capture collects data modifications as they occur without mining through redo logs or archivelogs.In the implementation of synchronous capture, redo logs are not involved.Row LCRs are visible in the persistent queue,which consists of stored messages on disk, not in memory, as soon as the commits are issued.

Synchronous capture identifies each modified row,converts it into a row LCR, and enqueues itdirectly to disk.Once LCRs are written to disk,the standard Streams processing controls the remaining process.It is important to note that the synchronous queue LCRs contain all the columns of the table even when not all of them are modified.

With synchronous capture, DML changes made to the underlying tables are captured as soon as commits occur.DML changes that are supported areinsert, update, delete, and merge commands.Behind the scenes, the merge statement is converted into an insert or an update.

Synchronous Capture

There are situations where you want to utilize the synchronous captureprocess. First, Streams is available only with an Oracle Enterprise Edition license. If you want to use Streams with the Standard Edition, you must use the synchronous option.

The synchronous option should be used cautiously.You should not turn onsynchronous capture for all the tables for high-volume OLTP transaction databases.You should consider setting up synchronous capture for a few tables that do not have high transaction volumes. Another great candidate is for databases that are not in archivelog mode or can enable the traditional Streams log-based capture.

Restrictions on Synchronous Capture

Please bear in mind the restrictions associated with synchronous capture. First, it is applicable only for DML.DDL changes cannot participate in synchronous capture. Second, synchronous capture will not capture changes on IOTs if the IOT has any of these datatypes:LONG, LONG RAW, CLOB, NCLOB, BLOB, BFILE, ROWID,and XMLType.

Synchronous capture cannot capture changes made to temporary tables or object tables.Synchronous capture can enqueue messages only from a commit-time queue.In addition, changes to sequence values are not captured.

SQL*Loader in direct load mode is not supported.Similar to theconventional Oracle Streams,the users sys, system, and ctxsys cannot participate in synchronous capture.Synchronous capture rules must be a positive rule set.Negative rules are prohibited.

The only rules that are valid for synchronous captures are rules specified at the table level or for subset of rows.Rules must be added using the add_table_rules or add_subset_rules procedure from theDBMS_STREAMS_ADM package.

The following commands are ignored in a synchronous capture:

  • plan
  • call
  • explain
  • lock
  • alter system
  • alter session
  • set role
  • dbms_redefinition
  • Rules created by the DBMS_RULE_ADM package
  • Calls to the PL/SQL (but the underlying changes to database objects are captured).

Configuring Synchronous Capture

The capture user must be set up to capture DML changes applicable for the rule set.This can be accomplished by invoking the DBMS_CAPTURE_ ADM.ALTER_SYNC_CAPTURE procedure. As statedbefore, the synchronous capture must use the ANYDATA queue.To create the ANYDATA queue,you can use the DBMS_STREAMS_ADM.SET_UP_QUEUE procedure. For example:

begin
dbms_streams_adm.set_up_queue
(queue_table => ' STREAMS_ADMIN.STREAMS_QUEUE_TABLE ',
queue_name => ' STREAMS_ADMIN.STREAMS_QUEUE ',
queue_user => ' RODBA ');
end;
/Configuring Synchronous Capture

To start the synchronous capture process, you have several procedures from which to choose:

  • DBMS_STREAMS_ADM.ADD_TABLE_RULES
  • DBMS_STREAMS_ADM.ADD_SUBSET_RULES
  • DBMS_CAPTURE_ADM.CREATE_SYNC_CAPTURE

A synchronous capture can capture changes only at the table level.Schema-level or database-level configurations cannot be set up for
synchronous capture.You can invoke theadd_table_rules or add_subset_rules procedure to configure synchronous capture.

Using the new create_sync_capture procedure,you can set up asynchronous capture at the source database.The create_sync_capture procedure takes these arguments:

queue_name: Name of the queue as defined in the call to theset_up_queue procedure

  • capture_name: Name of the sync capture process
  • rule_set_name: Name of the positive rule set
  • capture_user: Capture user

Here’s an example of the create_sync_capture invocation with all the arguments relative to this example:

begin
dbms_capture_adm.create_sync_capture
(queue_name => ' STREAMS_ADMIN.STREAMS_QUEUE ',
capture_name => ' SYNC_DOCS_CAPTURE ',
rule_set_name => ' STREAMS_ADMIN.SYNC_DOCS_RULE_SET ',
capture_user => ' RODBA ');
end;
/create_sync_capture invocation

The procedure will create a synchronous capture called sync_docs_capture. You will also notice that the QUEUE_NAME of the streams queue coincides with the QUEUE_NAME from our set_up_queue procedure.

Once the synchronous capture is defined, you can use theadd_tables_rules or add_subset_rules procedure to add a table.Using subsets is required only when replicating partial/selected data; otherwise, there is no need to use subsets.

In this example, we will use the add_subset_rules procedure to add the DOCS table where the DOC_ID >1000000:

begin
dbms_streams_adm.add_subset_rules
(table_name => 'RODBA.DOCS',
dml_condition => ' DOC_ID >1000000',
streams_type => 'SYNC_CAPTURE',
streams_name => 'SYNC_DOCS_CAPTURE',
queue_name => 'STREAMS_QUEUE',
include_tagged_lcr => FALSE);
END;
/

You can see that the STREAM_NAME argument takes the capture name from the previous create_sync_capture procedure.Also,there is an optionalDML_CONDITION parameter to specify a where clause to the table.Thesynchronous capture will use the SYNC_DOCS_ RULE_SET rule set name as specified from the create_sync_capture procedure.

Dropping Synchronous Capture

You can use the DBMS_CAPTURE_ADM.DROP_CAPTURE procedure to drop an existing synchronous capture. This procedure has twoparameters, CAPTURE_NAME and DROP_UNUSED_RULE_SETS. By default, theDROP_UNUSED_RULE_SETS parameter is set to FALSE.If the DROP_UNUSED_RULE_SETS parameter is set to TRUE, the drop_captureprocedure will also drop any rule sets specified by the CAPTURE_NAME para meter.However, if the rule set is used by another Streams client, it will not be dropped. In the following code example, we drop the sync_docs_capture synchronous capture:

begin
dbms_capture_adm.drop_capture
(capture_name => 'SYNC_DOCS_CAPTURE',
drop_unused_rule_sets => TRUE);
end;
/

DBA Views for Synchronous Capture

The view DBA_SYNC_CAPTURE_TABLES provides a listing of all the tables that are participating in synchronous capture for the database. For example:

SQL> select table_owner,table_name, enabled from dba_sync_capture_tables;
TABLE_OWNER TABLE_NAME ENA
------------ ----------- ---
RODBA DOCS YES

You can join this view with DBA_STREAMS_TABLE_RULES to correlate the stream name,rules,and subset operation information for each synchronous capture. Here’s an example showing how that’s done:

select r.streams_name, r.rule_name,
r.subsetting_operation,t.table_owner,
t.table_name, t.enabled
from dba_streams_rules r,dba_sync_capture_tables t
where r.streams_type = 'SYNCHRONOUS CAPTURE'
and r.object_name = t.table_name;

You can also query the DBA_SYN_CAPTURE view to display the queue and rule set of each synchronous capture:

SQL> select capture_name, queue_name, rule_set_name, capture_user
2* from dba_sync_capture
SQL> /
CAPTURE_NAME QUEUE_NAME RULE_SET_NAME CAPTURE_US
----------------- --------------- ------------------ ----------
SYNC_DOCS_CAPTURE STREAMS_QUEUE SYNC_DOCS_RULE_SET RODBA

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

Oracle 11g Topics