 Step-by-Step Recovering dump file using IMP [message #419462] |
Sat, 22 August 2009 15:06  |
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 #419466 is a reply to message #419462] |
Sat, 22 August 2009 15:53  |
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 #419472 is a reply to message #419462] |
Sat, 22 August 2009 16:30 |
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 #419474 is a reply to message #419462] |
Sat, 22 August 2009 17:41 |
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 #419476 is a reply to message #419462] |
Sat, 22 August 2009 18:19 |
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 #419478 is a reply to message #419462] |
Sat, 22 August 2009 18:44 |
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 #419490 is a reply to message #419462] |
Sun, 23 August 2009 03:59 |
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 #421966 is a reply to message #419462] |
Sat, 12 September 2009 10:15 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
