Oracle database Migration and upgrade-25



Migration is a major release change.

Ex: 9i to 10g or 11g (OR) 10g to 11g.

Migration will be going from lower to higher version. The windows to Linux will be cross platform migration.


Migrate from 9i Windows to Linux


Note down the following.

Sql> select username,default_tablespace from dba_users;

Sql> select * from dba_ts_quotas;

Sql> select tablespace_name sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;


Step 1: Export Schema

D:\> exp system/manager file D:\muthu.dump owner =Muthu log=D:\muthu.log

Step 2: Move the dump file to Linux

D:\> ftp


ftp> cd /opt

ftp> lcd D:\

ftp> bin

ftp> put Muthu.dump

ftp> bye


Step 3: Create a same size of the tablespace & users taken from windows

Sql> create tablespace tools  datafile ‘/opt/oracle/tools_01.dbf’ size 50m;

Sql> create tablespace data  datafile ‘/opt/oracle/data_01.dbf’ size 50m;

Sql> create user Muthu identified by Muthu default tablespace tools quota unlimited on tools

Quota 50m on data;

Sql> grant create session, create table to  Muthu;

Step 4: Import data

$ imp system/manager file=/opt/Muthu.dump fromuser=Muthu touser=Muthu ignore =y log=/opt/Muthu.log

Step 5: Shutdown the windows box and change the  IP address of the  Linux

Step 6: Stop & start the listener


Sql> select count (*) from dba_objects where owner=’MUTHU’;


Migrate from 10g Linux to 11g Linux


  1. Note down the pre requesting
  2. Export schema using datapump
  3. Move the dump file
  4. Create a tablespace in 11g
  5. Import the dump by datapump
  6. Change the IP and restart the listener

User/Schema will be created by data pump.


How to downgrade the Oracle Database

Ex: 10g to 9i

Login to 9i

$ tnsping test (Test is a net service name of 11g)

$ exp system/manager @test file /opt/muthu.dump owner =Muthu log=/opt/muthu.log


$ imp system/manager file=/opt/Muthu.dump fromuser=Muthu touser=Muthu ignore =y log=/opt/Muthu.log


Patching Upgradation
Download Patches Download Patchsets
Apply Opatch for binaries & for DBs run scripts 1. 10g, upgrade binaries by runinstaller and upgrade DB manual or DBUA
2. For 11g no binary upgrade, instead create a new OH and startup upgrade and run scripts.
In place only In place & side by side upgrade



Migration Cloning
Moving  DB to  another server can be same version & platform or different Moving  DB to  another server should be same version & platform
1. RMAN in transporting tablespaces cross platform & different version
2. Data pump/export/Import – Cross or same platform & different version
3. By using Database link CTAS – create table as select -Cross or same platform & different version
4. By using SQL developer tool Cross or same platform & different version
5. Same platform use clone + upgrade Same platform & version
We can use cold, hot & RMAN


I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 89 = 90