Handy Unix commands for a DBA

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Crontab  Syntax:

"crontab -e"  to edit the cron entries and "crontab -l" to list the cron entries

*     *   *  *   *  command to be executed -     -    -   -  - |     |     |   |    | |     |     |   |    +----- day of week (0 - 6) (Sunday=0) |     |     |   +------- month (1 - 12) |     |     +--------- day of month (1 - 31) |     +----------- hour (0 - 23) +------------- min (0 - 59)

For example:

A line in crontab file like below:

To submit a task to run every hour at 15 minutes past the hour on weekdays (days 1-5)

15 * 1-5 * * /oracle/scripts/truncate_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minuteson weekends (days 6 and 0)

00,15,30,45 * 0,6 * * /oracle/scripts/truncate_listener.sh > /dev/null 2>&1

Changing the parameter values as below will cause this command to run at different time schedule below :

min	hour	day/month	month	day/week	Execution time
30	0	1	1,6,12	*	– 00:30 Hrs  on 1st of Jan, June & Dec.
0	20	*	10	1-5	–8.00 PM every weekday (Mon-Fri) only in Oct.
0	0	1,10,15	*	*	– midnight on 1st ,10th & 15th of month
5,10	0	10	*	1	– At 12.05,12.10 every Monday & on 10th of every month

File permission details

Owner      Group      World      Permission
---------- ---------- ---------- -----------------------
7 (u+rwx)  7 (g+rwx)  7 (o+rwx)  read + write + execute
6 (u+wx)   6 (g+wx)   6 (o+wx)   write + execute
5 (u+Rx)   5 (g+Rx)   5 (o+Rx)   read + execute
4 (u+r)    4 (g+r)    4 (o+r)    read only
2 (u+w)    2 (g+w)    2 (o+w)    write only
1 (u+x)    1 (g+x)    1 (o+x)    execute only

Displays the full path of (shell) commands
which zip

How to kill all similar processes with single command (in this case devusr)
ps -ef | grep devusr | grep -v grep | awk '{print $2}' | xargs -i kill -9 {}

Locating Files under a particular directory
find . -print | grep -i test.sql

Find/Zip/Move/Delete files older than one day
Finds and lists the files       :  find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;
Finds and removes the files :  find ./ -name "*.ARC" -mtime +1 -exec rm {} \;
Finds and zips the files       :  find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;
Finds and moves the files    :  find ./ -name "*.arc" -mtime +1 -exec mv {} /oracle/backup/;

Remove DOS style CR/LF characters (^M) from UNIX files
sed -e 's/^M$//' filename > tempfile

Using AWK in UNIX
To get 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

Delete the 500 oldest files
rm -f `ls -tr | head -500`

Check all logs for ORA- errors
grep ^ORA- *log | cut -f2 -d"-" | cut -f1 -d: | awk '{print "ORA-" $1}' | sort -u

For Find and grep
find ./ -grep <what> {} \; -print 2>/dev/null
(or) find ./ -exec grep -l "string" {} \;

Display only directiories details
ls -l | grep ^d

Display the number of CPU's
Solaris : psrinfo -v | grep "Status of processor"|wc -l
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 '<ID>'

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

vi delete commands - reference

A lot of times all people need is a quick reference, so I'll start with a reference of vi/vim delete commands:

x   - delete current character
dw  - delete current word
dd  - delete current line
5dd - delete five lines

d$  - delete to end of line
d0  - delete to beginning of line

:1,.d
delete to beginning of file

:.,$d
delete to end of file