Deleting a Job- Automation and Troubleshooting

If you no longer require a job, you should delete it from the scheduler. Use the DOP_JOB procedure to permanently remove a job.

This example removes the RMAN_BACKUP job:

SQL> begin dbms_scheduler.drop_job(job_name=>’rman_backup’); end; /

The code will drop the job and remove any information regarding the dropped job from the DBA_SCHEDULER_JOBS view.

Examples of Automated DBA Jobs

In today’s often chaotic business environment, it is almost mandatory to automate jobs. If you do not automate, you may forget to do a task; or, if performing a job manually, you may introduce error into the procedure.

If you do not automate, you could find yourself replaced by a more efficient or cheaper set of DBAs.

Even if you look at Oracle’s Autonomous, it is being automated to take care of necessary tasks, patching, scaling, and tuning.

This definitely shows the need to automate in order to manage environments.

When a script fails, it makes sense to receive an email, but too many successful job emails can cause noise and miss a failure.

However, not receiving an email in success or failure is really a failure since it is in a state of uncertainty.

A way to report on scripts that run for databases is a centralized report that shows success or failure from the consolidated logs or queries from an output table.

Reviewing a daily email or having a dashboard will validate that all jobs are running properly.

DBAs automate a wide variety of tasks and jobs. Almost any type of environment requires that you create some sort of OS script that encapsulates a combination of OS commands, SQL statements, and PL/SQL blocks.

Besides scripts, there are now configurations in the database that will do some of the checks or process restarts.

The following scripts in this chapter are a sample of the wide variety of different types of tasks that DBAs automate.

This set of scripts is, by no means, complete. Many of these scripts may not be needed in your environment.

The point is to give you a good sampling of the types of jobs automated and the techniques used to accomplish a given task.

Tip use catcon.pl, an oracle-provided perl script, to run sQl statements on all pdbs in a Cdb. catcon.pl is found in $ORACLE_HOME/rdbms/admin, and the usage details are in mos note 1932340.1.

From the os prompt from the $ORACLE_HOME/rdbms/admin directory, run this:

catcon: $ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oradata -l / home/oracle/script_logs -b script_name script_name.sql