Oracle Full Import Error

dba2dba
dba2dba used Ask the Experts™
on

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
It's a bug.  Check out Metalink note: 734681.1.

Try pre-creating the tablespaces.
Devinder Singh VirdiLead Oracle DBA Team

Commented:
>> 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.

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

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.

Author

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
Most Valuable Expert 2012
Distinguished Expert 2018

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 Team

Commented:
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.

Author

Commented:
The database is around 5 GB and there are total 13 schemas.
Can we use this as a backup strategy.
Thanks,
RK
Most Valuable Expert 2012
Distinguished Expert 2018

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/???.
Most Valuable Expert 2012
Distinguished Expert 2018

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?

Author

Commented:
Yes, backup once a day is OK for now.

Thanks,
RK
Most Valuable Expert 2012
Distinguished Expert 2018

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 Team

Commented:
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.
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial