Solved

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

Posted on 2011-03-18
9
402 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sikyala
  • 4
  • 4
9 Comments
 
LVL 2

Expert Comment

by:choukssa
ID: 35166011
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
 

Author Comment

by:sikyala
ID: 35166056
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
 
LVL 7

Accepted Solution

by:
Piloute earned 500 total points
ID: 35166229
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
 
LVL 7

Expert Comment

by:Piloute
ID: 35166261
@choukksa

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

Rgds,
P
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sikyala
ID: 35166401
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
 

Author Comment

by:sikyala
ID: 35166724
also is there a way to check to see how much of the import is complete
0
 
LVL 7

Assisted Solution

by:Piloute
Piloute earned 500 total points
ID: 35167145
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
 

Author Closing Comment

by:sikyala
ID: 35207913
thanks
0
 
LVL 7

Expert Comment

by:Piloute
ID: 35213086
welcome
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wrap Oraccle SQL*Plus executable Command 4 70
Extract the first word (before the , ) 2 40
oracle 11g 23 51
Showing a username in UNIX and using that name in the script 4 17
Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now