You want to automatically shut down and start up both your database and your listener when the server is rebooted.

Follow the next several steps to automate your database and listener shutdown and startup:

Automating the shutdown and startup of your Oracle database will vary depending on whether you're using tools like cluster software or ASM. The solution in this recipe demonstrates the typical steps to implement the shutdown and startup of your database in the scenarios where you don't have other software that manages this task.

In step 1 from the solution, the format of the /etc/oratab file is as follows:

<SID>:<ORACLE_HOME>:Y

The Y on the end of the string signifies that the database can be started and stopped by the ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts.

In step 3 from the solution, ensure that you modify the dbora script to have values for ORA_HOME and ORA_OWNER that match your environment. Also, the following lines in the dbora file are mandatory:

# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

The previous two lines describe the service characteristics of the script. The 35 means the service will be started in runlevels 3 and 5. The 99 indicates that the service will be started near the end of the init processing. The 10 signifies that the service will be stopped near the beginning of the init processing.

In step 4, modify the group of the file so that the owner of the Oracle software (usually oracle) can view the dbora file. In step 5, make the script executable and readable.

In step 6, the chkconfig command registers the service script. This also creates the appropriate symbolic links to files beneath the /etc/rc.d directory.

Use the --list option to display whether a service is on or off for each runlevel:

# chkconfig --list | grep dbora
dbora           0:off   1:off   2:off   3:on    4:off   5:on    6:off

The previous output indicates the dbora service is on for runlevels 3 and 5. If you need to delete a service, use the --del option of chkconfig.

To test whether the dbora script is working, as root run the following to stop your database and listener:

# /etc/init.d/dbora stop

To test the startup of your database and listener, as root issue the following command:

# /etc/init.d/dbora start

If you have the opportunity to reboot your system, we recommend you do that to ensure that the database stops and restarts correctly. There are log files created in your ORACLE_HOME directory named startup.log and shutdown.log. You can inspect the contents of these to verify that the shutdown and startup are working as expected.

#   0 - halt (Do NOT set initdefault to this)
#   1 - Single user mode
#   2 - Multiuser, without NFS (The same as 3, if you do not have networking)
#   3 - Full multiuser mode
#   4 - unused
#   5 - X11
#   6 - reboot (Do NOT set initdefault to this)

id:5:initdefault:

# runlevel
N 5
# who -r
         run-level 5  Jun 17 00:29                   last=S

You want to automate the shutdown and startup of Oracle Application Server.

You can implement the appsora and oas_init.bash scripts to shut down and start up Oracle Application Server. Here's the content of the appsora script in the /etc/init.d directory:

#!/bin/sh
# File Name:  /etc/init.d/appsora
# Description: Oracle Application Server auto start-stop script.
#
# Set ORA_DB_HOME to be equivalent to the $ORACLE_HOME
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle software in ORA_DB_HOME
#
ORA_DB_HOME=/apps/oracle/product/ias10g
SH=/var/opt/oracle/sh
ORA_OWNER=oracle

export ORA_DB_HOME SH ORA_OWNER

#
case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$SH/oas_init.bash start" > /tmp/start_ias.log 2>&1
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$SH/oas_init.bash stop" > /tmp/shutdown_ias.log 2>&1
        ;;
esac

The appsora script merely calls the oas_init.bash script in the /var/opt/oracle/sh directory. The contents of the oas_init.bash script are as follows:

