You Asked
Hi,
I am trying to run classic dump import by calling imp from a dbms_scheduler job, in Oracle 11.2 on my Windows 10 machine. The import itself is completed successfully, but unfortunately the job is failed (The system cannot find the path specified), according to the user_scheduler_job_run_details.
Here is a sample code:
conn system/password
exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'sorin',JOB_TYPE => 'EXECUTABLE',JOB_ACTION =>'imp', NUMBER_OF_ARGUMENTS => 4);
exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 1, 'sorin/test');
exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 2, 'file="c:\1\dump.dmp"');
exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 3, 'full=Y');
exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 4, 'log="c:\1\sorin.log"');
exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'sorin', USE_CURRENT_SESSION => TRUE);
after a while returns:
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: The system cannot find
the path specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 196
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
But the import is made, and the sorin.log is created under c:\1 with a normal import log, ending with "Import terminated successfully with warnings.
". And status=FAILED in table user_scheduler_job_run_details for this job run.
I have tried various things like:
- creating a credential and passing it to the CREATE_JOB procedure
- JOB_ACTION => '?/BIN/imp', as well as giving explicitly the full path to imp.exe
- JOB_ACTION => 'c:\Windows\System32\cmd.exe' and passing as argument a .bat file which sets the current directory and then calls imp
But all with the same result. I haven't tried creating a program with dbms_Scheduler.create_program, but I don't think that this should be necessary. Please let me know if I should try that.
While it is good that the script basically does its job, it is somehow confusing and misleading that the job finishes as failed. Do you have any idea why that happens and how I should modify my code so that the dbms_scheduler job is not failed in such a situation: import was after all run and finished, so it could find the imp executable, the dump file and it created allright the import log file.
Thanks in advance for your help.
and Connor said...
Couple of things
- use a fully qualified path
- make sure the OracleJobSchedulerSID service is running.
Example on my machine
-- the service above is stopped
SQL> exec DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'sorin');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'sorin',JOB_TYPE => 'EXECUTABLE',JOB_ACTION =>'c:\oracle\product\12.1.0.2\bin\exp.exe', NUMBER_OF_ARGUMENTS => 4);
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 1, 'userid=mcdonac/xxxxxx@np12');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 2, 'file="c:\temp\dump.dmp"');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 3, 'owner=scott');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 4, 'log="c:\temp\sorin.log"');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'sorin', USE_CURRENT_SESSION => TRUE);
BEGIN DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'sorin', USE_CURRENT_SESSION => TRUE); END;
*
ERROR at line 1:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 5
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 1
Then I enabled and started the service
SQL> exec DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'sorin');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'sorin',JOB_TYPE => 'EXECUTABLE',JOB_ACTION =>'c:\oracle\product\12.1.0.2\bin\exp.exe', NUMBER_OF_ARGUMENTS => 4);
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 1, 'userid=mcdonac/******@np12');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 2, 'file="c:\temp\dump.dmp"');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 3, 'owner=scott');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('sorin', 4, 'log="c:\temp\sorin.log"');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME => 'sorin', USE_CURRENT_SESSION => TRUE);
PL/SQL procedure successfully completed.
SQL> col job_name format a30
SQL> select job_name, status from user_scheduler_job_run_details order by log_date;
JOB_NAME STATUS
------------------------------ ------------------------------
SORIN FAILED
SORIN SUCCEEDED
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment