Oracle Database exp/imp 9i TO 11g

Step-1

 Set the same character on source server:

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

Step-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;

Step-3
 
Extract the table script from soured database 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

Step-4


Ftp 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)

Step-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


Step-6

Set the same character on destination server:

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

Step-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;