|Applies To||Affected Versions: All Versions|
|Issue||If 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
|Resolution||There are several ways around this|
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
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.
As the oracle user start sqlplus and conect as sysdba, then try removing the table created by the import job
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.
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,
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
-- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
h1 := DBMS_DATAPUMP.ATTACH('AVDB_IMPORT_AVUSER','AVUSER');
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> -- locate Data Pump jobs:
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.