Re: GoldenGate Oracle to MSSQL Server - Initial Table Create

From: Powell, Mark <mark.powell2_at_dxc.com>
Date: Tue, 29 Mar 2022 14:48:04 +0000
Message-ID: <DM5PR01MB22515E8E4DDBA17EC40F3DAACE1E9_at_DM5PR01MB2251.prod.exchangelabs.com>

Martin, I have used 3 methods to create tables in SQL Server based on existing Oracle tables for use with Goldengate.

1. Create them manually
2. Let the database import data task (SSIS) in SSMS create the tables. You will likely need to add the indexes you want manually afterward but you can generate index commands in Oracle. You will also need to verify that DATE columns got created correctly.
3. Use the MS Data Migration Assistant which can create tables and copy the data

Option one may not be as much work as you think as you can generate DBMS_METADATA calls to generate the tables placing all the code into one script then perform global edits to change DATE data types to DATETIME, CLOB to varchar(max), etc....

Mark Powell
Database Administration
(313) 592-5148

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com> Sent: Tuesday, March 29, 2022 10:26 AM
To: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: GoldenGate Oracle to MSSQL Server - Initial Table Create

Hi listers,

currently I try to build a "simple" one-way GoldenGate 21c replication of a whole, self-contained Oracle-schema to a database on MS SQL Server. So far, I think I got the idea how to build the instantiation- and replication EXTRACTs and REPLICATs, and I can start them w/o errors and all.

BUT: How do I get GG to CREATE the actual tables in the target database? All I get during instantiation is

EXTRACT:

INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, initex.prm:  Extract group EXTRACT INITEX started.
INFO    OGG-02911  Oracle GoldenGate Capture for Oracle, initex.prm:  Processing table MYSCHEMA.MYTABLE.
WARNING OGG-01194  Oracle GoldenGate Capture for Oracle, initex.prm:  Extract task INITRP abended : Initial data load error reported by REPLICAT.
ERROR   OGG-01203  Oracle GoldenGate Capture for Oracle, initex.prm:  Extract abending.

REPLICAT:

WARNING OGG-00869  Oracle GoldenGate Delivery for SQL Server:  Could not retrieve definition for table dbo.MYTABLE.
ERROR   OGG-00199  Oracle GoldenGate Delivery for SQL Server:  Table dbo.MYTABLE does not exist in target database.
ERROR   OGG-01668  Oracle GoldenGate Delivery for SQL Server:  PROCESS ABENDING.

That, in fact, is true, but how do I get them there? I can't honestly extract, translate and create thousands of tables manually to SQL Server...

Thank you for your ideas!

--

Martin Klier // Performing Databases GmbH Managing Partner // Senior DB Consultant Oracle ACE Director

martin.klier_at_performing-db.com // https://clicktime.symantec.com/3SasWto9RkY9HZ3Fia9LUr16xU?u=https%3A%2F%2Fwww.performing-databases.com

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 29 2022 - 16:48:04 CEST