Sunday, July 17, 2011

Resolving ORA-31626,ORA-31633 while running Datapump job

Resolving ORA-31626,ORA-31633 while running Datapump job:
-------------------------------------------------------------
When I check my expdp logs(Datapump backup logs) and backup for one of my development Database I found both the logs and backup for the previous day missing,than I checked the cron job log and found the job actually ran the previous day.After some thinking I decided to run the expdp job(Datapump job) once again manually with the help of one my favourite command 'nohup' as follows:
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&

In the a.out(log file for nohup job used for running expdp job) I found the below error:

ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.FULL_EXPDP_DEVDB"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object

After some research,I found the cause of this error as given below:

Cause: This error occured because Job creation failed because a Master Table(A table created when a Datapump job is started and dropped when a Datapump job is completed) and its indexes could not be created,most commonly due to the pre-existance of a table with the same name (job name) in the user schema

Solution: I decided to Change the JOB_NAME in the expdp script and run the expdp script once again with the help of the 'nohup' command again as given below:

$vi expdp_FULL_DB_DEVDB.sh
JOB_NAME=EXPD_JOB_NEW_NAME
:wq

$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&

This time the 'nohup' command ran successfully,I checked the 'expdp' job it started successfully and the log file also looks fine.

$ps -eaf|grep expdp
=>Datapump(expdp) job found running

$tail -f expdp-18072011_095042.log
=>show running log file and I've verified here that expdp job is running fine and completed successfully.

After some time expdp job completed successfully,My only concern is next time this issue should not occur for this I'm not getting any clue if you have any please reply to this post.

Hope it helps...


Best regards,

Rafi.

4 comments:

  1. Hello Rafi thanks for your post i got the same error while doing export then i found your post and researched more about it and now i came to know the exact reason why i got these error and i resolved them , have a look here:
    http://www.chandu208.blogspot.com/2012/02/ora-31626-ora-31633-while-running.html

    ReplyDelete
  2. Hi Chandu,
    Useful information.Keep up the good work :)


    Best regards,

    Rafi.

    ReplyDelete