UNIX Commands for DBAs
This article contains a brief list of commands that most UNIX DBAs will need on a regular basis.
Basic File Navigation
The "pwd" command displays the current directory:
root> pwd
/u01/app/oracle/product/9.2.0.1.0
The "ls" command lists all files and directories in the specified directory. If no location is defined it acts on the current directory:
root> ls
root> ls /u01
root> ls -al
The "-a" flag lists hidden "." files. The "-l" flag lists file details.
The "cd" command is used to change directories:
root> cd /u01/app/oracle
The "touch" command is used to create a new empty file with the default permissions:
root> touch my.log
The "rm" command is used to delete files and directories:
root> rm my.log
root> rm -R /archive
The "-R" flag tells the command to recurse through subdirectories.
The "mv" command is used to move or rename files and directories:
root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .
The "." represents the current directory
The "cp" command is used to copy files and directories:
root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .
The "mkdir" command is used to create new directories:
root> mkdir archive
The "rmdir" command is used to delete directories:
root> rmdir archive
The "find" command can be used to find the location of specific files:
root> find / -name dbmspool.sql
root> find / -print | grep -i dbmspool.sql
The "/" flag represents the staring directory for the search. Wildcards such as "dbms*" can be used for the filename.
The "which" command can be used to find the location of an executable you are using:
oracle> which sqlplus
The "which" command searches your PATH setting for occurences of the specified executable.
File Permissions
The "umask" command can be used to read or set default file permissions for the current user:
root> umask 022
The umask value is subtracted from the default permissions (666) to give the final permission:
666 : Default permission
022 : - umask value
644 : final permission
The "chmod" command is used to alter file permissions after the file has been created:
root> chmod 777 *.log
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
Character eqivalents can be used in the chmod command:
root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.log
The "chown" command is used to reset the ownership of files after creation:
root> chown -R oinstall.dba *
The "-R" flag causes the command ro recurse through any subdirectories.
OS Users Management
The "useradd" command is used to add OS users:
root> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user
- The "-G" flag specifies the primary group.
- The "-g" flag specifies the secondary group.
- The "-d" flag specifies the default directory.
- The "-m" flag creates the default directory.
- The "-s" flag specifies the default shell.
The "usermod" command is used to modify the user settings after a user has been created:
root> usermod -s /bin/csh my_user
The "userdel" command is used to delete existing users:
root> userdel -r my_user
The "-r" flag removes the default directory.
The "passwd" command is used to set, or reset, the users login password:
root> passwd my_user
The "who" command can be used to list all users who have OS connections:
root> who
root> who | head -5
root> who | tail -5
root> who | grep -i ora
root> who | wc -l
- The "head -5" command restricts the output to the first 5 lines of the who command.
- The "tail -5" command restricts the output to the last 5 lines of the who command.
- The "grep -i ora" command restricts the output to lines containing "ora".
- The "wc -l" command returns the number of lines from "who", and hence the number of connected users.
Process Management
The "ps" command lists current process information:
root> ps
root> ps -ef | grep -i ora
Specific processes can be killed by specifying the process id in the kill command:
root> kill -9 12345
uname and hostname
The "uname" and "hostname" commands can be used to get information about the host:
root> uname -a
OSF1 oradb01.lynx.co.uk V5.1 2650 alpha
root> uname -a | awk '{ print $2 }'
oradb01.lynx.co.uk
root> hostname
oradb01.lynx.co.uk
Error Lines in Files
You can return the error lines in a file using:
root> cat alert_LIN1.log | grep -i ORA-
The "grep -i ORA-" command limits the output to lines containing "ORA-". The "-i" flag makes the comparison case insensitive. A count of the error lines can be returned using the "wc" command. This normally give a word count, but the "-l" flag alteres it to give a line count:
root> cat alert_LIN1.log | grep -i ORA- | wc -l
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
Remove Old Files
The
find
command can be used to supply a list of files to the
rm
command:
find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;
Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:
sed -e 's/^M$//' filename > tempfile
The newly created tempfile should have the ^M character removed.
Run Commands As Oracle User From Root
The following scripts shows how a number of commands can be run as the "oracle" user the "root" user:
#!/bin/ksh
su - oracle <
This is often necessary where CRON jobs are run from the root user rather than the oracle user.
Compress Files
In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the
gzip
or the
compress
commands. The
gzip
command results in a compressed copy of the original file with a ".gz" extension. The
gunzip
command reverses this process:
gzip myfile
gunzip myfile.gz
The
compress
command results in a compressed copy of the original file with a ".Z" extension. The
uncompress
command reverses this process:
compress myfile
uncompress myfile
General Performance
vmstat
$ vmstat 5 3Displays 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)
- r : in run queue
- b : blocked for resources (I/O, paging etc.)
- w : runnable but swapped
- memory (Reports on usage of virtual and real memory)
- swap : swap space currently available (Kbytes)
- free : size of free list (Kbytes)
- page (Reports information about page faults and paging activity (units per second)
- re : page reclaims
- mf : minor faults
- pi : Kbytes paged in
- po : Kbytes paged out
- fr : Kbytes freed
- de : anticipated short-term memory shortfall (Kbytes)
- 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)
- in : (non clock) device interupts
- si : system calls
- cs : CPU context switches
- cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
- us : user time
- si : system time
- cs : idle time
CPU Usage
sar
$ sar -u 10 8Reports 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 2Reports 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 -21rDisplays 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';
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.
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
Cluster Wide CRON Jobs On Tru64
On clustered systems cron is node-specific. If you need a job to fire once per cluster, rather than once per node you need an alternative approach to the standard cron job. One approach is put forward in the HP best practices document (), but in my opinion a more elegant solution is proposed by Jason Orendorf of HP Tru64 Unix Enterprise Team ().
In his solution Jason creates a file called /bin/cronrun with the following contents:
#!/bin/ksh
set -- $(/usr/sbin/cfsmgr -F raw /)
shift 12
[[ "$1" = "$(/bin/hostname -s)" ]] && exit 0
exit 1
This script returns TRUE (0) only on the node which is the CFS serving cluster_root.
All cluster wide jobs should have a crontab entry on each node of the cluster like:
5 * * * /bin/cronrun && /usr/local/bin/myjob
Although the cron jobs fire on all nodes, the "/bin/cronrun &&" part of the entry prevents the script from running on all nodes except the current CFS serving cluster_root.
NFS Mount (Sun)
The following deamons must be running for the share to be seen by a PC:
- /usr/lib/nfs/nfsd -a
- /usr/lib/nfs/mountd
- /opt/SUNWpcnfs/sbin/rpc.pcnfsd
To see a list of the nfs mounted drives already present type:
exportfs
First the mount point must be shared so it can be seen by remote machines:
share -F nfs -o ro /cdrom
Next the share can be mounted on a remote machine by
root using:
mkdir /cdrom#1
mount -o ro myhost:/cdrom /cdrom#1
NFS Mount (Tru64)
On the server machine:
If NFS is not currently setup do the following:
- Application Manager -> System Admin -> Configuration -> NFS
- Select the "Configure system as an NFS server" option.
- Accept all defaults.
Create mount point directory:
mkdir /u04/backup
Append the following entry to the "/etc/exports" file:
/u04/backup
Make sure the correct permissions are granted on the directory:
chmod -R 777 /u04/backup
On the client machine:
If NFS is not currently setup do the following:
- Application Manager -> System Admin -> Configuration -> NFS
- Select the "Configure system as an NFS client" option.
- Accept all defaults.
Create mount point directory:
mkdir /backup
Append an following entry to the "/etc/fstab" file:
nfs-server-name:/u04/backup /backup nfs rw,bg,intr 0 0
Finally, mount the fileset:
mount /backup
At this point you can start to use the mount point from your client machine. Thanks to Bryan Mills for his help with Tru64.
PC XStation Configuration
Download the CygWin setup.exe from .
Install, making sure to select all the X11R6 (or XFree86 in older versions) optional packages.
If you need root access add the following entry into the /etc/securettys file on each server:
:0
From the command promot on the PC do the following:
set PATH=PATH;c:cygwinbin;c:cygwinusrX11R6bin
XWin.exe :0 -query
The X environment should start in a new window.
Many Linux distributions do not start XDMCP by default. To allow XDMCP access from Cygwin edit the "/etc/X11/gdm/gdm.conf" file. Under the "[xdmcp]" section set "Enable=true".
If you are starting any X applications during the session you will need to set the DISPLAY environment variable. Remember, you are acting as an XStation, not the server itself, so this variable must be set as follows:
DISPLAY=:0.0; export DISPLAY
Useful Profile Settings
The following .profile settings rely on the default shell for the user being set to the Korn shell (/bin/ksh).
The backspace key can be configured by adding the following entry:
stty erase "^H"
The command line history can be accessed using the [Esc][k] by adding the following entry:
set -o vi
Auto completion of paths using a double strike of the [Esc] key can be configured by adding the following entry:
set filec
Useful Files
Here are some files that may be of use:
Path |
Contents |
/etc/passwd |
User settings |
/etc/group |
Group settings for users. |
/etc/hosts |
Hostname lookup information. |
/etc/system |
Kernel parameters for Solaris. |
/etc/sysconfigtab |
Kernel parameters for Tru64. |