Oracle Full Import Error


Oracle 10g on Win 2003 Server

I got the below errors when trying to import the database,

I have a database SMSI on a server
I created a test database TEST on the same server using DBCA

I exported the SMSI.DMP Dump file from SMSI

expdp 'sys/<passwrd>@SMSI as sysdba' directory=data_pump_dir FULL=y DUMPFILE=SMSI.dmp LOGFILE=SMSI.log


I tried to import it on to the TEST Database with the below command:


Below is the error:

U:\>imp 'sys/<passwrd>@TEST as sysdba' file='D:\TEST\SMSI.dmp' full=yes ignore=y;

Import: Release 10.1.0.5.0 - Production on Thu Mar 4 19:56:31 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1276:
 "CREATE TEMPORARY TABLESPACE "TEMPORARY_DATA" BLOCKSIZE 8192 TEMPFILE  'E:\O"
 "RADATA\SMSI\DATAFILE\O1_MF_TEMPORAR_4XYL80JS_.TMP' SIZE 104857600       AUT"
 "OEXTEND ON NEXT 655360  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  UNIFORM SIZ"
 "E 1048576"
IMP-00003: ORACLE error 1276 encountered
ORA-01276: Cannot add file E:\ORADATA\SMSI\DATAFILE\O1_MF_TEMPORAR_4XYL80JS_.TMP.  File has an Oracle Managed Files file name.
IMP-00017: following statement failed with ORACLE error 1276:
 "CREATE TABLESPACE "BEKINS" BLOCKSIZE 8192 DATAFILE  'E:\ORADATA\SMSI\DATAFI"
 "LE\O1_MF_BEKINS_4XYL8361_.DBF' SIZE 8192M       AUTOEXTEND ON NEXT 53687091"
 "2  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  "
 "SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1276 encountered
ORA-01276: Cannot add file E:\ORADATA\SMSI\DATAFILE\O1_MF_BEKINS_4XYL8361_.DBF.  File has an Oracle Managed Files file name.
IMP-00017: following statement failed with ORACLE error 1276:
 "CREATE TABLESPACE "INDEX_DATA" BLOCKSIZE 8192 DATAFILE  'E:\ORADATA\SMSI\DA"
 "TAFILE\O1_MF_INDEX_DA_4XYL9KKS_.DBF' SIZE 26214400       AUTOEXTEND ON NEXT"
 " 26214400  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PER"
 "MANENT  SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1276 encountered
ORA-01276: Cannot add file E:\ORADATA\SMSI\DATAFILE\O1_MF_INDEX_DA_4XYL9KKS_.DBF.  File has an Oracle Managed Files file name.
IMP-00017: following statement failed with ORACLE error 1276:
 "CREATE TABLESPACE "TOOLS" BLOCKSIZE 8192 DATAFILE  'E:\ORADATA\SMSI\DATAFIL"
 "E\O1_MF_TOOLS_4XYL9LDX_.DBF' SIZE 20971520       EXTENT MANAGEMENT LOCAL  A"
 "UTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1276 encountered
ORA-01276: Cannot add file E:\ORADATA\SMSI\DATAFILE\O1_MF_TOOLS_4XYL9LDX_.DBF.  File has an Oracle Managed Files file name.
IMP-00017: following statement failed with ORACLE error 1276:
 "CREATE TABLESPACE "USER_DATA" BLOCKSIZE 8192 DATAFILE  'E:\ORADATA\SMSI\DAT"
 "AFILE\O1_MF_USER_DAT_4XYL9M53_.DBF' SIZE 104857600       AUTOEXTEND ON NEXT"
 " 52428800  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PER"
 "MANENT  SEGMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1276 encountered
ORA-01276: Cannot add file E:\ORADATA\SMSI\DATAFILE\O1_MF_USER_DAT_4XYL9M53_.DBF.  File has an Oracle Managed Files file name.
IMP-00017: following statement failed with ORACLE error 959:
 "ALTER USER "SYS" IDENTIFIED BY VALUES '2F02560F43D0F8DE' TEMPORARY TABLESPA"
 "CE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEMPORARY_DATA' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "ALTER USER "SYSTEM" IDENTIFIED BY VALUES '3C9275607545BBE8' DEFAULT TABLESP"
 "ACE "BEKINS" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "BEKINS" IDENTIFIED BY VALUES '81C389C53F6C4B29' DEFAULT TABLES"
 "PACE "BEKINS" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "READ1" IDENTIFIED BY VALUES 'D8A6CD6E7F47767A' DEFAULT TABLESP"
 "ACE "BEKINS" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "TOAD" IDENTIFIED BY VALUES '4759257F78A8B5A3' DEFAULT TABLESPA"
 "CE "BEKINS" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "UPDATE1" IDENTIFIED BY VALUES '4EE56C51ACD31E3C' DEFAULT TABLE"
 "SPACE "USER_DATA" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USER_DATA' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "SYSADM" IDENTIFIED BY VALUES 'A33D87FC1E7EF048' DEFAULT TABLES"
 "PACE "USER_DATA" TEMPORARY TABLESPACE "TEMPORARY_DATA""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USER_DATA' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT UNLIMITED TABLESPACE TO "BEKINS" WITH ADMIN OPTION"
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT UNLIMITED TABLESPACE TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT CREATE PUBLIC SYNONYM TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT DROP PUBLIC SYNONYM TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ANY TABLE TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "DBA" TO "BEKINS" WITH ADMIN OPTION"
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "CONNECT" TO "READ1""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'READ1' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "CONNECT" TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "RESOURCE" TO "TOAD""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "QUEST_SL_SQLAB_ROLE" TO "BEKINS""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "CONNECT" TO "UPDATE1""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'UPDATE1' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT "CONNECT" TO "SYSADM""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'SYSADM' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "BEKINS" DEFAULT ROLE "DBA", "QUEST_SL_SQLAB_ROLE""
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'BEKINS' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "READ1" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'READ1' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "TOAD" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'TOAD' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "UPDATE1" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'UPDATE1' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "SYSADM" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'SYSADM' does not exist
IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "TOAD" QUOTA UNLIMITED ON "BEKINS""
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'TOAD' does not exist
. importing BEKINS's objects into BEKINS
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing WMSYS's objects into WMSYS
. importing SYSMAN's objects into SYSMAN
. importing BEKINS's objects into BEKINS
 "ALTER SESSION SET CURRENT_SCHEMA= "BEKINS""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully



