Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 19 September 2012

Migrating or Transferring data from Oracle 9i to 10g using import/export

IMPORT/EXPORT is the best way to transfer data between same or cross platform especially when the database is relatively small; if it is large it is better to use ‘Startup Upgrade’ steps as export is taking much time. Consider it is small database. Here you can proceed with two different Method. Choice is depending upon your requirement.
Method1:
        Execute Oracle 9i to 10g upgrade steps. The data which was in Oracle 9i is now available in Oracle 10g database automatically.
Method2:
        Export the data contained in Oracle 9i, using Oracle 9i version of 'exp' tool
        Clean-up the Oracle 9i database and create a new Oracle 10g database.
        Do an import of the previously exported data into new Oracle 10g database, using Oracle 10g version of 'imp' tool.
Here in the below example we will discuss about the first method only before that we will discuss the situation when we need to migrate or transfer the higher version data to lower version.
How can one export 10g data to 9i
To move data DOWN the present database version (from oracle 10g to oracle 9i), you need to export using that lower versions EXP (oracle 9i) tool and IMP using that lower versions tool. 
To move data UP the present database version (oracle 9i to oracle 10g), you export using the EXP of the database that contains the data (oracle 9i) and you IMP using the upper version tool.
In fact Export/Import is the best way to move data. The problem with this, the dump file produced by export is not the backward compatible. So if you use oracle 10g export to create dump then you cannot use oracle 9i import to import that data. In that case you need 9i export connect to 10g database then use the oracle 9i import to import that dump file.
If you are planning to Migrate/Upgrade your Oracle9i Release 2 data to Oracle 10g Release 2, you must be at Oracle 9.2.0.4 or higher to go directly to 10gR2. If you do not want to first patch your 9i binaries to 9.2.0.4, you must use export and import to upgrade your database to 10g (especially on different environment when the database is not very large). For more detail information on using export and import check my other post:
Steps:
1.      Export the data from the Oracle 9i system.
2.      Transfer the files across to the 10g system.
3.      On the 10g system you must create a 10g database (using “software only”)
4.      Once the 10g database has been created you must then import the data from the 9i system.
5.      Gather the statistics for verifying and comparing the database.

If you are planning to migrate the existing 9i database to 10g then it is better to refer the Oracle Database 10g Release 2 (10.2) Upgrade Guide providing several options for accomplishing task.
On 9i Server:
1.      Ensure the sufficient space available on the server for dump or other operation.
2.      Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to re-create them for use on the 10g system.
3.      Ensure the large UNDOTBS. Do not close the session or commit the transaction while the export is completing. If the export is large there is possibility you will run out of undo tablespace during export.
4.      Create a script to take the export dump of 9i database.
userid=sys/oracle@orcl3.world
log=H:\dump\oracle9i.dmp
full=y
consistent=y
filesize=4G
file=H:\dump\oracle9i.dmp
On 10g Server:
1.      Create a 10g database either using DBCA or Manually.
After Creating database do not forget to configure listener setting in case of manual.
2.      Create an import script to import the data on 10g system.
userid= sys/oracle@orcl3.world
log=H:\dump\oracle9i.log
full=y
commit=y
feedback=50000
buffer=10000000
file= H:\dump\oracle9i.dmp
  1. Create a SQL script for the creation of tablespaces and datafiles (Use Toad)
  2. Export Data from 9i database using previous created script.
  3. Run the SQL script for undo tablespace retention.
  4. Transfer the file across to the 10g system.
In oracle 9i you can either use FTP or copy the dump file to some external media (CD, DVD, external hard drive, etc.). FTP is nice because all of the transfer is done through the network. The external media requires you to physically transport that media to your final destination.
In Oracle 10g, new feature Data Pump utilities, can export from one database and import directly to another through your network. This saves you the step of actually creating a dump file.
  1. Import the data from 9i system using the import script previously created.

3 comments:

  1. Hi Shahid ,

    i need suggestion for migrating from 10.1.0.2 version to 12c database using Expdp/Impdp

    I have databases on windows server 2003 on 10.1.0.2 , so i want to upgrade that database to 12c with migration to Windows 2012 server

    ReplyDelete
  2. BlueHost is ultimately the best website hosting company with plans for all of your hosting requirements.

    ReplyDelete
  3. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your cell phone every day.

    Follow our signals NOW and make up to 270% a day.

    ReplyDelete