#!/bin/bash
# File Name: oas_init.bash
# Description:  Script called by /etc/init.d/appsora to actually startup
# or shutdown the application server.
#
export START_SHUTDOWN_FLAG=$1
# ------------------------------------------------------------------------------
# INITIAL SETUP
# ------------------------------------------------------------------------------
FN=`echo $0 | sed s/.*[/]//`
export ORACLE_BASE=/apps/oracle
export BINDIR=/var/opt/oracle/sh
#-----------------------------------------------
export FILENAME=$(basename $0 |awk -F"." {'print $1'})
export CONFIG_FILE="${SH}"/"${FILENAME}".conf
export CONTROL_FILE="${SH}"/"${FILENAME}".ctl
export FN=`print $0 | sed s/.*[/]//`
#---------------------------------------------------------------------
# Setup the Oracle Environment and pass the ORACLE_SID
#---------------------------------------------------------------------
export PATH=$PATH:/usr/local/bin
export ORAENV_ASK=NO
export ORACLE_SID=ias10g
. oraenv
[ "$START_SHUTDOWN_FLAG" = "" ] &&
{
  echo "No startup or shutdown flag specified!"
  echo "Aborting!"
  exit 1;
}
#
[ "$START_SHUTDOWN_FLAG" = "start" ] &&
{
export MODE="startall"
cd $ORACLE_HOME/opmn/bin
./opmnctl $MODE
emctl start agent
}
#
[ "$START_SHUTDOWN_FLAG" = "stop" ] &&
{

export MODE="stopall"
cd $ORACLE_HOME/opmn/bin
./opmnctl $MODE
}
#
emctl $START_SHUTDOWN_FLAG iasconsole

You can enable automatic startup and shutdown by creating symbolic links to the appsora file for start and kill actions. To create the symbolic links, execute the ln -s command as specified here from the /etc/rc.d/rc3.d directory:

ln -s ../init.d/appsora S99appsora
ln -s ../init.d/appsora K09appsora

The symbolic links associated with appsora should look like this in the /etc/rc.d/rc3.d directory:

lrwxrwxrwx  1 root root 17 Oct 26  2007 S99appsora -> ../init.d/appsora
lrwxrwxrwx  1 root root 17 Oct 26  2007 K09appsora -> ../init.d/appsora

The keyword stop or start will be passed on the command line to the symbolically linked files, depending on whether the server is booting up or shutting down. Usually the startup and kill files will link to the same file in the /etc/init.d directory. During the system startup processes, the S99appsora script will be executed as one of the last scripts. On the flip side, during the system shutdown process, K09appsora will be executed as one of the initial scripts.

OPMN is Oracle Application Server's Process Monitor and Notification (OPMN) server. Oracle provides a command-line interface called opmnctl to control and monitor Oracle Application Server components. Using the opmnctl executable, you can start all the components of Oracle's Application Server except for the Oracle Enterprise Manager (OEM) 10g Application Server Control Console. The opmnctl interface accepts two parameters, startall and stopall, to start and shut down all the OPMN-managed processes for Oracle Application Server. Since you cannot manage OEM 10g Application Server Control Console with opmnctl, the oas_init.bash startup script invokes the emctl executable to start and shut down the OEM's iAS Console.

As the oracle operating system user, you're attempting to add an entry to the cron table via the crontab utility, and you receive the following error message:

You (oracle) are not allowed to use this program (crontab)

You want to grant access to the oracle user to use the crontab utility.

As the root user, add oracle to the /etc/cron.allow file with the echo command:

# echo oracle >> /etc/cron.allow

Once the oracle entry is added to the /etc/cron.allow file, you can use the crontab utility to schedule a job.

The root user can always schedule jobs with the crontab utility. Other users must be listed in the /etc/cron.allow file. If the /etc/cron.allow file does not exist, then the operating system user must not appear in the /etc/cron.deny file. If neither the /etc/cron.allow nor the /etc/cron.deny file exists, then only the root user can access the crontab utility.

The cron program is a job-scheduling utility that is ubiquitous in Linux/Unix environments. This tool derives its name from chronos (the Greek word for time). The cron (the geek word for scheduler) tool allows you to schedule scripts or commands to run at a specified time and repeat at a designated frequency.

When your Linux server boots up, a cron background process is automatically started that manages all cron jobs on the system. The cron background process is also known as the cron daemon. This process is started on system startup by the /etc/init.d/crond script. You can check to see whether the cron daemon process is running with the ps command:

$ ps -ef | grep crond | grep -v grep
root      3049     1  0 Aug02 ?        00:00:00 crond

You can also check to see whether the cron daemon is running using the service command:

$ /sbin/service crond status
crond (pid 3049) is running...