Please suggest a solution as this is very critical for me.

Thanks,
Rama
LVL 8
dba2dbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It's a bug.  Check out Metalink note: 734681.1.

Try pre-creating the tablespaces.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> expdp 'sys/<passwrd>@SMSI as sysdba' directory=data_pump_dir FULL=y DUMPFILE=SMSI.dmp LOGFILE=SMSI.log

>> U:\>imp 'sys/<passwrd>@TEST as sysdba' file='D:\TEST\SMSI.dmp' full=yes ignore=y;

You are taking export using DATA PUMP (expdp) but while importing you are using traditional import (imp) instead of impdp.
dba2dbaAuthor Commented:
Thanks slightwv. I am not able to see that, can you suggest a work around.

virdi_ds: I typed it wrong in the question. I tried exp/ imp and expdp and impdp. Both resulted errors.

Is there a way I can create a new database from the existing DMP file.

Thanks,
Rama
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

slightwv (䄆 Netminder) Commented:
>> I am not able to see that
Do you mean you don't have access to Metalink?

The work-around is what I mentioned: pre-create the tablespaces.
dba2dbaAuthor Commented:
slightwv:

Thats correct, I am not able to check metalinka s I dont have access.

Pre creating a table spaces is not possible in a Disaster scenario as we are not aware of the tablespaces after disaster.

How should we use the import in this case.

I am trying to setuo daily exports of the database which can be imported during a disaster.  

I am new to oracle dba. I am forced to support few leftover oracle db's here.

Thanks for your patience.

-Rama
slightwv (䄆 Netminder) Commented:
emp/imp can only effectively used for backup/recovery in very limited situations.  If you can afford to lose data from the time of the last export until restore, then it's OK.

In other words:  exp/imp is an all or nothing recovery.  You export say at 10:00 PM.  The system crashes at 9:59PM.  You restore back the 10:00PM the previous day losing about 24 hours of data.

In most systems, that is unacceptable.  You should use RMAN.

>>we are not aware of the tablespaces after disaster.

Tablespaces aren't things that change much.  As the DBA, if you don't know them, who does?

Anyway, you can tell imp to generate output and not really do anything.  You can then extract the text to create the tablespaces.

Which version do you want to use (old exp/imp or datapump)?

>>I am new to oracle dba.
and no Support contract with Oracle?  What are you going to do if the system burps in the middle of the night?

Experts-Exchange is good for a lot of things but immediate support isn't one of them.  I wouldn't want to rely on the internet when I see strange errors and no one can log into my database.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
How big is your database is?
How many schemas are there?
If small, then you can take export of every schema instead of whole database.
dba2dbaAuthor Commented:
The database is around 5 GB and there are total 13 schemas.
Can we use this as a backup strategy.
Thanks,
RK
slightwv (䄆 Netminder) Commented:
>>every schema instead of whole database
They don't have a 'build' script or DBCA template.

They would still need to pre-create the tablespaces so this doesn't really help.  Besides this way they would also need a build script for the users/roles/???.
slightwv (䄆 Netminder) Commented:
>>Can we use this as a backup strategy.

Depends on your opinion of my post: http:#27383943.  Can you lose 23 hours of data?
dba2dbaAuthor Commented:
Yes, backup once a day is OK for now.

Thanks,
RK
slightwv (䄆 Netminder) Commented:
the 'for now' scares me but OK, it's your system.

Now how do you want to proceed?

Since there's a bug and you can't do a full, what method do you want to pursue?

1: create script to precreate tablespaces, keep up to date as you add tablespaces, full import
2: don't know tablespaces at import time, use import to 'show' tablespaces, manually create them, ful limport
3 (virdi_ds suggestion): export individual schemas, precreate tablespaces, precreate database users/roles, import individual schemas
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Since your database is very small, you can take hot backup also. If required, you can compress it as well, but you need at least 10G.
MujeeburRahmanCommented:
You are using imp command to import the backup file created by datapump. You have to use impdp in this case.
either you can use exp and imp or expdp and impdp.

It will be good if you create the tablespaces with the same size before you import any database
dba2dbaAuthor Commented:
Thank you experts for your suggestions. Suggestion from slightwv and virdi_ds has worked for me.
I have precreated the tablespaces and it import went through fine.

Thanks Again.
-RK
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.