Forum Stats

  • 3,854,665 Users
  • 2,264,397 Discussions
  • 7,905,749 Comments

Discussions

Issue in migrating thrid party database - postgres

anand_gp
anand_gp Member Posts: 751 Bronze Badge
edited Jan 19, 2020 4:57AM in SQL Developer

Hi,

I am using SQL Developer Version 18.4.0.376.  I am trying to migrate postgresql DB to oracle using Migration Wizard.  Under the "Source Database" option, I need to choose Third Party Database from Connection drop down option.  I am clicking on Add button to add a connection.  I get a list of existing connections.  ->

pastedImage_0.png

  I clicked on "postgresTESTdb" which is related to postgresql DB.  If I click on Test, it is successful.  However, if I choose that to add into connections, it doesnt appear in the connections list ->

pastedImage_1.png

I also checked "Add Platform", which shows, relevant jar file being used for the connection. 

pastedImage_2.png

However, "Available source platform" shows nothing ->

pastedImage_4.png

But, I have used that same jar file to create a connection to connect to a postgres database without any issues ->

pastedImage_5.png

Can someone help me to know what's going wrong here?  Many Thanks.

Regards,

-Anand

Joerg.Sobottkajoaogloria

Best Answer

«1

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee
    edited Jan 13, 2020 5:59AM

    That wizard doesn't support Postgres.

    Right now you can drag and drop tables to Oracle or right click on one and say Copy to Oracle.

    Formal Postgres migration support is coming later this year.

    Joerg.Sobottka
  • anand_gp
    anand_gp Member Posts: 751 Bronze Badge
    edited Jan 13, 2020 8:59AM

    Hi,

    Thanks for your pointers. 

    Right click "Copy to Oracle"   ->   This does not work.  Doesn't show any dailog after clicking on Copy to Oracle.

    Tried with drag and drop of table and got below error ->

    Copied Objects:
    pgARUNtest.SuVikas_Core.aadhaar.TABLE.aadhar_subsidy_type
    Drop Target: TEST2
    Copy DDL: Yes
    Replace
    Copy Data: Yes
    Task failed.

    1 tables failed.
        Failed to create Table SuVikas_Core.aadhaar.aadhar_subsidy_type. Rollback sucessful
            ERROR: syntax error at or near "`"
              Position: 15

    0 table(s) copied.

    And my Postgres ver 12 and PGAdmin version -> 4.16

    Thanks.

    -Anand

  • anand_gp
    anand_gp Member Posts: 751 Bronze Badge
    edited Jan 13, 2020 11:57AM

    When I try to drag and drop I get below options.  I tried all possible options but of no use.

    pastedImage_2.png

    pastedImage_0.png

    ERROR Detail -

    Copied Objects:
    pgARUNtest.SuVikas_Core.aadhaar.TABLE.aadhar_subsidy_type
    Drop Target: TEST2
    Copy DDL: Yes
    Do Not Replace Existing Objects
    Copy Data: Yes
    Append Existing Objects
    Task failed.1 tables failed.
        Failed to create Table SuVikas_Core.aadhaar.aadhar_subsidy_type. Rollback sucessful
            ERROR: syntax error at or near "`"
              Position: 150 table(s) copied.

    Regards,

    -Anand

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Jan 13, 2020 1:52PM

    Not sure how the migration rules might try to munge that table name, but "SuVikas_Core.aadhaar.aadhar_subsidy_type" is 40 characters and contains ".", so one or both of those factors may be the cause of the failure.  Longer than 30 characters is only supported in Oracle 12.2 & up with compatible parameter not set to a lower version, and a valid table name must include only alphanumerics and "$", "_", and "#".

  • anand_gp
    anand_gp Member Posts: 751 Bronze Badge
    edited Jan 14, 2020 2:35AM

    Hi,

    This time, I tried with a smaller database name TEST.  schema name is "sample".  Table name "emp".  But still it reports same error.

    When I drag and drop, it takes all these details into consideration as shown in below error message   TEST.sample.emp

    Copied Objects:
    pgTEST.TEST.sample.TABLE.emp
    Drop Target: TEST2
    Copy DDL: Yes
    Replace
    Copy Data: Yes
    Task failed.1 tables failed.
        Failed to create Table TEST.sample.emp. Rollback sucessful
            ERROR: syntax error at or near "`"
              Position: 150 table(s) copied.

    It could be some other issue I believe.  I downloaded latest postgres JDBC jar file from postgres website.  It is of no use.  I don't know, why I am able to connect to PG database, but not able to copy table.  Thanks.

    Regards,

    -Anand

  • anand_gp
    anand_gp Member Posts: 751 Bronze Badge
    edited Jan 14, 2020 7:09AM

    Hi,

    Anybody knows how to fill this "Available source platform".  I tried with "Add Platform", but it is not working either.  In some other setup, I have seen it showing a list of connections, where the whole migration works in their platform.

    pastedImage_1.png

    Regards,

    -Anand

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee
    edited Jan 14, 2020 8:11AM

    it's for sql server, sybase ase, db2, teradata, mysql

    there's no way to add postgresql

  • anand_gp
    anand_gp Member Posts: 751 Bronze Badge
    edited Jan 14, 2020 8:41AM

    That means, even Drag & Drop doesn't work?   Is there any other way to move all tables (primarily tables at the moment.  having 100s of tables in a DB) from Postgres to Oracle?  Thanks for your clarification.

    Best Regards,

    -Anand

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 14, 2020 9:55AM
    anand_gp wrote:That means, even Drag & Drop doesn't work? Is there any other way to move all tables (primarily tables at the moment. having 100s of tables in a DB) from Postgres to Oracle? Thanks for your clarification.Best Regards,-Anand

    I'm not familiar with postgres, but I've got to believe it has some utility for unloading tables to delimited text files.  Those files could then be used by oracle sqlldr to load into oracle.  Since you have a lot of tables, you'd want to figure out some way to script the operation. 

    anand_gp
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee
    edited Jan 14, 2020 10:37AM Answer ✓

    No, it means the generic code we have for Drag and Drop and Copy to Oracle is busted for postgresql. We see the issue and will have a fix for 20.1