Solved

DATABASE MIGRATION FROM SOLARIS TO WINDOWS 2000

Posted on 2004-04-14
11
1,148 Views
Last Modified: 2013-12-05
Hi,
need advise for database migration.
Current database oracle 8.16, platform Solaris e-450, os 2.6
New database oracle 9.2, platform window 2000.
Since full export file size over than 2G, the exp file is compressed in **.dmp.Z.

What is the command to uncompress dmp file and import the table to the new database on window 2000?
Dose all the old procedure still usefull?
Is any change on calling a DBA_JOB to schedule auto procedure run?
Please provide me all suggestion from your experience.
0
Comment
Question by:bea0883
11 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 84 total points
Comment Utility
if you have trouble in finding the uncompress tool in windows 2000.

try to break the one big dump file into smaller pieces like this:

exp userid=user/*****@<db_sid> file=exp01.dmp, exp02.dmp filesize=1500M ....(the rest of params are ignored)

then you will have the first file exp01.dmp at size of 1.5G, the rest of dump file will go to exp02.dmp.
then you transfer to windows 2000, (if ftp , use binary mode) to do the imp
0
 

Author Comment

by:bea0883
Comment Utility
Thanks seazodiac, but how many dmp file should I set? for example my data size is 20G. as a remind, the exp from Solaris platform.
thanks & Regards,
0
 
LVL 2

Assisted Solution

by:GDE
GDE earned 83 total points
Comment Utility
Autorun imp.exe/exp.exe you can from sheduler on windows 2000 (comand "at") without using DBA_JOB.

Best regards
Dmitry
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 83 total points
Comment Utility
If it not too big you can use Winzip to uncompress the file. On my Unix server I use:

$compress <file>

Then on my Windows I can unzip without any problem

As seazodiac said you can split the dump file to multi files using:

$exp user/pass file=(file_1.dmp,file_2.dmp,file_3.dmp...)  log=export.log filesize=2G full=y

Note that if the amount of data exported could not be fit into the list of files provided, export will prompt for more filenames. And this option jsut work with 8.1.6 and above

The number of files depend on your OS limitation. For example solaris 2.6 limits the file size to 2G then if the total size is 20G you have to split to 10 files

Hope this helps
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
And there is no change on DBMS_JOB package to schedule auto procedure run on 9.2. The procedure will work after importing sucessfully
0
 
LVL 5

Expert Comment

by:fmonroy
Comment Utility
that export is not useful in windows
use the utilities from 9.2 in win to export and import
0
 
LVL 1

Expert Comment

by:darshan6
Comment Utility
hi,

have the backup of the database in Solaris as

$exp user/password file=expFILE.dmp log=expfile.log full=y
$compress expFILE.dmp

then u get a file like expFILE.dmp.Z

or

$zip expFILE.dmp

in Windows unzip this file

and import the entire database.

regarrds
sudarshan
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
@bea:

you can do the math easily

for example 20G data dump

if you set filesize at 2G
then you will have 10 file exp file.

0
 
LVL 8

Expert Comment

by:Danielzt
Comment Utility

the following info is for using export/import between different Oracle version.
Matrix : Which EXPORT utility to use when importing into an Oracle8 or a higher database release          
(always use IMPORT utility of the target database):  
+----------+--------------------------------------------------------------+
|  EXPORT  |                        IMPORT into:                          |
|   from   +--------+--------+--------+--------+--------+--------+--------+
|    \/    |  8.0.5 |  8.0.6 |  8.1.5 |  8.1.6 |  8.1.7 |  9.0.1 |  9.2.0 |
+----------+--------+--------+--------+--------+--------+--------+--------+
| 5.x 1) 2)| EXP5x  | EXP5x  | EXP5x  | EXP5x  | EXP5x  | EXP5x  | EXP5x  |
| 6.x     2)| EXP6x  | EXP6x  | EXP6x  | EXP6x  | EXP6x  | EXP6x  | EXP6x  |
| 7.x     3)| EXP7x  | EXP7x  | EXP7x  | EXP7x  | EXP7x  | EXP7x  | EXP7x  |
+----------+--------+--------+--------+--------+--------+--------+--------+
| 8.0.4     | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 | EXP804 |
| 8.0.5    | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 | EXP805 |
| 8.0.6    | EXP805 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 | EXP806 |
+----------+--------+--------+--------+--------+--------+--------+--------+
| 8.1.5    | EXP805 | EXP806 | EXP815 | EXP815 | EXP815 | EXP815 | EXP815 |
| 8.1.6    | EXP805 | EXP806 | EXP815 | EXP816 | EXP816 | EXP816 | EXP816 |
| 8.1.7    | EXP805 | EXP806 | EXP815 | EXP816 | EXP817 | EXP817 | EXP817 |
+----------+--------+--------+--------+--------+--------+--------+--------+
| 9.0.1    | EXP805 | EXP806 | EXP815 | EXP816 | EXP817 | EXP901 | EXP901 |
| 9.2.0    | EXP805 | EXP806 | EXP815 | EXP816 | EXP817 | EXP901 | EXP920 |
+----------+--------+--------+--------+--------+--------+--------+--------+  
Remarks: 1) IMPORT can read export dump files created by EXPORT release 5.1.22 and     higher (up to same version).
 2) An Oracle5 or Oracle6 export dump and an Oracle8 or Oracle8i or Oracle9i    IMPORT: see the Oracle Utilities Manual, Chapter 2 "Import" for special     considerations to keep in mind.
3) An Oracle7 export dump and an Oracle8 or Oracle8i or Oracle9i IMPORT:     constraints on the DATE columns become invalid when TO_DATE function     has not been used in the constraint (this was not required in earlier    Oracle versions).  

Examples.
---------  
1. From 7.3.3 to 8.1.6 => Use the EXPORT 7.3.3 to export the data from the
                                  7.3.3 database and IMPORT 8.1.6 to import the data
                           into the 8.1.6 database.
2. From 8.1.7 to 8.1.7 => Use the EXPORT 8.1.7 to export the data from the
                           8.1.7 database and IMPORT 8.1.7 to import the data
                           into the 8.1.7 database.
3. From 9.0.1 to 8.1.7 => Use the EXPORT 8.1.7 to export the data from the
                           9.0.1 database and IMPORT 8.1.7 to import the data
                           into the 8.1.7 database.
4. From 8.1.7 to 9.2.0 => Use the EXPORT 8.1.7 to export the data from the
                           8.1.7 database and IMPORT 9.2.0 to import the data
                           into the 9.2.0 database.
5. From 9.0.1 to 9.2.0 => Use the EXPORT 9.0.1 to export the data from the
                           9.0.1 database and IMPORT 9.2.0 to import the data
                           into the 9.2.0 database.
6. From 8.1.7 to 7.3.4 => Only if never run before, first run CATEXP7.SQL
                           on the 8.1.7 database in order to create the
                           Oracle7 data dictionary views; then use the
                           EXPORT 7.3.4 to export the data from the 8.1.7
                           database and IMPORT 7.3.4 to import the data into
                           the 7.3.4 database.
7. From 9.0.1 to 7.3.4 => Not supported.
8. From 8.0.5 to 9.0.1 => Not supported with export FULL=Y and import FULL=Y
9. From 8.1.5 to 9.2.0 => Not supported with export FULL=Y and import FULL=Y

based on the above information, the following way should be practical.

1: on your Windows PC, install a oracle 8.1.6 client.  you can have Oracle 816 and Oracle 9i installed in the same PC and in different Oracle home.

2: use Oracle 816 client connect to your Solar database and to do the export.
    you should have no 2G file size problem.

3: use Oracle 9.2 import to import the dump file got in step 2.

try it!







0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now