In this section, we'll go over some advanced settings you could apply such as filtering, mapping, and data transformation.
OGG, in addition to replicating, allows data filtering, mapping, extraction, and transformation. Following, you'll discover a number of other functions that OGG can perform at either the source host or target host.
To capture all table changes in a schema, use the following wildcard specification:
TABLE SCOTT.* ;When you need to exclude tables from being replicated, use the
tableexcludeclause as follows:TABLEEXLCUDE SCOTT.EMP ; TABLE SCOTT.* ;
OGG allows you to filter data based on Data Manipulation Language (DML) as follows:
… IGNOREDELETES MAP SCOTT.* , TARGET SCOTT.* ; … … IGNOREUPDATES MAP SCOTT.* , TARGET SCOTT.* ; … … IGNOREINSERTS MAP SCOTT.* , TARGET SCOTT.* ; …
You don't need to replicate every column of a table if the downstream system only requires a subset. We do it using the following:
TABLE SCOTT.TCUSTMER, COLS(name,city,state) ; TABLE SCOTT.TCUSTMER, COLEXCEP(CODE_ID) ;
You can also ignore specific users from being captured. This is useful when the majority of schemas are being replicated with a few exceptions. An example is as follows:
… TRANLOGOPTIONS EXCLUDEUSER <user> …
Filter rows based on specific column values or when specific conditions are met. This can be done using the following format:
… MAP SCOTT.TCUSTMER , TARGET SCOTT.TCUSTMER, WHERE (STATE in "CA") ; …
Test for existence of a column in a data record as follows:
… MAP SCOTT.EMP , TARGET SCOTT.EMP,WHERE (SAL=@PRESENT AND SALARY > 999) ; …
Data transformation is one of the tool's powerful capabilities for processes such as ETL (Extract-Transformation-Load). It can be done for the following:
For mapping columns as follows:
… MAP SCOTT.EMP , TARGET SCOTT.EMP, COLMAP(USEDEFAULTS, SAL=SALARY); …
For entering default dates as follows:
… MAP SCOTT.TCUSTORD , TARGET SCOTT.TCUSTORD, COLMAP(USEDEFAULTS, ORDER_DATE=@DATENOW()); …
For concatenating strings as follows:
… MAP SCOTT.EMP , TARGET SCOTT.EMP, COLMAP(USEDEFAULTS, NAME=@STRCAT(FIRST_NAME," ",LAST_NAME)); …
We can perform the DML conversion in the following way. Inserts will become updates, updates will become deletes, and so on:
INSERTUPDATES INSERTDELETES UPDATEDELETES
For troubleshooting and reporting examples with OGG, tail the last 50 or so lines of the following file to view informational entries, warnings, and errors in OGG processing:
$ <OGG_HOME>/ggserr.logViewing information about an Extract/Replicat's checkpoints, RBA, and trailfile sequence can be done using the following commands:
$ GGSCI> info all | <group>View the process report should the extract not start.
Groupcan be an extract, data pump, or replicat.$ GGSCI> view report <group> GGSCI> start ex01sand GGSCI> info all
The output would be as follows:
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EX01SAND 00:00:00 00:02:59 EXTRACT RUNNING PP01SAND 00:00:00 00:00:01 REPLICAT RUNNING RE01SAND 00:00:00 00:00:03
We can see that
ex01sandwon't start.GGSCI> view report ex01sandThe output would be as follows:
2012-12-12 19:46:23 ERROR OGG-00664 OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied). 2012-12-12 19:46:23 ERROR OGG-01668 PROCESS ABENDING.
Edit the extract parameter file and make sure you update it with the correct password and then re-start the extract.
GGSCI> edit param ex01sandEnsure you add the correct password.
GGSCI> start ex01sand GGSCI> info all
The output should be as follows:
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EX01SAND 00:07:11 00:00:01 EXTRACT RUNNING PP01SAND 00:00:00 00:00:02 REPLICAT RUNNING RE01SAND 00:00:00 00:00:05
Oracle GoldenGate is very flexible when it comes to dealing with data transformation and row filtering. Not only can it filter rows, but it can also filter based on the type of DML operations, such as updates, inserts, and deletes.
We've seen data capture by specifying explicitly the table name. However, you can use wild cards as well for an entire schema, or a specific table pattern such as TABLE SCOTT.*_TMP.
When you want to capture an entire schema and would like to exclude one or more tables from the capture process, make certain that the TABLEEXCLUDE clause is placed before the TABLE capture.
You might wonder why you would filter based on the DML statements, but consider the following; say you have source data where you have deletes but you don't want to propagate them to the target so as to keep a record of the row. You would ignore that delete operation on the target by stating IGNOREDELETES in the Replicat parameter file.
When extracting tables, you don't necessarily have to extract every column if they are not needed in the downstream system. You can use the COLS and COLSEXCEPT clause to control the columns of interest at the source.
Filtering rows is a nice way to ensure you only extract those rows with specific column values for delivery to your downstream system. Another useful operation is the presence or absence of particular column values such as the built in column function @PRESENT. GoldenGate has a number of functions that are preceded by the @ sign. Other examples are; @ABSENT and @NULL. Another way to filter is based on the FILTER clause on specific DML operations. We have not seen it yet, but here is an example:
TABLE SCOTT.SAL, FILTER (ON UPDATE, ON DELETE, SALARY > 499); TABLE SCOTT.SAL, FILTER (ON INSERT, SALARY < 500);
When transforming data using COLMAP, you need to determine whether or not to use a definitions file. This depends whether or not source and target column structures are identical as defined by Oracle GoldenGate. GoldenGate considers columns identical when they have the same names, lengths, data type, semantics, and column order. As in the preceding example with data transformation, the @DATE function is synonymous to the SYSDATE function in Oracle and @STRCAT, is a string concatenation function.
The USEDEFAULTS clause applies default mapping rules to map source and target columns automatically if they have the same name.
DML conversions are interesting for the following reasons:
The
INSERTUPDATESclause converts source updates to inserts at the target. This is useful for maintaining a transaction history on that table.The
INSERTDELETESclause converts source deletes to inserts at the target. This is necessary for retaining a history of all records that were present at the source.The
UPDATEDELETESclause converts source deletes to updates at the target.
The error log file ggserr.log located at the root directory of your OGG installation, is one of the first sources of troubleshooting and diagnosing problems. It is a chronological log of events, commands, statistics, information, warnings, errors, and so on.
The INFO command is a quick way to view status information regarding the OGG processes, whether they are running, stopped, or abended, and, followed by the VIEW REPORT <group> as in the preceding example, will often point you to the root cause of the error.