cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle to EDB migration steps:

Highlighted
Level 2 Adventurer

Oracle to EDB migration steps:

Hi,

 

I am doing a migration from Oracle to EDB, the database is large and it have complex structure.

I have few question for EDB experts:

 

1) In which order we have to do the migration, eg. tables, functions, procedure, packages, views, synonyms?

2) which type of migration is better: online or offline or db_link?

3) when we run the migration, the migration pops ups the error script. how do we fix the error and recompile the migration? is there are a way recompile the migration, instaed of re run the migration? which creates more error than previous run.

 

 

 

 

 

9 REPLIES 9
EDB Team Member

Re: Oracle to EDB migration steps:

Hi Sunthar,

 

Please refer the answers inline :

 

1) In which order we have to do the migration, eg. tables, functions, procedure, packages, views, synonyms?

>> The best way would be to migrate the base objects firsts like tables which may be referenced in functions, procedures, and packages. 

 

2) which type of migration is better: online or offline or db_link?

>> db_link migration will be much faster than the other two. If you have a huge schema to migrate then prefer db_link method for migration.

 

3) when we run the migration, the migration pops ups the error script. how do we fix the error and recompile the migration? is there are a way recompile the migration, instaed of re run the migration? which creates more error than previous run.

>> No. There is no way to recompile the error that popped up and then start the migration from the same point. Could you please share the kind of errors that you are getting while running the migration command? So that we can suggest.

 
Level 2 Adventurer

Re: Oracle to EDB migration steps:

Thank you for your help, I am receiving error like "relations x does not exist" or the functions are looking for other relations and all the dependabilities during the compilation, is there a way we can just import or insert all the migration script without compilation and after finishing; inserting all the dependecies then we can compile the whole EDB postgres? the insertion looking for dependencies.

 

thank you!

Moderator

Re: Oracle to EDB migration steps:

Hi,

Thank you for the update.

Below link may be useful to find out the meaning of the error's which you are getting.

https://www.enterprisedb.com/docs/en/51/migrate/EDB_Postgres_Migration_Guide.1.36.html#pID0E0VG0HA

As per the shared information, it seems that you are trying to migrate with "-offilinemigration" option ? if yes then could you please try migrating with "-schemaOnly" option and then try with "-dataOnly" and let us know if you still face the same issue.

Please check below link for commands,

https://www.enterprisedb.com/docs/en/51/migrate/EDB_Postgres_Migration_Guide.1.25.html#

Level 2 Adventurer

Re: Oracle to EDB migration steps:

Thank You Ranjeet!

I am still struggling to get the migration running without any error. still objects are missing, I have tried offline, online, and dblink types of migration. none of them are successful until now. Is there a way, we can compile migration step by step? because when I compile the whole migration thats brings error one object is missing and etc..etc.....please help me !!!!!

 

Level 2 Adventurer

Re: Oracle to EDB migration steps:

Sunthar :

 

how large is your DB ?  

In general this approach will work best.  This is a broad set of steps, just dishing it out of my head.  You will have to fine tune it a bit.

 

1.  Create Group role in EDB Postgres ( get scripts from Oracle or you can gen using MTK ).

2.  Create users in EDB Postgres ( get scripts from Oracle or you can gen using MTK ).

3. Assuming you are doing for one schema only

              run mtk for schema only

4.  At the target ( EDB POSTGRES ) - drop all indexes, disable all foregin key constraints, disable all triggers

5.  Split the entire set of tables into 3 groups - containing a mix of equal number of small/medium/large tables.  This way you can run multiple streams

      Run for mtk for each of these sets, with -dataonly - truncload -loadercount <n> -copyViaDBLinkOra  options

      loadercount is parallelism - depends on the cpu power you have.  You have to try and see how much you can ratchet up to.

6.   After successful migration of data, enable constraints, triggers and build indexes ( from scripts generated earlier ).

7.  Recompile/rebuild views that have gone bad.

 

 

 

   

 

Level 2 Adventurer

Re: Oracle to EDB migration steps:

Thank you vkalipi!                   

The database is very large and we are looking into moving to EDB from Oracle. we are just testing it how much effort and work requires to move from Orcale to EDB.

Where I can get the fine tuned steps? I know you mention most of it should work, I will try your steps and will let you know how did it go.

thanks a lot again!

Level 2 Adventurer

Re: Oracle to EDB migration steps:

Hi,

 

How can I disable all Triggers and foregin key constraints in the entire schema? 

how can I drop all indexes in the entire schema?

 

 

Moderator

Re: Oracle to EDB migration steps:

Hi,

Please check our comments inline to your questions,

>>> How can I disable all Triggers and foregin key constraints in the entire schema?

The table you are migrating includes a foreign key constraint on a table that does not exist in the target database. Migration Toolkit creates the table, omitting the foreign key constraint.
You can avoid generating the error message by including the -skipFKConst option in the Migration Toolkit command.

By default triggers are disabled at the time migration. However "-triggers" used to import the table triggers. This option is valid when importing an entire schema or when the -allTables or -tables table_list option is specified.

>>> how can I drop all indexes in the entire schema?

It seems that this question is not related to MTK. If you are looking for dropping the indexes from postgres database then in that case you need to query pg_index table as we dont have a single command to drop all the indexes.

For Example:

SELECT 'DROP INDEX ' || string_agg(indexrelid::regclass::text, ', ')
FROM   pg_index  i

LEFT   JOIN pg_depend d ON d.objid = i.indexrelid  AND d.deptype = 'i'
WHERE  d.objid IS NULL and not indrelid::regclass::varchar like 'pg_%';

This will create the DROP INDEX command which you can execute to drop all the indexes from entire database.

Note: Please modify above command as per your requirement and make sure to verify that command is dropping only the indexes which you want to drop.

Level 2 Adventurer

Re: Oracle to EDB migration steps:

Sunthar :

 

These are mentioned in the mtk documentation, but not in the exact way as I had mentioned.  I have done many, so I know the general methodology that works. 2T in 4-5 hours - if you can achieve this, I think that is optimal.  ( This is what I was able to achieve ).

 

Multiple streams , optimal loadercount value ( that has to be arrived by an iterative apporach, based on the cpu resources on your box ).