Oracle Streams Senkron Kullanım

Giriş

Şeklen şöyle

Senkron kullanım için adımlar burada

1. Create User & Setup Privileges : Stream Administrator rolü yaratılır

2. Create Database Links at source and target

3. Create Queue at source and target

4. Create Capture at source

5. Create Apply Process at target

6. Create Propagation at source

7. Instantiation at target

8 Start Apply at target

9. Start Capture at source

Kullanılan PL/SQL paketleri şöyle

DBMS_APPLY_ADM

DBMS_CAPTURE_ADM

DBMS_FLASHBACK

DBMS_OUTPUT

DBMS_STREAMS_ADM

DBMS_APPLY_ADM Paketi

START_APPLY(), STOP_APPLY(), DELETE_ALL_ERRORS() gibi metodlar kullanılabilir

DBMS_APPLY_ADM.START_APPLY(apply_name => 'SYNC_APPLY_1');
...
DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'SYNC_APPLY_1');
DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'SYNC_APPLY_1');

Duplicate Rows

Açıklaması şöyle

1. You cannot use Oracle Streams to replicate a table which has duplicate rows. It will not work.
2. If you don’t have unique constraint on your table in some form, you will have duplicate rows.

Create Database Links

Şöyle yaparız

--on source
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK REP01 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'REP01';
select count(*) from dba_tables@REP01;

-- on target
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK DB05 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DB05';
select count(*) from dba_tables@DB05

Create Queue

Örnek

Şöyle yaparız

DBMS_STREAMS_ADM.SET_UP_QUEUE(
 queue_table =>'STRADMIN.SQT_IMMEDIATE1_S', 
 queue_name => 'STRADMIN.SQ_IMMEDIATE_S');

Apply

streams_type olarak APPLY verilebilir

Örnek

Şöyle yaparız.

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => ...,
  streams_type => 'apply',
  streams_name => ...,
  queue_name => ...
);

Capture

streams_type olarak CAPTURE, SYNC_CAPTURE verilebilir. Açıklaması şöyle

Synchronous Mode

Synchronous Capture uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real-time on the source database. The change tables are populated when DML operations on the source table are committed. Hence it is titled “Synchronous” Change Data Capture.

New change data arrives automatically as DML operations on the source tables are committed. Publishers can define new change sets in the predefined SYNC_SOURCE change source or use the predefined change set, SYNC_SET. The SYNC_SET change set cannot be altered or dropped.

While the “invasive” nature of the approach of the synchronous mode of Change Data Capture adds overhead to the source database at capture time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section) by simplifying the extraction of change data.

Örnek - Senkron

Şöyle yaparız. inclusion_rule FALSE verilerek capture işlemi yapılmıyor

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name=>'DEV110.APP_QUEUE',
  streams_type=>'capture',
  streams_name=>'CAPTURE_110DEV',
  queue_name=>'REP_CAPTURE_QUEUE',
  include_dml=>TRUE,
  include_ddl=>TRUE,
  include_tagged_lcr=> FALSE,
  source_database=>'DB05',
  inclusion_rule=>FALSE,
  and_condition=>NULL);