on Jun 15, 2016Article Content
| Article Number | 000028940 |
| 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 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. |