WE PROVIDE ALL

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

•File Permissions

•OS User Management

•Process Management

•uname and hostname

•Error Lines in Files

•File Exists Check

•Remove Old Files

•Remove DOS CR/LFs (^M)

•Run Commands As Oracle User From Root

•Compress Files

•General Performance 

?vmstat

?free

?iostat

•CPU Usage 

?sar

?mpstat

?top

•Hide Passwords

•Automatic Startup Scripts on Linux

•CRON

•Cluster Wide CRON Jobs On Tru64

•NFS Mount (Sun)

•NFS Mount (Tru64)

•Samba/CIFS Mount (Linux)

•PC XStation Configuration

•Useful Profile Settings

•Useful Files

Basic File Navigation

The "pwd" command displays the current directory.

root> pwd

/u01/app/oracle/product/9.2.0.1.0The "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 -alThe "-a" flag lists hidden "." files. The "-l" flag lists file details.

The "cd" command is used to change directories.

root> cd /u01/app/oracleThe "touch" command is used to create a new empty file with the default permissions.

root> touch my.logThe "rm" command is used to delete files and directories.

root> rm my.log

root> rm -R /archiveThe "-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 archiveThe "rmdir" command is used to delete directories.

root> rmdir archiveThe "find" command can be used to find the location of specific files.

root> find / -name dbmspool.sql

root> find / -print | grep -i dbmspool.sqlThe "/" 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 sqlplusThe "which" command searches your PATH setting for occurrences 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 022The umask value is subtracted from the default permissions (666) to give the final permission.

666 : Default permission

022 : - umask value

644 : final permissionThe "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 onlyCharacter eqivalents can be used in the chmod command.

root> chmod o+rwx *.log

root> chmod g+r   *.log

root> chmod -Rx   *.logThe "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_userThe "-r" flag removes the default directory.

The "passwd" command is used to set, or reset, the users login password.

root> passwd my_userThe "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 oraSpecific processes can be killed by specifying the process id in the kill command.

root> kill -9 12345uname 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.ukError 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 -lFile 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

fiRemove 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 > tempfileThe 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 <<EOF

ORACLE_SID=LIN1; export ORACLE_SID

rman catalog=rman/rman@w2k1 target=/ cmdfile=my_cmdfile log=my_logfile append 

EOFThis 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.gzThe compress command results in a compressed copy of the original file with a ".Z" extension. The uncompress command reverses this process.

compress myfile

uncompress myfileGeneral 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

sar

Collect, report, or save system activity information.

# sar -u 10 8

Linux 2.6.32-100.0.19.el5 (ol5-112.localdomain) 06/27/2011

02:01:09 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle

02:01:19 PM       all      1.01      0.00      0.50      0.00      0.00     98.49

02:01:29 PM       all      2.72      0.00      2.62      0.10      0.00     94.56

02:01:39 PM       all      1.21      0.00      0.60      0.40      0.00     97.79

02:01:49 PM       all      1.00      0.00      0.60      0.10      0.00     98.29

02:01:59 PM       all      1.21      0.00      0.70      0.10      0.00     97.99

02:02:09 PM       all      1.01      0.00      0.40      0.10      0.00     98.49

02:02:19 PM       all      0.80      0.00      0.50      0.20      0.00     98.49

02:02:29 PM       all      2.92      0.00      2.42      0.10      0.00     94.56

Average:          all      1.48      0.00      1.04      0.14      0.00     97.34

#See the sar man page.

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

Cpu(s): 77.1%us, 22.6%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.3%hi,  0.0%si,  0.0%st

Mem:   2058872k total,   879072k used,  1179800k free,    23580k buffers

