Oracle DBA unix useful command

Unix for the DBA How to kill all similar processes with single command (in this case opmn)

 

stty erase ^?
 stty erase ^H

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory

find . -print |grep -i test.sql

 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

 Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

 Show number of active Oracle dedicated connection users for a particular ORACLE_SID

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

 Display the number of CPU’s in Solaris

psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l
Aix : lsps -a

 Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''

 Show mount points for a disk in AIX

lspv -l hdisk13

 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

 Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

 Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

 Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

 Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

    sed -e 's/^M$//' filename > tempfile


sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time 	%usr 	%sys 	%wio 	%idle
11:57:31 	72 	28 	0 	0
11:57:41 	70 	30 	0 	0
11:57:51 	70 	30 	0 	0
11:58:01 	68 	32 	0 	0
11:58:11 	67 	33 	0 	0
11:58:21 	65 	28 	0 	7
11:58:31 	73 	27 	0 	0
11:58:41 	69 	31 	0 	0
Average 	69 	30 	0 	1

%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle


mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU 	minf 	mjf 	xcal 	intr 	ithr 	csw 	icsw 	migr 	smtx 	srw 	syscl 	usr 	sys 	wt 	idl
0 	6 	8 	0 	438 	237 	246 	85 	0 	0 	21 	8542 	23 	9 	9 	59
0 	0 	29 	0 	744 	544 	494 	206 	0 	0 	95 	110911 	65 	29 	6 	0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU 	PID 	USER 	COMMAND
78.1 	4789 	oracle 	ora_dbwr_DDDS2
8.5 	4793 	oracle 	ora_lgwr_DDDS2
2.4 	6206 	oracle 	oracleDDDS2 (LOCAL=NO)
0.1 	4797 	oracle 	ora_smon_DDDS2
0.1 	6207 	oracle 	oracleDDDS2 (LOCAL=NO)
etc. 	etc. 	etc. 	etc.

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:

    SELECT a.username, 
           a.osuser, 
           a.program, 
           spid, 
           sid, 
           a.serial#
    FROM   v$session a,
           v$process b
    WHERE  a.paddr = b.addr
    AND    spid = '&pid';


CRON
There are two methods of editing the crontab file. First you can use the "crontab -l > filename" option to list the contents and pipe this to a file. Once you've editied the file you can then apply it using the "crontab filename":

    * Login as root
    * crontab -l > newcron
    * Edit newcron file.
    * crontab newcron

Alternatively you can use the "crontab -e" option to edit the crontab file directly.

The entries have the following elements:

    field          allowed values
    -----          --------------
    minute         0-59
    hour           0-23
    day of month   1-31
    month          1-12
    day of week    0-7 (both 0 and 7 are Sunday)
    user           Valid OS user
    command        Valid command or script.

The first 5 fields can be specified using the following rules:

    *       - All available values or "first-last".
    3-4     - A single range representing each possible from the start to the end of the range inclusive.
    1,2,5,6 - A specific list of values.
    1-3,5-8 - A specific list of ranges.
    0-23/2  - Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root:

    0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1


vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs 	memory 	page 	disk 	faults 	cpu
r 	b 	w 	swap 	free 	re 	mf 	pi 	po 	fr 	de 	sr 	s0 	s1 	s2 	s3 	in 	sy 	cs 	us 	sy 	id
0 	0 	0 	28872 	8792 	8 	5 	172 	142 	210 	0 	24 	3 	11 	17 	2 	289 	1081 	201 	14 	6 	80
0 	0 	0 	102920 	1936 	1 	95 	193 	6 	302 	1264 	235 	12 	1 	0 	3 	240 	459 	211 	0 	2 	97
0 	0 	0 	102800 	1960 	0 	0 	0 	0 	0 	464 	0 	0 	0 	0 	0 	107 	146 	29 	0 	0 	100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.

    * procs (Reports the number of processes in each of the following states)
          o r : in run queue
          o b : blocked for resources (I/O, paging etc.)
          o w : runnable but swapped
    * memory (Reports on usage of virtual and real memory)
          o swap : swap space currently available (Kbytes)
          o free : size of free list (Kbytes)
    * page (Reports information about page faults and paging activity (units per second)
          o re : page reclaims
          o mf : minor faults
          o pi : Kbytes paged in
          o po : Kbytes paged out
          o fr : Kbytes freed
          o de : anticipated short-term memory shortfall (Kbytes)
          o sr : pages scanned by clock algorith
    * disk (Reports the number of disk operations per second for up to 4 disks
    * faults (Reports the trap/interupt rates (per second)
          o in : (non clock) device interupts
          o si : system calls
          o cs : CPU context switches
    * cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
          o us : user time
          o si : system time
          o cs : idle time


File Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:

    #!/bin/ksh
    if test -s /backup/daily_backup.log
    then
      DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
      mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
    fi

==


Automatic Startup Scripts on Linux
Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:

    chmod 750 /etc/init.d/myservice

Link the file into the appropriate run-level script directories:

    ln -s /etc/init.d/myservice /etc/rc0.d/K10myservice
    ln -s /etc/init.d/myservice /etc/rc3.d/S99myservice

Associate the myservice service with the appropriate run levels:

    chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.