cancel
Showing results for 
Search instead for 
Did you mean: 

Recommendation for speeding up migration of Large tables from Oracle to PPAS via Migration Toolkit

 

In certain cases when a user wants to migrate large tables from his Oracle database to Advanced Server he might find the JDBC Copy method used by Migration Toolkit to be too slow.

Here user can opt to perform his migration over dblink_ora.

The dblink_ora module provides Advanced Server-to-Oracle connectivity at the SQL level.

dblink_ora is bundled and installed as part of the Advanced Server database installation. It utilizes the COPY API method to transfer data between databases. This method is considerably faster than the JDBC COPY method.

As a prerequisite for using this option the target Advanced Server database must have dblink_ora installed and configured.


https://www.enterprisedb.com/docs/en/9.5/migrate/Postgres_Plus_Migration_Guide.1.31.html#
https://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guid...

Once successfully configured, you can run the Migration Toolkit with 'copyViaDBLinkOra' parameter as shown below:

./runMTK.sh -copyViaDBLinkOra -allTables HR

 

Version history
Revision #:
1 of 1
Last update:
‎06-05-2017 11:39 PM
Updated by:
 
Labels (2)
Contributors
Comments
Roy

Hello Amit,

 

Recently, I dealt with two tables in Oracle that cannot be migrated using the “copyViaDBLinkOra” option. These are the only ones among 100+ tables that contains CLOB columns and it seems to be the reason why the migration programs fails when trying to migrating their data.I then tried to migrate these 2 tables without the db link option and it worked without failing. So is that a limitation of copyViaDBLinkOra of MTK50 PPAS 9.5 Or do you suggest using EDB*Loader to load data from flat files in such cases?

Also, Within the migration toolkit,  I had to make the connection to the Postgres database using the Postgres cluster admin account otherwise EDB Migration Kit cannot deal with CLOB tables (even a super user account won’t do…). Is this a limitation of Postgres.
It is really disturbing because every single object that is created is owned by cluster admin account,  but their ownership has to be respective schema which was in Oracle
So I had to create some functions to change the ownership of functions, procedures, views, sequences and tables and another one to change the data tablespace of all tables since it shoudn’t be “pg_default” but the one that were specifically created after the cluster was initialized.

Also, how to retain the privileges/grants on the users/schema as it was in Oracle

 

Thanks,

Roy

Hi Roy, MTK is able to migrate the CLOB coloumns and we have seen similar issues with one of our other customers. We are working on to fix the same in upcoming version of the MTK. If you would like to pursue the fix, let us know and we will have someone get in touch with you.