Looking for something? Try here..

Thursday, December 29, 2016

Inserting > 4000 chars in a CLOB column

For a small testing involving CLOB column, I need to create a test table with clob column containing more than 4000 chars.

In order to achieve this, I did the following.
SQL> create table clob_test (name varchar2(20), details clob) tablespace tools;

Table created.

SQL> insert into clob_test values ('AAA', 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
  2  cartography and place names. Although many
  3  geographers are trained in toponymy and cartology, this is not their main preoccupation. Geographers study the space and the temporal database distribution of
phenomena, processes, and features as well as the interaction of humans and their environment.[7] Because space and place affect a variety of topics, such as
  4    5  economics, health, climate, plants and animals, geography is highly interdisciplinary. The interdisciplinary nature of the geographical approach depends on an
  6  attentiveness to the relationship between physical and human phenomena and its spatial patterns.[8]
  7  Names of places...are not geography...know by heart a whole gazetteer full of them would not, in itself, constitute anyone a geographer. Geography has higher
  8  aims than this: it seeks to classify phenomena (alike of the natural and of the political world, in so far as it treats of the latter), to compare, to
  9  generalize, to ascend from effects to causes, and, in doing so, to trace out the laws of nature and to mark their influences upon man. This is a description of
 10  the world—that is Geography. In a word Geography is a Science—a thing not of mere names but of argument and reason, of cause and effect.[9]
 11  —William Hughes, 1863
 12  Just as all phenomena exist in time and thus have a history, they also exist in space and have a geography.[10]
 13  —United States National Research Council, 1997
 14  Geography as a discipline can be split broadly into two main subsidiary fields: human geography and physical geography. The former largely focuses on the built
 15  environment and how humans create, view, manage, and influence space. The latter examines the natural environment, and how organisms, climate, soil, water, and
 16  landforms produce and interact.[11] The difference between these approaches led to a third field, environmental geography, which combines the physical and the
 17  human geography, and looks at the interactions between the environment and humans.[7]
 18  The oldest known world maps date back to ancient Babylon from the 9th century BC.[12] The best known Babylonian world map, however, is the Imago Mundi of 600
 19  BC.[13] The map as reconstructed by Eckhard Unger shows Babylon on the Euphrates, surrounded by a circular landmass showing Assyria, Urartu[14] and several
 20  cities, in turn surrounded by a "bitter river" (Oceanus), with seven islands arranged around it so as to form a seven-pointed star. The accompanying text
 21  mentions seven outer regions beyond the encircling ocean. The descriptions of five of them have survived.[15] In contrast to the Imago Mundi, an earlier
 22  Babylonian world map dating back to the 9th century BC depicted Babylon as being further north from the center of the world, though it is not certain what
 23  that center was supposed to represent.[12]
The ideas of Anaximander (c. 610 BC-c. 545 BC): considered by later Greek writers to be the true founder of geography, come to us through fragments quoted by
 24   25  his successors. Anaximander is credited with the invention of the gnomon, the simple, yet efficient Greek instrument that allowed the early measurement of
 26  latitude. Thales is also credited with the prediction of eclipses. The foundations of geography can be traced to the ancient cultures, such as the ancient,
 27  medieval, and early modern Chinese. The Greeks, who were the first to explore geography as both art and science, achieved this through Cartography,
Philosophy, and Literature, or through Mathematics. There is some debate about who was the first person to assert that the Earth is spherical in shape, with
 28   29  the credit going either to Parmenides or Pythagoras. Anaxagoras was able to demonstrate that the profile of the Earth was circular by explaining eclipses.
 30  However, he still believed that the Earth was a flat disk, as did many of his contemporaries. One of the first estimates of the radius of the Earth was made
 31  by Eratosthenes.[16]
 32  The first rigorous system of latitude and longitude lines is credited to Hipparchus. He employed a sexagesimal system that was derived from Babylonian
 33  mathematics. The meridians were sub-divided into 360°, with each degree further subdivided 60 (minutes). To measure the longitude at different location on
 34  Earth, he suggested using eclipses to determine the relative difference in time.[17] The extensive mapping by the Romans as they explored new lands would
 35  later provide a high level of information for Ptolemy to construct detailed atlases. He extended the work of Hipparchus, using a grid system on his maps and
 36  adopting a length of 56.5 miles for a degree.[18]
 37  From the 3rd century onwards, Chinese methods of geographical study and writing of geographical literature became much more complex than what was found in
 38  Europe at the time (until the 13th century).[19] Chinese geographers such as Liu An, Pei Xiu, Jia Dan, Shen Kuo, Fan Chengda, Zhou Daguan, and Xu Xiake wrote
important treatises, yet by the 17th century advanced ideas and methods of Western-style geography were adopted in China.' 39  );
insert into clob_test values ('AAA', 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
                                     *
ERROR at line 1:
ORA-01704: string literal too long
 
 
You can see that the insert statement erred out with ORA-01704 as the length of the field that I tried to insert is of 5147 characters.
So to over come this situation, we can make use of bind variables and a pl/sql block which can handle data upto 32k characters as shown below.

The code is very simple:

declare
clobdata varchar2(32767) := <data to be inserted>;
begin
execute immediate 'insert into <table> values(col1, :1)' using clobdata;
end;
/

Eg: as below.
SQL> set serverout on
declare
SQL>   2  clobdata varchar2(32767) := 'Geography is a systematic study of earth and its features. Traditionally, geography has been associated with
  3  cartography and place names. Although many
geographers are trained in toponymy and cartology, this is not their main preoccupation. Geographers study the space and the temporal database distribution of
  4    5  phenomena, processes, and features as well as the interaction of humans and their environment.[7] Because space and place affect a variety of topics, such as
  6  economics, health, climate, plants and animals, geography is highly interdisciplinary. The interdisciplinary nature of the geographical approach depends on an
  7  attentiveness to the relationship between physical and human phenomena and its spatial patterns.[8]
  8  Names of places...are not geography...know by heart a whole gazetteer full of them would not, in itself, constitute anyone a geographer. Geography has higher
  9  aims than this: it seeks to classify phenomena (alike of the natural and of the political world, in so far as it treats of the latter), to compare, to
generalize, to ascend from effects to causes, and, in doing so, to trace out the laws of nature and to mark their influences upon man. This is a description of
 10   11  the world—that is Geography. In a word Geography is a Science—a thing not of mere names but of argument and reason, of cause and effect.[9]
 12  —William Hughes, 1863
 13  Just as all phenomena exist in time and thus have a history, they also exist in space and have a geography.[10]
—United States National Research Council, 1997
 14   15  Geography as a discipline can be split broadly into two main subsidiary fields: human geography and physical geography. The former largely focuses on the built
 16  environment and how humans create, view, manage, and influence space. The latter examines the natural environment, and how organisms, climate, soil, water, and
landforms produce and interact.[11] The difference between these approaches led to a third field, environmental geography, which combines the physical and the
 17   18  human geography, and looks at the interactions between the environment and humans.[7]
 19  The oldest known world maps date back to ancient Babylon from the 9th century BC.[12] The best known Babylonian world map, however, is the Imago Mundi of 600
 20  BC.[13] The map as reconstructed by Eckhard Unger shows Babylon on the Euphrates, surrounded by a circular landmass showing Assyria, Urartu[14] and several
 21  cities, in turn surrounded by a "bitter river" (Oceanus), with seven islands arranged around it so as to form a seven-pointed star. The accompanying text
 22  mentions seven outer regions beyond the encircling ocean. The descriptions of five of them have survived.[15] In contrast to the Imago Mundi, an earlier
Babylonian world map dating back to the 9th century BC depicted Babylon as being further north from the center of the world, though it is not certain what
 23   24  that center was supposed to represent.[12]
 25  The ideas of Anaximander (c. 610 BC-c. 545 BC): considered by later Greek writers to be the true founder of geography, come to us through fragments quoted by
 26  his successors. Anaximander is credited with the invention of the gnomon, the simple, yet efficient Greek instrument that allowed the early measurement of
latitude. Thales is also credited with the prediction of eclipses. The foundations of geography can be traced to the ancient cultures, such as the ancient,
 27   28  medieval, and early modern Chinese. The Greeks, who were the first to explore geography as both art and science, achieved this through Cartography,
Philosophy, and Literature, or through Mathematics. There is some debate about who was the first person to assert that the Earth is spherical in shape, with
 29   30  the credit going either to Parmenides or Pythagoras. Anaxagoras was able to demonstrate that the profile of the Earth was circular by explaining eclipses.
However, he still believed that the Earth was a flat disk, as did many of his contemporaries. One of the first estimates of the radius of the Earth was made
 31   32  by Eratosthenes.[16]
 33  The first rigorous system of latitude and longitude lines is credited to Hipparchus. He employed a sexagesimal system that was derived from Babylonian
 34  mathematics. The meridians were sub-divided into 360°, with each degree further subdivided 60 (minutes). To measure the longitude at different location on
Earth, he suggested using eclipses to determine the relative difference in time.[17] The extensive mapping by the Romans as they explored new lands would
 35   36  later provide a high level of information for Ptolemy to construct detailed atlases. He extended the work of Hipparchus, using a grid system on his maps and
adopting a length of 56.5 miles for a degree.[18]
 37   38  From the 3rd century onwards, Chinese methods of geographical study and writing of geographical literature became much more complex than what was found in
 39  Europe at the time (until the 13th century).[19] Chinese geographers such as Liu An, Pei Xiu, Jia Dan, Shen Kuo, Fan Chengda, Zhou Daguan, and Xu Xiake wrote
important treatises, yet by the 17th century advanced ideas and methods of Western-style geography were adopted in China.';
 40   41  begin
   execute immediate 'insert into clob_test values (''BBB'', :1)' using clobdata;
 42   43  end;
/  44

PL/SQL procedure successfully completed.

SQL> select * from clob_test;

NAME
--------------------
DETAILS
--------------------------------------------------------------------------------
BBB
Geography is a systematic study of earth and its features. Traditionally, geogra


SQL>

Now you can see that the data we are trying to insert worked without issues.

There are also other ways using DBMS_SQL package and also using SQL*Loader. You can check about them using Oracle support notes Doc ID 160885.1 and Doc ID 2158771.1

Happy data loading...!!!

Monday, October 3, 2016

IGNOREDELETES with Oracle GoldenGate SQLEXEC

This is one of the several issues I underwent during my current project involving Oracle GoldenGate.
I'm using the below mapping statement with a lookup query on another table using SQLEXEC.
SQLEXEC (ID LOOKUP_MOI, QUERY "select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE &
where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN", PARAMS (TR_IN = C911001001, I_IN = C911001102)),& 
COLMAP ( 
ATTRIBUTEID=@COLSTAT (MISSING), 
TROUBLEREPORTID=@IF (@STRFIND (C911001001,'ID',1)=1,@COLSTAT (NULL),C911001001), 
NAME=C911003014, 
VALUE=C911005010, 
TYPE=C911003018, 
PARENTNAME=C911005001, 
INSTANCEID=@IF (@COLTEST (@GETVAL(LOOKUP_MOI.instanceid),NULL,INVALID),@COLSTAT (NULL),@GETVAL(LOOKUP_MOI.instanceid)), 
ENTRYID=C1);
Here when the lookup query executes, for the data it can find, we don't have any issues, but when a record doesn't have any value, for eg. let's say there is no matching where clause values to find, then the query results in ORA-01403 no data found.

Few data were never replaced by the new instanceid value or NULL during update operations. This is not an expected behaviour and hence in order to find which record's data the query is not able to find or to confirm that the query is actually looked up to result in ORA-01403, I had to turn on reporting of the error records as instanceid is a NOT NULL column. Reporting of the records erring can be turned on by using ERROR REPORT in the SQLEXEC statement as below.
SQLEXEC (ID LOOKUP_MOI, QUERY "select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE &
where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN", PARAMS (TR_IN = C911001001, I_IN = C911001102), ERROR REPORT) & 
This makes the GoldenGate process to register the details on the discard file with the records erred. I'm getting the following "VALUE NOT MAPPED" in the discard file for few records.
Stored procedure LOOKUP_MOI failed at seqno 1 rba 144430729 
Missing required param: TR_IN 

Input parameter values... 

TR_IN: * VALUE NOT MAPPED * 
I_IN: stbordel 
TR_IN: * VALUE NOT MAPPED * 
I_IN: * VALUE NOT MAPPED * 
* 
* 
This shouldn't be the case as I'm forcing the source table to log both C911001001 and C911001102 columns. C911001102 is not mapped to any of target table column but used in the lookup query.
GGSCI (xxx00450 as ggs_dba@XXXDB) 5> info trandata aradmin.t539 

Logging of supplemental redo log data is enabled for table ARADMIN.T539. 

Columns supplementally logged for table ARADMIN.T539: C1, C911001001, C911001102. 

Prepared CSN for table ARADMIN.T539: 13678637504852 
Now to check whether the columns are actually logged by the extract process, I use logdump utility to dig the trail file at that particular rba which reveals the values are actually logged.
Logdump 68 >pos 144430729 
Reading forward from RBA 144430729 
Logdump 69 >n 
___________________________________________________________________ 
Hdr-Ind : E (x45) Partition : . (x0c) 
UndoFlag : . (x00) BeforeAfter: A (x41) 
RecLength : 171 (x00ab) IO Time : 2016/09/12 03:44:44.000.000 
IOType : 134 (x86) OrigNode : 255 (xff) 
TransInd : . (x00) FormatType : R (x52) 
SyskeyLen : 0 (x00) Incomplete : . (x00) 
AuditRBA : 50785 AuditPos : 51572240 
Continued : N (x00) RecCount : 1 (x01) 

2016/09/12 03:44:44.000.000 GGSUnifiedUpdate Len 171 RBA 144430729 
Name: ARADMIN.T539 (TDR Index: 2) 
After Image: Partition 12 G b 
0000 0068 0000 0013 0000 000f 3030 3030 3030 3034 | ...h........00000004 
3236 3731 3136 3200 0400 0a00 0000 0674 6d75 7365 | 2671162........tmuse 
7200 0500 0a00 0000 0000 0057 d65c 4a00 0600 0a00 | r..........W.\J..... 
0000 0000 0000 0000 0200 0a00 1300 0000 0f54 5230 | .................TR0 
3030 3030 3137 3137 3838 3537 0017 000c 0000 0008 | 000017178857........ 
7374 626f 7264 656c 0000 0013 0000 000f 3030 3030 | stbordel........0000 
3030 3034 3236 3731 3136 3200 0400 0800 0000 0474 | 00042671162........t 
Before Image Len 108 (x0000006c) 
BeforeColumnLen 104 (x00000068) 
Column 0 (x0000), Len 19 (x0013) 
Column 4 (x0004), Len 10 (x000a) 
Column 5 (x0005), Len 10 (x000a) 
Column 6 (x0006), Len 10 (x000a) 
Column 10 (x000a), Len 19 (x0013) 
Column 23 (x0017), Len 12 (x000c) 

After Image Len 63 (x0000003f) 
Column 0 (x0000), Len 19 (x0013) 
Column 4 (x0004), Len 8 (x0008) 
Column 5 (x0005), Len 10 (x000a) 
Column 6 (x0006), Len 10 (x000a) 
Now, I got confused on why is that the discard file shows or GoldenGate throws that the values are not mapped. Raised an SR to get it clarified but unfortunately Oracle support was dead slow in responding and with no proper updates after uploading all the required param files, report files, discard files, trail log files, etc.

After careful consideration and investigation and comparing the data which were discarded and which were correctly getting the instanceid values, I can get a common pattern on the data as only the records deleted on the source database are having this VALUE NOT MAPPED error. I now got the clue on why this is happening. As per the application requirement, we are not propagating deletes from the source DB to the target DB, target DB being a form of warehousing for the closed tickets. I am using IGNOREDELETES in my replicat parameter file. The records are deleted and hence the fields in the trail has no value on it. :)

Coming to the actual issue on how to resolve the SQLEXEC not to reject the record, according to Oracle GoldenGate documents, when a SQLEXEC encounters an issue/ error, this column should be a "column missing" condition

IGNORE
Causes Oracle GoldenGate to ignore all errors associated with the stored procedure or query and continue processing. Any resulting parameter extraction results in "column missing" conditions. This is the default.


Find details here
So in my above mapping statement, by making INSTANCEID column to check for MISSING instead of NULL or INVALID should fix the issue. Isn't it? 
INSTANCEID=@IF (@COLTEST (@GETVAL(LOOKUP_MOI.instanceid),MISSING),@COLSTAT (NULL),@GETVAL(LOOKUP_MOI.instanceid)),
If you have answered Yes, then we have another twist here..
As there is actually no value mapped for the query to execute, this record is rejected and is not returning NULL as desired.
From Oracle documentation,

PARAMS [OPTIONAL | REQUIRED] parameter_name = {source_column | OGG_function} |NOPARAMS


For Oracle, whether or not a parameter is optional is automatically determined when retrieving the stored procedure definition.
REQUIRED indicates that a parameter value must be present. If the parameter value is not present, the SQL will not be executed.

We can also create a procedure to return NULL if there is an ORA-01403 returned only in case of providing both the required parameters. So this effort will again result in rejection of the record (doing nothing on the record)

The solution is to take the value from the lookup query itself by using the case statement of the select query which I have used in my parameter file as below.
select case when exists (select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN)   
then (select instanceid from DATASTORE.MANAGEDOBJECTINSTANCE where TROUBLEREPORTID=:TR_IN and INSTANCENAME=:I_IN)   
else NULL end instanceid from dual;
This actually returns either a valid result or a NULL so we don't have a missing column here.. The suggestion is not to leave the record unhandled.
Thanks K.Gan for letting me know this via OTN community discussion here.

The solution seems simple but the effort it took to debug the issue and to derive the actual result is really a painful task. The result is a bliss..

Few things..
The entire investigation including SR update and deriving at the solution/desired result took around a week's time..
When ignoring part of transaction (IGNOREDELETES in our case), be very careful on what might happen to the data and how to deal with it..
Tech forums such as OTN community is your best friend when seeking technical help. Make friends/ Network as much as you can..

Happy Ignoring transactions !! :)

Saturday, October 1, 2016

Oracle GoldenGate Replicat erroring with OCI Error ORA-24369

This post is to talk about an interesting issue that I troubleshooted after the project is moved on to pre-production and in soak period.

All is done with the UAT testing and all the defects were fixed and got the UAT pass signed off. Deployed the code in the production environment. Initial loading of data went on for 2 days without any issues. Now its time for real time sync. 

I started the real time replicat and monitoring the system thinking nothing should go wrong as this was a 3 months non stop, sort of no good sleep hard work project involving huge amount of learning as self and as a team. Periodically checking the status of the process and bang!!! The process got ABENDED.. 

Now started the nightmare. I started digging the issue. First, report file to see what was the issue? Ok, I have issue with some data in a particular table. Now discard file to see which record got discarded? Ok, got it. Check whether it is a valid data to the database as I'm ignoring few orphan records (records without parent records) originating from source system. Well, this is a valid data allowed to the database.

The error I received is as below.
2016-09-26 17:43:54 INFO OGG-01021 Command received from GGSCI: STATS. 

2016-09-26 17:46:02 INFO OGG-01971 The previous message, 'INFO OGG-01021', repeated 1 times. 

2016-09-26 17:46:02 WARNING OGG-00869 OCI Error ORA-24369: required callbacks not registered for one or more bind handles (status = 24369), SQL <UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = EMPTY_CLOB() WHERE x."ENTRYID" = :b0 RETURNING x."DETAILS" INTO :dl0>. 

2016-09-26 17:46:02 WARNING OGG-01004 Aborted grouped transaction on 'DATASTORE.WORKLOG', Database error 24369 (OCI Error ORA-24369: required callbacks not registered for one or more bind handles (status = 24369), SQL <UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = EMPTY_CLOB() WHERE x."ENTRYID" = :b0 RETURNING x."DETAILS" INTO :dl0>). 

2016-09-26 17:46:02 WARNING OGG-01003 Repositioning to rba 1597961 in seqno 0. 

2016-09-26 17:46:04 WARNING OGG-01154 SQL error 24369 mapping ARADMIN.T433 to DATASTORE.WORKLOG OCI Error ORA-24369: required callbacks not registered for one or more bind handles (status = 24369), SQL <UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = EMPTY_CLOB() WHERE x."ENTRYID" = :b0 RETURNING x."DETAILS" INTO :dl0>. 


The below statement is what was registered as abended from the discard file.
UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = EMPTY_CLOB() WHERE x."ENTRYID" = :b0 RETURNING x."DETAILS" INTO :dl0 
Oracle support site didn't help much at my first search and hit only 2 articles related to this error.. So as this is a production issue, opened a severity ticket and provided all the details, report files, parameter files and even the trail files as they requested for investigation.
EMPTY_CLOB() is an Oracle internal package to initialize a CLOB variable and my guess was whether this function is being called by application or by database internal call.
As the ticket is for 24/7 support, the engineers are rotated and from one of the engineer got a clue on how to make the replicat to display the SQL statements as they were applied. As per advice, included the following parameters in the replicat param file and started the replicat from GoldenGate home.

showsyntax
grouptransops 1
maxtransops 1

started the replicat as below from GoldenGate home
$ ./replicat paramfile ./dirprm/<replicat_name>.prm

The showsyntax is used to display the statements that the replicat is going to run against the database and from this method it is found that the actual run varies against the erred query.

This is the exact update statement that the showsyntax produced..
UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = '<LOB Data>' WHERE x."ENTRYID"='000000431066777' 
There is a query transformation as below when the error occurs.
UPDATE "DATASTORE"."WORKLOG" x SET x."DETAILS" = EMPTY_CLOB() WHERE x."ENTRYID" = :b0 RETURNING x."DETAILS" INTO :dl0 
By this time I came across another bug note "Bug 1555887" from Oracle Support site in which the problem statement had the below lines..
In OCI, as long as there's only one row to update, the OCI happy,however, if 
the test case is run once (several iterations), then run again without 
truncating the clob table, the following errors occur if more than one row 
matches the update statement where clause:
 
1st time with 2 rows - onexecute
 
ORA-24369: required callbacks not registered for one or more bindhandles

So now with this clue, I started to look for duplicate data in the table and yes, I have got the duplicate data produced in the table. So this is the root cause that the process got abended.
Now I can't just say this is a duplicate record (source might contain 2 records as well) but to figure out how this data got duplicated, I started the investigation again from application side which is the source for all the data. The application sql logs stated as below.. (Example shown of a different data as logs turned on to see the sequence of operation on the table)
....
....
....
INSERT INTO T433 (C2,C7,C8,C112,C911001009,C911001089,C911001302,C911002100,C911018012,C911018013,C911018014,C911018016,C911018045,C911018046,C911018047,C911018056,C911018057,C911018058,C911018201,C911018254,C911203021,C911203022,C911203023,C3,C5,C6,C1) 
VALUES ('TXGUser',0,'Task # 1.1.1 Assigned to Group N/A',';0;',NULL,'A00','1.1.TR0000019131158',' 1.1.1',EMPTY_CLOB(),'TR0000019131158','Notification','BRE','BRE','Ball','Notification','TXGUser','911SMC','808-827-0515','TSK000076213737','Ball','No','No','No',1475015152,'TXGUser',1475015152,'000000431352674') 
...
SELECT C911018012 FROM T433 WHERE C1 IN ('000000431352674') FOR UPDATE 
...
Setting LOB into the above row ... 
....
....
So this is just a single update happening on a single row. So duplicates from application is ruled out.
Checking in the source database where the application connects, shows no sign of duplicates as the main column (C1) has a unique constraint to avoid duplicates.
Checking on the target system reveals many duplicate data sitting happily occupying space..
SQL> select entryid, count(*) from worklog group by entryid having count(*) >1;

ENTRYID        COUNT(*)
-------------------- ----------
000000431066591               2
000000431066615               2
000000431066617               2
...
...
...
The count of those records was around 2000. So how was this possible?
I used HANDLECOLLISIONS when turning on the real time replicat which has KEYCOLS as ENTRYID for the WORKLOG table.
So is this duplicates still possible? Shouldn't it eliminate the records that were already loaded via initial loading as we have mentioned KEYCOLS in the table mapping?
The answers is in the Oracle support note Doc ID 1350948.1 OGG Can Insert Duplicate Rows When There Is No Unique Index Primary Key UI PK Defined.
Cool, I don't have an unique key defined on the target table which allowed all the data (delta data) to be inserted one more time creating duplicates making the bug to kick in here as well.
So I just removed all the duplicates by running the following block
create table del as select entryid from (select entryid, count(*) from worklog group by entryid having count(*) >1);
DECLARE
   wld        NUMBER;
   lv_stmt2   VARCHAR2 (1000);
BEGIN
   FOR eid IN (SELECT entryid
                 FROM del)
   LOOP
      lv_Stmt2 :=
            'select worklistid from (select * from worklog where entryid= '
         || eid.entryid
         || ' order by 1 desc) where rownum=1';

      EXECUTE IMMEDIATE lv_stmt2 INTO wld;

      DELETE FROM worklog
            WHERE worklistid = wld;
   END LOOP;
END;
/
drop table del purge;
Now to make sure I don't get duplicates again, I just created a unique index on ENTRYID column which is again my key column to do the table operations.
The data then replicated happily..

Moral of the story: Duplication is the worst enemy of the well built system :)

Few things:
To ignore orphan records, I used REPERROR (2291, ignore) in my parameter file.
The bug note mentioned in this blog is said to be fixed in product version 8.1.6.1.4 in the same note whereas my current database version this issue is on 11.2.0.4 with GoldenGate version 12.2.0.1
The whole troubleshooting exercise took around 3 days to fix the issue

Happy troubleshooting!!

Thursday, May 12, 2016

MS SQL Server to Oracle data replication using Oracle GoldenGate - Part 2

The part 1 of this blog post can be found in this link

I’ll go through the rest of the steps quickly here with explanations wherever required. Rest of the explanation can be read from this link.

5. Param Files Setup

5.1        Source files:

Place all these files in the dirprm directory under source GoldenGate home.

mgr.prm
PORT 11000
DYNAMICPORTLIST 15010-15020

msd1e.prm
EXTRACT MSD1E
SOURCEDB GG
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.56.131, MGRPORT 12000
RMTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za
TABLE GG_USER.EMP;

When you enable supplemental logging with the ADD TRANDATA command for at least one table in a SQL Server database, a secondary truncation point is created in the transaction log that has to be moved for log space to be released as needed, following subsequent log backups.
MANAGESECONDARYTRUNCATIONPOINT - Extract will not be running concurrently (for the same source database) with SQL Server transactional replication and/or CDC that is configured for applications other than Oracle GoldenGate. To read more about this parameter see this link.

msd1i.prm
SOURCEISTABLE
SOURCEDB GG
RMTHOST 192.168.56.131, MGRPORT 12000
RMTFILE /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi
TABLE gg_user.emp;

SOURCEISTABLE should be used on initial load extract alone which dictates the process to extract data directly from the table and use created DSN name for SOURCEDB to connect to the database (GG in our case).

Note: In this demo I have not used the local trail and pump processes unlike I have used in the Oracle to Oracle replication as explained in this link. It is always best practice to use local trails for real time capture and send to remote trail using data pump process.

5.2        Target Files:

I would like to replicate the data on to EMPLOYEE table under OPENSUSE user. I already have a table with the below definition. ID column has a unique constraint enabled.
SQL> desc opensuse.employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 FIRST_NAME                                         VARCHAR2(50)
 LAST_NAME                                          VARCHAR2(50)
 
Place all these files in the dirprm directory under target GoldenGate home.

mgr.prm
PORT 12000
DYNAMICPORTLIST 12010-12020
USERID GGS_DBA, PASSWORD ggs_dba

msd1r.prm
REPLICAT MSD1R
SOURCEDEFS /u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
USERID ggs_dba, PASSWORD ggs_dba
HANDLECOLLISIONS
MAP GG_USER.emp, TARGET opensuse.employee;

msd1i.prm
SPECIALRUN
END RUNTIME
USERID ggs_dba, PASSWORD ggs_dba
EXTFILE /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi
SOURCEDEFS /u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
MAP gg_user.emp, TARGET opensuse.employee;

SPECIALRUN should be used for initial load parameter file only and END RUNTIME dictates the process to end when the loading from the trial file has been completed.

6. Final Destination – The replication

Now once all the files are placed in their respective directories, do the following operations in the Source and Target as shown below.

Target:

Start the manager process to allow incoming connections for GoldenGate.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> 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.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (opensuse.selvapc.com) 1> start manager
Manager started.


GGSCI (opensuse.selvapc.com) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (opensuse.selvapc.com) 3> exit
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
 
Source:

Start the manager, real time extraction process and initial extract process.
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as windows-1252.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (Prinky) 1> start manager
Manager started.


GGSCI (Prinky) 2> ADD EXTRACT msd1e, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (Prinky) 3> ADD RMTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, EXTRACT msd1e
RMTTRAIL added.

GGSCI (Prinky) 4> start ext msd1e

Sending START request to MANAGER ...
EXTRACT MSD1E starting


GGSCI (Prinky) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     MSD1E       00:00:47      00:00:05


GGSCI (Prinky) 6> stats ext msd1e

Sending STATS request to EXTRACT MSD1E ...

No active extraction maps.
 
-- At this point, we don't have any active changes happening at the database in the real time even though we have data in the table.
-- When there is a change in the database (either of an insert, update, delete), then the process will show the status as below.
-- Inserted 4 rows to the gg_user.emp table now
-- Data in the source table now is as below
use ggate
select * from gg_user.emp

-- Result:
id first_name last_name
1 Selva  Kumar
2 Rajini  Kanth
3 David  Billa
4 Manic  Badsha
5 Manne  Pacquo
6 Rolls  Royse
7 Big  Ben
8 Masala  Poori
 
Continuing the operations in ggsci prompt
GGSCI (Prinky) 7> stats ext msd1e

Sending STATS request to EXTRACT MSD1E ...

Start of Statistics at 2016-05-08 02:19:16.

Output to /u01/app/oracle/product/12.1/oggcore_1/dirdat/za:

Extracting from gg_user.emp to gg_user.emp:

*** Total statistics since 2016-05-08 02:17:42 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

*** Daily statistics since 2016-05-08 02:17:42 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

*** Hourly statistics since 2016-05-08 02:17:42 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

*** Latest statistics since 2016-05-08 02:17:42 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

End of Statistics.


GGSCI (Prinky) 8> exit
 
Start the initial extract using "extract" executable so that the process ends when the initial data is completely extracted.
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>extract paramfile dirprm\msd1i.prm reportfile dirrpt\msd1i.rpt

2016-05-08 02:23:23  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************
               Oracle GoldenGate Capture for SQL Server
      Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
 Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 16:22:53

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2016-05-08 02:23:23
***********************************************************************

Operating System Version:
Microsoft Windows 8 , on x64
Version 6.2 (Build 9200)

Process id: 3328

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2016-05-08 02:23:23  INFO    OGG-03059  Operating system character set identified as windows-1252.

2016-05-08 02:23:23  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.

2016-05-08 02:23:23  WARNING OGG-05236  ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.

2016-05-08 02:23:23  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2016-05-08 02:23:23  INFO    OGG-03037  Session character set identified as windows-1252.

2016-05-08 02:23:23  INFO    OGG-06509  Using the following key columns for source table gg_user.emp: id.

2016-05-08 02:23:23  INFO    OGG-01851  filecaching started: thread ID: 72465440.

2016-05-08 02:23:23  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: MapViewOfFile  anon free: UnmapViewOfFile
    file alloc: MapViewOfFile  file free: UnmapViewOfFile
    target directories:
    E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                                4G
CACHEPAGEOUTSIZE (default):               4M
PROCESS VM AVAIL FROM OS (min):        7.97G
CACHESIZEMAX (strict force to disk):   6.88G

2016-05-08 02:23:23  WARNING OGG-01842  CACHESIZE PER DYNAMIC DETERMINATION (4G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 7.97G
Check swap space. Recommended swap/extract: 128G (64bit system).

Database Version:
Microsoft SQL Server
Version 11.00.5058
ODBC Version 03.80.0000

Driver Information:
SQLSRV32.DLL
Version 10.00.10240
ODBC Version 03.52

2016-05-08 02:23:29  INFO    OGG-01226  Socket buffer size set to 65536 (flush size 27985).

2016-05-08 02:23:29  INFO    OGG-01478  Output file /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi is using format RELEASE 12.2.

2016-05-08 02:23:29  INFO    OGG-02911  Processing table gg_user.emp.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************


Report at 2016-05-08 02:23:29 (activity since 2016-05-08 02:23:23)

Output to /u01/app/oracle/product/12.1/oggcore_1/dirdat/zi:

From Table gg_user.emp:
       #                   inserts:         8
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 
Now you can notice that all the 8 records in the table has been extracted by the initial load extract.

Target:
Start the initial replicat, real time replicat with HANDLECOLLISIONS and without HANDLECOLLISIONS and check for data sync.

Initial replicat can be started as a background process so that it can completed and exited when loading is done.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> sqlplus opensuse/opensuse

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 8 02:59:24 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 30 2016 08:46:44 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from employee;

no rows selected

SQL> !
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> echo $OGG_HOME
/u01/app/oracle/product/12.1/oggcore_1
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
[1] 27150
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> nohup: ignoring input and appending output to ‘nohup.out’

oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+  Running                 nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+  Running                 nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt &
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> jobs
[1]+  Done                    nohup replicat paramfile $OGG_HOME/dirprm/msd1i.prm reportfile $OGG_HOME/dirrpt/msd1i.rpt
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> exit
exit

SQL> set lines 300
SQL> select * from employee;

        ID FIRST_NAME                                         LAST_NAME
---------- -------------------------------------------------- --------------------------------------------------
         1 Selva                                              Kumar
         2 Rajini                                             Kanth
         3 David                                              Billa
         4 Manic                                              Badsha
         5 Manne                                              Pacquo
         6 Rolls                                              Royse
         7 Big                                                Ben
         8 Masala                                             Poori

8 rows selected.

SQL> !
Now at this point I have added one record to the table at source by using the below command.
use ggate
insert into gg_user.emp values (9, 'Mercedez', 'Benz')

Continuing with the replicat operation for real time sync…
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> 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.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (opensuse.selvapc.com) 1> dblogin userid ggs_dba, password ggs_dba
Successfully logged into database.

GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 2> ADD REPLICAT msd1r, EXTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, CHECKPOINTTABLE ggs_dba.checkpoint
REPLICAT added.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 3> alter replicat msd1r, begin 2016-05-08 02:23:23 -- This is the time we started our initial extract.

2016-05-08 19:43:04  INFO    OGG-06594  Replicat MSD1R has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start MSD1R with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 4> start rep msd1r

Sending START request to MANAGER ...
REPLICAT MSD1R starting


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     MSD1R       00:00:00      00:00:04


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 6> stats rep MSD1R

Sending STATS request to REPLICAT MSD1R ...

Start of Statistics at 2016-05-08 19:43:22.

Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:

*** Total statistics since 2016-05-08 19:43:11 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-05-08 19:43:11 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-05-08 19:43:11 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-05-08 19:43:11 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 7> exit
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> exit
exit

SQL> select * from employee;

        ID FIRST_NAME                                         LAST_NAME
---------- -------------------------------------------------- --------------------------------------------------
         1 Selva                                              Kumar
         2 Rajini                                             Kanth
         3 David                                              Billa
         4 Manic                                              Badsha
         5 Manne                                              Pacquo
         6 Rolls                                              Royse
         7 Big                                                Ben
         8 Masala                                             Poori
         9 Mercedez                                           Benz

9 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
 
If you take a look at the above command line 3, you might notice I have altered the replicat to start at the exact time the initial extract is started (can be obtained from initial extract report file). This is because, the initial extract will take all the records from the table directly and trying to applying the real time changes before this point is unnecessary and would be an overhead for GoldenGate process as it has to handle any collisions of data. That’s the reason, the real time replicat has inserted only one record that it captured after the mentioned time.

Now let’s see what would have happened if we didn’t alter the begin time of the replicat.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> 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.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (opensuse.selvapc.com) 1> dblogin userid ggs_dba, password ggs_dba
Successfully logged into database.

GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 2> ADD REPLICAT msd1r, EXTTRAIL /u01/app/oracle/product/12.1/oggcore_1/dirdat/za, CHECKPOINTTABLE ggs_dba.checkpoint
REPLICAT added.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 3> start replicat msdr1
ERROR: REPLICAT MSDR1 does not exist.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 4> start replicat msd1r

Sending START request to MANAGER ...
REPLICAT MSD1R starting


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     MSD1R       00:00:00      00:00:03


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 6> stats rep msd1r

Sending STATS request to REPLICAT MSD1R ...

Start of Statistics at 2016-05-08 13:31:26.

Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:

*** Total statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00
        Total insert collisions                            4.00

*** Daily statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00
        Total insert collisions                            4.00

*** Hourly statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00
        Total insert collisions                            4.00

*** Latest statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      4.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00
        Total insert collisions                            4.00

End of Statistics.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     MSD1R       00:00:00      00:00:08

-- The insert on the source database happened at this point in time.

GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 8> stats rep MSD1R

Sending STATS request to REPLICAT MSD1R ...

Start of Statistics at 2016-05-08 19:40:01.

Replicating from gg_user.emp to OPENSUSE.EMPLOYEE:

*** Total statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      5.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00
        Total insert collisions                            4.00

*** Daily statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      5.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00
        Total insert collisions                            4.00

*** Hourly statistics since 2016-05-08 19:00:00 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-05-08 13:31:17 ***
        Total inserts                                      5.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00
        Total insert collisions                            4.00

End of Statistics.


GGSCI (opensuse.selvapc.com as ggs_dba@susedb12) 9> exit
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1>
 
In the above scenario, you can see that we have 4 collisions that the GoldenGate process has handled and inserted only the record which is missing. Once after all the records has been applied, you can stop the replicat process, edit the parameter to remove the HANDLECOLLISIONS and then start the replicat again to reduce the work overhead.
The parameter file would look like below.

msd1r.prm
REPLICAT MSD1R
SOURCEDEFS /u01/app/oracle/product/12.1/oggcore_1/dirdef/emp.def
USERID ggs_dba, PASSWORD ggs_dba
MAP GG_USER.emp, TARGET opensuse.employee;


This completes the replication setup between the source MS SQL Server database and the target Oracle database. Hope you have enjoyed the article.

7. References







Happy Replicating!!!

MS SQL Server to Oracle data replication using Oracle GoldenGate - Part 1

This post provides a simple step by step procedure on how to configure and achieve data replication between MS SQL Server database and Oracle database using Oracle GoldenGate software

Note: I have provided the simple demo on how to perform a replication from an Oracle to Oracle database in my previous blog post Oracle GoldenGate simple hands-on demo. Most of the Oracle related steps will be redirected to refer the blog for details. This post is to provide information mostly on the prerequisites and how to configure GoldenGate software on the MS SQL Server database and perform replication (minimum mandatory setup to achieve replication where you have multiple options on the parameters available to be used). 

Since the post is long, I'll be breaking it to two parts and I have also provided a downloadable copy of the post in this link. Click on images to view full size if required.
Part 1 covers all the prerequisites required to be met before attempting Oracle GoldenGate replication and part 2 covers the param file set up along with the actual steps on achiveing replication.

1. Environment Details

Source:
MS SQL Server Enterprise 2012 running on Windows 10 enterprise

Source database properties

Target:
Oracle 12.1.0.2.0 database running on openSUSE 13.2 (Harlequin) (x86_64) under Oracle VM Virtual box with Windows 10 enterprise host.
Target Database properties

GoldenGate software used:
Source:
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Windows x64 (optimized)
Target:
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO Linux, x64, 64bit (optimized)
GoldenGate software can be downloaded from this link.

2.  GoldenGate Installation

Once the software has been downloaded from the link, let’s proceed with the installation of the software on both source and target and prepare the environments for the replication.

Source:
Unzip the file to a directory and that would be the installation of Oracle GoldenGate software for SQL Server on Windows.


Once the software is extracted, we can then login to the GoldenGate Software Command Interface (GGSCI) and create all the sub directories required.
Note: You should change your present working directory to the software location or the GoldenGate Home location and start ggsci.
C:\Windows\system32>cd E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit

C:\Windows\system32>e:

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as windows-1252.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (Prinky) 1> create subdirs

Creating subdirectories under current directory E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit

Parameter files                E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirprm: created
Report files                   E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirrpt: created
Checkpoint files               E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirchk: created
Process status files           E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirpcs: created
SQL script files               E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirsql: created
Database definitions files     E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef: created
Extract data files             E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdat: created
Temporary files                E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirtmp: created
Credential store files         E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dircrd: created
Masterkey wallet files         E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirwlt: created
Dump files                     E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdmp: created


GGSCI (Prinky) 2> exit

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 
Optional step: To create manager process as a Windows service to run in background even when user logs off the system
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit> INSTALL ADDSERVICE

Service 'GGSMGR' created.

Install program terminated normally.

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 

Target:
For installation on the Target Linux based system, refer my blog post and follow the steps intended to be performed on the Destination

Once installation is complete, login to the GoldenGate Software Command Interface (GGSCI) and create all the sub directories required.

Note: You should change your present working directory to the software location or the GoldenGate Home location and start ggsci.
oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> 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.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (opensuse.selvapc.com) 1> create subdirs
 
We are now done with the installation and configuration of Oracle GoldenGate software on both source and target systems.

3. Environment preparation

 Once the GoldenGate installation is successful, we should prepare both the environments to be replication ready. Now let’s prepare the environment.

3.1        Source Table and Data:

I will be using GGATE database in my MS SQL Server for the purpose of this demo. You can your own existing database.

Under GGATE database
create schema gg_user
go

create table [gg_user].[emp] (
     [id] [smallint] not null,
     [first_name] varchar(50) not null,
     [last_name] varchar(50) not null,
constraint [emp_pk] primary key clustered (
     [id] asc
) with (pad_index = off, statistics_norecompute=off, ignore_dup_key=off, allow_row_locks=on, allow_page_locks=on) on [primary]
) on [primary]

go
 
-- Insert a few data into the table so that we have our source table set up. 
-- Query the table to see the records.

use ggate
select * from gg_user.emp

-- Result:
id first_name last_name
1 Selva  Kumar
2 Rajini  Kanth
3 David  Billa
4 Manic  Badsha
  
Now our Test DB with its data is ready.

3.2        Data Server Name (DSN) creation

Create DSN for the MSSQL data to be accessed by Oracle GoldenGate software. This step is very important as Oracle GoldenGate software will communicate to the database using DSN

Steps:
Type “ODBC Data Source” in the “Search the web and Windows” tab in the task bar and click on “ODBC Data Sources (64 bit)

Click on System DSN and fill data as below. Use the database you are trying to connect.

Click on Next and enter details as below

Click on Next and provide the default database to connect

Click on Next and then Click Finish

Now Test the connectivity whether check if it works. Click on Test Data Source


Now we are done with the setup of DSN successfully.

3.3        Backup Type and performing Backup

It is a mandatory step to set up “Full recovery model” as backup strategy for the MS SQL database and a backup has to be taken and available at disk to start with the real time extraction.
Note: Initial replication doesn’t need the above to be done but is necessary for real time synchronization.

So now let’s turn on the Full recovery model and take a backup as below.
Check the current setting and change to Full recovery model if required.
In SQL Server Management Studio, right click on database name (GGATE in our case) à Properties à Options à Recovery Model

Once set, click on OK so the setting be saved.

Take a backup of database with transaction logs. Transaction logs backup is mandatory to be taken and it will be taken along with the database backup by default in the Full recovery mode. Oracle GoldenGate gets the changed data from transaction logs in most cases and if it not found in the logs then it would get it from the transaction log backup and for this reason the backup should be of native MS SQL Server backup and Oracle GoldenGate doesn’t support third party backup.

Use the graphical method to take backup by Right click on database (GGATE in our case) à Tasks à Backup as shown below.

Edit the fields as desired and click OK to perform the backup.

If you would like to use command line to take the backup, open New Query in the SQL Server Management Studio and execute the below commands.
USE [master]
GO
BACKUP DATABASE [Ggate] 
TO DISK = N'E:\oracle\Ggate_Full.bak' 
WITH NAME = N'Ggate-Full Database Backup', STATS = 10, INIT
GO
 

3.4        Target environment setup

Once software installation is completed on the target system, please refer to Oracle GoldenGate simple hands-on demo, section 3. Prerequisites and Oracle GoldenGate software set up and perform the On Target part of 3.2 OGG software setup

Now once this is done, we have set up our source and target environment for the GoldenGate replication and let’s continue with the rest of the operations.

4. Enable Trandata

Enabling trandata on the source database would require login to the database and hence use the created DSN name (Command line 1) to connect to the database using GGSCI. The defgen parameter file will be created in the command line 3.
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as windows-1252.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (Prinky) 1> dblogin sourcedb GG 

2016-05-07 16:27:30  WARNING OGG-05236  ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.

2016-05-07 16:27:30  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2016-05-07 16:27:30  INFO    OGG-03037  Session character set identified as windows-1252.
Successfully logged into database.

GGSCI (Prinky) 2> add trandata gg_user.emp

Logging of supplemental log data is enabled for table gg_user.emp

GGSCI (Prinky) 3> edit params defgen

GGSCI (Prinky) 4> exit

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 
The contents of the defgen.prm are as below
defsfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def
sourcedb GG
table gg_user.emp;

You can see all the tables for which supplement logging (or published for replication) from MS-SQL databases using the below command.
SELECT *
FROM sys.tables
WHERE is_replicated = 1

-- Result
name object_id is_replicated create_date modify_date
emp 245575913 1 2016-05-07 15:41:04.973 2016-05-07 16:28:03.593
 
Now we need to invoke defgen utility to create a definitions file for the table EMP under GG_USER schema. So let’s do it.
E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>defgen paramfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirprm\defgen.prm

***********************************************************************
      Oracle GoldenGate Table Definition Generator for SQL Server
      Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
 Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 16:16:15

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2016-05-07 16:42:09
***********************************************************************

Operating System Version:
Microsoft Windows 8 , on x64
Version 6.2 (Build 9200)

Process id: 3116

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def
sourcedb GG

2016-05-07 16:42:09  WARNING OGG-05236  ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.

2016-05-07 16:42:09  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2016-05-07 16:42:09  INFO    OGG-03037  Session character set identified as windows-1252.
table gg_user.emp;
Retrieving definition for gg_user.emp.


Definitions generated for 1 table in E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def.


E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 
We can see that the definition file has been created in the dirdef directory. You can check the contents of the definition file create if you would like to…

This definition file is needed for Oracle to understand and translate the data types from MS SQL to Oracle data types. Transfer the generated definition file E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def to the target server under dirdef directory of the GoldenGate home. I’m using Filezilla client to transfer the file and you are free to use any of your tools such as Filezilla, Winscp, etc.

Once the above said steps has been completed, then the rest of the steps are of same like the Oracle to Oracle replication as described in this post.


I’ll go through the steps quickly with explanations wherever required. Rest of the steps is available in the next part of the blog post and is available in this link

Happy Replicating!!