Change Data Capture (5) Oracle CDC Autolog online example

1. The cdc mode is used when the source database is a cluster! Source Library
Log_archive_dest_2 = 'service = algjstg lgwr async optional noregister reopen = 5 valid_for = (online_logfile, primary_role) sid = '1''
Log_archive_dest_2 = 'service = algjstg lgwr async optional noregister reopen = 5 valid_for = (online_logfile, primary_role) sid = '2''

1. CDC autolog online

1. Prerequisites:
(1). The database version must be Enterprise Edition.
(2). The operating system version of the source database and intermediate database must be the same.
(3). the SYS password of the source database and intermediate database must be the same.
(4) the source and intermediate databases can access each other through tnsnames. ora.

========================================================== ========================================================== ==================

2. database parameter configuration:
(1). stagdb end:
-- Enable the local archiving path and the path for accepting remote archiving or remote redo logs:
Alter system set global_names = true;
Alter system set java_pool_size = 50 M;
Alter system set streams_pool_size = 50 M;
Alter system set remote_login_passwordfile = shared scope = spfile;
Alter system set log_archive_dest_1 = 'location =/u01/oracle/oradata/hua/archivelog mandatory reopen = 5 valid_for = (online_logfile, primary_role )';
Alter system set log_archive_dest_2 = 'location =/u01/oracle/oradata/hua/staglog mandatory valid_for = (standby_logfile, primary_role )';
-- Place stagdb in archive mode:
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
Alter system switch logfile;
-- Add standby logfile to stagdb to receive logs transmitted from the source end. Therefore, the number of standby logfiles in stagdb must be greater than the number of online redo logs in the source end. the size of each standby logfile should be greater than or equal to the online log Size of the source turbulence.
Alter database add standby logfile group 5 ('<path to standby redo log>') size *** M;
......

(2). Source end:
-- Set local archiving and remote Archiving:
Alter system set remote_login_passwordfile = shared scope = spfile;
Alter system set log_archive_dest_1 = 'location =/u01/oracle/oradata/yun/archivelog ';
Alter system set log_archive_dest_2 = 'service = hua lgwr async optional noregister reopen = 5 valid_for = (online_logfile, primary_role )';
-- Enable archiving, force logging, and additional logs:
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
Alter system switch logfile;
Alter database add supplemental log data (primary key, unique index) columns;
Alter database force logging;
========================================================== ========================================================== ==================

3. Create a user:
(1). Source end:
Conn sys/oracleabcd @ yun as sysdba

Drop user cdc_admin cascade;

Create user cdc_admin identified by cdc_admin;
Grant create session to cdc_admin;
Grant create table to cdc_admin;
Grant create tablespace to cdc_admin;
Grant unlimited tablespace to cdc_admin;
Grant select_catalog_role to cdc_admin;
Grant execute_catalog_role to cdc_admin;
Grant execute on dbms_cdc_publish to cdc_admin;

(2). stagdb end:
Conn sys/oracleabcd @ hua as sysdba

Drop user cdc_admin cascade;

Create user cdc_admin identified by cdc_admin;
Grant create session to cdc_admin;
Grant create table to cdc_admin;
Grant create tablespace to cdc_admin;
Grant create sequence to cdc_admin;
Grant select_catalog_role to cdc_admin;
Grant execute_catalog_role to cdc_admin;
Grant unlimited tablespace to cdc_admin;
Grant select_catalog_role to cdc_admin;
Grant execute_catalog_role to cdc_admin;
Grant connect, resource, dba to cdc_admin;
Grant execute on dbms_cdc_publish to cdc_admin;
Execute dbms_streams_auth.grant_admin_privilege (grantee => 'cdc _ admin ');

========================================================== ========================================================== ==================
4. Configure the source database:
(1). Create a test table:
-- Create source table.
Connect CDC_ADMIN/CDC_ADMIN @ yun

Create table CDC_HEARTBEAT_DW
(
CDC_NAME VARCHAR2 (128) CONSTRAINT CHB_COL01_NN not null,
LATEST_DATETIME TIMESTAMP (6) DEFAULT incluimestamp CONSTRAINT CHB_COL02_NN NOT NULL
);

Alter table CDC_HEARTBEAT_DW add constraint pk_CDC_HEARTBEAT_DW primary key (CDC_NAME) using index;

Alter Table cdc_heartbeat_dw add supplemental log data (ALL) COLUMNS;

(2). view the SCN of the record:
-- Run the following and record the SCN.

Conn sys/oracleabcd @ yun as sysdba

SET SERVEROUTPUT ON
DECLARE
Rochelle miner_scn NUMBER;
BEGIN
Dbms_capture_adm.build (l_logminer_scn );
DBMS_OUTPUT.PUT_LINE ('scn: '| l_logminer_scn );
END;
/

Example: 12045675269343

(3). instantiate the source table:
-- Prepare table for instantiation.

Conn sys/oracleabcd @ yun as sysdba

EXECUTE dbms_capture_adm.prepare_table_instantiation ('cdc _ ADMIN. CDC_HEARTBEAT_DW ');

(4). view the source global_name:
Conn sys/oracleabcd @ yun as sysdba

Select global_name from global_name;

========================================================== ========================================================== ==================

5. Configure stagdb:

-- Create Change source
Conn CDC_ADMIN/CDC_ADMIN @ hua
Begin
Dbms_cdc_publish.create_autolog_change_source (
Change_source_name => 'DW ',
Description => 'source Database is yun ',
Source_database => 'yun ',
First_scn => 12045675269343,
Online_log => 'y ');
End;
/
Parameter description:
The source_database parameter has to have the global_name for the source database.
First_scn must be the value you captured from the source database.

