Oracle GoldenGate Extract Trail Formats FORMATASCII | FORMATSQL | FORMATXML

By default Oracle GoldenGate will write the trail files in it’s own Canonical Format. But other than this default format, we can make the Extract Process to write the Trail files in the below formats which is offered by Oracle GoldenGate itself.,

ASCII
SQL
XML

In this article, we will see how to make the Extract process to write it’s file in the above formats.

ASCII Format

To make the Extract process write the data in an ASCII format, you need to use the below parameter in the Extract process parameter file,

FORMATASCII

You can format output that is compatible with most database load utilities and other programs that require ASCII input. This parameter is required by the file-to-database-utility initial load method.

A FORMATASCII statement affects all extract files or trails that are listed after it in the parameter file. The relative order of the statements in the parameter file is important. If listed after a file or trail specification, FORMATASCII will not take effect.

All the trail files (extract files) will be affected if they are mentioned or listed after the parameter FORMATASCII in the parameter file.

There are certain limitations needs to be noted when this parameter is used.,

Do not use FORMATASCII

if the data will be processed by the Replicat process. Replicat expects the default canonical format.
if FORMATSQL or FORMATXML is being used.
if the data contains LOBs.
if Extract is connected to a multi-byte DB2 subsystem.
if Oracle GoldenGate DDL support is active.
in pass-through mode in a data pump because there is no table metadata available to generate trail output in the specified form.,

Now, let us see how to use this parameter FORMATASCII and how the output is generated.,

I have a table as below in the source schema and it contains no rows.,

SQL> desc source.t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                    NOT NULL NUMBER
 EMP_NAME                                           VARCHAR2(20)
 SALARY                                             NUMBER(6)

SQL> select * from source.t1;

no rows selected

Add the parameter to the Extract process parameter file as below,

GGSCI (OGGR2-1.localdomain) 9> view params ext1

EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
FORMATASCII
EXTTRAIL /vol3/ogg/dirdat/et
TABLE source.*;

Let us start the extract now.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 52> info ext1

EXTRACT    EXT1      Last Started 2017-05-25 18:51   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           3106
Log Read Checkpoint  Oracle Redo Logs
                     2017-05-25 18:51:51  Seqno 255, RBA 4478464
                     SCN 0.3646969 (3646969)

Now the Extract EXT1 is started and it is in RUNNING state. Let us insert a record in the table SOURCE.T1 and update it. Check how the data is written in the extract file (trail file)

SQL> insert into source.t1 values(1,'VEERA',1000);

1 row created.

SQL> update source.t1 set salary=2000 where emp_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from source.t1;

    EMP_ID EMP_NAME                 SALARY
---------- -------------------- ----------
         1 VEERA                      2000

Let us open the extract file et000000 and check how the record is written.,

[oracle@OGGR2-1 dirdat]$ cat et000000
B,2017-05-28:16:05:37.000000,1495967737,256,
I,A,SOURCE.T1,EMP_ID,'1',EMP_NAME,'VEERA',SALARY,1000
V,B,SOURCE.T1,EMP_ID,'1',SALARY,1000
V,A,SOURCE.T1,EMP_ID,'1',SALARY,2000
C,

In the above output both Before and After images are captured by default. So by default the output contains the below,

1. Database object names, such as table and column names, and CHAR and VARCHAR data are written in the default character set of the operating system.
2. Without specifying any parameter options, FORMATASCII generates records in the following format.

Line 1 contains the following tab-delimited list:

The operation-type indicator: I, D, U, V (insert, delete, update, compressed update).
A before or after image indicator: B or A.
The table name in the character set of the operating system.
A column name, column value, column name, column value, and so forth.
A newline character (starts a new line).

Line 2 contains the following tab-delimited begin-transaction record:

The begin transaction indicator, B.
The timestamp at which the transaction committed.
The sequence number of the transaction log in which the commit was found.
The relative byte address (RBA) of the commit record within the transaction log.
Line 3 contains the following tab-delimited commit record:
The commit character C.
A newline character.

There are lot more options which can used with the FORMATASCII parameter. For complete detail of the options used please check the “Oracle GoldenGate Reference Guide”.

SQL Format

If you want the output of the Extract file in an SQL format then use the below parameter in the Extract process parameter file.

FORMATSQL

This parameter also have some limitations as below,

Do not use FORMATSQL