Swap:  4095992k total,        0k used,  4095992k free,   620116k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

 2882 oracle    20   0  610m  64m  56m R 24.9  3.2   0:02.20 oracle

 2927 root      20   0 90328 3832 2604 R 24.6  0.2   0:00.89 Xorg

 2931 oracle    20   0  605m  34m  31m R 11.5  1.7   0:00.35 oracle

 2933 oracle    20   0  605m  34m  30m S  9.8  1.7   0:00.30 oracle

 2888 oracle    20   0  614m  52m  40m S  6.9  2.6   0:00.78 oracle

 2935 oracle    20   0  604m  22m  20m S  6.2  1.1   0:00.19 oracle

 2937 oracle    20   0  604m  19m  17m R  4.6  1.0   0:00.14 oracle

 2688 oracle    -2   0  603m  15m  13m S  4.3  0.8   0:01.08 oracle

 2685 oracle    20   0  603m  15m  13m S  0.7  0.8   0:00.22 oracle

 2939 oracle    20   0  217m 4084 3504 R  0.7  0.2   0:00.02 oracle

 2698 oracle    20   0  604m  18m  16m S  0.3  0.9   0:00.17 oracle

 2702 oracle    20   0  609m  22m  14m S  0.3  1.1   0:00.17 oracle

 2704 oracle    20   0  618m  21m  19m S  0.3  1.1   0:00.21 oracle

 2714 oracle    20   0  603m  20m  18m S  0.3  1.0   0:00.18 oracle

    1 root      20   0 10364  704  588 S  0.0  0.0   0:00.36 init

    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd

    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0

    4 root      20   0     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0

    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0

    6 root      20   0     0    0    0 S  0.0  0.0   0:00.03 events/0

    7 root      20   0     0    0    0 S  0.0  0.0   0:00.00 cpuset

    8 root      20   0     0    0    0 S  0.0  0.0   0:00.00 khelper

    9 root      20   0     0    0    0 S  0.0  0.0   0:00.00 netns

#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/tigerChange the permissions to make sure the file is only visible to the owner.

$ chmod 600 /home/oracle/.scottcredNow 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.logAlternatively, 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

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>&1Cluster 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 (Using cron in a TruCluster Server Cluster), but in my opinion a more elegant solution is proposed by Jason Orendorf of HP Tru64 Unix Enterprise Team (TruCluster Clustercron).

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 1This 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/myjobAlthough 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.

exportfsFirst the mount point must be shared so it can be seen by remote machines.

share -F nfs -o ro /cdromNext the share can be mounted on a remote machine by root using.

mkdir /cdrom#1

mount -o ro myhost:/cdrom /cdrom#1NFS 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/backupAppend the following entry to the "/etc/exports" file.

/u04/backupMake sure the correct permissions are granted on the directory.

chmod -R 777 /u04/backupOn 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 /backupAppend an following entry to the "/etc/fstab" file.

nfs-server-name:/u04/backup     /backup         nfs rw,bg,intr 0 0Finally, mount the fileset.

mount /backupAt this point you can start to use the mount point from your client machine. Thanks to Bryan Mills for his help with Tru64.

Samba/CIFS Mount (Linux)

Create a directory to use for the mount point.

# mkdir /hostAdd the following line to the "/etc/fstab" file.

//192.168.0.4/public /host cifs rw,credentials=/root/.smbcred,uid=500,guid=500 0 0Create a file called "/root/.smbcred" with the following contents.

username=myuser

password=mypasswordChange the permissions on the credentials file.

# chmod 600 /root/.smbcredMount the share.

# mount /hostPC XStation Configuration

Download the CygWin setup.exe from http://www.cygwin.com.

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.

<client-name>:0From the command promot on the PC do the following.

set PATH=PATH;c:cygwinbin;c:cygwinusrX11R6bin

XWin.exe :0 -query <server-name>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=<client-name>:0.0; export DISPLAYUseful 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 viAuto completion of paths using a double strike of the [Esc] key can be configured by adding the following entry.

set filecUseful 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. 

For more information see:

•OS Backup Commands

•Oracle9i Administrator's Reference Release 2 (9.2.0.1.0) for UNIX Systems

Hope this helps. Regards Tim...

OS BACKUP COMMANDS:

OS Backup Commands

This article contains a summary of the operating system backup commands you might encounter whilst backing up Oracle databases.

•ntbackup

•tar

•cpio

•dd

•vdump, rvdump, vrestore and rvrestore

