Restoring A Full Export

[adewri]

Originally posted by kgb
This part says me that you have no idea what are you talking about:

if you donot create tablespaces, the import will create the datafiles in $ORACLE_BASE/oradata or which ever it thinks is the default location.

This part says me that you do not know what you do:

And 50% bigger so that in case export was taken with compress=Y, while import it will try to import the data in one extent. This can give problem it there is lot of data.

Originally posted by julian
[B]Oh, really ? :-)



Good, good, how about studying some Oracle too :-)

woooow, i need a place to hide and brush up again.......

[adewri]

i did the home work and this is what i found...

I did full export of one database on node 1

the location of the datafiles was

SQL> select name from v$datafile;

NAME
---------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\ORADATA\ORCL\INDX01.DBF
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
E:\ORACLE\ORADATA\ORCL\USERS01.DBF
E:\ORADATA\PUBS.DBF
E:\ORADATA\TEST01.DBF

Now i created a fresh database on another machine which did not have the last two tablespaces and e:\oradata path.

i did the full import.

and i got
-----------------------------------------------------------------

Import: Release 9.2.0.2.1 - Production on Mon Jan 20 15:23:35 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "PUBS" BLOCKSIZE 16384 DATAFILE 'E:\ORADATA\PUBS.DBF' SI"
"ZE 52428800 AUTOEXTEND ON NEXT 2097152 MAXSIZE 65535M EXTENT MANAGEM"
"ENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORADATA\PUBS.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "TEST" BLOCKSIZE 16384 DATAFILE 'E:\ORADATA\TEST01.DBF' "
"SIZE 14680064 AUTOEXTEND ON NEXT 16384 MAXSIZE 65535M EXTENT MANAGEM"
"ENT LOCAL UNIFORM SIZE 4194304 ONLINE PERMANENT NOLOGGING"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORADATA\TEST01.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
-------------------------------------------------------------------

So what i would like to say is that i was right on the part that yes you need to create tablespaces if the new machine does not have the same filesystem structure as on the previous machine
or
create the file systems on which the datafiles were present.

And yes i was wrong on the part that the "datafiles will be created in $ORACLE_BASE/oradata"

and about the part


This part says me that you do not know what you do:

And 50% bigger so that in case export was taken with compress=Y, while import it will try to import the data in one extent. This can give problem it there is lot of data.

Well KGB,
It does happen that another DBA takes the backup and you just get the dump files for import, in such cases you are not sure if the export was taken with compress=Y or Compress=N. and Compress=Y being the default parameter, most export dumps come with that option enabled.

No hard feelings, just an explanation to what i wrote and why i wrote.

Any comments??

Cheers
Amar

[kgb]

Adewri,
Do you know difference between "Oracle 8.1.5" and "Oracle 9.2.0.2.1". ?

About, lessons:
If I have a chance, I am always glad to take lessons :-)


From question:
... We have a Oracle 8.1.5 database running on Solaris 2.8. Every night full exports are taken on this database. ...

[jmodic]

Originally posted by kgb
Do you know difference between "Oracle 8.1.5" and "Oracle 9.2.0.2.1". ?

I don't know how is this remark relevant to adewri's answer (or to the whole discused isue, for that matter). I do not know of any important changes in exp/imp utilities between the two releases regarding the isues that adewri has pointed out in his reply.