This is the basic process for setting up CDC on an Oracle Data Integrator data model. Each of these steps is described in more detail below.
A lot of different journalizing knowledge module are delivered with the standard installation of ODI. You must import one in the project in order to use it.
Edit the data model you want to journalize, and then select the Journalizing tab.
Depending of the JKM that you choose, you have extra user configuration steps to perform. To have them, you must read the description part of the JKM.
create/configure the user for AUTO_CONFIGURATION and ASYNCHRONOUS_MODE:
create user ODI identified by ODI;
-- Allow the user to grant system privileges to other users, to change instance parameters and to create objects
grant dba to ODI;
-- Grant the user with administrator privileges for Oracle Streams (asynchronous mode only)
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'ODI');
END;
The work schema must be granted the SELECT ANY TABLE privilege be able to create views referring to tables stored in other schemas.
grant SELECT ANY TABLE to ODI;
You should now flag the datastores that you want to journalize. A change in the datastore flag is taken into account the next time the journals are (re)started.
When flagging a model or a sub-model (tables), all of the datastores contained in the model or sub-model (tables) are flagged.
It is possible to add datastores to the CDC after the journal creation phase. In this case, the journals should be re-started.
If a datastore with journals running is removed from the CDC in simple mode, the journals should be stopped for this individual datastore. If a datastore is removed from CDC in Consistent Set mode, the journals should be restarted for the model (Journalizing information is preserved for the other datastores).
You only need to arrange the datastores in order when using consistent set journalizing.
You should arrange the datastores in the consistent set into an order which preserves referential integrity when using their changed data.
For example, if an ORDER table has references imported from an ORDER_LINE datastore (i.e. ORDER_LINE has a foreign key constraint that references ORDER), and both are added to the CDC, the ORDER datastore should come before ORDER_LINE. If the PRODUCT datastore has references imported from both ORDER and ORDER_LINE (i.e. both ORDER and ORDER_LINE have foreign key constraints to the ORDER table), its order should be lower still.
Changes to the order of datastores are taken into account the next time the journals are (re)started.
If existing scenarios consume changes from this CDC set, you should regenerate them to take into account the new organization of the CDC set.
When you add a subscriber for the first time, the Archive Log Mode of the database is verified on the step 25. This will be checked only the first time the journal is created. Afterwards, this statement is ignored thanks to the source query.
You must perform this action: Oracle Database - How to change the Database Archiving Mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM ARCHIVE LOG START;
To verify that archive logging is properly configured, perform:
ALTER SYSTEM SWITCH LOGFILE;
and verify that a new archive log file has been created in the archive log directory. Do not continue, until this works.
Starting the journals creates the CDC infrastructure if it does not exist yet. It also validates the addition, removal and order changes for journalized datastores.
Stopping the journals deletes the entire the journalizing infrastructure and all captured changes are lost. Restarting a journal does not remove or alter any changed data that has already been captured.
A session begins to start or drops the journals. You can track this session from the Operator. The journalizing status of the table must become green.
Datastores in models or interfaces have an icon marker indicating their journalizing status in Designer's current context:
Once journalizing is started and changes are tracked for subscribers, it is possible to view the changes captured.
This window selects data using the journalizing views.
The changed data displays three extra columns for the changes details:
Journalized data is mostly used within integration processes. Changed data can be used as the source of integration interfaces. The way it is used depends on the journalizing mode.
The journalizing infrastructure is implemented by the journalizing KM at the physical level. Consequently, Add Subscribers and Start Journals operations should be performed in each context where journalizing is required for the data model. It is possible to automate these operations using Oracle Data Integrator packages. Automating these operations is recommended to deploy a journalized infrastructure across different contexts.
Typical situation: the developer manually configures CDC in the Development context. After this is working well, CDC is automatically deployed in the Test context by using a package. Eventually the same package is used to deploy CDC in the Production context.
To automate journalizing setup:
When this scenario is executed in a context, it starts the journals according to the model configuration and creates the specified subscribers using this context.
It is possible to split subscriber and journal management into different steps and packages. Deleting subscribers and stopping journals can be automated in the same manner.
ALTER SYSTEM SET STREAMS_POOL_SIZE=52428800 SCOPE=BOTH;
alter table "SALES" add constraint SALES_PK primary key("PROD_ID","CUST_ID","TIME_ID","CHANNEL_ID","PROMO_ID")