In almost every type of Linux environment—from development to production—database administrators rely heavily on automating tasks such as database backups, monitoring, and maintenance. For highly available systems, it's often an absolute requirement to automate tasks such as the startup and shutdown of the Oracle database and listener. Automating routine tasks allows DBAs to be much more effective and productive. Automated environments inherently run smoother and more efficiently than manually administered systems.
This chapter starts by showing you how to automate the startup/shutdown of your database in the event of a system reboot. We next focus on showing you how to leverage the cron
job scheduler. The cron
utility is a scheduling tool universally available on most Linux/Unix systems. It's readily available, simple, and easy to use. For these reasons, this ubiquitous utility is frequently used by DBAs to automate database jobs.
This chapter builds heavily on your knowledge of Linux operating system commands, editing files, and shell scripting. You'll need this eclectic skill set to automate your database surroundings. The last several recipes in this chapter show you how to implement several real-world DBA jobs such as performance reporting, monitoring, and operating system file maintenance. You should be able to extend these recipes to meet the automation requirements of your environment.
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:
Edit the /etc/oratab
file, and place a Y
at the end of the entry for the databases you want to automatically restart when the system reboots. You might need root
privileges to edit the file:
# vi /etc/oratab
Place within the file a line similar to this for your environment:
RMDB1:/oracle/product/10.2:Y
In the previous line, RMDB1
is the database name, and /oracle/product/10.2
specifies ORACLE_HOME
.
As root
, navigate to the /etc/init.d
directory, and create a file named dbora
:
# cd /etc/init.d # vi dbora
Place the following lines in the dbora
file. Make sure you change the values of variables ORA_HOME
and ORA_OWNER
to match your environment:
#!/bin/bash # chkconfig: 35 99 10 # description: Starts and stops Oracle processes ORA_HOME=/oracle/product/10.2 ORA_OWNER=oracle case "$1" in 'start') su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" su - $ORA_OWNER -c $ORA_HOME/bin/dbstart ;; 'stop') su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop" su - $ORA_OWNER -c $ORA_HOME/bin/dbshut ;; esac
Change the group of the dbora
file to match the group assigned to the operating system owner of the Oracle software (usually oinstall
or dba
):
# chgrp oinstall dbora
Change the permissions on the dbora
file to 750:
# chmod 750 dbora
Run the following chkconfig
command:
# /sbin/chkconfig --add dbora
If you want to automatically stop and start (on system reboots) other processes such as the Intelligent Agent, Management Server, and the HTTP Server, see Oracle MetaLink Note 222813.1 for details.
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.
With some Unix systems (such as Solaris), the oratab
file is usually located in the /var/opt/oracle
directory.
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.
A Linux runlevel is a logical container for specifying which services will run when the system is started.
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
.
If you are attempting to implement this shutdown/startup script on a non-Linux system (that is, Solaris, AIX, and so on), then see Oracle's installation documentation specific to that operating system for the details on required symbolic links.
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.
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.
You can also use an editing utility (such as vi
) to add an entry to this file.
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.
On some Unix operating systems (such as Solaris), the cron.allow
and cron.deny
files are located in the /etc/cron.d
directory.
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.
Table 11.1. Descriptions of Files and Directories Used by the cron
Utility
File | Purpose |
---|---|
| Starts the |
| System messages related to the |
| User |
| Specifies users who can create a |
| Specifies users who are not allowed to create a |
| The system |
| A directory that contains |
| Directory that contains system scripts to run on an hourly basis. |
| Directory that contains system scripts to run on a daily basis. |
| Directory that contains system scripts to run on a weekly basis. |
| Directory that contains system scripts to run on a monthly basis. |
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:
Editing the cron
table file directly
Loading the cron
table from a file
These two techniques are described in the following sections.
You can edit your cron
table directly with the -e
(editor) option of the crontab
command:
$ crontab -e
When issuing the previous command, you will be presented with a file to edit (see the "How It Works" section of this recipe for a discussion on the default editor). This file is known as your cron
table (or crontab
). To schedule a script named backup.bsh
to run daily at 11:05 p.m., enter the following line into your cron
table:
5 23 * * * /home/oracle/bin/backup.bsh
Exit the cron
table file. If your default editor is vi
, then type :wq
to exit. When you exit crontab
, your cron
table is saved for you. To view your cron
entries, use the -l
(list) option of the crontab
command:
$ crontab -l
To completely remove your cron
table, use the -r
option:
$ crontab -r
Before running the previous command, we recommend you save your cron
table in a text file:
$ crontab -l > saved.cron
That way you can refer to the saved file in the event that you didn't really mean to delete your cron
table.
The other way to modify your cron
table is to load it directly with a file name using the following syntax:
$ crontab <filename>
In the previous line of code, the crontab
utility will load the contents of the specified file into your cron
table. We recommend you perform the following steps when modifying your cron
table with this method:
First create a file with the contents of your existing cron
table:
$ crontab -l > mycron.txt
Make a copy of your cron
table before you edit it. This allows you to revert to the original in the event you introduce errors and can't readily figure out what's incorrect. This also provides you with an audit trail of changes to your cron
table:
$ cp mycron.txt mycron.jul29.txt
You can now edit the mycron.txt
file with your favorite text editor:
$ vi mycron.txt
To schedule a script named backup.bsh
to run daily at 11:05 p.m., enter the following into the file:
5 23 * * * /home/oracle/bin/backup.bsh
When you are finished making edits, load the crontab
back, as shown here:
$ crontab mycron.txt
If your file doesn't conform to the cron
syntax, you'll receive an error such as the following:
"mycron.txt":6: bad day-of-week errors in crontab file, can't install.
In this situation, either correct the syntax error or reload the original copy of the cron
table.
You can use the at
command to schedule a job to run once at a specified point in the future.
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
Your cron
table is a list of numbers and commands that the cron
background process (cron
daemon) will run at a specified time and schedule. The crontab
utility expects entries to follow a well-defined format. We recommend you add a comment line at the beginning of your crontab
file that documents the required format:
# min(0-59) hr(0-23) dayMonth(1-31) monthYear(1-12) dayWeek(0/7-6) commandOrScript
In the previous line, the number (#
) sign in a cron
file represents the start of a comment. Any text entered after #
is ignored by cron
.
Each entry in the crontab
is a single line comprised of six fields. The first five fields specify the execution time and frequency. These entries can be separated by commas or hyphens. A comma indicates multiple values for an entry, whereas a hyphen indicates a range of values. An entry can also be an asterisk (*
), which indicates that all possible values are in effect. Here's an example to help clarify. The following entry sends an e-mail saying "Wake up" every half hour, from 8 a.m. to 4:30 p.m., on Monday through Friday:
0,30 8-16 * * 1-5 echo "wake up" | mailx -s "wake up" [email protected]
On some Linux systems, you can skip a value within a range by following the entry with /<integer>
. For example, if you wanted to run a job every other minute, use 0–59/2
in the minute column. You can also use a slash (/
) with an asterisk to skip values. For example, to run a job every fourth minute, use */4
in the minute column.
The sixth field in the crontab
can be one or more Linux commands or a shell script. Or put another way, the sixth column can be any combination of commands or a script that you can run on one line from the Linux command line.
The cron
utility has a few quirks that need further explanation. The fifth column is the day of the week. Sunday is designated by either a 0 or a 7, Monday by a 1, Tuesday by a 2, and so forth, to Saturday, which is indicated with a 6.
The hour numbers in the second column are in military time format ranging from 0 to 23. The fourth column (month of year) and fifth column (day of week) can be represented with numeric values or by three-letter character abbreviations. For example, the following entry in the crontab
uses three-letter character abbreviations for months and days:
0,30 8-16 * Jan-Dec Mon-Fri echo "wake up" | mailx -s "get up" [email protected]
There also appear to be overlapping columns such as the third column (day of the month) and the fifth column (day of the week). These columns allow you to create flexible schedules for jobs that need to run on schedules such as the 1st and 15th day of the month or on every Tuesday. Put an asterisk in the column that you're not using. If you need to run a job on the 1st and 15th and every Tuesday, then fill in both columns.
If you're running a shell script from cron
that contains a call to an Oracle utility such as sqlplus
or rman
, ensure that you instantiate (source) any required operating system variables such as ORACLE_SID
and ORACLE_HOME
. If you don't source these variables, you'll see errors such as the following when your shell script runs from cron
:
sqlplus: command not found
When cron
runs a script as a user, it doesn't run the user's startup or login files (like .bashrc
). Therefore, any script (being run from cron
) needs to explicitly set any required variables. You can directly set the variables within the script or call another script (such as Oracle's oraenv
script) that exports these variables.
Try not to schedule every job that you enter in cron
to run at the same time every day. Rather, spread them out so as not to bog down cron
or the system at any particular time.
You can specify that any output generated by a cron
entry be redirected to a file. The following example writes standard output and standard error to a file named bck.log
:
11 12 * * * /home/oracle/bin/backup.bsh 1>/home/oracle/bin/log/bck.log 2>&1
In the previous line, 1>
redirects standard output, and 2>&1
specifies that the standard error should go to the same location that the standard output is located (see recipe 2-9 for more details on redirecting output). If you don't redirect the output for a cron
job, then any output from the job will be e-mailed to the user who owns the cron
job. You can override this by specifying the MAILTO
variable directly within the cron
table. In this next example, we want to aggravate the system administrator and send cron
output to the root
user:
MAILTO=root 11 12 * * * /home/oracle/bin/backup.bsh
If you don't want the output to go anywhere, then redirect it to the proverbial bit bucket. The following entry sends the standard output and standard error to the /dev/null
device:
11 12 * * * /home/oracle/bin/backup.bsh 1>/dev/null 2>&1
If you have a cron
job that isn't running correctly, follow these steps to troubleshoot the issue:
Copy your cron
entry, paste it to the operating system command line, and manually run the command. Often a slight typo in a directory or file name can be the source of the problem. Manually running the command will highlight errors like this.
If the script runs Oracle utilities, ensure that you source (set) the required operating system variables within the script such as ORACLE_HOME
and ORACLE_SID
. Oftentimes these variables are set by startup scripts (like HOME/.bashrc
) when you log on. Since cron
doesn't run a user's startup scripts, any required variables must be set explicitly within the script.
Ensure that the first line of any shell scripts invoked from cron
specifies the name of the program that will be used to interpret the commands within the script. For example, #!/bin/bash
should be the first entry in a Bash shell script. Since cron
doesn't run a user's startup scripts (like HOME/.bashrc
), you can't assume that your operating system user's default shell will be used to run a command or script evoked from cron
.
Ensure that the cron
background process is running. Issue a ps -ef | grep crond
to verify.
Check your e-mail on the server. The cron
utility will usually send an e-mail to the operating system account when there are issues with a misbehaving cron
job.
Inspect the contents of the /var/log/cron
file for any errors. Sometimes this file has relevant information regarding a cron
job that has failed to run.
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:
Make a copy of the ORACLE_HOME/rdbms/admin/awrrpti.sql
file:
$ cd $ORACLE_HOME/rdbms/admin $ cp awrrpti.sql awrcustom.sql
Modify the SQL in the awrcustom.sql
file, as shown here:
$ vi awrcustom.sql
At the top of the report, replace the "Customer-customizable reporting settings" section with the following SQL code:
DEFINE num_days=0; DEFINE report_type='text'; -- COL bsnap NEW_VALUE begin_snap COL esnap NEW_VALUE end_snap -- select max(snap_id) bsnap from dba_hist_snapshot where begin_interval_time < sysdate-1; -- select max(snap_id) esnap from dba_hist_snapshot; -- COL idname NEW_VALUE dbid COL dname NEW_VALUE db_name COL inum NEW_VALUE inst_num COL iname NEW_VALUE inst_name -- select d.dbid idname, lower(d.name) dname, i.instance_number inum, i.instance_name iname from v$database d, v$instance i; -- -- Set the name for the report DEFINE report_name='/orahome/scripts/awrrpt.&&db_name..txt'
The previous code defines variables that allow you to report on the prior 24 hours worth of statistics in your database. To automate the running of the awrcustom.sql
script, create a shell script, as shown in step 4.
Create a Bash shell script named awr.bsh
that calls awrcustom.sql
:
#!/bin/bash # source oracle OS variables; see recipe 7-7 for an example of oraset script, # otherwise hardcode the values of ORACLE_HOME, ORACLE_SID, and PATH . /var/opt/oracle/oraset RMDB1 # for DB in proddb prod2db; do sqlplus -s << EOF system/foo@${DB} @?/rdbms/admin/awrcustom.sql exit EOF # mail -s "AWR Rpt: ${DB}" [email protected] </orahome/scripts/awrrpt.${DB}.txt # done # for DB in... exit 0
Call the report from cron
:
0 6 * * * /orahome/scripts/awr.bsh 1>/orahome/scripts/log/awr.log 2>&1
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
See recipe 4-11 for details on displaying line numbers in a file that is edited via vi
.
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 orase
t 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;
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
See recipe 4-11 for details on displaying line numbers in a file that is edited via vi
.
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 orase
t 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 can also use Enterprise Manager tablespace alerts to monitor either percent full or percent free. These alerts can be enabled via the DBMS_SERVER_ALERT
PL/SQL package.
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
See recipe 4-11 for details on displaying line numbers in a file that is edited with vi
.
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.
See recipe 11-9 or Appendix B for an example of using the logrotate
utility to rename, compress, and delete log files.
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:
Create a configuration file named alert.conf
in the directory /home/oracle/config
(create the directory if it doesn't already exist):
/oracle/RMDB1/admin/bdump/*.log { daily missingok rotate 7 compress mail oracle@localhost }
In the previous configuration file, the first line specifies the location of the log file. The asterisk (a wildcard) tells logrotate
to look for any file with the extension of .log
in that directory. The daily
keyword specifies that the log file should be rotated on a daily basis. The missingok
keyword specifies that logrotate
should not throw an error if it doesn't find any log files. The rotate 7
keywords specify the number of times to rotate a log file before it is removed. The compress keyword compresses the rotated log file. Last, a status e-mail is sent to the local oracle
user on the server.
Create a cron
job to automatically run the job on a daily basis:
0 9 * * * /usr/sbin/logrotate -s /home/oracle/config/alrotate.status /home/oracle/config/alert.conf
The previous two lines of code should be one line in your cron
table (it didn't fit nicely on this page on one line). The cron
job runs the logrotate
utility every day at 9 a.m. The -s
(status) option directs the status file to the specified directory and file. The configuration file used is /home/oracle/config/alert.conf
.
Manually test the job to see whether it rotates the alert.log
correctly. Use the -f
switch to force logrotate
to do a rotation:
$ /usr/sbin/logrotate -f -s /home/oracle/config/alrotate.status /home/oracle/config/alert.conf
As shown in the previous steps, you can use the logrotate
utility to easily set up log rotation jobs.
If you need to troubleshoot issues with your logrotate
job, use the -d
(debug) or -v (verbose) option.
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).
The copytruncate
command is appropriate for any log file in which the logging activity cannot be temporarily halted.
Table 11.2. Descriptions of Commonly Used logrotate
Options
Option | Purpose |
---|---|
| Compresses old versions of log files with |
| Copies the log file but doesn't change the original |
| Copies the log file and then truncates the original |
| Rotates the log files daily |
| Mails any deleted log files to the specified account |
| Rotates the log files only the first time logrotate is executed in a month |
| Moves the log files to the specified directory |
| Rotates the logs |
| Rotates the log file when it is greater than |
| Rotates the log files if more than a week has passed since the last rotation |
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
:
Can make the execution of a job dependent on the completion of another job
Robust resource balancing and flexible scheduling features
Can run jobs based on a database event
DBMS_SCHEDULER
syntax works the same regardless of the operating system
Can run status reports using the data dictionary
If working in clustered environment, no need to worry about synchronizing multiple cron
tables for each node in the cluster
Listed next are some of the advantages of using cron
:
Easy to use, simple, tried and true
Almost universally available on all Linux/Unix boxes; for the most part, runs nearly identically regardless of the Linux/Unix platform (yes, there are minor differences)
Database agnostic; operates independently of the database and works the same regardless of the database vendor or database version
Works whether the database is available or not
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
.