Oracle exp and imp: oracle9i to 11g

Setp — 1
==========
Set the same character on source server:
========================================

In source database
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
Do actual export(exp parfile=exp.par)

Setp — 2
==========
Exporting the data from source database:
========================================

exp system/system123 file=NAS30.dmp log=NAS30.log owner=NAS30 buffer=100000000 grants=y rows=y statistics=none direct=y;

Setp — 3
==========
Extract the table sript from soured databae with the help of below script:
===========================================================================

For tablespace:
===============

select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ ||  decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

For the Users:
==============

set pagesize 0
set escape on
select ‘create user ‘ || U.username || ‘ identified ‘ ||
DECODE(password,
NULL, ‘EXTERNALLY’,
‘ by values ‘ || ”” || password || ””
)
|| chr(10) ||
‘default tablespace ‘ || default_tablespace || chr(10) ||
‘temporary tablespace ‘ || temporary_Tablespace || chr(10) ||
‘ profile ‘ || profile || chr(10) ||
‘quota ‘ ||
decode ( Q.max_bytes, -1, ‘UNLIMITED’, NULL, ‘UNLIMITED’, Q.max_bytes) ||
‘ on ‘ || default_tablespace ||
decode (account_status,’LOCKED’, ‘ account lock’,
‘EXPIRED’, ‘ password expire’,
‘EXPIRED \& LOCKED’, ‘ account lock password expire’,
null)
||
‘;’
from dba_users U, dba_ts_quotas Q
— Comment this clause out to include system & default users
where U.username not in (‘SYS’,’SYSTEM’)
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off

Setp — 4
==========

Fttp all the dumps and script of tablespace and users to destionation server:
=============================================================================

FTP:-
======
1.    Copy the file to tmp location.

#cp filename /tmp

2.    Change that file permission in the tmp location

#chmod 755 filename

3.    Open the command prompt.

In Run type CMD
>ftp source/destination ip address
>username
>password
>cd /tmp (file location)
>ls -lrt (To see all the files)
>lcd “location” (location is where you want to copy the file)
>bi (binary mode transfer)
>get filename (if its source ip address)
>put filename (if its destination ip address)
If you want to copy many files then (mput.file extension & mget.file extension)

Setp — 5
==========

1)    Instal Binary of Oracle 11g on Destination Server
=================================================

2)    Create the database on installed 11g
=====================================

3)    Run the script for creating tablespace and users
================================================

Setp — 6
=========
Set the same character on destination server:
=============================================

In destination database
export NLS_LANGAMERICAN_AMERICA.UTF8
Do actual import (imp parfile=imp.par)

Setp — 7
=========

Importing dump file to destination server
=========================================

imp nas30/nas30 file=NAS30.dmp log=imp_nas30.log fromuser=nas30 touser=imp_test buffer=100000000 grants=y ignore=y rows=y commit=y statistics=none;

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

11 Responses to Oracle exp and imp: oracle9i to 11g

  1. Panjian says:

    Very Good!

  2. bhaskar says:

    is there any chance to get some traing sessin from you 🙂
    thank you
    regards,
    Bhaskar
    9985390218

  3. baviri says:

    Can any one tested this procedure ? export from 9i import into 11g ?

  4. vimal says:

    Sir,

    need bit more explained way here,
    where these command will work
    (command prompt or sql plus)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s