Database Reference
In-Depth Information
AND jl.log_id=jd.log_id
ORDER BY jl.log_id;
LOG_ID STATUS LOG_ADDTL_INFO STATUS DETAILS_ADDTL_INFO
------ ------ --------------------- ------ ----------------------------------
245 FAILED FAILED ORA-27369: job of type EXECUTABLE
failed with exit code: Incorrect
function.
STANDARD_ERROR="About to exit with
exit code 1. "
246 FAILED REASON="manually run" FAILED ORA-27369: job of type EXECUTABLE
failed with exit code: Incorrect
function.
STANDARD_ERROR="About to exit with
exit code 1. "
The results of the test presented previously, as well as additional tests are summarized below:
The column
ADDITIONAL_INFO
of the view
DBA_SCHEDULER_JOB_RUN_DETAILS
captures stan-
dard error output for external jobs irrespective of their exit codes. This column is a
CLOB
,
such that it could theoretically capture up to 8 TB or 128 TB depending on the setting of
the parameter
DB_BLOCK_SIZE
(see
Database Reference 10g Release 2
, page A1).
There is a size limitation of 200 bytes on
DBA_SCHEDULER_JOB_RUN_DETAILS.ADDITIONAL_INFO
.
Standard output is never captured in
DBA_SCHEDULER_JOB_RUN_DETAILS.ADDITIONAL_INFO
.
The column
ADDITIONAL_INFO
of the view
DBA_SCHEDULER_JOB_LOG
is
NULL
for jobs that are
run based on a schedule and has the value REASON="manually run" for jobs that were
run manually by a call to the procedure
DBMS_SCHEDULER.RUN_JOB
.
To drop the job named “FAILURE_TEST”, execute the call to
DBMS_SCHEDULER.DROP_JOB
reproduced here:
SQL> EXEC dbms_scheduler.drop_job('failure_test')
External Jobs on UNIX
Wouldn't it be nice to teach a database how to back itself up by running RMAN as an external
job? The unsuspecting DBA might think that all that needs to be done is to run RMAN through
the scheduler with exactly the same command line options as from a shell. Actual tests, however,
reveal that matters are more intricate. Environment variables and argument passing are two of
the issues that pose undocumented obstacles and must be overcome.
In the source code depot, I provide a prototype of a job that controls RMAN through the
pipe interface discussed in Chapter 36 (file
rman_backup.sql
). It creates an external job, which
runs RMAN through the scheduler, and a job of type PLSQL_BLOCK. This latter job starts RMAN by
manually executing the aforementioned external job and passes commands to RMAN through
the pipe interface. Thus, the database has been taught to back itself up. There is no longer any
need for scheduling backups outside of the DBMS instance. In a high availability environment,
guarding the DBMS instance against failure automatically protects the backup job too.
