Frequently used OS commands for DBA

As DBA you need to use frequent OS command, here I would like to share you some of the important day to day commands:

1. To delete files older than N number of days ? (Useful in delete log, trace, tmp file )

find . -name ‘*.*’ -mtime +[N in days] -exec rm {} \;

Example : find . -mtime +5 -exec rm {} \;

(This command will delete files older then N days in that directory

2. To list files modified in last N days
find . -mtime - -exec ls -lt {} \;

Example: find . -mtime +3 -exec ls -lt {} \;1

So to find files modified in last 3 days

3. To sort files based on Size of file ?

ls -l | sort -nk 5 | more

useful to find large files in log directory to delete in case disk is full

4. To find files changed in last N days
find -mtime -N –print

Example: find -mtime -2 -print

5. To extract cpio file
cpio -idmv < file_name (Don’t forget to use sign < before file name)

6. To find CPU & Memory detail of linux
cat /proc/cpuinfo (CPU)
cat /proc/meminfo (Memory)

7. To find if Operating system in 32 bit or 64 bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit
but if you see output like
64-bit sparcv9 applications
32-bit sparc applicationsabove means your o.s. is 64 bit & can support both 32 & 64 bit applications

8. To find if any service is listening on particular port or not ?
netstat -an | grep {port no}

Example: netstat -an | grep 1523

9. To find Process ID (PID) associated with any port ?
This command is useful if any service is running on a particular port (389, 1521..) and that is run away process which you wish to terminate using kill command

lsof | grep {port no.} (lsof should be installed and in path)

9. To change a particular pattern in a file ?
Open file using vi or any other editor, go in escape mode (by pressing escape) and use

:1,$s/old_pattern/new_parameter/gc ( g will change globally, c will ask for confirmation before changing )

10. To find a pattern in some file in a directory ?
grep pattern file_name ( to find pattern in particular file )
grep pattern * ( in all files in that directory )
If you know how to find a pattern in files in that directory recursively please answer that as comment

11. To create symbolic link to a file ?
ln -s pointing_to symbolic_name
Example : ln -s b a
If you want to create symbolic link from a -> b
(Condition:you should have file b in that directory & there should not be any file with name a)

12. To setup cronjob (cronjob is used to schedule job in Unix at O.s. Level )
crontab -l( list current jobs in cron)
crontab -e ( edit current jobs in cron)

_1_ _2_ _3_ _4_ _5_ executable_or_job
Where
1 – Minutes (0-59)
2 – Hours ( 0-24)
3 – day of month ( 1- 31 )
4 – Month ( 1-12)
5 – A day of week ( 0- 6 ) 0 -> sunday 1-> monday

e.g. 0 3 * * 6 Means run job at 3AM every saturday

This is useful for scheduling tablespace threshold, ftp, rman backup or removed old log files, or other scripts regularly.

Sample Scheduled backup:

$ crontab –l

Rman Database:
00	20	*	*	1,4	/u07/rman/scripts/call_dbbkup_cm0.scp
00	15	*	*	6	/u07/rman/scripts/offbkup_rman.sh
00	20	*	*	0,2,3,6	/u07/rman/scripts/call_arch_bkup.scp
OTA Database:
50	23	*	*	0,2,3,6	/u01/ota/dailyexp_ota.sh
50	23	*	*	1,4	/u01/ota/offbkup_ota.sh
15	14	*	*	0,1,2,3,4,6	/u01/ota/morning_arch.sh

How to kill all similar processes with single command (in this case opmn)

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

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

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

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1