0

I currently support replication from Oracle 11 to SQL Server 2014 via GoldenGate 12.1.

I am now trying to setup replication in my home lab from Oracle 12.2 Multitenant to SQL Server 2016. I am using GoldenGate 12.3.0.1 on the source (Linux) and target (Windows) systems. I am trying to replicate the HR tables in the Pluggable database ORCL to a SQL Server database.

Steps taken

  1. Downloaded Oracle Developer template and imported into virtual box

  2. Downloaded and installed GoldenGate 12.3.0.1 on Linux and Windows

  3. Setup GG Manager process and extract on Linux (ORCL PDB) -- running

  4. Installed SQL Server 2016 on Windows 2016 Virtual Box

  5. Create HR schema and tables in SQL Server

  6. use defgen to create definitions files for heterogeneous replication copied to windows dirdef folder

  7. Setup GG Manager process and replicat on Windows --- running

  8. Made data changes to HR.Department table, but changes don't replicate to SQL Server

Extract file

extract extora

useridalias ggadm

RMTHOST goldengatewin, MGRPORT 7809

RMTTRAIL C:\GoldenGate\dirdat\aa

LOGALLSUPCOLS;

table ORCL.HR.*;

Output of stats command

GGSCI (GoldenGateLinux) 1> stats extora





Sending STATS request to EXTRACT EXTORA ...





Start of Statistics at 2018-08-13 13:05:42.





Output to C:\GoldenGate\dirdat\aa:





Extracting from ORCL.HR.DEPARTMENTS to ORCL.HR.DEPARTMENTS:





*** Total statistics since 2018-08-13 12:46:39 ***

Total inserts                               2.00

Total updates                               0.00

Total deletes                               2.00

Total discards                             0.00

Total operations                           4.00





*** Daily statistics since 2018-08-13 12:46:39 ***

Total inserts                               2.00

Total updates                               0.00

Total deletes                               2.00

Total discards                             0.00

Total operations                           4.00





*** Hourly statistics since 2018-08-13 13: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 2018-08-13 12:46:39 ***

Total inserts                               2.00

Total updates                               0.00

Total deletes                               2.00

Total discards                             0.00

Total operations                           4.00





End of Statistics.

Replicat file

REPLICAT repsql

TARGETDB OracleToSQL, useridalias ggadm

SOURCEDEFS .\dirdef\hr_objects.sql

DISCARDFILE .\dirrpt\hr_discard.txt, APPEND, MEGABYTES 50

HANDLECOLLISIONS

MAP ORCL.HR.COUNTRIES, TARGET HR.COUNTRIES, KEYCOLS(COUNTRY_ID);

MAP ORCL.HR.DEPARTMENT, TARGET HR.DEPARTMENT, KEYCOLS(DEPARTMENT_ID);

MAP ORCL.HR.EMPLOYEES, TARGET HR.EMPLOYEES, KEYCOLS(EMPLOYEE_ID);

MAP ORCL.HR.JOB_HISTORY, TARGET HR.JOB_HISTORY, KEYCOLS(EMPLOYEE_ID);

MAP ORCL.HR.JOBS, TARGET HR.JOBS, KEYCOLS(JOB_ID);

MAP ORCL.HR.LOCATIONS, TARGET HR.LOCATIONS, KEYCOLS(LOCATION_ID);

MAP ORCL.HR.REGIONS, TARGET HR.REGIONS, KEYCOLS(REGION_ID);

Output of info command

GGSCI (GoldenGateWin) 10> info repsql





REPLICAT   REPSQL    Last Started 2018-08-13 08:22   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:09 ago)

Process ID           5044

Log Read Checkpoint  File C:\GoldenGate\dirdat/aa000000003

                     2018-08-13 10:03:07.893810  RBA 2836

Output of stats command

GGSCI (GoldenGateWin) 11> stats repsql





Sending STATS request to REPLICAT REPSQL ...





No active replication maps.

Logdump remote trail file output

Logdump 27 >next



2018/08/13 09:34:43.808.705 Delete               Len    61 RBA 2101

Name: ORCL.HR.DEPARTMENTS  (TDR Index: 1)

Before Image:                                             Partition 12   G  s

0000 0a00 0000 0000 0000 0000 0118 0100 0f00 0000 | ....................

0b00 456e 6769 6e65 6572 696e 6702 000a 00ff ff00 | ..Engineering.......

0000 0000 0000 0003 000a 0000 0000 0000 0000 0006 | ....................

a4                                                | .



Logdump 27 >next





2018/08/13 09:48:55.103.386 Insert               Len    61 RBA 2288

Name: ORCL.HR.DEPARTMENTS  (TDR Index: 1)

After  Image:                                             Partition 12   G  s

0000 0a00 0000 0000 0000 0000 0118 0100 0f00 0000 | ....................

0b00 456e 6769 6e65 6572 696e 6702 000a 00ff ff00 | ..Engineering.......

0000 0000 0000 0003 000a 0000 0000 0000 0000 0006 | ....................

a4

Is it possible to replicate from a PDB to SQL Server? Is there something wrong with the mapping in my replicat parameter file?

Any assistance is appreciated...Thx

  • I did all that plus created the common user tbl space and supplemental logging for all the objects in the HR schema.... I managed a traditional GG environment just want setup from a PDB to SQL Server. I will retrace my steps with the document. Thank you for all your help – Goforebroke Aug 15 '18 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.