Import Database from Windows

[Pinki Meggi]

hi all,

i have installed oracle9i database on a HP-UX11.11, and i need to import all the info from another oracle 9i database on a windows server, is it possible? if yes, how can i do it?
i have the dump file created by the exp command, but the imp command fails because there are many things that arent compatible, for example the location of the datafiles...

Can anyone help me please.

Best Regards

Pinki

[Tomek Gryszkiewicz]

You can try to copy the datafiles (source database should be down at the moment!) and then, on the new machine, recreate control files.

Something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DBNAME" RESETLOGS NOARCHIVELOG
MAXLOGFILES xxx
MAXLOGMEMBERS xxx
MAXDATAFILES xxx
MAXINSTANCES xxx
MAXLOGHISTORY xxx
LOGFILE
GROUP 1 (
'/path/to/redo/log/number/one',
'/path/to/mirror'
) SIZE xxxM,
GROUP 2 (
..............
) SIZE 70M
DATAFILE
'/path/to/first/datafile',
'/etc/etc/etc'
CHARACTER SET xxxxxxxxx;
ALTER DATABASE OPEN RESETLOGS;

[Peter Godron]

Pinki,
can you not just import the user data with the user=(user1,user2..) parameter?
Or are you asking about a complete database move? Are you set up for portable tablespaces?

[Eric Antunes]

Hi Pinki,

It should be possible...

First things you need to tell us:

1- the windows and HP-UX database versions?

2- The Win export utlity and the HP-UX import utility versions?

3- What is the exact command you are using for export?

Best Regards,

Eric Antunes

[Sivakumar TS]

dear pinki,

Importing oracle database from windows to unix shouldnt be a problem.

export the full database using exp command from sqlplus

import it using imp in unix at sqlplus after installing and configuring oracle.

You have to make sure of the following..

all required kernel tuning has been done for oracle.

oracle is installed without any erros

all PATH variabled has been set

all datafiles exist in the database created in unix, the locations of the datafiles can differ and it should not be a problem.

Could you get the error details?

With Regards,

Siva

[Eric Antunes]

Hi Pinki,

Forget the first 2 questions: what is the exact command you are using for export?

Best Regards,

Eric Antunes

[Pinki Meggi]

Hi all, thanks for replies

Im asking for a complete database move.

The windows and HP-UX version are 9.2.0 and 9.2.0.1 respectively

The win import/export utility are the same and the command are:

exp admin/password@siges_hostname FILE=expdat.dmp log=export.log FULL=y


imp sys/password@instance AS SYSDBA FULL=y FILE=expdat.dmp log=import.log

The first error i got is:

. importing objects from user system to user system
IMP-00017: failed with ORACLE 12913 error:
"CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADATA\SIGES\USERS01.dbf' ...."

IMP-00003: Cannot create dictionary managed tablespace
...


I can attach the log file if it is necessary

Best Regards

Pinki

[Eric Antunes]

Hi Pinki,

I think it is better for you to a "User Mode" export as Peter Godron suggested:

exp admin/password@siges_hostname FILE=expdat.dmp log=export.log direct=Y OWNER=(User A,...)

And while the export runs in the source database (Windows) you can create the necessary tablespaces in the destination database (HP-UX):

creare tablespace
datafile '/.../FILE1' SIZE M
default storage (
initial 100K
next 50K
minextents 1
maxextents 50
pctincrease 0);


Best Regards,

Eric Antunes

[Eric Antunes]

Well,

It is better not to use direct if you want to export REF, LOB, BFILE or object type columns (which include VARRAYs and nested tables)...

[Yogeeraj_1]

hi Pinki,

is it feasible for you to precreate all the objects before importing the data?

e.g. tablespaces, tables, views, packages, etc.

if yes, let us know, the steps will be much clearer to define.


kind regards
yogeeraj