The root user uses several files and directories when executing system cron jobs. The /etc/crontab file contains commands to run system cron jobs. Here is a typical listing of the contents of the /etc/crontab file:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly

This /etc/crontab file uses the run-parts utility to run scripts located in the following directories: /etc/cron.hourly, /etc/cron.daily, /etc/cron.weekly, and /etc/cron.monthly. If there is a system utility that needs to run other than on an hourly, daily, weekly, or monthly basis, then it can be placed in the /etc/cron.d directory.

Each user can create a crontab (also known as a cron table) file. This file contains the list of programs that you want to run at a specific time and interval (see recipe 11-4 for details). This file is usually located in the /var/spool/cron directory. For every user who creates a cron table, there will be a file in the /var/spool/cron directory named after the user. As root, you can list the files in that directory:

# ls /var/spool/cron
oracle  root

The cron background process is mostly idle. It wakes up once every minute and checks /etc/crontab, /etc/cron.d, and the user cron table files and determines whether there are any jobs that need to be executed.

Table 11-1 summarizes the purpose of the various files and directories used by cron. Knowledge of these files and directories will help you troubleshoot any issues as well as understand cron in more detail.

You want to have a backup script run automatically at 11:05 p.m. every night.

This solution details how to schedule a job using the cron utility. To schedule a job, you must add a line in your cron table specifying the time you want the job to execute. There are two methods for adding entries in your cron table:

These two techniques are described in the following sections.

$ crontab -e

5 23 * * * /home/oracle/bin/backup.bsh

$ crontab -l

$ crontab -r

$ crontab -l > saved.cron

$ crontab <filename>

5 23 * * * /home/oracle/bin/backup.bsh

The default editor invoked to modify the cron table is dependent on the value of your VISUAL operating system variable. In our environment, the VISUAL variable is set to vi:

$ echo $VISUAL
vi

If the VISUAL operating system variable isn't set, then the value of EDITOR is used to define the default editor. Make sure that either VISUAL or EDITOR is set to your editor of choice. If neither VISUAL nor EDITOR is set, your system will default to the ed editor. In this scenario, you'll be presented with the following prompt:

26
<blank prompt>

Press the Q key to exit from ed. You can have the VISUAL or EDITOR variable automatically set for you when you log on to the system (see recipe 2-5 for additional details). You can also manually set the editor with the export command. The following sets the default editor to vi:

$ export EDITOR=vi

# min(0-59) hr(0-23) dayMonth(1-31) monthYear(1-12) dayWeek(0/7-6) commandOrScript

0,30 8-16 * * 1-5 echo "wake up" | mailx -s "wake up" [email protected]

0,30 8-16 * Jan-Dec Mon-Fri echo "wake up" | mailx -s "get up" [email protected]

sqlplus: command not found

11 12 * * * /home/oracle/bin/backup.bsh 1>/home/oracle/bin/log/bck.log 2>&1

MAILTO=root
11 12 * * * /home/oracle/bin/backup.bsh

11 12 * * * /home/oracle/bin/backup.bsh 1>/dev/null 2>&1

You want to automate the distribution of Oracle performance reports such as the Automatic Workload Repository (AWR) report. You're thinking about using cron to do this task.

On your Linux database server, navigate to the ORACLE_HOME/rdbms/admin directory, and then follow these steps:

The previous cron job runs the custom script daily at 6 am. The standard output and standard error are written to a file named /orahome/scripts/log/awr.log.

When using this script, ensure that you modify the shell script to reflect databases used in your environment; the operating system variables such as ORACLE_HOME, ORACLE_SID, and PATH; and the password for your DBA schema.

The "Solution" section of this recipe combines the power of SQL, shell scripts, cron, and mail to automate a task such as sending daily performance reports. You should be able to extend these methods to automate any type of DBA task.

With any type of automation task, first you need to create a script that encapsulates the job that will be automated. Once you're confident that the script is ready, then schedule the job to run from cron. We recommend you send the output to a log file. This allows you to check to make sure job is running as intended.

You want to monitor whether the RMAN database backups have been running by checking the last backup date in V$RMAN_BACKUP_JOB_DETAILS.

