Question: Today, I receive a set of Database JOB exception alerts on the monitoring system. Here I will record the solution process and share it with you!
Alert Log:
Tue Dec 13 04:00:03 2011
Errors in file/opt/Oracle/diag/rdbms/ndmcb/trace/ndmcb_j000_11169.trc:
ORA-12012: error on auto execute of job 30
ORA-29283: invalid file operation
ORA-06512: at "SYS. UTL_FILE", line 41
ORA-06512: at "SYS. UTL_FILE", line 512
ORA-29283: invalid file operation
ORA-06512: at "NDMC. PROC_TODO_TASK", line 74
ORA-06512: at line 1
Query in the database:
Job Schema User Last Date Next Date Total Time B Interval Failures What
-----------------------------------------------------------------------------------------------------------------------------------------------
30 NDMC01-Jan-4000 00:00:00 0 YTRUNC (sysdate + 1) + 4/24 16 PROC_TODO_TASK;
Now this Job is unavailable.
Find the stored procedure using PL/SQL Developer as prompted in the log:
Locate 74 rows:
Here we want to open a directory. Where is the PATH defined?
View the database:
SQL> col DIRECTORY_PATH for a50
SQL> col OWNER for a20
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----------------------------------------------------------------------------------------------------
SYS NDMIGEXPDIR2/home/oracle/ndmig_expdpdata
Sys ndmigexpdir/home/oracle/archive/ndmig/expdpdata
SYS BACK_MSG_LOGS_PATH/home/oracle/msgBackup/work
SYS UNSUB_DATA_PATH/home/oracle/backup/unsub_data
Sys sharelog/home/oracle/share/
Sys diskpkgfilecatalog/home/oracle/backup/pkg_incon_data/diskpkgfile
Sys logcatalog/home/oracle/backup/pkg_incon_data
SYS DATA_PUMP_DIR/opt/oracle/product/11g/db/rdbms/log/
SYS ORACLE_OCM_CONFIG_DIR/opt/oracle/product/11g/db/Cr/state
Check whether the directory exists in the system or whether the directory permission is correct:
Oracle @ ndmcb05: ~> Cd/home/oracle/backup/unsub_data
-Bash: cd:/home/oracle/backup/unsub_data: No such file or directory
If this directory does not exist, create it manually:
Oracle @ ndmcb05: ~> Mkdir-p/home/oracle/backup/unsub_data
Then, modify the job attributes correctly:
Solve the problem!