Tuesday 11 December 2012

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

    No comments:

    Post a Comment