In this blog, you will see how to output Goldengate Trailfile contents to a CSV file. This can be achieved through 2 methods.
1) Using FORMATASCII
2) Using Oracle Goldengate Application Adapter
1) Using FORMATASCII
FORMATASCII is a parameter of the Oracle Goldengate CORE product and can be leveraged to get the output in the form of CSV from a trail or database directly. There are pros and cons to this method.
-
Pros :
- Don’t need a separately licensed product to extract data to CSV format.
- Easier to extract using the core Oracle Goldengate product.
-
Cons :
- Can’t format the datatypes such as LONG RAW or CLOB or BLOB to ASCII.
- Limited options for the output such as the output can only be formatted into single quotes or noquotes
EXTRACT EFA
userid ggs, password ggs
REPORTCOUNT EVERY 5 MINUTES, RATE
FORMATASCII , NOHDRFIELDS, NONAMES, DELIMITER ‘~’
EXTFILE dirdat/fa
EOFDELAYCSECS 2
FETCHOPTIONS NOUSESNAPSHOT
STATOPTIONS REPORTFETCH
NOTCPSOURCETIMER
discardfile ./dirrpt/efa.dsc, append, megabytes 100
table live.rawt ;
GGSCI (myserver.techprof.org) 2> add extract EFA , exttrailsource dirdat/cc
EXTRACT added.
GGSCI (celclnx19.us.oracle.com) 3> start EFA
Sending START request to MANAGER …
EXTRACT EFA starting
cat fa000000
‘1’~’12345′
‘2’~’678910′
‘3’~’1112131415′
Note: The output of RAW is formatted to Hexadecimal format.
2) Using Oracle Goldengate File Adapter
This is a separately licensed product and needs to configure as a separate product. We will explain to you how to configure the Oracle Goldengate File Adapter.
Please download and install the Oracle Goldengate Version 12.2.0.1.170221 and configure the extract:
GGSCI (myserver.techprof.org) 3> edit params EIL
EXTRACT EIL
— DB environment settings
–SETENV (NLS_LANG = “AMERICAN_AMERICA.UTF8”)
userid ggs, password ggs
REPORTCOUNT EVERY 5 MINUTES, RATE
EXTTRAIL dirdat/cc
FETCHOPTIONS NOUSESNAPSHOT
STATOPTIONS REPORTFETCH
NOTCPSOURCETIMER
discardfile ./dirrpt/eil.dsc, append, megabytes 100
table live.rawt ;
GGSCI (myserver.techprof.org) 3>add extract EIL , tranlog , begin now
Extract added
GGSCI (myserver.techprof.org) 10> add exttrail dirdat/cc extract EIL
EXTTRAIL added.
GGSCI (myserver.techprof.org) 11> start EIL
Sending START request to MANAGER …
EXTRACT EIL starting
GGSCI (myserver.techprof.org) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EIL 00:00:00 00:00:02
SQL> create table rawt (id number primary key, rc raw(100));
Table Created
SQL> insert into rawt values (1, UTL_RAW.CAST_TO_RAW(‘12345’));
1 row created.
SQL> insert into rawt values (2, UTL_RAW.CAST_TO_RAW(‘678910’));
1 row created.
SQL> insert into rawt values (3, UTL_RAW.CAST_TO_RAW(‘1112131415’));
1 row created.
SQL> commit;
Commit complete.
Logdump 1 >ghdr on
Logdump 4 >detail data
Logdump 11 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 22 (x0016) IO Time : 2017/07/07 21:54:57.000.000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 873 AuditPos : 24012816
Continued : N (x00) RecCount : 1 (x01)
2017/07/07 21:54:57.000.000 Insert Len 22 RBA 1826
Name: LIVE.RAWT (TDR Index: 1)
After Image: Partition 12 G b
0000 0005 0000 0001 3100 0100 0900 0000 0531 3233 | ……..1……..123
3435 | 45
Column 0 (x0000), Len 5 (x0005)
0000 0001 31 | ….1
Column 1 (x0001), Len 9 (x0009)
0000 0005 3132 3334 35 | ….12345
Logdump 12 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 23 (x0017) IO Time : 2017/07/07 21:54:57.000.000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x01) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 873 AuditPos : 24284688
Continued : N (x00) RecCount : 1 (x01)
2017/07/07 21:54:57.000.000 Insert Len 23 RBA 1960
Name: LIVE.RAWT (TDR Index: 1)
After Image: Partition 12 G m
0000 0005 0000 0001 3200 0100 0a00 0000 0636 3738 | ……..2……..678
3931 30 | 910
Column 0 (x0000), Len 5 (x0005)
0000 0001 32 | ….2
Column 1 (x0001), Len 10 (x000a)
0000 0006 3637 3839 3130 | ….678910
Logdump 13 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x0c)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 27 (x001b) IO Time : 2017/07/07 21:54:57.000.000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 873 AuditPos : 26386448
Continued : N (x00) RecCount : 1 (x01)
2017/07/07 21:54:57.000.000 Insert Len 27 RBA 2063
Name: LIVE.RAWT (TDR Index: 1)
After Image: Partition 12 G e
0000 0005 0000 0001 3300 0100 0e00 0000 0a31 3131 | ……..3……..111
3231 3331 3431 35 | 2131415
Column 0 (x0000), Len 5 (x0005)
0000 0001 33 | ….3
Column 1 (x0001), Len 14 (x000e)
0000 000a 3131 3132 3133 3134 3135 | ….1112131415
Now download the Oracle Goldengate Flat file Adapter from edelivery.oracle.com
Oracle GoldenGate Command Interpreter
Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_161019.1437
Linux, x64, 64bit (optimized), Generic on Oct 19 2016 16:01:40
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.
GGSCI (myserver.techprof.org) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Create the properties file for the Flat file adapter
cat ffue.properties
#————————
#LOGGING OPTIONS
#————————
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true
#————————
#FLAT FILE WRITER OPTIONS
#————————
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_
#————————
# dsvwriter options
#————————
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
#dsvwriter.files.onepertable=false
dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
dsvwriter.files.control.use=true
dsvwriter.files.control.ext=_data.control
dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=<NULL>
dsvwriter.dsv.fielddelim.chars=|
dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars=”
dsvwriter.dsv.quotes.escaped.chars=””
dsvwriter.metacols=position,txind,opcode,timestamp,schema,table
dsvwriter.metacols.txind.fixedlen=1
dsvwriter.metacols.txind.begin.chars=B
dsvwriter.metacols.txind.middle.chars=M
dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n
create the Flatfile parameter file
cat ffue.prm
— To setup extract as a pump to read the trail and output flat-files
— ggsci> add extract ffue, extTrailSource dirdat/tc
— ggsci> info ffue
Extract ffue
CUserExit flatfilewriter.so CUSEREXIT PassThru IncludeUpdateBefores, PARAMS “dirprm/ffue.properties”
— CUserExit flatfilewriter.dll CUSEREXIT PassThru IncludeUpdateBefores, PARAMS “dirprm/ffue.properties”
SourceDefs dirdef/tc.def
— Table gg.employees;
— Table gg.job_history;
Table live.*;
GGSCI (myserver.techprof.org) 7> ADD EXTRACT FFUE,EXTTRAILSOURCE dirdat/cc
EXTRACT added.
GGSCI (myserver.techprof.org) 7> start FFUE
Sending START request to MANAGER …
EXTRACT FFUE starting
GGSCI (myserver.techprof.org) 2> view report FFUE
***********************************************************************
Oracle GoldenGate Capture
Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_161019.1437
Linux, x64, 64bit (optimized), Generic on Oct 19 2016 16:19:51
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.
Starting at 2017-07-07 22:05:32
***********************************************************************
***********************************************************************
Started Oracle GoldenGate Adapter for Flat Files
12.2.0.1.5 OGGADP_12.2.0.1.2ADBP_PLATFORMS_161206.1109
***********************************************************************
2017-07-07 22:05:32 INFO OGG-04525 /u01/app/oracle/product/GG1/Adapter/extract running with user exit library flatfilewriter.so, current session character se
t is CESU-8.
***********************************************************************
** Run Time Messages **
***********************************************************************
2017-07-07 22:05:32 INFO OGG-02243 Opened trail file dirdat/cc000000000 at 2017-07-07 22:05:32.722450.
2017-07-07 22:05:32 WARNING OGG-02761 Source definitions file, dirdef/tc.def, is ignored because trail file dirdat/cc000000000 contains table definitions.
2017-07-07 22:05:32 INFO OGG-06508 Wildcard MAP (TABLE) resolved (entry live.*): Table “LIVE”.”RAWT”.
2017-07-07 22:05:32 INFO OGG-02756 The definition for table LIVE.RAWT is obtained from the trail file.
2017-07-07 22:05:32 INFO OGG-06509 Using the following key columns for source table LIVE.RAWT: ID.
cat pump_LIVE_RAWT_2017-07-07_22-05-32_00000_data.dsv
“0000000000000722”|”B”|”I”|”2017-07-07 21:54:57.000000″|”LIVE”|”RAWT”|1|<NULL>
“00000000000007a8″|”M”|”I”|”2017-07-07 21:54:57.000000″|”LIVE”|”RAWT”|2|<NULL>
“000000000000080f”|”E”|”I”|”2017-07-07 21:54:57.000000″|”LIVE”|”RAWT”|3|<NULL>
[myserver]/u01/app/oracle/product/GG1/Adapter/dirout> ls -ltr
-rw-r—– 1 bugmnt bugmnt 237 Jul 7 22:05 pump_LIVE_RAWT_2017-07-07_22-05-32_00000_data.dsv
-rw-r—– 1 bugmnt bugmnt 59 Jul 7 22:05 LIVE.RAWT_data.control
[myserver]/u01/app/oracle/product/GG1/Adapter
There you can see the data is written in the form of a CSV file.
Recent Comments