Migrating data from Db to Db

[Former Member]

Hi All

In our Hybris project we are trying to migrate data from one Oracle database to another Oracle database. The schemas are the same.

On one of our runs we got a dump of all the data from our original database and tried to load it. But that caused an exception that says invalid column name.

On the second attempt we just took one table from the original database. We truncated the data we had in the second database, and then loaded the table data from the original database. But Hybris was not able to read the table in the second database.

When querying the database from the Oracle SQL Developer, we could the data on both attempts. But querying from HAC, we got the Invalid Column Name exception for the first attempt, and no data at all for the second attempt.

Can anyone please guide us as to what the best practice is to migrate data over from database to database?

Thanks in advance

[former_member624549]

if you want the exact same thing the quickest is to oracle export/ import (oracle: exp,imp or expdp, impdp utilities)

[arvind-kumar_avinash]

From the question, I assume that he has already performed Oracle export and import. , isn't it the case?

[Former Member]

that is correct

[Former Member]

Hi,

Based on the previous conversations, it looks like the Oracle import is failing because columns in the tables in the target database have a different order than columns in the tables in the source database.

If you just want to move your hybris commerce from one Oracle database to another one, a good approach is to copy the schema as well as data. 1) Remove the schema or drop all the tables in the target database. 2) Copy the schema from the source to the target database. 3) Copy the data from the source to the target database. This should resolve your current issue.

Regards, Viktor

[geffchang]

You say that the schema is the same, but you didn't really say anything about the Hybris set-up. Is the source Hybris system existing from before? Is the target Hybris system newly-initialized?

I am guessing the source system has been around for a long time, and has undergone some schema changes. It's possible that thes source DB contains tables which doesn't exist in the target DB. I suggest to check first if the table names, column names, and column types of both DBs are the same or not.

If it is doable, another way to migrate data is to use impex. Export from source DB and import it to target DB, though this could take a while depending on the volume.

[Former Member]

The source system has been around for a while yes. The target system is a new initialized system.

We compared the database schemas between the old and the new, the column names were the same. They were just located on different lines in the SQL file.

Impex is an option we tried but the export didn't finish. Is better to run an asynchronous export or synchronous? Which one would be faster?