000028940 - KB-1263 - Unable to restore database export ORA-31637

Document created by RSA Customer Support Employee on Jun 15, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000028940
Applies ToAffected Versions: All Versions
IssueIf you abort an in progress database import (restoring a backup) with CTRL-C then attempt to restart the job and
receive the error:
Connected to: Oracle Database 10g Release 10.2.0.2.0 - Production

ORA-31634: job already exists

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

ORA-06512: at "SYS.KUPV$FT", line 859

ORA-31637: cannot create job AVDB_IMPORT_AVUSER for user AVUSER
ResolutionThere are several ways around this


Method #1:
As the oracle user try connecting with the impdp command (the underlying datapump command that the Aveksa import uses) and stopping the job:
impdp avuser/secret attach=AVDB_IMPORT_AVUSER

impdp> kill_job
This should stop the job and remove the database table AVUSER.AVDB_IMPORT_AVUSER
from the database. If there are no errors you can then retry the import, if there are errors from impdp or the import fails please try method #2.


Method #2:
As the oracle user start sqlplus and conect as sysdba, then try removing the table created by the import job
sqlplus /nolog

connect / as sysdba



SQL> drop table AVUSER.AVDB_IMPORT_AVUSER;
If the table drop succeeds then restart the import, if it still fails try the third option.


Method #3:
A. run the following sql as sysdba (place this in a file and run using "@<filename>"


SET lines 200

COL owner_name FORMAT a10;

COL job_name FORMAT a20

COL state FORMAT a11

COL operation LIKE state

COL job_mode LIKE state



-- locate Data Pump jobs:



SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;
-- end of sql
The output might look something like this:
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ----------- ----------- ----------- -----------------
AVUSER AVDB_IMPORT_AVUSER IMPORT EXECUTING 1
There are two things needed to perform the kill:
1. OWNER_NAME (Which is AVUSER)
2. JOB_NAME (Which is AVDB_IMPORT_AVUSER)
B. With the information from step A, we can now attempt to stop and kill the job using this
sql - run from a file as sysdba
-- start sql

SET serveroutput on

SET lines 100

DECLARE

h1 NUMBER;

BEGIN

-- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');

h1 := DBMS_DATAPUMP.ATTACH('AVDB_IMPORT_AVUSER','AVUSER');

DBMS_DATAPUMP.STOP_JOB (h1,1,0);

END;

/
C Stop and restart the database, you may need to do a "shutdown abort" through sqlplus
on both AVDB and ASM instances or just reboot.
D. Check that the job has stopped (run the sql from step A): this is the output below


SQL> SET lines 200

SQL> COL owner_name FORMAT a10;

SQL> COL job_name FORMAT a20

SQL> COL state FORMAT a11

SQL> COL operation LIKE state

SQL> COL job_mode LIKE state

SQL>

SQL> -- locate Data Pump jobs:

SQL>

SQL> SELECT owner_name, job_name, operation, job_mode,

2 state, attached_sessions

3 FROM dba_datapump_jobs

4 WHERE job_name NOT LIKE 'BIN$%'

5 ORDER BY 1,2;
no rows selected
E. If you see "no rows selected" you can safely restart the import.
If none of the above methods work, please contact Aveksa Support. A final option is to reload the base schema, then restore the backup. 

Attachments

    Outcomes