Aman- Oracle E-Business DBA: UNIX for DBA's

Collect server statistics

$ sadc

Collect system activity

$sar

The vmstat utility is used to report virtual memory statistics.

$vmstat

The Unix (w)atch Command

$w

iostat is used to report I/O statistics.

# iostat

Memory and CPUs

Display the top CPU users with the command ps -ef | sort +6 | tail -10.
```
# ps -ef | sort +6 | tail -10
```

Display allocated memory and semaphores using ipcs -mqs and ipcs -pmb as shown below. Use the ipcrm -m to deallocate memory segments. Execute oradebug ipc in svrmgrl to write Oracle used semaphores into a trace file.

# ipcs -mqs

Display RAM size with the command prtconf | grep "Memory Size".

$ prtconf|grep -i "Memory Size"
Memory size: 1024 Megabytes

Display the number of CPUs using the psrinfo -v command.

# psrinfo -v

Display system details using the uname -X command.

# uname -X

Find the number CPUs and the speed of those CPUs or memory using dmesg | grep -i [cpu | mem].

# dmesg | grep -i cpu

# dmesg | grep -i mem

To find the files that contain a string we can use:
find . -exec grep -l “string to find” {} \;
This starts the search from the current directory, looks for files that contain the specified string, and then it prints their names.

Monitoring Oracle

Display active dedicated connections as shown below. Execute the command below excluding |wc -l to view connected dedicated server processes.

ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

# ps -ef |grep $ORACLE_SID|grep -v grep|grep -v ora_ oracle 24440 24439 0 16:35:42 ? 0:00 oracle<SID> (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Examining Processes

Find all Oracle processes. grep -v grep removes the grep command from the list of processes. Also note that the results of one unix shell command can be passed to the next using the | (pipe) command.

ps -ef | grep ora_ | grep -v grep

Check the listener and the agent.

ps -ef | grep [lsnr|dbsnmp] | grep -v grep

List available instances on a server:$ ps -ef | grep smon

List available listeners on a server:$ ps -ef | grep listener | grep -v grep

Find out file system usage for Oracle archive destination:$ df -k | grep oraarch

List number of lines in the alert.log file:$ cat alert.log | wc -l

List all Oracle error messages from the alert.log file:$ grep ORA- alert.log

To view a crontab file, type:

Crontab -l

Check Oracle Instance Availability
The oratab file lists all the databases on a server:
$ cat /var/opt/oracle/oratab

File Permissions and Ownership:

The chown command changes who owns the file. It does not actually change what permissions the file's owner has. So, for example, if you have a file set to 555 (r-xr-xr-x) and you want to write to it, then chown'ing it won't do you any good. Note that you can only chown a file if you already own it (so you're giving ownership to someone else), or if you are root.

By the way, there's also a chgrp command, to change the group with which the file is associated. The example I used for chown holds here as well. You must own the file before you can chgrp it, and you can usually only chgrp the file to a group that you belong to. Root, of course, can chgrp any file to any group it wants.

The chmod command changes the actual permissions on a file. So if you want to write to the file, you can add write permissions to it. I believe you can only chmod a file if you could chown it (again, meaning you must either own the file or be root).

Which should you use? In the end, it depends on the circumstances. Each is appropriate at different times. You'll probably use chmod the most, followed by chown and finally chgrp.

read-only for all users:

read=4
write=2
execute=1

Add the numbers to get the combination you want--the numbers for the example you show are 644.

For example:

$ chmod 777 <filename>

$ chown oracle:dba <filename>

Command to kill all Oracle processes

ps -ef | grep ora_ | grep -v grep | grep $ORACLE_SID | awk '{print $2}' | xargs kill -9 {}

Finding a file:
find . -name "listener.ora" -print

Disk space:
# df -k

The command below could constitute a basis for a disk space running-low warning script.

# df -k | awk '{print $1 " " $5}'

Find and Display Files

ls -lat - list recently touched files.
ls -lac - list recently changed files.
du -s * | sort -n - du -s shows file size only, piping to sort orders the results in ascending order.
```
# du -s $ORACLE_HOME | sort -n
1801978 /<mount-point-1>/oracle/product/8.1.7
```
du -sk $ORACLE_HOME - changes number displayed from units of 512 bytes to 1024 bytes.
```
# du -sk $ORACLE_HOME
900989  /<mount-point-1>/oracle/product/8.1.7
```
find . -print | xargs grep -i oracle - finds lines in files, in the current directory recursively, which have the word oracle in them.
find . -mtime -1 -print - files created or altered today.
find . -size +1048576c -print - files greater than 1Mb.
find . -mtime +7 -exec rm {} \; - remove files in the current directory created more than 7 days ago.

