UNIX Commands for DBAs

UNIX Commands for DBAs
———————–

This article contains a brief list of commands that most UNIX DBAs will need on a regular basis. Over time I’ve been adding more Linux-related entries.

–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 starting 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 occurrences of the specified executable.
File Permissions

See Linux Files, Directories and 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+rw) 6 (g+rw) 6 (o+rw) read + write
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

See Linux Groups and Users.

–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
——————

See Linux Process Management (ps, top, renice, kill).

–The “ps” command lists current process information.

# ps
# ps -ef | grep -i ora
# ps -ef | grep -i ora | grep -v grep
# ps -ef | grep -i [o]ra

–Specific processes can be killed by specifying the process id in the kill command.

# kill 12345
# kill -9 12345

–You can kill multiple processes using a single command by combining “kill” with the “ps” and “awk” commands.

# kill -9 `ps -ef | grep ora | awk ‘{print $2}’`

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

–Compress Files

See Linux Archive Tools (tar, star, gzip, bzip2, zip, cpio).

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

Reports virtual memory statistics.

# vmstat 5 3
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1060608 24372 739080 0 0 1334 63 1018 1571 14 11 66 10 0
0 0 0 995244 24392 799656 0 0 6302 160 1221 1962 10 10 62 18 0
0 0 0 992376 24400 799784 0 0 1 28 992 1886 3 2 95 0 0
#

See the vmstat man page.
–free

Reports the current memory usage. The “-/+ buffers/cache:” line represents the true used and free memory, ignoring the Linux file system cache.

# free
total used free shared buffers cached
Mem: 8178884 4669760 3509124 0 324056 1717756
-/+ buffers/cache: 2627948 5550936
Swap: 10289148 0 10289148
#

–iostat

Reports I/O statistics.

# iostat
Linux 3.2.10-3.fc16.x86_64 (maggie.localdomain) 03/19/2012 _x86_64_(4 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
2.02 0.23 0.51 0.78 0.00 96.46

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 9.23 100.55 62.99 1796672 1125538
dm-0 13.60 100.31 62.99 1792386 1125524
dm-1 0.02 0.08 0.00 1432 0

#

–CPU Usage

See Linux Process Management (ps, top, renice, kill).
sar

On Linux systems sar (System Activity Reporter) is probably one of the simplest and most versatile tools for reporting system utilization including CPU, memory, disk and network activity. It automatically collects system activity statistics when installed using the following command.

# yum install sysstat

The sar command syntax takes the following form.

# sar [options] [interval [count]]

The “options” parameters determine what is reported, which will be discussed later. The “interval” parameter indicates the time interval in seconds between samples. The “count” parameter indicates the number of samples that will be taken before the command ends. If “count” is omitted, the sampling will continue indefinitely. If both “interval” and “count” are omitted, the command will report the values from the 10 minute samples taken since the machine was last restarted.

As seen in the sar man page, there are lots of available options, but some starting points you may find interesting include:

CPU:
Basic CPU: sar [-u] [interval [count]]
Load Average: sar -q [interval [count]]
Memory:
Kernel Paging: sar -B [interval [count]]
Unused Memory: sar -r [interval [count]]
Swap Space: sar -S [interval [count]]
Disk:
Average Disk I/O: sar -b [interval [count]]
Disk I/O: sar -dp [interval [count]]
Network:
Network: sar -n DEV [interval [count]]
Network Errors: sar -n EDEV [interval [count]]

Here is an example of the output from a CPU report.

# sar -u 1 5
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

03:10:07 PM CPU %user %nice %system %iowait %steal %idle
03:10:08 PM all 0.00 1.01 23.23 75.76 0.00 0.00
03:10:09 PM all 0.00 1.02 35.71 63.27 0.00 0.00
03:10:10 PM all 0.98 3.92 35.29 59.80 0.00 0.00
03:10:11 PM all 0.00 1.03 29.90 69.07 0.00 0.00
03:10:12 PM all 0.00 2.00 35.00 63.00 0.00 0.00
Average: all 0.20 1.81 31.85 66.13 0.00 0.00
#

–mpstat

Reports processor related statistics.

# mpstat 10 2
Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

01:59:57 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
02:00:07 PM all 1.21 0.00 0.90 0.20 0.00 0.00 0.00 97.69 980.50
02:00:17 PM all 0.70 0.00 0.40 0.00 0.00 0.10 0.00 98.79 973.77
Average: all 0.95 0.00 0.65 0.10 0.00 0.05 0.00 98.24 977.14
#

See the mpstat man page.
–top

Displays top tasks.

# top
top – 13:58:17 up 2 min, 1 user, load average: 2.54, 1.11, 0.41
Tasks: 160 total, 6 running, 154 sleeping, 0 stopped, 0 zombie
#

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’;

See the top man page.

–Hide Passwords

You may be required to use passwords in scripts calling Oracle tools, like SQL*Plus, Export/Import and RMAN etc. One method to remove the credentials from the script itself is to create a credentials file to hold them. In this case I’m using “/home/oracle/.scottcred”, which contains the following.

scott/tiger

Change the permissions to make sure the file is only visible to the owner.

$ chmod 600 /home/oracle/.scottcred

Now replace references to the credentials with the contents of the file.

$ expdp < /home/oracle/.scottcred schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Alternatively, consider using one of the following:

Secure External Password Store
OS Authentication

–Automatic Startup Scripts on Linux

This text has been replaced by a separate article here.
CRON

See CRON : Scheduling Tasks on Linux.

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
—-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.
/etc/sysctl.conf Kernel parameters for Linux.

Advertisements
Previous Post
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: