/* make the above changes for the source database by creating an include file (ifile) and referencing it in the spfile. Create the ifile with vi, create the pfile to edit from the spfile and then recreate the spfile with the ifile parameter before proceeding. */

shutdown immediate;

startup mount;

-- begin archiving redo logs
alter database archivelog;

-- force logging of all transactions: override nologging statements
alter database force logging;

-- one option among several
alter database add supplemental log data;

alter database open;

-- validate archivelogging
archive log list;

alter system switch logfile;

archive log list;

-- retest initalization parameters changes to verify modification to planned values

-- validate force and supplemental logging
col log_min format a7
col log_pk format a6
col log_pk format a6
col log_ui format a6
col log_fk format a6
col log_all format a7
col force_log format a9

SELECT supplemental_log_data_min LOG_MIN, supplemental_log_data_pk LOG_PK, supplemental_log_data_ui LOG_UI, supplemental_log_data_fk LOG_FK, 
supplemental_log_data_all LOG_ALL, force_logging FORCE_LOG
FROM v$database;

SELECT tablespace_name, force_logging
FROM dba_tablespaces;

-- examine CDC related data dictionary objects
SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE 'CDC%$';

desc cdc_system$

SELECT * FROM cdc_system$;

/* The publisher will need to reference the global name of the source database. The global name of the source database will be used on the destination database to create the AutoLog change source. The source database DBA can query the GLOBAL_NAME column in the GLOBAL_NAME view on the source database to retrieve this information for the publisher. So on the source db save the result from the following query: */

SELECT * FROM global_name;

/* also determine the log file size used on the source database and, also, the number of log file groups. The number of standby log files groups, created later, must be one more than the number  of redo log groups on the source. */

desc v$log

SELECT group#, bytes
FROM v$log;

/* make the above changes for the destination database by creating an include file (ifile) and referencing it in the spfile. Create the ifile with vi, create the pfile to edit from the spfile and then recreate the spfile with the ifile parameter before proceeding. */

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

-- validate archivelogging
archive log list;

alter system switch logfile;

archive log list;

-- retest initalization parameters changes to verify modification to planned values

-- examine CDC related data dictionary objects
SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE 'CDC%$';

desc cdc_system$

SELECT * FROM cdc_system$;

exec dbms_capture_adm.prepare_table_instantiation('sh.products');

SELECT table_name, scn, supplemental_log_data_pk PK, supplemental_log_data_ui UI,
supplemental_log_data_fk FK, supplemental_log_data_all "ALL"
FROM dba_capture_prepared_tables;

exec dbms_cdc_publish.create_autolog_change_source('CS_DEMO3', 'AutoLog Demo', 'PROD', 207722, 'Y');

-- to create an AutoLog archive change source omit the last parameter

exec dbms_cdc_publish.create_change_set('OMEGA_CSET', 'change set info', 'CS_DEMO3', 'Y');

SELECT set_name, change_source_name, capture_name, queue_name, publisher, stop_on_ddl
FROM all_change_sets;

exec dbms_cdc_publish.create_change_table('CDCADMIN', 'PRODUCT_CTAB', 'OMEGA_CSET', 'SH', 'CDC_DEMO3', 'PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50), PROD_LIST_PRICE NUMBER(8,2)', JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)', 'BOTH', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'TABLESPACE CDCTBSP');

SELECT object_name, object_type
FROM user_objects
ORDER BY 2,1;

col high_value format a15

SELECT table_name, composite, partition_name, high_value
FROM user_tab_partitions;

GRANT select ON product_ctab TO <new_end_user>;

conn / as sysdba

desc cdc_change_tables$

SELECT change_set_name, source_schema_name, source_table_name
FROM cdc_change_tables$;

/* This example creates a change table named product_ctab within change set OMEGA_CSET. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside in the source database, not the destination database.

The capture_values setting in the example indicates that for update operations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the update occurred and the other row will contain the row values after the update occurred. */

exec dbms_cdc_publish.alter change_set('OMEGA_CSET', enable_capture => 'Y');

SELECT set_name, change_source_name, capture_enabled
FROM cdc_change_sets$;

/* The publisher controls subscriber access to change data by granting and revoking the SQL SELECT privilege on change tables for users and roles on the destination database. The publisher grants access to specific change tables. Without this step, a subscriber cannot access any change data. This example assumes that user subscriber1 already exists. */

-- on the destination database

conn cdcadmin/cdcadmin

GRANT SELECT ON cdcadmin.products_ct TO subscriber1;

-- The Change Data Capture asynchronous AutoLog system is now ready for subscriber1 to create subscriptions.