if the data will be processed by the Replicat process. Replicat expects the default canonical format.
if FORMATASCII or FORMATXML is being used.
if Oracle GoldenGate DDL support is active.
in pass-through mode in a data pump because there is no table metadata available to generate trail output in the specified form.

Now, I have edited the parameter file of the Extract process EXT1 as below,

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 67> view params ext1

EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
FORMATSQL
EXTTRAIL /vol3/ogg/dirdat/et
TABLE source.*;

Now the Extract EXT1 is started and it is in RUNNING state. Let us insert a record in the table SOURCE.T1 and update it. Check how the data is written in the extract file (trail file)

SQL> select * from source.t1;

no rows selected

SQL> insert into source.t1 values(1,'VEERA',1000);

1 row created.

SQL> update source.t1 set salary=2000 where emp_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from source.t1;

    EMP_ID EMP_NAME                 SALARY
---------- -------------------- ----------
         1 VEERA                      2000

Let us open the extract file et000000 and check how the record is written.,

[oracle@OGGR2-1 dirdat]$ cat et000000
B,2017-05-28:16:12:43.000000,1495968163,259,
INSERT INTO SOURCE.T1 (EMP_ID,EMP_NAME,SALARY) VALUES ('1','VEERA',1000);
UPDATE SOURCE.T1 SET SALARY=1000 WHERE EMP_ID='1';
UPDATE SOURCE.T1 SET SALARY=2000 WHERE EMP_ID='1';
C,

The default output is as below,
Database object names, such as table and column names, and CHAR and VARCHAR data are written in the default character set of the operating system.

Without options, FORMATSQL transactions are output as follows, in comma-delimited format:

The begin-transaction indicator, B.
The timestamp at which the transaction was committed.
The sequence number of the transaction log in which the commit was found.
The relative byte address (RBA) of the commit record within the transaction log.
The SQL statements.
The commit indicator, C.
A newline indicator.

This parameter FORMATSQL has three options which are below,

NONAMES

Omits column names for insert operations, because inserts contain all column names. This option conserves file size.

NOPKUPDATES

Converts UPDATE operations that affect columns in the target primary key to a DELETE followed by an INSERT. By default (without NOPKUPDATES), the output is a standard UPDATE operation.

ORACLE

Formats records for compatibility with Oracle databases by converting date and time columns to a format accepted by SQL*Plus.

For complete detail of the options used please check the “Oracle GoldenGate Reference Guide”.

XML Format

If you want the output of the Extract file in an XML format then use the below parameter in the Extract process parameter file.

FORMATXML

This parameter also have some limitations as below,

Do not use FORMATXML

if the data will be processed by the Replicat process. Replicat expects the default canonical format. Do not use FORMATXML if FORMATASCII or FORMATSQL is being used.
if Extract is connected to a multi-byte DB2 subsystem.
if Oracle GoldenGate DDL support is active.
in pass-through mode in a data pump because there is no table metadata available to generate trail output in the specified form.

Now, I have edited the parameter file of the Extract process EXT1 as below,

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 86> view params ext1

EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
FORMATXML
EXTTRAIL /vol3/ogg/dirdat/et
TABLE source.*;

Now the Extract EXT1 is started and it is in RUNNING state. Let us insert a record in the table SOURCE.T1 and update it. Check how the data is written in the extract file (trail file)

SQL> select * from source.t1;

no rows selected

SQL> insert into source.t1 values(1,'VEERA',1000);

1 row created.

SQL> update source.t1 set salary=2000 where emp_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from source.t1;

    EMP_ID EMP_NAME                 SALARY
---------- -------------------- ----------
         1 VEERA                      2000

Let us open the extract file et000000 and check how the record is written.,

The parameter FORMATXML have below options which can used along with this.,

ENCODING UTF-8

Outputs the full sized XML to the XML file in UTF-8, but does not output headers.
The XML header tag and root node are included in the XML output. The root node is output as OracleGoldenGateFormatXML.
Regardless of their size, XML stored as CLOB is output in a CDATA section and binary data including BLOB is output to Base64 encoding.

INLINEPROPERTIES | NOINLINEPROPERTIES

Controls whether or not properties are included within the XML tag or written separately. INLINEPROPERTIES is the default.

TRANS | NOTRANS

Controls whether or not transaction boundaries and commit timestamps should be included in the XML output. TRANS is the default.

For complete detail of the options used please check the “Oracle GoldenGate Reference Guide”.

Hope this was helpful. Your comments and suggestions are always welcome.

Cheers,
Veera 🙂