News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Goldengate 12.2 New Feature Self-describing Trail Files

  • Posted by Gavin Soorma
  • On December 22, 2015
  • 0 Comments
  • 12.2, assumetargetdefs, DDR, golden gate, GoldenGate, LOGDUMP, new features, sourcedefs, TDR, trail file

One of the top new features introduced in Oracle GoldenGate 12.2 is the Self-describing trail files feature.

What this means is that no more do we have to worry about differences in table structures in the source and target databases and no more do we have to use the defgen utility or even the parameters ASSUMETARGETDEFS or SOURCEDEFS which we had to do in the earlier releases.

So many of the manual steps have been eliminated.

Now GoldenGate 12.2 supports replication even if source and target have different structures or different databases for that matter.

Metadata information is now contained in the trail files!

We will have a look at this in more detail in our example below, but now the trail files contains two important pieces of information – Data Definition Record (DDR) and Table Definition Record (TDR).

Each trail file contains a Database Definition Record (DDR) before first occurrence of a DML record or a SEQUENCE from a particular database. The DDR contains database specific information like characterset, database name, type of database etc.

Also each trail file contains a Table Definition Record (TDR) before first occurrence of a DML record for a particular table and this TDR section will have the table and column definition and metadata  including column number, data types, column lengths and so on.

Example

Let us now create a test table on both the source as well as target database with different column names.

 

Source


SQL> create table system.test_ogg
  2  (emp_id number, first_name varchar2(20), last_name varchar2(20));

Table created.

SQL> alter table system.test_ogg
  2  add constraint pk_test_ogg primary key (emp_id);

Table altered.

 

Target

 

SQL> create table system.test_ogg
2 (emp_id number,f_name varchar(20),l_name varchar2(20));

Table created.

SQL> alter table system.test_ogg
2 add constraint pk_test_ogg primary key (emp_id);

Table altered.

 

Create the Extract and Pump processes on the source
 
Source

 

host1>./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
Operating system character set identified as UTF-8.



GGSCI (host1 as oggsuser@DB01) 5> add extract etest integrated tranlog begin now                                                                                   
EXTRACT (Integrated) added.


GGSCI (host1 as oggsuser@DB01) 6> add exttrail ./dirdat/auxdit/lt extract                                                                                        etest
EXTTRAIL added.


GGSCI (host1 as oggsuser@DB01) 9> add extract ptest  exttrailsource ./dir                                                                                        dat/auxdit/lt
EXTRACT added.

GGSCI (host1 as oggsuser@DB01) 11> add rmttrail ./dirdat/bsstg/rt extract ptest
RMTTRAIL added.


GGSCI (host1 as oggsuser@DB01) 10> register extract etest database

2015-12-21 05:09:33  INFO    OGG-02003  Extract ETEST successfully registered with database at SCN 391450385.

 

Extract and Pump Parameter files


extract etest

USERIDALIAS oggsuser_bsstg

LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT


TRANLOGOPTIONS EXCLUDEUSER OGGSUSER
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048, parallelism 2)

EXTTRAIL ./dirdat/auxdit/lt

WARNLONGTRANS 2h, CHECKINTERVAL 30m
REPORTCOUNT EVERY 15 MINUTES, RATE
STATOPTIONS  RESETREPORTSTATS
REPORT AT 23:59
REPORTROLLOVER AT 00:01 ON MONDAY
GETUPDATEBEFORES

TABLE SYSTEM.TEST_OGG;



EXTRACT ptest

USERIDALIAS oggsuser_bsstg

RMTHOST host2,  MGRPORT 7809 TCPBUFSIZE 200000000, TCPFLUSHBYTES 200000000, compress

RMTTRAIL ./dirdat/bsstg/rt

PASSTHRU

REPORTCOUNT EVERY 15 MINUTES, RATE

TABLE SYSTEM.TEST_OGG;

On the target create and start the replicat process

 
Target

 

GGSCI (host2) 2> add replicat rtest integrated exttrail ./dirdat/bsstg/rt
REPLICAT (Integrated) added.

 

Replicat parameter file – note NO parameter ASSUMETARGETDEFS


REPLICAT rtest

SETENV (ORACLE_HOME="/orasw/app/oracle/product/12.1.0/db_1")
SETENV (TNS_ADMIN="/orasw/app/oracle/product/12.1.0/db_1/network/admin")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

USERIDALIAS oggsuser_auxdit


MAP SYSTEM.TEST_OGG, TARGET SYSTEM.TEST_OGG;

Start the Extract, Pump and Replicat processes
 

Source

 

GGSCI (host1 as oggsuser@DB01) 15> start manager
Manager started.


GGSCI (host1 as oggsuser@DB01) 16> start etest
EXTRACT ETEST starting


GGSCI (host1 as oggsuser@DB01) 17> start ptest

Sending START request to MANAGER ...
EXTRACT PTEST starting

 

Target

 

GGSCI (host2) 3> start rtest

Sending START request to MANAGER ...
REPLICAT RTEST starting


GGSCI (host2) 4> info rtest

REPLICAT   RTEST     Last Started 2015-12-21 05:21   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:08:53 ago)
Process ID           29864
Log Read Checkpoint  File ./dirdat/bsstg/rt000000000
                     First Record  RBA 0


 

On the source database insert a row into the TEST_OGG table

 

Source

 

SQL> insert into system.test_ogg
  2   values
  3   (007, 'JAMES','BOND');

1 row created.

SQL> commit;

Commit complete.

 

On the target we can see that the change has been replicated

 

Target

 

GGSCI (host2) 5> stats rtest latest

Sending STATS request to REPLICAT RTEST ...

Start of Statistics at 2015-12-21 05:26:32.


Integrated Replicat Statistics:

        Total transactions                                 1.00
        Redirected                                         0.00
        DDL operations                                     0.00
        Stored procedures                                  0.00
        Datatype functionality                             0.00
        Event actions                                      0.00
        Direct transactions ratio                          0.00%

Replicating from SYSTEM.TEST_OGG to SYSTEM.TEST_OGG:

*** Latest statistics since 2015-12-21 05:25:33 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.



 

From the replicat report file we can see that definition for the TEST_OGG table was obtained via the GoldenGate trail file.

 

2015-12-21 05:25:22  INFO    OGG-06505  MAP resolved (entry SYSTEM.TEST_OGG): MAP "SYSTEM"."TEST_OGG", TARGET SYSTEM.TEST_OGG.

2015-12-21 05:25:33  INFO    OGG-02756  The definition for table SYSTEM.TEST_OGG is obtained from the trail file.

By using the logdump utility we can view the Database Definition Record (DDR) as well as Table Definition Record (TDR) information contained in the trail file.

DDR Version: 1
Database type: ORACLE
Character set ID: we8iso8859p1
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT-07:00
Global name: BSSTG

2015/12/21 05:25:18.534.893 Metadata             Len 277 RBA 1541
Name: SYSTEM.TEST_OGG
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level
 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type
*
TDR version: 1
Definition for table SYSTEM.TEST_OGG
Record Length: 108
Columns: 3

EMP_ID       64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
FIRST_NAME   64     20       56  0  0 1 0     20     20      0 0 0 0 0 1    0 0   0    1       -1      0 0 0
LAST_NAME    64     20       82  0  0 1 0     20     20      0 0 0 0 0 1    0 0   0    1       -1      0 0 0
End of definition


 1

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *