Pro Oracle Database 11g Administration (97 page)

Read Pro Oracle Database 11g Administration Online

Authors: Darl Kuhn

Tags: #Oracle DBA

BOOK: Pro Oracle Database 11g Administration
9.92Mb size Format: txt, pdf, ePub

#!/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::initdefault line in the /etc/inittab file. The following example sets the default runlevel to 5:

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.d, where corresponds to the runlevel. For runlevel 5, the scripts are in the

/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}'`

#

loc=`sqlplus -s <

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`

#

free_space=`df -k | grep ${loc} | awk '{print $4}'ècho box = ${BOX}, sid = ${DB}, Arch Log Mnt Pnt = ${loc}

echo "free_space = ${free_space} K"

echo "THRESH_GET_WORRIED= ${THRESH_GET_WORRIED} K"

echo "THRESH_SPACE_CRIT = ${THRESH_SPACE_CRIT} K"

#

if [ $free_space -le $THRESH_GET_WORRIED ]; then

$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

#

if [ $free_space -le $THRESH_SPACE_CRIT ]; then

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='; EOF

else

echo no need to switch, ${free_space} KB free on ${loc}

fi

587

CHAPTER 21 ■ AUTOMATING JOBS

#

exit 0

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

#

if [ $# -ne 1 ]; then

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

#

MAILX='/bin/mailx'

MAIL_LIST='[email protected]'

#

if [ -f $TNS_ADMIN/../log/listener.log ]; then

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

#

exit 0

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

Other books

A Proper Mistress by Shannon Donnelly
Legacy & Spellbound by Nancy Holder
Mirage by Cook, Kristi
Merline Lovelace by A Savage Beauty
Learning to Stay by Erin Celello
Indulgence 2: One Glimpse by Lydia Gastrell
The Resurrection of the Romanovs by Greg King, Penny Wilson
One Minute Past Eight by George Harmon Coxe