First create a shell script that has the desired logic within it. We have added line numbers to the following shell script for discussion purposes. You'll need to take the line numbers out before you attempt to run the script. This script checks to see whether the RMAN backups have run within the last 24 hours:

1  #!/bin/bash
2  #
3  if [ $# -ne 2 ]; then
4    echo "Usage: $0 SID threshold"
5    exit 1
6  fi
7 # source oracle OS variables; see recipe 7-7 for an example of oraset script
8  . /var/opt/oracle/oraset $1
9  crit_var=$(sqlplus -s <<EOF
10  system/foo
11  SET HEAD OFF FEEDBACK OFF
12  SELECT COUNT(*) FROM
13  (SELECT (sysdate - MAX(end_time)) delta
14   FROM v$rman_backup_job_details WHERE status='COMPLETED') a

15  WHERE a.delta > $2;
16  EXIT;
17  EOF)
18  if [ $crit_var -ne 0 ]; then
19    echo "rman backup problem with $1" | mailx -s "rman problem" [email protected]
20  else
21    echo "rman ok"
22  fi
23  exit 0

Now schedule the job via cron to run once a day. Ideally this job would run soon after your RMAN backups are finished. In this example, the previous code is contained within a script named rman_chk.bsh. This script is run once a day from cron at 5:25 a.m.; two parameters are passed to the script: DWREP (the database name) and 1 (for one day):

#---------------------------------------------------
# RMAN check
25 5 * * * /orahome/bin/rman_chk.bsh DWREP 1 1>/orahome/bin/log/rman_chk.log 2>&1
#---------------------------------------------------

In the previous cron entry, the rman_chk.bsh script is located in the /orahome/bin directory, and the standard output and standard error are written to the /orahome/bin/log/rman_chk.log file.

The script in the "Solution" section of this recipe is very simplistic and checks to see only whether there has been one valid RMAN backup created within the last day. You can extend this script to meet the requirements of your environment. We kept the script as simple as possible to illustrate this point. This type of monitoring technique works well when you have a date-related column that is updated in the data dictionary when an event occurs. When using Oracle tools such as RMAN or materialized views, you can easily check the appropriate data dictionary view date column to validate whether the given event has occurred.

Line 1 sets the shell interpreter for the script. Lines 3 through 6 check to see whether two parameters have been passed to the script. In this case, the first parameter should be the database name. The second parameter is a threshold number. Passing in 1 for this parameter will instruct the script to check to see whether backups have occurred in the last day. If you passed in 2, then it would check to see whether a backup had occurred in the last two days.

Line 8 runs a script to source the operating system variables such as ORACLE_SID, ORACLE_HOME, and PATH. These variables need to be set to the appropriate values before you can access Oracle utilities like SQL*Plus. It is possible to hard-code those variables directly in the shell script like this (change these to reflect values in your environment):

ORACLE_SID=DWREP
ORACLE_HOME=/oracle/product/11.0.1
PATH=$ORACLE_HOME/bin:$PATH

However, we recommend you not hard-code those inside the script and instead source those variables through a called script (see recipe 7-7 for details). In the shell script in the "Solution" section of this recipe, we call a script named oraset that sets the previously listed variables. This script uses the oratab file to determine the location of ORACLE_HOME. This approach is more flexible and portable (than hard-coding values).

On line 10, you'll have to change the schema name and password in the script that works with a DBA account in your environment.

Lines 12 through 15 query the data dictionary to see whether a backup has occurred in the last n number of days, where n is the value of the second variable passed to the script.

Look at line 14. There's a reference to the following data dictionary view:

v$rman_backup_job_details

Why is there a backslash () in front of the dollar sign ($)? If the data dictionary view you are referencing contains $, it must be escaped with a backslash because the dollar sign is a special Linux character that signifies a variable. The backslash placed before the dollar sign tells the Bash script to ignore the special meaning of the dollar sign variable. If you didn't do this, then the Bash shell would interpret the line to mean the value of v concatenated with the contents of a variable named rman_back_job_details.

If you set your NLS_DATE_FORMAT in the ORACLE_HOME/sqlplus/admin/glogin.sql script, this will cause the script to fail because the message "Session altered." gets displayed when logging into SQL*Plus and then subsequently is embedded into the crit_var variable. To work around this, set feedback off in glogin.sql, as shown here:

set feedback off;
alter session set nls_date_format='dd-mon-rr hh24:mi:ss';
set feedback on;

You want to have a job automatically check and e-mail you if a tablespace is getting full.

Create a script similar to the one shown in this solution. Line numbers have been added for explanation purposes, so you'll have to take the line numbers out before you attempt to run this script:

1  #!/bin/bash
2  #
3  if [ $# -ne 2 ]; then
4   echo "Usage: $0 SID threshold"
5    exit 1

6  fi
7  # source oracle OS variables; see recipe 7-7 for an example of oraset script
8  . /var/opt/oracle/oraset $1
9  #
10  crit_var=$(
11  sqlplus -s <<EOF
12  system/foo
13  SET HEAD OFF TERM OFF FEED OFF VERIFY OFF
14  COL pct_free FORMAT 999
15  SELECT (f.bytes/a.bytes)*100 pct_free,'% free',a.tablespace_name||','
16  FROM
17  (SELECT NVL(SUM(bytes),0) bytes, x.tablespace_name
18   FROM dba_free_space y, dba_tablespaces x
19   WHERE x.tablespace_name = y.tablespace_name(+)
20   AND x.contents != 'TEMPORARY' AND x.status != 'READ ONLY'
21   GROUP BY x.tablespace_name) f,
22  (SELECT SUM(bytes) bytes, tablespace_name
23   FROM dba_data_files
24   GROUP BY tablespace_name) a
25  WHERE a.tablespace_name = f.tablespace_name
26  AND  (f.bytes/a.bytes)*100 <= $2
27  ORDER BY 1;
28  EXIT;
29  EOF)
30  if [ "$crit_var" = "" ]; then
31    echo "space okay"
32  else
33    echo "space not okay"
34    echo $crit_var
35    echo $crit_var | mailx -s "tbsp getting full on $1" [email protected]
36  fi
37  exit 0

In this example, the code is contained in a script named tbsp_chk.bsh. Here we cron the job to run every hour (at nine minutes after the hour) of every day:

#---------------------------------------------------
# Tbsp check
9 * * * * /orahome/bin/tbsp_chk.bsh DWREP 30 1>/orahome/bin/log/tbsp_chk.log 2>&1
#---------------------------------------------------

In the previous cron entry, the tbsp_chk.bsh script is located in the /orahome/bin directory, and the standard output and standard error are and standard error are written to the /orahome/bin/log/tbsp_chk.log file.

The script in the "Solution" section contains the underpinnings for most of what you would need to check to see whether a tablespace is filling up. You can take that code and extend it for any additional requirements in your environment.

Lines 3 through 6 check to ensure that two parameters are passed to the script. In this case, the script needs an Oracle SID name and a threshold limit. The threshold limit is the amount below which a tablespace percent of free space is considered to be low. For example, if you passed the script 15, then any tablespace that had less than 15 percent free space would be considered to be low on space.

Line 8 runs a script to source the operating system variables such as ORACLE_SID, ORACLE_HOME, and PATH. These variables need to be set to appropriate values before you can access Oracle utilities such as SQL*Plus. It is possible to hard-code those variables directly in the shell script like this (change these to reflect values in your environment):

ORACLE_SID=DWREP
ORACLE_HOME=/oracle/product/11.0.1
PATH=$ORACLE_HOME/bin:$PATH

However, we recommend you not hard-code those inside the script and instead source those variables through a called script (see recipe 7-7 for details). In the shell script in the "Solution" section of this recipe, we call a script named oraset that sets the previously listed variables. This script uses the oratab file to determine the location of ORACLE_HOME. This approach is more flexible and portable (than hard-coding the values).

Lines 11 through 29 run a SQL*Plus script that queries the data dictionary and determines the percentage of free space in each tablespace. You'll have to modify this script on line 12 to contain a valid username and password for your environment.

Lines 17 through 21 constitute an inline view that determines the amount of free space in a tablespace. The DBA_TABLESPACES is outer-joined to DBA_FREE_SPACE because DBA_FREE_SPACE will not return a row when there is no free space left in a datafile. On line 20 we exclude tablespaces that have a content type of TEMPORARY and a status of READ ONLY.

Lines 22 through 24 determine the amount of space allocated to a tablespace. Line 26 checks to see whether the amount of free space divided by the allocated bytes is less than the threshold value passed into the script.

On line 30, if there is no value returned from the query into the variable crit_var, then none of the tablespaces is less than the designated percentage free, and therefore there is no reason to send an e-mail alert. On line 35, an e-mail is sent if there is a value returned to the crit_var variable.

Here we run the script manually and pass an Oracle SID of DWREP and a threshold value of 30:

$ tbsp_chk.bsh DWREP 30

Listed next is a typical line denoting the contents of crit_var when several tablespaces are less than a 30 percent free threshold value:

space not okay
6 % free UNDOTBS1, 19 % free SYSAUX, 28 % free MV_DATA,

The output indicates that the UNDOTBS1 tablespace has only 6 percent of free space, the SYSAUX tablespace has 19 percent free space, and the MV_DATA tablespace has 28 percent of space free in its allocated datafiles.

You want to automate a job such as compressing and moving the alert.log file and deleting old compressed files.

Create a script similar to the one shown in this solution. Line numbers have been added for explanation purposes, so you'll have to take the line numbers out before you attempt to run this script:

1 #!/bin/bash
2 DAY=$(date '+%m.%d:%H:%M:%S')
3 SID=RMDB1
4 RMCMP='+14'
5 #----------------------
6 # source oracle OS variables; see recipe 7-7 for an example of the oraset script
7 . /var/opt/oracle/oraset $SID
8 #
9 TARGDIR=$(sqlplus -s <<EOF
10 system/foo
11 SET HEADING OFF FEEDBACK OFF
12 SELECT value FROM v$parameter WHERE name='background_dump_dest';
13 EXIT;
14 EOF)
15 #
16 # Move and compress the alert.log file
17 if [ -f $TARGDIR/alert_${SID}.log ]; then
18   mv $TARGDIR/alert_${SID}.log $TARGDIR/${DAY}_alert_${SID}.log
19   gzip -f $TARGDIR/${DAY}_alert_${SID}.log
20 fi
21 # Remove old compressed files
22 find $TARGDIR -name "*.gz" -type f -mtime $RMCMP -exec rm -f {} ;
23 #
24 exit 0

In this example, the code is contained in a script named rm_old.bsh. Here we cron the job to run every day at 3:35 a.m.:

#---------------------------------------------------
# Tbsp check
35 3 * * * /orahome/bin/rm_old.bsh 1>/orahome/bin/log/rm_old.log 2>&1
#---------------------------------------------------

In the previous cron entry, the rm_old.bsh script is located in the /orahome/bin directory, and the standard output and standard error are written to the /orahome/bin/log/rm_old.log file.

The script in the "Solution" section is a typical job that will find files, compress them, and remove files older than a certain age. DBAs use scripts like this to manage files on the operating system. You can take the code and modify it so that it works in your environment.

Line 1 specifies that the script runs in the Bash shell. The second line populates the DAY variable in a date format that will be used to create a uniquely named log file for a day. Line 3 contains the name of the target database name. Line 4 contains the variable that determines the number of days after which a file will be deleted.

Line 7 sources the oracle operating system variables. See recipe 7-7 for details on using a file like oraset to source variables such as ORACLE_SID, ORACLE_HOME, and so on.

Lines 9 through 14 query the data dictionary for the location of the background dump destination. On line 10 you'll need to change the script to contain a valid username and password for your environment.

Lines 17 through 20 check for the existence of an alert.log file, and if one is found, then it is renamed and compressed. Line 22 finds and removes any old compressed files that are more than 14 days old (defined by the RMCMP variable on line 4). Line 24 exits the shell script.

You want to rename, compress, save, and/or remove the alert.log file before it grows to an unmanageable size.

Use the logrotate utility to perform tasks such as renaming, compressing, and removing old log files. Here are the steps for setting up a job to rotate the alert.log file of an Oracle database:

As shown in the previous steps, you can use the logrotate utility to easily set up log rotation jobs.

The process of copying, renaming, compressing, and deleting log files is colloquially known as log rotation. In Linux, it's fairly easy to enable log rotation via the logrotate utility. The logrotate utility is sometimes perceived as a utility only for system administrators. However, any user on the system can utilize logrotate to rotate log files for applications that they have read/write permissions on the specified log files.

You can also use the logrotate utility to manage log files such as the listener.log file. This file has some special considerations. For example, if you attempt to rename the listener.log file, the listener process will continue to write to the renamed file. If you remove the file, then a new one won't be written to until you stop and start the listener process. In this scenario, use the copytruncate option of logrotate to make a copy of the file, and then truncate it (see Table 11-2 for descriptions of commonly used logrotate options). In this situation, use a configuration file similar to this:

/oracle/product/10.2/network/log/listener.log {
weekly
copytruncate
rotate 7
mail oracle@localhost
}

In this example, the previous lines of code are placed in a file named listener.conf. Here we schedule the job to run in cron once a week on Sunday at 5:20 a.m.:

20 5 * * 0 /usr/sbin/logrotate -s /home/oracle/config/alrotate.status
/home/oracle/config/listener.conf

The previous two lines of code should be one line of code in your cron table (this amount of output did not fit nicely on one line on this page).

You want to run an RMAN backup. You want to use the Oracle DBMS_SCHEDULER PL/SQL package to do this.

First create a shell script that performs an RMAN backup. This example creates a file named rmanback.bsh and places it in the /home/oracle/bin directory:

#!/bin/bash
# source oracle OS variables; see recipe 7-7 for an example of the oraset script
. /var/opt/oracle/oraset  RMDB1
rman target / <<EOF
spool log to '/home/oracle/bin/log/rmanback.log'
backup database;
spool log off;
EOF
exit 0

Next create a DBMS_SCHEDULER job. Run the following as SYS (from the SQL*Plus):

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'RMAN_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/bin/rmanback.bsh',
repeat_interval => 'FREQ=DAILY;BYHOUR=18;BYMINUTE=45',
start_date => to_date('16-SEP-08'),
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => TRUE);
END;
/

The previous example creates a daily RMAN backup job that runs at 6:45 p.m. Before you use the previous script, you'll need to modify the start date and schedule parameters to meet the requirements of your environment. If you want to view details about the job, you can query the DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_RUN_DETAILS views:

SELECT
  job_name
 ,status
 ,error#
 ,actual_start_date
FROM dba_scheduler_job_run_details
WHERE job_name='RMAN_BACKUP';

SELECT
  job_name
 ,last_start_date
 ,last_run_duration
 ,next_run_date
FROM dba_scheduler_jobs
WHERE job_name='RMAN_BACKUP';

If you want to delete a job, use the DROP_JOB procedure:

BEGIN
dbms_scheduler.drop_job(job_name=>'RMAN_BACKUP'),
END;
/

It's beyond the scope of this chapter or book to describe all the characteristics of DBMS_SCHEDULER. The example in the "Solution" section of this recipe barely scratches the surface of the wide variety of features available with this package.

DBAs often debate whether they should use DBMS_SCHEDULER or cron for scheduling and automating tasks. Listed next are some of the benefits that DBMS_SCHEDULER has over cron:

Listed next are some of the advantages of using cron:

These bulleted lists aren't comprehensive, but they should give you a flavor of the uses of each tool. See Chapters 26, 27, and 28 of the Oracle Database Administrator's Guide 11g and Oracle Database PL/SQL Packages Reference documentation for details on how to use DBMS_SCHEDULER. This documentation is available on Oracle's website (http://otn.oracle.com).

See recipe 11-4 for details on how to use cron.