data migration

[Indira Aramandla]

Hi Edgar,

Import with indexes = N and you can rebuild the indexes later on. Like this import will be faster.

If you had exported schema or tables in groups then you could had scheduled more than one imp jobs simultanously.


IA

[Indira Aramandla]

Hi Edgar,

Submitted tooo quickly, there's more.

To speed up Import you can use the BUFFER parameter and COMMIT. Although exercise caution if rerunning an Import if you have used the COMMIT parameter previously and do not have a uniqueness constraint on the table (ie beware of duplicate rows).

Before running your Import of data rows you may consider running an Import using INDEXFILE to create a file of index creation statements. This will not include table constraints, so you should also specify CONSTRAINTS=Y to get the table constraints written to this file.

Then run your main data Import with INDEXES=N to Import the data. Once this is done you can run the index creation statements within your file (edited as necessary). Separating the two operations may well speed up the overall process as the indexes do not have to be maintained as data is inserted.

REMEMBER THE RULE OF THUMB: Import should be minimum 2 to 2.5 times the export time.

Attached is a document form metalink which describes seom basic changes to speed up import process.

IA

[Indira Aramandla]

Forgot attachment

[Edgar_8]

Hi Indira,

Thanks for the advice! FYI we exported the data as individual schema dumps so as to run multiple imports in parallel.And yeah, we also aware of the fact that the import should take approx.2-3 times longer.

Thanks in advance!

[Gerald Thai]

Considering the size (1.2TB), your best, quick and cheapest option is to rent a portable diskfarm. They can customize the OS to match your production machine. Attach this big boy to your backend and take a netbackup.
Move this big boy to the new location and restore.

Wish we had one. We can use one right now.

no points please

[Eric Antunes]

Hi Edgar,

Did you did it?

The next time you may consider renting a VPN for a week between the 2 locations: I think this is the best way to transfer huge amounts of data between to remote sites...

Best Regards,

Eric Antunes

[Edgar_8]

Hi Eric/All,

Yeah we managed to do the full DB import in approx.36hrs and all is well with the DB. All things considered I know that we will follow the same steps should we need to in the future with such a large DB migration, unless we have otther options at our disposal.
Aagain thanks for all the advice & suggestions it was most appreciated. Below are the steps we took:
1. made a full DB export,therefore 1 43GB compressed dump file.
2. ftp'd data to new location
3. imported compressed dump file using a unix pipe
4. recompiled all invalid objects.