Delete Old Trace and Audit Files

#!/bin/ksh # # --------------------------------------------------------------------------------------------------------------------------------- # export ORACLE_BASE=/<mount-point-1>/oracle export ORACLE_HOME=$ORACLE_BASE/product/8.1.7 export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/openwin/lib export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME export host=`hostname` export email=$1 export USAGE="$0: Incorrect arguments, Usage: $0 <\"email1 email2 ... emailn\">" if [ -z "$email" ]; then echo "$USAGE" mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null exit 1 elif [ `whoami` != 'oracle' ]; then echo "$0 aborted - user `whoami` is incorrect, must be user root" mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null exit 1 fi if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]; then echo "$0 aborted - variable not defined" mailx -s "$0 aborted - variable not defined" $email < /dev/null exit 1 fi echo "Executing $0 on $host" # # --------------------------------------------------------------------------------------------------------------------------------- # echo Cleaning trace and transaction audit files for $1 upto 14 days ago unalias rm find $ORACLE_BASE/admin/$1/bdump/*.trc -mtime +14 | xargs rm -f find $ORACLE_BASE/admin/$1/udump/*.trc -mtime +14 | xargs rm -f find $ORACLE_BASE/admin/$1/cdump/*.* -mtime +14 | xargs rm -f find $ORACLE_HOME/rdbms/audit/*.aud -mtime +14 | xargs rm -f alias rm 'rm -i'

ORA-600 Errors in the Alert Log
The script below will detect and email ORA-00600 errors in the Oracle database alter log file.
#!/bin/ksh # # --------------------------------------------------------------------------------------------------------------------------------- # export ORACLE_BASE=/<mount-point-1>/oracle export ORACLE_HOME=$ORACLE_BASE/product/8.1.7 export TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_SBIN=$ORACLE_HOME/sbin export PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME export host=`hostname` export email=$1 USAGE="$0: Incorrect arguments, Usage: $0 <email>" if [ -z "$email" ]; then echo "$USAGE" mailx -s "$USAGE" emergencyEMail@whatever.com < /dev/null exit 1 elif [ `whoami` != 'oracle' ]; then echo "$0 aborted - user `whoami` is incorrect, must be user root" mailx -s "$0 aborted - user `whoami` is incorrect, must be user root" $email < /dev/null exit 1 fi if [ -z "$PATH" ] || [ -z "$host" ] || [ -z "$ORACLE_BASE" ] || [ -z "$ORACLE_HOME" ]\ || [ -z "$password" ] || [ -z "$ORACLE_SID" ] || [ -z "$tnsname" ]; then echo "$0 aborted - variable not defined" mailx -s "$0 aborted - variable not defined" $email < /dev/null exit 1 fi echo "Executing $0 on $host" # # --------------------------------------------------------------------------------------------------------------------------------- # err="`tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep | wc -l`" if [ $err -gt 0 ]; then tail -50 $ORACLE_ALERT/alert_$ORACLE_SID.log | grep ORA-00600 | grep -v grep > $ORACLE_SBIN/logs/ora600.log sub="Script $0 on $host detected ORA-00600 for SID $ORACLE_SID" echo $sub mailx -s "$sub" $email < $ORACLE_SBIN/logs/ora600.log exit 1 fi exit 0

Changing Strings in Files Using the sed Editor
Be extremely careful using a script such as shown below where global changes can be made.
#!/bin/ksh for file in $* do cp $file $file.old sed -e 's/find/replace/g' < $file > $file done

