Bookmark and Share

Monday, March 2, 2009

Differences Between Asynchronous And Synchronous Capture

Oracle 11g provides a new client that captures changes made to a database: the synchronous capture(1). "Synchronous" may sound confusing at the first hearing. Actually, only the capture is synchronous; in other words, the changes are captured by a trigger-like mechanism. The staging and apply are always done in background and there is no guaranty if you query the replicated data they will reflect the source committed changes right away. You still need to rely on the monitoring to insure the changes have been applied

I would not think of synchronous capture as an capture process-ersatz for Oracle Standard Edition; instead, it comes with its own set of benefits you can leverage or reproduce to better address some of your requirements. For this reason and because the information is spread in several part of the documentation, I've tried to summarize the differences between the 2 implicit capture methods of Oracle 11g. If you think something is missing or is worth to mention, leave a comment. Below is the table that compares asynchronous and synchronous captures for Oracle 11.1:

TypeAsynchronous CaptureSynchronous Capture
First release9.211.1
EditionEnterprise Only Enterprise, Standard and Standard One
Database modeRequires the database to run in ARCHIVELOG mode with the appropriate supplemental logging for the captured informationsCan run in NOARCHIVELOG and doesn't require any supplemental logging information
Captured eventsDML and DDLDML Only
Capture levelglobal, schema, table or table subsettable or table subset only
Before you create can instantiate the objects on the destinationYou need to execute the build and prepare_[xxx]_instanciation procedure of the dbms_capture_adm package (explicitly or implicitly) to prepare the logminer data dictionary and to make sure all the changes can be captured from the logs, i.e. there is no pending changesYou need to execute the prepare_sync_instantiation function from the dbms_capture_adm package to make sure you'll be able to capture the changes to be applied. Actually that function is run implicitly when you create the rule with dbms_streams_adm.
Before the capture creation-
You must create the apply processes or a DML handlers to consume the messages as well as the queue propagation processes before the synchronous capture. If you don't, any DML change to the source tables will fail with "ORA-24033: no recipients for message"
Starting/stopping the capture-You cannot stop a synchronous capture.
Rules associated with the captureRules can be simple or complex. They can be in a positive or in a negative rule set. They can be created with any procedure of dbms_streams_adm or dbms_capture_admRules must be simple; they must be in the positive rule set and be table or table subset rules. They must be created with the add_table_rules or the add_subset_rules procedure of the dbms_streams_adm package.
MessagesCaptured messages are sent to the buffered part of the queue and are kept in the Streams Pool up to they are consumed or they spill.Captured messages are persistent and are kept in the buffer cache or on disks.
OLD and NEW values for an updated rowdepends on the supplemental logging definitionAll OLD and NEW values are always stored in the LCR of an updated row.
Queues-The queue used to enqueue messages from the synchronous capture must be a commit time queue.
Table with unsupported data typesIf one column of a table is not supported by the capture process, you must remove the whole table from the capture.If one column is not supported, you can use a transformation rule to capture the table without the unsupported column
List of Unsupported table/columnIs available via the dba_streams_unsupported or the dba_streams_newly_supported viewsIs available via the dba_streams_columns view
Supported Types with Oracle 11.1The list of supported data type of capture processes is the following:
  • VARCHAR2
  • NVARCHAR2
  • FLOAT
  • NUMBER
  • DATE
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • RAW
  • CHAR
  • NCHAR
  • UROWID
  • LONG
  • LONG RAW
  • CLOB with BASICFILE storage
  • NCLOB with BASICFILE storage
  • BLOB with BASICFILE storage
  • XMLType stored as CLOB
The list of supported data type of a synchronous capture is the following:
  • VARCHAR2
  • NVARCHAR2
  • FLOAT
  • NUMBER
  • DATE
  • BINARY_FLOAT
  • BINARY_DOUBLE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
  • RAW
  • CHAR
  • NCHAR
  • UROWID
Others ConsiderationsCapture Processes have some advanced abilities that are not available with synchronous capture:
  • Combined Capture and Apply
  • Downstream Capture
-

(1) You'll find an example of synchronous capture implementation in a previous post entitled Streams Synchronous Capture 101.

No comments:

Post a Comment