Home » RDBMS Server » Server Utilities » Step-by-Step Recovering dump file using IMP (OracleXE, 10g, WinXP)
icon3.gif  Step-by-Step Recovering dump file using IMP [message #419462] Sat, 22 August 2009 15:06 Go to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Dear Folks,

I'm a newbie in Oracle and I've managed myself in a real problem not only to solve the issue but to start learning Oracle as well.
The situation is that I've been given a dump file of a system which the system is to be renwed because of support problems. Besides, I don't have any kind of access to information about that syste,.
I need to import the contents of the dump file to an oracle database (only tables, their data and relations are sufficient) to migrate them in the new system.
I'm planning to import the dump file (which is claimed to be a complete dump file) to my OracleXE and write a Java program to convert the data to the format of the new DB (Which is ready by now).

Googling around, I found out about the imp tool but I couldn't use it effectively (nothing ported).
Firstly, I used the intercative tool (imp) and provided system account (and it's pass) as asked but I ecountered the following error:

IMP-00008: unrecognized statement in the export file

I searched the forums and increased the buffer size to 10000000 but no use.
Besides, I'm sure that the dump file is healthy since I successed in porting it's tables and a few records of each to MSSQL using an evaluation version of ord2mssd tool.

I don't know anything about the user who has created the backup (but I think it should be sys) and even the version of the oracle by which the dump file has been created. Besides, I've Oracle Database 10g Express Edition Release 10.2.0.1.0

I've seen some other guides to create a new table space, etc. but I confused using them.

Anyone can help me step by step to do this? Any kind of help is appreciated,

Cheers,
--Hossein
Re: Step-by-Step Recovering dump file using IMP [message #419463 is a reply to message #419462] Sat, 22 August 2009 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
00008, 00000, "unrecognized statement in the export file: \n  %s"
// *Cause:  Import did not recognize a statement in the export file. Either 
//          the export file was corrupted, or an Import internal error has 
//          occurred. 
// *Action: If the export file was corrupted, retry with a new export file. 
//          Otherwise, report this as an Import internal error and submit
//          the export file to customer support.


One possible cause is the version of imp that was used did not match the version of exp that was used.

You really, really need to know which version of Oracle to 4 decimal places that created this file.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

In the future use CUT & PASTE a stated in Posting Guidelines.


Re: Step-by-Step Recovering dump file using IMP [message #419464 is a reply to message #419462] Sat, 22 August 2009 15:34 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
BlackSwan:

Thank you very much for your reply and guidance.
I'm sure that the exporting Oracle's version is <=10g. Does this provide any kind of help?
Re: Step-by-Step Recovering dump file using IMP [message #419465 is a reply to message #419462] Sat, 22 August 2009 15:43 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I'm sure that the exporting Oracle's version is <=10g.
If this means V9 or V8 made the file, then V10 imp should be able to use it.
Since you got an error, then other factors are involved.

do imp again, also include LOG=capture.log

Post back here whole terminal session along with contents of capture.log
Re: Step-by-Step Recovering dump file using IMP [message #419466 is a reply to message #419462] Sat, 22 August 2009 15:53 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Thanks again.
I ran the command
imp file=h:\sh.dmp LOG=h:\capture.log FULL=Y


the contents of the log file is as follows:
Username: 
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
IMP-00008: unrecognized statement in the export file: 
  
. importing SAD's objects into SAD
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing SUNFLOWER's objects into SUNFLOWER
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing INTERNET's objects into INTERNET
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
. importing SAD's objects into SAD
 "ALTER SESSION SET CURRENT_SCHEMA= "SAD""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully


Besides, user system with my password easily connects to my OracleXE using it's web interface.
Re: Step-by-Step Recovering dump file using IMP [message #419467 is a reply to message #419462] Sat, 22 August 2009 15:57 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Using the GUI tool (EasyDump for Oracle) I found out that the dump file has been created by Oracle 10.01.00 and mine is 10.2.0.10
Re: Step-by-Step Recovering dump file using IMP [message #419468 is a reply to message #419462] Sat, 22 August 2009 16:13 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Another thing I've discovered is that the dump file contains records in non-english language (farsi) as well as english ones and the character set might be AL32UTF8
Re: Step-by-Step Recovering dump file using IMP [message #419469 is a reply to message #419462] Sat, 22 August 2009 16:14 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>. importing SAD's objects into SAD
>IMP-00003: ORACLE error 1435 encountered
>ORA-01435: user does not exist
>. importing SUNFLOWER's objects into SUNFLOWER
>IMP-00003: ORACLE error 1435 encountered
>ORA-01435: user does not exist
>. importing INTERNET's objects into INTERNET

It appears you need to manually create users SAD, SUNFLOWER, & INTERNET
Re: Step-by-Step Recovering dump file using IMP [message #419470 is a reply to message #419462] Sat, 22 August 2009 16:19 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
What should be their passwords and how should I specify their passwords to imp?
Re: Step-by-Step Recovering dump file using IMP [message #419471 is a reply to message #419462] Sat, 22 August 2009 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>What should be their passwords
Anything you want


>and how should I specify their passwords to imp?

CREATE USER ......

Then issue the same imp command you previously posted.
Re: Step-by-Step Recovering dump file using IMP [message #419472 is a reply to message #419462] Sat, 22 August 2009 16:30 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
I created users INTERNET and SAD and executed the following command
imp  file=H:\sh.dmp fromuser=SAD touser=SAD  buffer=10000000 Charset=AL32UTF8 commit=y show=y skip_unusable_indexes=y


the following is the result
Import: Release 10.2.0.1.0 - Production on Sun Aug 23 02:01:05 2009



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





Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production



Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

export client uses AL32UTF8 character set (possible charset conversion)

IMP-00008: unrecognized statement in the export file: 

  

. importing SAD's objects into SAD

 "BEGIN  "

 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"

 "CURRENT_SCHEMA'), export_db_name=>'SAD', inst_scn=>'155737357');"

 "COMMIT; END;"

 "ALTER SESSION SET CURRENT_SCHEMA= "SAD""

 "CREATE SYNONYM "AUTO_UPDATE_EXE" FOR "SUNFLOWER"."AUTO_UPDATE_EXE""

 "CREATE SYNONYM "FUN_TEST" FOR "SUNFLOWER"."FUN_TEST""

Import terminated successfully with warnings.

8/23/2009 2:01:19 AM: Done


As you can see it still has the problem of "unrecognized statement in the export file". Although it said "Import terminated successfully with warnings", When I browsed tables of the users SAD and INTERNET, nothing was imported.
Re: Step-by-Step Recovering dump file using IMP [message #419473 is a reply to message #419462] Sat, 22 August 2009 16:42 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Above would preclude from Fasri being loaded.


imp file=H:\sh.dmp full=Y show=Y

Above might reveal clues, then again might not

FYI
imp help=yes
displays all possible command line options
Re: Step-by-Step Recovering dump file using IMP [message #419474 is a reply to message #419462] Sat, 22 August 2009 17:41 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Thank you very much. I think we are close to the final solution.
using the command
imp  file=H:\sh.dmp fromuser=SYSTEM,SYS,SAD,INTERNET,SUNFLOWER touser=system  buffer=10000000 
Charset=AL32UTF8 commit=y Recordlength=1000000 skip_unusable_indexes=y

importing took place and now I have some of the tables but some errors still exist. It is worth noting that imp generated thousands of pages of output. In the following, I'm providing some of them

Import: Release 10.2.0.1.0 - Production on Sun Aug 23 02:25:54 2009



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





Note: RECORDLENGTH=1000000 truncated to 65535



Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production



Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

export client uses AL32UTF8 character set (possible charset conversion)

IMP-00008: unrecognized statement in the export file: 

  

. importing SAD's objects into SAD

. importing SUNFLOWER's objects into SUNFLOWER

. importing INTERNET's objects into INTERNET

. importing SAD's objects into SAD

IMP-00015: following statement failed because the object already exists:

 "CREATE SYNONYM "AUTO_UPDATE_EXE" FOR "SUNFLOWER"."AUTO_UPDATE_EXE""

IMP-00015: following statement failed because the object already exists:

 "CREATE SYNONYM "FUN_TEST" FOR "SUNFLOWER"."FUN_TEST""

. importing SUNFLOWER's objects into SUNFLOWER

. . importing table                           "AA"         18 rows imported

. . importing table                          "AAA"          1 rows imported

. . importing table                  "ADD_DOC_REQ"          0 rows imported

IMP-00017: following statement failed with ORACLE error 959:

 "CREATE TABLE "ALLOWED_CLASS" ("ID" NUMBER(10, 0) NOT NULL ENABLE, "CLASS_SE"

 "CTION_LINK" NUMBER(10, 0) NOT NULL ENABLE, "UNIVERSITY_TERM_LINK" NUMBER(10"

 ", 0) NOT NULL ENABLE, "CONDITION_TEXT_ADD" CLOB, "CONDITION_TEXT_DEC" CLOB,"

 " "DESCRIPTION_ADD" CLOB, "DESCRIPTION_DEC" CLOB)  PCTFREE 10 PCTUSED 40 INI"

 "TRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 FREELISTS 1 FREELIST GROUPS 1 B"

 "UFFER_POOL DEFAULT) TABLESPACE "SAD" LOGGING NOCOMPRESS LOB ("CONDITION_TEX"

 "T_ADD") STORE AS "SYS_LOB0000030904C00004$$"  (TABLESPACE "SAD" ENABLE STOR"

 "AGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING  STORAGE(INITIAL 65536 "

 "FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("CONDITION_TEXT_DE"

 "C") STORE AS "SYS_LOB0000030904C00005$$"  (TABLESPACE "SAD" ENABLE STORAGE "

 "IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING  STORAGE(INITIAL 65536 FREE"

 "LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("DESCRIPTION_ADD") STO"

 "RE AS "SYS_LOB0000030904C00008$$"  (TABLESPACE "SAD" ENABLE STORAGE IN ROW "

 "CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1"

 " FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("DESCRIPTION_DEC") STORE AS ""

 "SYS_LOB0000030904C00009$$"  (TABLESPACE "SAD" ENABLE STORAGE IN ROW CHUNK 8"

 "192 PCTVERSION 10 NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELI"

 "ST GROUPS 1 BUFFER_POOL DEFAULT))"

IMP-00003: ORACLE error 959 encountered

ORA-00959: tablespace 'SAD' does not exist

. . importing table      "ALLOWED_STRUC_CLASS_ADD"        338 rows imported

. . importing table      "ALLOWED_STRUC_CLASS_DEC"         36 rows imported

. . importing table     "ALLOWED_STRUC_LESSON_ADD"          0 rows imported

. . importing table "ALLOWED_STRUC_LESSON_ADD_TERM"          0 rows imported

. . importing table "ALLOWED_STRUC_LESSON_DEC_TERM"          0 rows imported

. . importing table   "ALLOW_STRUC_CO_PRE_REQ_ADD"       3302 rows imported

. . importing table "ALLOW_STRUC_COPRE_REQ_ADD_TERM"          0 rows imported

. . importing table   "ALLOW_STRUC_CO_PRE_REQ_DEC"       2689 rows imported

. . importing table "ALLOW_STRUC_COPRE_REQ_DEC_TERM"          0 rows imported

. . importing table                "AQ$_REPLAY_AQ"

IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SUNFLOWER"."AQ$_REPLAY_AQ"."ORDER01#" (actual: 89, maximum: 60)

MP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SUNFLOWER"."CLASSES_PLACE_INFO"."NAME" (actual: 33, maximum: 30)


now, my questions are:
1- Why do I need a tablespace SAD for table "ALLOWED_CLASS" but not for "ALLOW_STRUC_CO_PRE_REQ_ADD"?
2- How can I create the required tablespace? Is it possible in my OracleXE?
3 - What is the reason for the errors:
IMP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SUNFLOWER"."AQ$_REPLAY_AQ"."ORDER01#" (actual: 89, maximum: 60)

MP-00019: row rejected due to ORACLE error 12899

IMP-00003: ORACLE error 12899 encountered

ORA-12899: value too large for column "SUNFLOWER"."CLASSES_PLACE_INFO"."NAME" (actual: 33, maximum: 30)


I should appreciate your kind support.

[Updated on: Sun, 23 August 2009 00:25] by Moderator

Report message to a moderator

Re: Step-by-Step Recovering dump file using IMP [message #419475 is a reply to message #419462] Sat, 22 August 2009 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>1- Why do I need a tablespace SAD for table "ALLOWED_CLASS" but not for "ALLOW_STRUC_CO_PRE_REQ_ADD"?

table ALLOW_STRUC_CO_PRE_REQ_ADD likely resides in a different tablespace that already exists

2- How can I create the required tablespace? Is it possible in my OracleXE?

You could create the new tablespace, or "pre-create" the table with no rows in a tablespace that already exists.

CREATE TABLESPACE SAD DATAFILE '/DIRECTORY/SAD_DATA.DBF' SIZE 2048M;

3 - What is the reason for the errors:
>ORA-12899: value too large for column "SUNFLOWER"."AQ$_REPLAY_AQ"."ORDER01#" (actual: 89, maximum: 60)
self-explanatory.
Re: Step-by-Step Recovering dump file using IMP [message #419476 is a reply to message #419462] Sat, 22 August 2009 18:19 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Omitting the character encoding preferences made the "value too large..." messages disappear but the funny thing is that no message for lack of SAD tablespace is given and the tables in that tablespace don't import.

What do you think of this?
Re: Step-by-Step Recovering dump file using IMP [message #419477 is a reply to message #419462] Sat, 22 August 2009 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>What do you think of this?
Somewhat makes sense.
With the WE.... characterset I believe the largest character consumes 2 bytes.
I would not be surprised if Farsi took 3 or 4 bytes per character.

In thinking about this situation, you may need to start with new DB that is created with UTF-8 as base character set.

I'll admit that this area of expanded character sets is not my strong point.

Again, I don't do XE either.

dbca (Data Base Creation Assistant) is a GUI which creates new DBs.
Near the end of the wizard you have the ability to change/select character set.

I am not sure of your best course of action from here.
Re: Step-by-Step Recovering dump file using IMP [message #419478 is a reply to message #419462] Sat, 22 August 2009 18:44 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Exploring in the DB made me convinced that omitting the characterset resulted in skipping fromm all the tables!

- How can I ensure what charset a dump file uses?

an odd thing happened. I dropped the three users (SAD, INTERNET, SUNFLOWERS) and created them again, now re-executing the former successful command results in skipping all the tables with non of them actually ported.

It is worth noting that, After the first successful import, I was able to see Farsi texts in tables data with no problem.

BlackSwan: I could hardly find a word to express my gratitude to you.
Re: Step-by-Step Recovering dump file using IMP [message #419479 is a reply to message #419462] Sat, 22 August 2009 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>- How can I ensure what charset a dump file uses?
If it can be done, I don't know how to do it.

FWIW - Starting now I'll be offline for next 12 - 16 hours.
Re: Step-by-Step Recovering dump file using IMP [message #419481 is a reply to message #419462] Sat, 22 August 2009 19:15 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Thanks for everything.
Hopefully I'll post the success story shortly
Re: Step-by-Step Recovering dump file using IMP [message #419490 is a reply to message #419462] Sun, 23 August 2009 03:59 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Partial Success:
By now, I've imported the dump but still experiencing problems about too large records caused by charset and the full fields. I tried nlsreader which intends to findout about a dump files' charset but it returned 0309 which has no special meaning for me.

I saw in an email list that this is should be due to binary/text file problems. my dump file is a rar archive on a cdrom. Does anyone know how can I extract the archive in binary mode?
Re: Step-by-Step Recovering dump file using IMP [message #419500 is a reply to message #419462] Sun, 23 August 2009 10:19 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>ORA-12899: value too large for column "SUNFLOWER"."AQ$_REPLAY_AQ"."ORDER01#" (actual: 89, maximum: 60)
I can not offer an direct proof, but I strongly suspect this error results from the database not supporting UTF-8 characterset.
The multibyte Farsi string is just too long for the existing fields

The only way I can think to validate this idea would be to create a new database with UTF-8 & do the import again.

If my hypothesis is correct, the new DB will be the only way to successfully import all the data.

This is unless or until somebody else has any better idea.
Re: Step-by-Step Recovering dump file using IMP [message #421966 is a reply to message #419462] Sat, 12 September 2009 10:15 Go to previous messageGo to next message
hosm
Messages: 13
Registered: August 2009
Junior Member
Solved, Thank you all!

I developed a simple java program which reads the names and descriptions of the partially imported tables from my imp resulted DB and create the appropriate tables with longer fields. (It wasn't possible to do it manually since there are more than 200 tables) then performing the imp again with the tables precreated.

Thanks Swan.
Re: Step-by-Step Recovering dump file using IMP [message #422109 is a reply to message #421966] Mon, 14 September 2009 08:16 Go to previous message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks for the update.
Previous Topic: SQLLDR - Deriving another field while loading
Next Topic: How to insert the data from data file into multiple tables using the delimited (like comma)
Goto Forum:
  


Current Time: Thu Sep 29 09:01:29 CDT 2016

Total time taken to generate the page: 0.09622 seconds