oracle cdc autolog

oracle cdc autolog

docker run --privileged --name oracle11g -p 1521:1521 -v /home/oracle/install:/install jaspeen/oracle-11g

docker exec -it oracle11g bash
su oracle

sqlplus / as sysdba

alter system set log_archive_dest_1 ="location=/opt/oracle/dbs mandatory reopen=5" log_archive_dest_state_1=enable

alter system set streams_pool_size='10' java_pool_size=50000000;


CREATE USER pink IDENTIFIED BY pink
QUOTA UNLIMITED ON SYSTEM 
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO pink;
GRANT CREATE TABLE TO pink;
GRANT CREATE TABLESPACE TO pink;
GRANT UNLIMITED TABLESPACE TO pink;
GRANT SELECT_CATALOG_ROLE TO pink;
GRANT EXECUTE_CATALOG_ROLE TO pink;
GRANT CREATE SEQUENCE TO pink;
GRANT CONNECT, RESOURCE, DBA TO pink;


EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub')




ALTER DATABASE FORCE LOGGING;


ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


create tablespace ts_chicago_daily datafile 'ts_cdcpub02.dbf' size 300m;

CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
   QUOTA UNLIMITED ON SYSTEM
   QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CONNECT, RESOURCE, DBA TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;

EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');


SET SERVEROUTPUT ON
VARIABLE f_scn NUMBER;
BEGIN
    :f_scn := 0;
    DBMS_CAPTURE_ADM.BUILD(:f_scn);
    DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :f_scn);
END;
/

1016084


BEGIN
   DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
       TABLE_NAME => 'pink.kiki');
END;
/

SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL




BEGIN
   DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
       change_source_name => 'CHICAGO',
       description => 'test source',
       source_database => 'ORCL',
       first_scn => 1016084);
END;
/


BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'CHICAGO_DAILY',
       description => 'change set for job history info',
       change_source_name => 'CHICAGO',
       stop_on_ddl => 'y');
END;
/


BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner => 'cdcpub',
       change_table_name => 'JOB_HIST_CT',
       change_set_name => 'CHICAGO_DAILY',
       source_schema => 'PINK',
       source_table => 'KIKI',
       column_type_list   => 'ID NUMBER(12), NAME VARCHAR2(23)',
       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 TS_CHICAGO_DAILY');
END;
/



BEGIN
   DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
       change_set_name => 'CHICAGO_DAILY',
       enable_capture => 'y');
END;
/


ALTER SYSTEM SWITCH LOGFILE;
--ALTER SYSTEM ARCHIVE LOG ALL;

GRANT SELECT ON cdcpub.job_hist_ct TO pink;


conn cdcpub/cdcpub

SELECT * FROM ALL_SOURCE_TABLES;

SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID 
FROM ALL_PUBLISHED_COLUMNS 
WHERE SOURCE_SCHEMA_NAME ='PINK' AND SOURCE_TABLE_NAME = 'KIKI';



BEGIN
       DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
       change_set_name => 'CHICAGO_DAILY',
       description => 'Change data for JOB_HISTORY',
       subscription_name => 'JOBHIST_SUB');
END;
/

BEGIN
       DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
       subscription_name => 'JOBHIST_SUB',
       source_schema => 'PINK',
       source_table => 'KIKI',
       column_list => 'ID,NAME',
       subscriber_view => 'JOBHIST_VIEW');
END;
/

SELECT UNIQUE CHANGE_SET_NAME, SOURCE_TABLE_NAME, COLUMN_NAME, PUB_ID 
FROM ALL_PUBLISHED_COLUMNS 
WHERE SOURCE_SCHEMA_NAME ='PINK' AND SOURCE_TABLE_NAME = 'KIKI';


BEGIN
   DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
       subscription_name => 'JOBHIST_SUB');
END;
/

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'JOBHIST_SUB');
END;
/


SELECT * FROM JOBHIST_VIEW;


conn pink/pink

insert into pink.kiki values('1','AMITRATHssssaaaa');

conn cdcpub/cdcpub

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'JOBHIST_SUB');
END;
/


SELECT * FROM JOBHIST_VIEW;