import data from database 7.3.4 to 8.1.7

[dngaya]

hello,
I have a database oracle 7.3.4 and another database oracle 8.1.7. I wish to import the data of database 7.3.4 towards the version oracle 8.1.7.
which are the various precautions to be taken?.
is what that can be done without problem?. thank you for your assistance.

[Jean-Luc Oudart]

HI,

should I mention the Character_set in source and target database ?
You have to set your NLS_LANG variable properly before the export and import.

Regards,
Jean-Luc

[Brian Crabtree]

You should also check your procedures and packages on the database as well, and verify that they loaded successfully. PL/SQL is normally backwards compatible, however there could be changes made as well. You should do this once or twice as a test before doing it for real.

Thanks,

Brian

[Indira Aramandla · Accepted solution]

Hi,

I had done this export from 7.3.4 and inport into 8.1.7 last week. It will be a simple and straight farward procedure.

Verify that your users and roles are pre-created so that you do not get warning / errors role or user does not exists.

To save time while export and import you can do simultanious exports and imports by the parameter tables=.

And before you start make a note of the objects like triggers, procedures, functions, packages.

Make sure they come across fine, and then ckeck for their status. If you find them invalid, all you need to do is re-compile them.

The other thing to watch for are sequences, if you have any. Make sure the sequences next_val ties up with the MAX(value + 1) of the filed value.

IA.

[Yogeeraj_1]

hi,

to add to the above replies...

To move data DOWN a version(s), you need to export using that lower versions EXP tool and IMP using that lower versions tool.
To move data UP a version, you export using the EXP of the database that contains the data and you IMP using the imp that ships with the TARGET database.

Eg:

to move data from 8.0 to 7.3:

o run catexp7 in the 8.0 database.
o exp73 over sqlnet to the 8.0 database.
o imp73 natively into the 7.3 database.


to move data from 7.3 to 8.1

o exp73 natively on the 7.3 instance
o imp natively using the 8.1 imp.exe

You also have the sqlplus COPY command - a nice tool as well as it avoids the intermediate files and just copies
data about. Its pretty fast too.

Also note that:
during import/export the data can and often does go through character set translation.

Unless
a) source database has CHARACTER SET X
b) client running export has their NLS_LANG set to X
c) client running import has their NLS_LANG set to X
d) target database has CHARACTER SET X

and finally:
Import is a big sequential process.

So, to "speed that up", you have to do things to make those processes faster

o make sure your alert log doesn't have any "checkpoint not complete" or "archival required" messages. If it does, you have insufficient log configured, add more.

o check your sort_area_size for your index builds, consider increasing it during the import.

o don't use a simple exp owner=, imp owner=. Export table by table (in parallel, just fire off export more then once). Import table by table (in parallel, just fire off import more then once).

o don't use import to create the indexes -- use imp ... indexfile=temp.sql to generate a script with the create indexes. then modify them to have nologging and parallel. Run them in sqlplus faster.

hope this helps too!

regards
Yogeeraj