The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases:
###################################################################
## ckinstance.ksh ##
###################################################################
ORATAB=/var/opt/oracle/oratab
echo "`date`   "
echo "Oracle Database(s) Status `hostname` :\n"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "\#" | grep -v "\*"`
pslist="`ps -ef | grep pmon`"
for i in $db ; do
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>$1
if (( $? )); then
        echo "Oracle Instance - $i:       Down"
else
        echo "Oracle Instance - $i:       Up"
fi
done

Check Oracle Listener's Availability

A similar script checks for the Oracle listener. If the listener is down, the script will restart the listener:

#######################################################################
## cklsnr.sh                                                         ##
#######################################################################
#!/bin/ksh
DBALIST="primary.dba@company.com,another.dba@company.com";export DBALIST
cd /var/opt/oracle
rm -f lsnr.exist
ps -ef | grep mylsnr | grep -v grep > lsnr.exist
if [ -s lsnr.exist ]
then
    echo
else
echo "Alert" | mailx -s "Listener 'mylsnr' on `hostname` is down" $DBALIST
    TNS_ADMIN=/var/opt/oracle; export TNS_ADMIN
    ORACLE_SID=db1; export ORACLE_SID
    ORAENV_ASK=NO; export ORAENV_ASK
    PATH=$PATH:/bin:/usr/local/bin; export PATH
    . oraenv
    LD_LIBRARY_PATH=${ORACLE_HOME}/lib;export LD_LIBRARY_PATH
    lsnrctl start mylsnr
fi

Clean Up Old Archived Logs

The following script cleans up old archive logs if the log file system reaches 90-percent capacity:

$ df -k | grep arch
Filesystem                kbytes   used     avail    capacity Mounted on
/dev/vx/dsk/proddg/archive 71123968 30210248 40594232   43% /u08/archive#######################################################################
## clean_arch.ksh                                                    ##
#######################################################################
#!/bin/ksh
df -k | grep arch > dfk.result
archive_filesystem=`awk -F" " '{ print $6 }' dfk.result`
archive_capacity=`awk -F" " '{ print $5 }' dfk.result`

if [[ $archive_capacity > 90% ] ]
then
    echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled"
    # try one of the following option depend on your need
    find $archive_filesystem -type f -mtime +2 -exec rm -r {} \;
    tar
    rman
fi

Check Tablespace Usage

This scripts checks for tablespace usage. If tablespace is 10 percent free, it will send an alert e-mail.

#####################################################################
## ck_tbsp.sh ##
#####################################################################
#!/bin/ksh
sqlplus -s <<!
oracle/$1@$2
set feed off
set linesize 100
set pagesize 200
spool tablespace.alert
SELECT F.TABLESPACE_NAME,
       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
       TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM   (
       SELECT       TABLESPACE_NAME,
                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024
                                        FROM V\$PARAMETER
                                        WHERE NAME = 'db_block_size')/1024)
                           ) FREE_SPACE
       FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
       ) F,
       (
       SELECT TABLESPACE_NAME,
       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
       FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off
exit!
if [ `cat tablespace.alert|wc -l` -gt 0 ]
then
          cat tablespace.alert -l tablespace.alert > tablespace.tmp
          mailx -s "TABLESPACE ALERT for ${2}" $DBALIST < tablespace.tmp
fi

The following finds out invalid database objects:

#####################################################################
## invalid_object_alert.sh ##
#####################################################################
#!/bin/ksh
. /etc/oracle.profile
sqlplus -s <<!
oracle/$1@$2
set          feed off
set heading off
column object_name format a30
spool invalid_object.alert
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM    DBA_OBJECTS
WHERE   STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off
exit
!
if [ `cat invalid_object.alert|wc -l` -gt 0 ]
then
    mailx -s "INVALID OBJECTS for ${2}" $DBALIST < invalid_object.alert
fi

Monitor Users and Transactions (Dead Locks, et al)

This script sends out an alert e-mail if dead lock occurs:

###################################################################
## deadlock_alert.sh ##
###################################################################
#!/bin/ksh
. /etc/oracle.profile
sqlplus -s <<!
oracle/$1@$2
set feed off
set heading off
spool deadlock.alert
SELECT   SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
              DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM     V$LOCK
WHERE    REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;
spool off
exit
!
if [ `cat deadlock.alert|wc -l` -gt 0 ]
then
    mailx -s "DEADLOCK ALERT for ${2}" $DBALIST < deadlock.alert
fi

Aman- Oracle E-Business DBA

Tuesday, 11 December 2012

UNIX for DBA's

The Unix (w)atch Command

Memory and CPUs

Monitoring Oracle

Examining Processes

File Permissions and Ownership:

Find and Display Files

Delete Old Trace and Audit Files

ORA-600 Errors in the Alert Log

Changing Strings in Files Using the sed Editor

Check Oracle Listener's Availability

Clean Up Old Archived Logs

Check Tablespace Usage

Monitor Users and Transactions (Dead Locks, et al)

No comments:

Post a Comment