DBA Hub

📋Steps in this guide1/3

COLS & COLSEXCEPT FILTER in oracle goldengate DBACLASS

COLS & COLSEXCEPT filter parameters are used to exclude or include(select) few columns of a table from goldengate extraction. i.e if a table on source has 5 column, and requirement is to replicat only 3 columns, then COLS and COLSEXCEPT filter can be used These […]

oracle replicationintermediate
by OracleDba
11 views
1

EXAMPLE:

Here we will extract only EMP_ID, EMP_NAME, DEPTNO and exclude SALCATGOERY, SERVICE_TYPE. COLS and COLEXCPET parameter can be defined either in EXTRACT or EXTRACT PUMP PARAMETER.
Step 1
2

COLSEXCEPT:

If we are defining COLSEXCEPT parameter in extract parameter, then extract prm file will look as below. extract parameter file: Extract pump parameter file: As we used COLSEXCEPT filter in the extract, We need to use PASSTHRU parameter in pump parameter file. But, If we are defining colsexcept parameter in pump parameter, then pump prm file will look as below.i.e no changes to extract file. Extract parameter file: As we used COLSEXCEPT filter in the pump itself, No need to add PASSTHRU parameter.
Step 2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
EXTRACT EXT1
userid ggate_user, password ggate_user
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100
EXTTRAIL /u01/ggate/datacapture/T0
WARNLONGTRANS 6H, CHECKINTERVAL 1H
TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);

EXTRACT EXT1P
userid ggatebss, password ggatebss#123
PASSTHRU
RMTHOST 172.18.83.472, PORT 7893
RMTTRAIL /ggatebss/oradata/TI2/y0
TABLE APPLIANCE.EMPTAG;

EXTRACT EXT1
userid ggate_user, password ggate_user
FETCHOPTIONS  FETCHPKUPDATECOLS, USESNAPSHOT, USELATESTVERSION
DBOPTIONS ALLOWUNUSEDCOLUMN
DISCARDFILE /u01/ggate/datacapture/EXT4.dsc, APPEND, MEGABYTES 100
EXTTRAIL /u01/ggate/datacapture/T0
WARNLONGTRANS 6H, CHECKINTERVAL 1H

EXTRACT EXT1P
userid ggatebss, password ggatebss#123
RMTHOST 172.18.83.472, PORT 7893
RMTTRAIL /ggatebss/oradata/TI2/y0
TABLE APPLIANCE.EMPTAG,COLSEXCEPT (SALCATGOERY,SERVICE_TYPE);
3

COLS:

Instead of COLSEXCEPT, if you are using COLS , then the only change is COLSEXCEPT (SALCATGOERY,SERVICE_TYPE); – >> COLS(EMP_ID,EMP_NAME,DEPTNO); RESTRICTIONS : - Do not exclude key columns, and do not use COLSEXCEPT to exclude columns that contain unsupported data types. - COLS should include the column used in KEYCOL in replicat.
Step 3

Comments (0)

Please to add comments

No comments yet. Be the first to comment!