Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to deploy Oracle_CDC. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Implementation steps of CDC (asynchronous online log) 1.1. Database initialization

SQL >

Alter system set job_queue_processes = 100

Alter system set java_pool_size = 50m

Alter system set streams_pool_size=50m

Alter system set undo_retention=3600

Alter database force logging

Alter database add supplemental log data

Select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database

1.2. Prepare the test table

SQL >

Create table scott.test (id int,name varchar2 (30), mark varchar2 (50))

1.3. Create a publisher

SQL >

Conn / as sysdba

Create tablespace cdc_tbsp

Create user cdc_publisher identified by cdc_publisher default tablespace cdc_tbsp temporary tablespace temp

GRANT CREATE SESSION TO cdc_publisher

GRANT CREATE TABLE TO cdc_publisher

Grant create sequence TO cdc_publisher

Grant create procedure TO cdc_publisher

Grant create any job TO cdc_publisher

GRANT CREATE TABLESPACE TO cdc_publisher

GRANT UNLIMITED TABLESPACE TO cdc_publisher

GRANT SELECT_CATALOG_ROLE TO cdc_publisher

GRANT EXECUTE_CATALOG_ROLE TO cdc_publisher

GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_publisher

Grant execute ON dbms_lock TO cdc_publisher

Execute dbms_streams_auth.grant_admin_privilege ('CDC_PUBLISHER')

Grant all on scott.test to cdc_publisher

Grant dba to cdc_publisher

1.4. Create a subscriber

SQL >

Create user cdc_subscriber identified by cdc_subscriber default tablespace cdc_tbsp temporary tablespace temp

Grant create session TO cdc_subscriber

Grant resource to cdc_subscriber

Grant connect to cdc_subscriber

GRANT CREATE TABLE TO cdc_subscriber

GRANT CREATE VIEW TO cdc_subscriber

GRANT UNLIMITED TABLESPACE TO cdc_subscriber

1.5. Release data 1.5.1. Publish-prepare source table

SQL >

Conn cdc_publisher/cdc_publisher

BEGIN

DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION (TABLE_NAME = > 'scott.test')

END

/

1.5.2. Publish-create changeset

SQL >

Conn cdc_publisher/cdc_publisher

BEGIN

DBMS_CDC_PUBLISH.CREATE_CHANGE_SET (

Change_set_name = > 'CDC_SCOTT_TEST'

Description = > 'Change set for product info'

Change_source_name = > 'HOTLOG_SOURCE'

Stop_on_ddl = >'y')

END

/

Note:

Change_source_name parameters:

Must be: SYNC_SOURCE in synchronous mode

The asynchronous online log mode must be: HOTLOG_SOURCE

1.5.3. Publish-create a change table

SQL >

Conn cdc_publisher/cdc_publisher

BEGIN

DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE (

Owner = > 'cdc_publisher'

Change_table_name = > 'cdc_test'

Change_set_name = > 'CDC_SCOTT_TEST'

Source_schema = > 'SCOTT'

Source_table = > 'TEST'

Column_type_list = >'ID NUMBER (5), NAME VARCHAR2 (30), MARK VARCHAR2 (50)'

Capture_values = > 'both'

Rs_id = >'y'

Row_id = >'n'

User_id = >'n'

Timestamp = >'n'

Object_id = >'n'

Source_colmap = >'n'

Target_colmap = >'y'

Options_string = > 'TABLESPACE CDC_TBSP')

END

/

Note:

Owner refers to publishing users.

Source_schema is the user to which the source table belongs

Synchronization mode needs to add a parameter ddl_markers = >'n'

Options_string specifies to change the storage parameters of the table, and you can use the storage parameters specified in all create table except partition, such as tablespace, pctfree, and so on.

1.5.4. Publish-activate changeset

SQL >

Conn cdc_publisher/cdc_publisher

BEGIN

DBMS_CDC_PUBLISH.ALTER_CHANGE_SET (

Change_set_name = > 'CDC_SCOTT_TEST'

Enable_capture = >'y')

END

/

1.5.5. Authorization change form to subscribers

Conn cdc_publisher/cdc_publisher

Grant select on cdc_test to cdc_subscriber

Note:

At this point, cdc_subscriber users can detect changes in the scott.test table.

Test:

$sqlplus scott/tiger

SQL >

