Re: GoldenGate Oracle to MSSQL Server - Initial Table Create

Hi community friends,

thank you all for the replies and ideas, I really appreciate it!

How I solved the issue:

- Import the schema into Oracle SQL Developer Data Modeler 
- Pick exactly what I need and what I do not need for export, up to even making adaptions to the schema structure 
- Export from Data Modeler to MS SQL DDL as a file (it's very capable, you can pick the structure how the scripts should be created and how they call each other in detail, like one script per table) 
- Run the DDL script(s) in SSMS (or any other session to the SQL Server, even from Oracle SQL Dev) 

Advantages:

- No need to set up anything on the SQL Server (which will be out of our admin domain in production) 
- Convenient way to modify things on the schema structure if you need to 
- I work a lot with SQL Dev anyway, so the tool is familiar 

Disadvantages:

- I can't automate it this way. Guess I will have to write some smart PL/SQL magic for it, one rainy day 
- makes trouble if you have function based indexes with special sorting requirements like NLS_SORT(), the DDL files will need a sweep because these functions break out of the comments created by Data Modeler. Used tricks like ## sed -n '/^CREATE TABLE /,/^GO/P' ## to get rid of the rubbish. 
- not sure if the feature will be maintained in Data Modeler on the long term, it's nearly a bit too good to be true 

Thank you everybody who contributed ideas and suggested solutions!

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

martin.klier_at_performing-db.com // https://www.performing-databases.com 



> Von: "Martin Klier" <martin.klier_at_performing-db.com>

> An: "Oracle-L Freelists" <oracle-l_at_freelists.org>

> Gesendet: Dienstag, 29. März 2022 16:26:37


> Betreff: 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://www.performing-databases.com



--
http://www.freelists.org/webpage/oracle-l