how do I import contents of dmp file after partially import previously

I backed up a tablespace and dropped it because I had a corrupt datafile. Then I started reimporting the data from the backup back into tablespace after recreating it. Everything was going well. Then I was asked to abort the import by developers in order for them to do other tasks. So I killed the import. Now I am being asked to finish the import. I tried importing the same way I did the first time and it is taking an extremely long time. It has been 2 and a half days. All I see is errors where it is trying to import individual records that were successfully imported without error the first time. I expected there to be errors. But I didn't expect it to go through each record in each schema and to go run this long. Is there something I can do to get this done quicker? Do I kill this import and change the parameters or use some different command?

here is the command I used:

nohup imp system/<password entered here> file=dataT.dmp log=dat3152011.log full=y statistics=none
sikyalaSenior Database AdministratorAsked:
Who is Participating?
 
PilouteConnect With a Mentor Commented:
Hi sikyala,

Please clarify following :

- oracle version
- what kind of export is it ? data pump / classical one / RMAN backup

If it is a classical export / import as I guess, all you can do is :

1) use a larger BUFFER (parameter)
2) next time you import, activate the RESUMABLE feature (parameter) and related name & timeout.
3) check also the IGNORE & COMMIT parameters,
- the first allows you not to report errors => not to spend time to send you back useless error messages.
- the second is useful to allow restarting a stopped import.
Warning : if ALL of your tables have at least one unique key, you can use these parameter. Otherways, you will end up with double entries in tables...
4) do your import operation on the server itself. You will spare the network transactions time.


Official doc here :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2391


Cheers,
P

0
 
choukssaCommented:
Use TABLE_EXISTS_ACTION=APPEND.


nohup imp system/<password entered here> file=dataT.dmp log=dat3152011.log full=y statistics=none TABLE_EXISTS_ACTION=APPEND

- choukssa
0
 
sikyalaSenior Database AdministratorAuthor Commented:
so if the record is already in the table will it skip it or keep giving the error. This seems to be all it is doing during the import

example

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (S_AD.PK_SP) violated
Column 1 CBA
Column 2 CB-020322
Column 3 /ret-data/cba_single_page_pages/302/CB-020322_00...
Column 4 33
Column 5 423590
Column 6 1079973
Column 7 302
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PilouteCommented:
@choukksa

that parameter doesn't exist in original import... It is a DataPump feature.

Rgds,
P
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Piloute:

- oracle version >> Oracle 10g
- what kind of export is it ? data pump / classical one / RMAN backup >> classical

If it is a classical export / import as I guess, all you can do is :

2) next time you import, activate the RESUMABLE feature (parameter) and related name & timeout. >> How do I do this
3) check also the IGNORE & COMMIT parameters,
- the first allows you not to report errors => not to spend time to send you back useless error messages.
- the second is useful to allow restarting a stopped import.
Warning : if ALL of your tables have at least one unique key, you can use these parameter. Otherways, you will end up with double entries in tables... >>  are you saying ignore=y commit=n

4) do your import operation on the server itself. You will spare the network transactions time. >> ok


0
 
sikyalaSenior Database AdministratorAuthor Commented:
also is there a way to check to see how much of the import is complete
0
 
PilouteConnect With a Mentor Commented:
A suggestion : since you have a 10g, next time use DataPump (that gives you more features). And unfortunately no, there's no way to import with DP an export that has been made with original exp, in case you'd wonder...

---

2) >> How do I do this  -> that's why I gave you the documentation link.
In a word, you add the following to your import command : imp ... RESUMABLE=Y RESUMABLE_NAME=<a string> RESUMABLE_TIME=<how many seconds>...

3) >>  are you saying ignore=y commit=n
YES. But you must be" aware of the duplicate lines issue in case you don't have primary keys (or unique keys) on tables.
Oh, I just realize default COMMIT=N. So you can forget it...

---

I really really encourage you to read the doc. That's why I gave you the link...

P


0
 
sikyalaSenior Database AdministratorAuthor Commented:
thanks
0
 
PilouteCommented:
welcome
0
All Courses

From novice to tech pro — start learning today.