- 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.
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:
You can filter out content not needed for the target database with the FILTER parameter shown as follows:
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.
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.