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

[Pinki Meggi]

Hi, thanks once again for your kind attention.

We are migrating from windows to hp-ux, and i need to copy all information from database running on windows system, i mean i need to have the same database running now on HP-UX.

so in your opinion i have to create first all datafiles, tablespaces, users, etc ... and after that import, just the data? It will be a tough job, but it is feasible. I prefered to do the import in one step, because the database is being used by the users of my company but if i have no option i can do it.


Thanks for help

Best Regards

Pinki

[Alexander M. Ermes]

Hi Pinki.
With the Oracle Enterprise Manager you can create many of the necessary scripts to create your new database ( DDL scripts ).
And give a thought to the item, whether you need locally managed tablespace, because you do not have that much influence on these.
For the export create a parameter file with all necessary options.
Rgds
Alexander M. Ermes

[Eric Antunes · Accepted solution]

Hi Pinki,

You may want to try to clone the database (this works fine from an HP-UX instance to create a new HP-UX instance but I never tried it with your situation).

Here are the steps...


In the source DB (windows):

shutdown immediate;

ftp the datafiles, redolog files, control files in BINARY mode and init.ora in ASCII mode to there new HP-UX destinations (init.ora is normally at $ORACLE_HOME/dbs)


In the destination DB (HP-UX):

Make sure init.ora is pointing to the correct control files location.

svrmgrl or sqlplus

connect / as sysdba;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
'/disc1/.../log01a.dbf',
'/disc2/.../log01b.dbf'
) SIZE 10M,
GROUP 2 (
'/disc1/.../log02a.dbf',
'/disc2/.../log02b.dbf'
) SIZE 10M
DATAFILE
'/.../system01.dbf',
'/.../rbs01.dbf',
'/.../temp01.dbf',
'/.../user1x01.dbf',
'/.../user1d01.dbf',
...
;

recover database; --it may error out but continue anyway...

alter database open resetlogs;


As I said, I never tried it but it may work...

Best Regards,

Eric Antunes

[Pinki Meggi]

Hi,

thanks a lot fo help


I will try it, hope it will work.

Best Regards

Pinki

[Yogeeraj_1]

hi pinki,

to generate the DDLs you can also use:

yd@MYDB.MU> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

Hope this helps too!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

for the generation of tablespace scripts you can also proceed as follows:


$ exp userid=/ full=y

Export: Release 8.1.7.4.0 - Production on Wed Jan 13 14:14:13 2006

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions ^C <<<<<=== press CTRL-C
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully


$ grep 'CREATE TABLESPACE' expdat.dmp
CREATE TABLESPACE "RBS_TS_01" DATAFILE '/d01/oradata/ora817dev/rbs_ts_01.dbf'
SIZE 31563776 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_02" DATAFILE '/d02/oradata/ora817dev/rbs_ts_02.dbf'
SIZE 157392896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_03" DATAFILE '/d03/oradata/ora817dev/rbs_ts_03.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_04" DATAFILE '/d04/oradata/ora817dev/rbs_ts_04.dbf'
SIZE 48340992 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_05" DATAFILE '/d01/oradata/ora817dev/rbs_ts_05.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "USERS" DATAFILE '/d04/oradata/ora817dev/users.dbf' SIZE
661127168 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "DRSYS" DATAFILE '/d04/oradata/ora817dev/drsys.dbf' SIZE
36700160 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "XXX" DATAFILE '/tmp/xxx.dbf' SIZE 105373696 REUSE AUTOEXTEND
ON NEXT 104857600 MAXSIZE 32767M DEFAULT STORAGE(INITIAL 40960 NEXT 40960
MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ONLINE PERMANENT
CREATE TABLESPACE "TMP" DATAFILE '/tmp/tmp.dbf' SIZE 1048576 REUSE EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 65536
CREATE TABLESPACE "CLAMS" DATAFILE '/d03/oradata/ora817dev/clams.dbf' SIZE
78118912 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288

Alternatively, in Oracle 9i, you can again use the DBMS_METADATA package to get the tablespace scripts:

yd@MYDB.MU> select dbms_metadata.get_ddl( 'TABLESPACE', 'USERS' ) from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

CREATE TABLESPACE "USERS" DATAFILE
'/u01/oracle/p1/oradata/ora920/users01.dbf' SIZE 26214400 REUSE
AUTOEXTEND ON NEXT 1310720 MAXSIZE UNLIMITED
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO


hope this helps too!


kind regards
yogeeraj

[R. Allan Hicks]

You might find it helpful to use RMAN or sqlplus to back up the control file to trace on the source system. Then, on the target system take the control file from the source and modifiy the database file locations (since windows and HPUX won't agree on the locations). Build the database on the target prior to attempting to import the source. Since the datafiles are already built, the imp won't (or at least should not) try to build them. I believe that was the source of your problems early on where imp complained about trying to create a data file at E:/.....

Hope this helps. I've moved from HPUX to linux using imp so it takes care of the Big Endian/Little Endian compatibility problem.

-Hope this helps

[SteveKirby]

A database clone will not work. You must export/import.

The 'lazy' way to do the export/import is to:
- exp full=y to get everything on the Windows Box
- Create a new database on the HP-UX with the tablespaces you need and the same version or higher of the database (e.g. 9.2.0.7)
- Import the full Windows export into the HP.
- Re-run catalog/catproc to clean up orphan packages on HP-UX

A slightly better way is the user import rather than 'full', but I usually don't bother since the database I migrate often have a LOT of users.

[Simon Wickham_6]

Install Oracle on to the taget database and do a Export of your existing database then Import this will setup the structures. You will need to run catalog.sql and catproc.sql.

1. catalog.sql creates the data dictionary.
2. catproc.sql creates all structures required for PL/SQL.

Export
------
set ORACLE_SID=mysid
set ORACLE_HOME=c:oracleora9i
exp userid=system/manager file=c:exportsmysid.dmp
log=c:exportsmysid.log full=y

Import
------
imp userid=system/oracle file=c:exportsmysid.dmp log=c:\temp\uw.log

Monitor Import
--------------
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

Regards,
Simon