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

We will explain in detail how to use both.

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.