ntbackup

Under WindowsNT and Windows2000 filesystem backups are done using the ntbackup program. This is a GUI tool with easy to use wizards to get you started, but it is also accessible from the command line. The command line parameters differ between WindowsNT and Windows2000. Under WindowsNT a typical backup command would look like.

ntbackup backup c:\ /d "Daily Backup" /hc:on /l "C:\backup.log" /e /t normal /v

c:\                : The drive to backup.

/d "Daily Backup"  : The name of the backup set.

/hc:on             : Harware compression on.

/l "C:\backup.log" : Location of the logfile.

/e                 : Log exceptions only.

/t normal          : Backup type normal.

/v                 : Verify backup.Under Windows 2000 a similar command would look like.

ntbackup backup c:\ /D "Daily Backup" /HC:on /L:s /M normal /P DLT /V:yes /UM

c:\                : The drive to backup.

/D "Daily Backup"  : The name of the backup set.

/HC:on             : Harware compression on.

/L:s               : Summary data only in log.

/M normal          : Backup type normal.

/P DLT             : Media pool assignment (Backup/DLT).

/V:yes             : Verify backup.

/UM                : Unmanaged.The Windows2000 backup logs always appear in.

C:\Documents and Settings<user-name>Local Settings\Application Data\Microsoft\Windows NT\NTbackup\DataWhere user-name is the user who ran ntbackup.

The Windows2000 is rather troublesome regarding reuse of tapes. Using the following procedure should alleviate most of these problems.

•From the task bar click "Start" -> "Run..."

•Enter "ntbackup" and click "OK".

•From the ntbackup menu select "Tools" -> "Options" and click on the "General" tab.

•Check the "Always move new import mediato the Backup media pool" option and click "OK".

•Exit ntbackup.

If there are still issues you must right-click the appropriate tape drive in "Computer Management" and select the "Mark as clean" option. At this point the tape should be reused properly.

tar

The tar command can be used to backup and restore files to another filesystem or an offile storage device.

# Create archive.

cd /u01/app/oracle

tar -cvf /tmp/admin.tar admin

# Restore archive.

cd /tmp

tar -xvf admin.tarIf a full path is used during the archive creation the extract locations are fixed rather than relative. The process is similar when accessing a tape device except the destination is the mounted device.

# Mount and rewind the tape.

mt -f /dev/rmt/2m rew

# Create archive.

tar -cvf /dev/rmt/2m /u01/*

# Restore archive.

tar -xvf /dev/rmt/2mdd

The dd command is similar to the tar command.

# Mount and rewind the tape.

mt -f /dev/rmt/2m rew

# Create archive.

dd if=/u01/app/oracle/* of=/dev/rmt/2m BS=32K

# Restore archive.

dd if=/dev/rmt/2m of=/u01/app/oracle BS=32Kcpio

The cpio command deals with the standard input so filesystem paths must be piped to it.

# Create archive.

cd /u01/app/oracle

find admin | cpio -oc > /tmp/admin.cpio

# Restore archive.

cd /tmp

cpio -idmv < admin.cpioIf a full path is used during the archive creation the extract locations are fixed rather than relative:

find /u01/app/oracle/admin | cpio -oc > /tmp/admin.cpiovdump, rvdump, vrestore and rvrestore

Full level 0 backup of a local filesystem (/u01) to a local device (/dev/tape/tape1_d6).

/sbin/vdump -0 -u -f /dev/tape/tape1_d6 /u01Full level 0 backup of a local filesystem (/u01) to a remote device (server2:/dev/tape/tape1_d6).

/sbin/rvdump -0 -u -f server2:/dev/tape/tape1_d6 /u01Restore a vdump or rvdump archive from a local device (/dev/tape/tape1_d6) to a local filesystem (/u01).

/sbin/vrestore -xf /dev/tape/tape1_d6 -D /u01Restore a vdump or rvdump archive from a remote device (server2:/dev/tape/tape1_d6) to a local filesystem (/u01).

/sbin/rvrestore -xf server2:/dev/tape/tape1_d6 -D /u01