-- Create change set
Begin
Dbms_cdc_publish.create_change_set (
Change_set_name => 'cs05 ',
Description => 'source Database is SDTAIS with change set CS05 ',
Change_source_name => 'DW ',
Stop_on_ddl => 'y ');
End;
/

-- Create change table.
Begin
Dbms_cdc_publish.create_change_table (
Owner => 'cdc _ admin ',
Change_table_name => 'ct _ CDC_HEARTBEAT_CS05 ',
Change_set_name => 'cs05 ',
Source_schema => 'cdc _ admin ',
Source_table => 'cdc _ HEARTBEAT_DW ',
Column_type_list => 'cdc _ NAME VARCHAR2 (128), LATEST_DATETIME TIMESTAMP (6 )',
Capture_values => 'both ',
Rs_id => 'y ',
Row_id => 'n ',
User_id => 'n ',
Timestamp => 'y ',
Object_id => 'n ',
Source_colmap => 'n ',
Target_colmap => 'y ',
Options_string => null );
End;
/
Note: JVM is required when create_change_table is called. If JVM is not installed in the stage database, an error is reported. It is best to install JVM on both the source and target ends.

-- Alter few tuning parameters:
EXECUTE dbms_capture_adm.alter_capture (capture_name => 'cdc $ C_CS05 ', checkpoint_retention_time => 1 );
EXECUTE dbms_capture_adm.set_parameter (capture_name => 'cdc $ C_CS05 ', parameter => 'Disable _ ON_LIMIT', value => 'y ');
EXECUTE dbms_capture_adm.set_parameter (capture_name => 'cdc $ C_CS05 ', parameter => 'maximum _ SCN', value => 'infinite ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter => 'commit _ serialization', value => 'None ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter => 'Disable _ ON_ERROR', value => 'y ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter => 'Disable _ ON_LIMIT', value => 'y ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter => 'parallelism', value => '1 ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter =>' _ dynamic_cmdts ', value => 'y ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter =>' _ HASH_TABLE_SIZE ', value => '123 ');
EXECUTE dbms_apply_adm.set_parameter (apply_name => 'cdc $ A_CS05 ', parameter =>' _ TXN_BUFFER_SIZE ', value => '10 ');

-- Active change_set:
Begin
Dbms_cdc_publish.alter_change_set (
Change_set_name => 'cs05 ',
Enable_capture => 'y ');
End;
/

========================================================== ========================================================== ==================
5. The source switch log transmits the data dictionary at the source end to stagdb for initialization of the capture process:
-- Switch logfile:
Alter system switch logfile;
========================================================== ========================================================== ==================

6. test:

Connect sys/oracleabcd @ yun as sysdba

Insert into cdc_heartbeat_dw (CDC_NAME, LATEST_DATETIME) VALUES ('iddfdfd2st', SYSDATE );
Commit;

Alter system archive log current;

Conn CDC_ADMIN/CDC_ADMIN @ hua
Select count (*) from CT_CDC_HEARTBEAT_CS05;
Select * from CT_CDC_HEARTBEAT_CS05;

========================================================== ========================================================== ====================

7. Configuration Verification:
1. Verify that the change source has been created successfully. If it is in autolog online mode, the value of source_type is autolog online:
Select source_name, source_description, source_type, source_database
From change_sources
Where source_name = 'DW ';

2. Verify the change set:
Change set will create an associated, still disabled, streams apply process, an apply queue and apply queue table.
Select set_name,
Set_description,
Change_source_name,
Apply_name,
Queue_name,
Queue_table_name
From change_sets
Where set_name = 'cs05 ';

-- Check the definition of the underlying stream apply process:
Select app. apply_name, q. name, app. status, qt. queue_table
From dba_apply app, dba_queues q, dba_queue_tables qt
Where app. apply_user = 'cdc _ admin'
And q. owner = 'cdc _ admin'
And qt. owner = 'cdc _ admin'
And q. name = app. queue_name
And qt. queue_table = q. queue_table
This statement displays the status of the apply process of the underlying streams.

3. Verify the change table:
Change tables will creates the Streams apply rules as well as the Streams capture rules on the staging database.
Select change_table_name,
Change_set_name,
Source_schema_name,
Source_table_name
From change_tables
Where change_table_schema = 'cdc _ admin'
Order by change_table_name;

-- Check the underlying streams capture and apply rules:
Select streams_name,
Streams_type,
Table_owner,
Table_name,
Rule_type,
Source_database
From dba_streams_table_rules
Where rule_owner = 'cdc _ admin'
Order by table_name, rule_type, streams_type;
Note:
At this point, the underlying streams configuration is complete, but the capture, propagation, and apply processes are still inactive.

4.After the change set is activated, check whether the status of the apply process is enable:
Select apply_name, status from dba_apply where apply_user = 'cdc _ admin ';

5. Check the capture status:
Select capture_name, state, total_messages_captured from v $ streams_capture;
Note:
STATE can show several values until it shows capturing changes. Until state is capturing changes you will not see the value for TOTAL_MESSAGES_CAPTURED increase.

6. Check whether autolog online is correctly activated:
Select group #, thread #, sequence #, archived, status from v $ standby_log;
Note:
If autolog online is activated correctly, at least one standby logfile is in the ACTIVE status.

========================================================== ========================================================== ====================
Note: The autolog online method is based on AIX in 10.2.0.4. When you create a change set, a BUG occurs. BUG: 11768445.