Pro Oracle Database 11g Administration (97 page)
#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
ORA_HOME=/oracle/app/oracle/product/11.2.0/db_1
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
583
CHAPTER 21 ■ AUTOMATING JOBS
These lines look like comments in the dbora file, but are actually mandatory lines:
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes
These 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 description is also required that provides textual information about the service.
■
Note
A Linux runlevel is a logical container for specifying which services will run when the system is started.
1. 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 dba dbora
2. Change the permissions on the
dbora
file to 750:
# chmod 750 dbora
3. Run the following
chkconfig
command:
# /sbin/chkconfig --add dbora
Here, 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 This 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.
■
Tip
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 My Oracle Support 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 section 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.
584
CHAPTER 21 ■ AUTOMATING JOBS
To set the default runlevel, specify N in the id:
id:5:initdefault:
The runlevel of 1 is used by system administrators (SAs) when performing maintenance and repairs. The runlevel of 5 will start the Linux server with a graphical login screen at the console plus networking capabilities. However, if you have a problem running the display manager at the console—due to a video driver issue, for example—then you can start in runlevel 3 instead, which is command-line-based but still has networking services.
Most SAs who are security conscious operate their servers on runlevel 3. With the wide acceptance of VNC, SAs oftentimes do not see the benefit of running on runlevel 5. If an SA wants to take advantage of graphical utilities, they’ll just use VNC (or a similar tool). Do not, by the way, attempt to set initdefault to either 0 or 6, because your Linux server will never start.
To determine the current runlevel, you can run who -r or runlevel as follows:
# runlevel
N 5
# who -r
run-level 5 Jun 17 00:29 last=S
A given runlevel governs which scripts Linux will run when starting. These scripts are located in the directory /etc/rc.d/rc
/etc/rc.d/rc5.d directory. For example, when Linux starts up in runlevel 5, one of the scripts it will run is /etc/rc.d/rc5.d/S55sshd, which is actually a softlink to /etc/rc.d/init.d/sshd.
Checking for Archive Redo Destination Fullness
Sometimes DBAs and system administrators don't adequately plan and implement a location to be used on disk to store archive redo log files. In these scenarios it's sometimes convenient to have a script that checks for space in the primary location and send out warnings before the archive redo destination becomes full. In addition, you may want to implement within the script to automatically switch the archive redo log location to an alternate location that has adequate disk space.
I've only used scripts like this in chaotic environments that have issues with the archive redo log destination filling up at unpredictable frequencies. If the archive redo log destination fills up, the database will hang. In some environments, this is highly unacceptable. You could argue that a DBA should plan and never let herself get into this type of situation. However, if you're brought in to maintain an unpredictable environment and you're the one getting the phone calls at 2:00 a.m., you may want to consider implementing a script such as the one listed in the section.
Before using the script in Listing 21–4, change the variables within the script to match your environment. For example, SWITCH_DIR should point to an alternate location on disk where you can safely switch the archive redo log destination in the event the primary destination becomes full. The script will send warning e-mails when the threshold gets below the amount of space specified by the THRESH_GET_WORRIED variable. If the archive redo log space falls below the value contained in the THRESH_SPACE_CRIT variable, then the destination will automatically be switched to the directory contained in the SWITCH_DIR variable.
586
CHAPTER 21 ■ AUTOMATING JOBS
Listing 21–4.
Archive Redo Destination Space Survey Script
#!/bin/bash
PRG=`basename $0`
DB=$1
USAGE="Usage: ${PRG}
if [ -z "$DB" ]; then
echo "${USAGE}"
exit 1
fi
# source OS variables
. /var/opt/oracle/oraset ${DB}
# Set an alternative location, make sure it exists and has space.
SWITCH_DIR=/oradump01/${DB}/archivelog
# Set thresholds for getting concerned and switching.
THRESH_GET_WORRIED=2000000 # 2Gig from df -k
THRESH_SPACE_CRIT=1000000 # 1Gig from df -k
MAILX="/bin/mailx"
MAIL_LIST="[email protected] "
BOX=ùname -a | awk '{print$2}'`
#
CONNECT / AS sysdba
SET HEAD OFF FEEDBACK OFF
SELECT SUBSTR(destination,1,INSTR(destination,'/',1,2)-1)
FROM v\\$archive_dest WHERE dest_name='LOG_ARCHIVE_DEST_1';
EOF`
#
echo "free_space = ${free_space} K"
echo "THRESH_GET_WORRIED= ${THRESH_GET_WORRIED} K"
echo "THRESH_SPACE_CRIT = ${THRESH_SPACE_CRIT} K"
#
$MAILX -s "Arch Redo Space Low ${DB} on $BOX" $MAIL_LIST <
Archive log dest space low, box: $BOX, sid: ${DB}, free space: $free_space EOF
fi
#
sqlplus -s << EOF
CONNECT / AS sysdba
ALTER SYSTEM SET log_archive_dest_1='location=${SWITCH_DIR}';
ALTER SYSTEM SWITCH LOGFILE;
EOF
$MAILX -s "Archive Switch ${DB} on $BOX" $MAIL_LIST <
Archive log dest, box: $BOX, sid: ${DB} has switched.
Then ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=
else
echo no need to switch, ${free_space} KB free on ${loc}
fi
587
CHAPTER 21 ■ AUTOMATING JOBS
#
The prior script assumes that you've set your LOG_ARCHIVE_DEST_1 initialization parameter to set your archive redo location. If you're using a FRA for the location of your archive redo log files, you can derive the archive location from the V$ARCHIVED_LOG view, for example: select
substr(name,1,instr(name,'/',1,2)-1)
from v$archived_log
where first_time =
(select max(first_time) from v$archived_log);
Typically I'll run a script to check the archive redo log destination once an hour. Here's a typical cron entry:
#---------------------------------------------------
# Archive directory check for fullness.
38 * * * * /u01/oracle/bin/arch_check.bsh DWREP
1>/u01/oracle/bin/log/arch_check.log 2>&1
#---------------------------------------------------
(Note that the code should be on one line. It's placed on two lines in this book so that it fits on the page.)
Truncating Large Log Files
Sometimes log files can grow to very large sizes and cause issues by filling up critical mount points. The listener.log will record information about incoming connections to the database. For active systems, this file can quickly grow to several gigabytes. For most of my environments, the information in the listener.log file does not need to be retained for any reason. If there are Oracle Net connectivity issues, then the file can be inspected to help troubleshoot issues.
The listener.log file is actively written to, so you shouldn't just delete it. If you remove the file, the listener process won't recreate the file and start writing to it again. You have to stop and restart the listener to reinstatiate its writing to the listener.log file. You can, however, null out the listener.log file or truncate it. In Linux/Unix environments this is done via the following technique: $ cat /dev/null >listener.log
The previous command replaces the contents of the listener.log file with the contents of
/dev/null (a default file on Linux/Unix systems that contains nothing). The result of the prior line of code is that the listener.log file is truncated and the listener can continue to actively write to it.
Listing 21–5 is a shell script that truncates the default listener.log file and a named listener appinvprd.log file. This script is dependent on setting on the operating system variable TNS_ADMIN. If you don't set that variable in your environment, you'll have to hard code the directory path within this script: 588
CHAPTER 21 ■ AUTOMATING JOBS
Listing 21–5.
Script to Truncate the Default listener.log
#!/bin/bash
#
echo "Usage: $0 SID"
exit 1
fi
# See chapter 2 for details on setting OS variables
# Source oracle OS variables with oraset script
. /var/opt/oracle/oraset $1
#
MAIL_LIST='[email protected]'
#
cat /dev/null > $TNS_ADMIN/../log/listener.log
fi
if [ $? -ne 0 ]; then
echo "trunc list. problem" | $MAILX -s "trunc list. problem $1" $MAIL_LIST
else
echo "no problem..."
fi
# A named listener log file
if [ -f $TNS_ADMIN/../log/appinvprd.log ]; then
cat /dev/null > $TNS_ADMIN/../log/appinvprd.log
fi
if [ $? -ne 0 ]; then
echo "trunc list. problem" | $MAILX -s "trunc list. problem $1" $MAIL_LIST
else
echo "no problem..."
fi
#
The following cron entry runs the prior script on a monthly basis:
#---------------------------------------------------
# Trunc log files once a month.
30 6 1 * * /orahome/oracle/bin/trunc_log.bsh DWREP
1>/orahome/oracle/bin/log/trunc_log.log 2>&1
#---------------------------------------------------
(Note that this cron table entry is broken into two lines to fit on the page. In the cron table, it needs to be all on one line.)
Checking for Locked Production Accounts
Usually I have a database profile in place that specifies that a database account become locked after a specified number of failed login attempts. For example, I'll set the DEFAULT profile FAILED_LOGIN_ATTEMPTS to 5. What sometimes happens is that a rogue user or developer will attempt to guess the production account password, and after 5 attempts, this locks the production account. When 589
CHAPTER 21 ■ AUTOMATING JOBS
this happens, I need to know about it as soon as possible so that I can investigate the issue and then unlock the account.
Listing 21–6 is a shell script that checks the LOCK_DATE value in DBA_USERS for a list of production database accounts:
Listing 21–6.
A shell script to check the LOCK_DATE value in DBA_USERS
#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 SID"
exit 1
fi
# source oracle OS variables