- Making sure the DB metadata is well defined.
ASSUMETARGETDEFS, SOURCEDEF, TARGETDEFS - Mapping data between the source and target database tables.
COLMAP and COLMATCH - Selecting and filtering data.
WHERE, FILTER, COLS, COLEXCEPT - Performing data transformation as needed
@STREQ, @STRCAT, @STREXT, @COLTEST @STRFIND, SQLEXEC - Adding more information for auditing purposes
- Calling event actions for alerts and notification.
EVENTACTIONS
Let's discuss these setup with the examples. Please refer to the following articles for in--depth discussion for different types of databases.
Mapping (with COLMAP and COLMATCH) can be performed either in pump or replicat. However, it's best practices to have the mapping on the replicat side. The followings are some examples:
COLMATCH PREFIX A_ Map account, target accnt, COLMAP (usedefaults, CUST_ID = C_CUST_ID);
You can filter out content not needed for the target database with the FILTER parameter shown as follows:
map account, target account, filter (a_account_balance>1000);
You don't want to included extensive transformation in the replicat because it slows down the data loading process and introduces lag time. In this case, you can consider the ETL or ELT tools. However, Oracle GoldenGate provides the data transformation support, you can still perform data transformation as needed. The following is an example.
Map customer, target customer, COLMAP (USEDEFAULTS, C_FULL_NAME = @STRCAT(C_NAME, " ", C_MIDDLE_NAME, " ", C_FAMILY_NAME));
In replicat, you need to consider the following constraints on the target database: primary key and foreign key. Turning on the constraints off can help improved the performance. The following pl/sql predure is a good example to setup this.
create or replace PROCEDURE toggle_constraints(toggle IN VARCHAR2) IS begin if(toggle = 'DISABLE') THEN for cur in (select owner,constraint_name, table_name from user_constraints where constraint_type = 'P' and table_name in ('ACCOUNT','BRANCH','CUSTOMER')) loop execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE'; end loop; ELSIF (toggle = 'ENABLE') THEN for cur in (select owner,constraint_name, table_name from user_constraints where constraint_type = 'P' and table_name in ('ACCOUNT','BRANCH','CUSTOMER')) loop execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE'; end loop; END IF; end;