Insert into scott.test values (1 recordable beijingjingjingjingjingjingjingjinghuo 11')

Commit

Update scott.test set name='shanghai' where id=1

Commit

Delete scott.test where id=1

Commit

SQL >

Conn cdc_subscriber/cdc_subscriber

SQL > select t.operationtheatrical .recording timestamptheatrical t.idrecoveryt.namerecovert.mark from cdc_publisher.cdc_test t

OPERATION$ COMMIT_TIMESTAMP$ ID NAME MARK

I 2018-2-8 20:04:58 1 beijing 11

UO 2018-2-8 20:04:58 1 beijing 11

UN 2018-2-8 20:04:58 1 shanghai 11

D 2018-2-8 20:04:58 1 shanghai 11

Remarks: the meaning of operation

The value in this column can be any of the following foot 1:

I: indicates that this line represents the insert operation

Indicates that this row represents the pre-image of the source table row that is updated in the following cases: UO

Asynchronous change data capture

Synchronize the change data capture when the change table includes the primary key-based object ID, rather than the capture column of the primary key.

UU: indicates that this row represents the previous image of the updated source table row, which is used to synchronize change data capture, not by. UO.

UN: indicates that this row represents the post-image of the updated source table row.

D: indicates that this line represents a delete operation.

When the publisher publishes the relevant change table, a unique release id (publication ID) is generated. You can consult the view ALL_PUBLISHED_COLUMNS to get the published table and field information.

SQL > conn CDC_PUBLISHER/CDC_PUBLISHER

Select change_set_name,pub_id,source_table_name from ALL_PUBLISHED_COLUMNS

CHANGE_SET_NAME PUB_ID SOURCE_TABLE_NAME

-

CDC_SCOTT_TEST 91956 TEST

CDC_SCOTT_TEST 91956 TEST

CDC_SCOTT_TEST 91956 TEST

1.6. Subscribe to 1.6.1. Subscription-create a subscription set

SQL >

Conn cdc_subscriber/cdc_subscriber

BEGIN

Dbms_cdc_subscribe.create_subscription (

Change_set_name= > 'CDC_SCOTT_TEST'

Description= > 'cdc scott subx'

Subscription_name= > 'CDC_SCOTT_SUB')

END

/

Note:

A subscription corresponds to a change set. Because there is an one-to-many relationship between the change set and the source table, multiple tables can be subscribed at a time.

1.6.2. Subscribe-start subscribing to data

SQL >

BEGIN

Dbms_cdc_subscribe.subscribe (

Subscription_name= > 'CDC_SCOTT_SUB'

Source_schema= > 'SCOTT'

Source_table= > 'TEST'

Column_list= >'ID, NAME,MARK'

Subscriber_view= > 'TEST_TEMP')

END

/

SQL > select view_name,text from user_views

VIEW_NAME TEXT

TEST_TEMP SELECT OPERATION$, CSCN$, COMMIT_TIMESTAMP$, XIDUSN$, XIDSLT$, XIDSEQ$, DDLDES

Note:

If there are multiple tables in the change set, you need to operate multiple times

1.6.3. Subscription-activate subscription

SQL >

BEGIN

Dbms_cdc_subscribe.activate_subscription (

Subscription_name= > 'CDC_SCOTT_SUB')

END

/

1.6.4. Subscription-extended subscription window

SQL >

Conn cdc_subscriber/cdc_subscriber

BEGIN

Dbms_cdc_subscribe.extend_window (

Subscription_name= > 'CDC_SCOTT_SUB')

END

/

Note:

The subscription calls the DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW procedure to get the collection of change data, and if executed for the first time, it gets all the change data after the subscription is activated. After each execution of DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW, the expansion window only sees the data since the last execution of DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW.

1.6.5. View subscriptions

SQL >

Conn cdc_subscriber/cdc_subscriber

SQL > select t.operationtheatrical .recording timestamptheatrical t.idrecoveryt.namerecovert.mark from test_temp t

OPERATION$ COMMIT_TIMESTAMP$ ID NAME MARK

-

I 2018-2-8 20:04:58 1 beijing 11

UO 2018-2-8 20:04:58 1 beijing 11

UN 2018-2-8 20:04:58 1 shanghai 11

D 2018-2-8 20:04:58 1 shanghai 11

1.7. Test 1.7.1. Source table change

SQL > conn scott/tiger

Insert into test values (2 recordings renqingleigh girls pencils AA')

Commit

Update test set mark='tt' where id=2

Commit

Delete test where id=2

Commit

1.7.2. Query data release

SQL > conn cdc_publisher/cdc_publisher

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from cdc_publisher.cdc_test t

OP COMMIT_TIMESTAMP$ ID NAME MARK

I 13-JAN-16 1 beijing 11

UO 13-JAN-16 1 beijing 11

UN 13-JAN-16 1 shanghai 11

D 13-JAN-16 1 shanghai 11

I 13-JAN-16 2 renqinglei aa

UO 13-JAN-16 2 renqinglei aa

UN 13-JAN-16 2 renqinglei tt

D 13-JAN-16 2 renqinglei tt

1.7.3. Query data subscription

SQL > conn cdc_subscriber/cdc_subscriber

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from test_temp t

OP COMMIT_TIMESTAMP$ ID NAME MARK

I 13-JAN-16 1 beijing 11

UO 13-JAN-16 1 beijing 11

UN 13-JAN-16 1 shanghai 11

D 13-JAN-16 1 shanghai 11.

1.7.4. Find that the data of the subscription has not changed, expand the subscription window:

SQL > conn cdc_subscriber/cdc_subscriber

BEGIN

Dbms_cdc_subscribe.extend_window (

Subscription_name= > 'CDC_SCOTT_SUB')

END

/

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from test_temp t

OPERATION$ COMMIT_TIMESTAMP$ ID NAME MARK

I 2018-2-8 20:04:58 1 beijing 11

UO 2018-2-8 20:04:58 1 beijing 11

UN 2018-2-8 20:04:58 1 shanghai 11

D 2018-2-8 20:04:58 1 shanghai 11

I 2018-2-8 20:26:01 2 renqinglei aa

UO 2018-2-8 20:26:01 2 renqinglei aa

UN 2018-2-8 20:26:01 2 renqinglei tt

D 2018-2-8 20:26:01 2 renqinglei tt

1.7.5. Clear the change dataset

SQL > conn cdc_subscriber/cdc_subscriber

BEGIN

DBMS_CDC_SUBSCRIBE.PURGE_WINDOW (

Subscription_name = > 'CDC_SCOTT_SUB')

END

/

View subscription data is empty

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from test_temp t

Remarks:

The data of the extended window can be emptied to avoid the system load caused by changing too much data.

1.7.6. Regenerate change data

Conn scott/tiger

Insert into test values (3 recordings shandongzhongjinghuh')

Insert into test values (4 recordings diankeyuanjia dongh')

Commit

View release information

Conn cdc_publisher/cdc_publisher

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from cdc_publisher.cdc_test t

OPERATION$ COMMIT_TIMESTAMP$ ID NAME MARK

-2018-2-8 20:04:58 1 beijing 11

UO 2018-2-8 20:04:58 1 beijing 11

UN 2018-2-8 20:04:58 1 shanghai 11

D 2018-2-8 20:04:58 1 shanghai 11

I 2018-2-8 20:26:01 2 renqinglei aa

UO 2018-2-8 20:26:01 2 renqinglei aa

UN 2018-2-8 20:26:01 2 renqinglei tt

D 2018-2-8 20:26:01 2 renqinglei tt

I 2018-2-8 20:33:48 3 shandong hh

I 2018-2-8 20:33:48 4 diankeyuan hh

SQL > conn cdc_subscriber/cdc_subscriber

BEGIN

Dbms_cdc_subscribe.extend_window (

Subscription_name= > 'CDC_SCOTT_SUB')

END

/

View subscription information

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from test_temp t

OPERATION$ COMMIT_TIMESTAMP$ ID NAME MARK

I 2018-2-8 20:33:48 3 shandong hh

I 2018-2-8 20:33:48 4 diankeyuan hh

1.7.7. Delete published data

SQL >

Conn cdc_publisher/cdc_publisher

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from cdc_publisher.cdc_test t

OP COMMIT_TIMESTAMP$ ID NAME MARK

I 13-JAN-16 1 beijing 11

UO 13-JAN-16 1 beijing 11

UN 13-JAN-16 1 shanghai 11

D 13-JAN-16 1 shanghai 11

I 13-JAN-16 2 renqinglei aa

UO 13-JAN-16 2 renqinglei aa

UN 13-JAN-16 2 renqinglei tt

D 13-JAN-16 2 renqinglei tt

I 13-JAN-16 3 shandong hh

I 13-JAN-16 4 diankeyuan hh

Rows selected.

Not truncate

SQL > truncate cdc_test

ERROR at line 1:

ORA-03290: Invalid truncate command-missing CLUSTER or TABLE keyword

No record after deletion

SQL >

Delete cdc_test

Commit

Select t.operationtheatrical t. Classic timestampwriting theatrical t.idmemery t.namerecovert.mark from cdc_publisher.cdc_test t

After reading the above, do you have any further understanding of how to deploy Oracle_CDC? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.