Sujeet APPS DBA: LINUX / UNIX Command for DBA

Command to Check the RAM Size

ORACLE:/home/oracle>free -m

total used free shared buffers cached

Mem: 998 976 22 0 56 540

-/+ buffers/cache: 379 619

Swap: 4502 77 4425

ORACLE:/home/oracle>dmesg | grep RAM

BIOS-provided physical RAM map:

RAMDISK driver initialized: 16 RAM disks of 16384K size 1024 blocksize

ORACLE:/home/oracle>vmstat

procs ----memory----swap--io--system-- ----cpu----

r b swpd free buff cache si so bi bo in cs us sy id wa

0 0 78976 27396 57228 547856 0 0 16 32 575 359 3 0 97 0

ORACLE:/home/oracle>vmstat 2 10

procs -----memory---swap--io----system-- ----cpu----

r b swpd free buff cache si so bi bo in cs us sy id wa

0 0 78976 30396 58040 543516 0 0 16 32 575 357 3 0 97 0

0 0 78976 30252 58040 543516 0 0 00 1014 218 0 0 100 0

0 0 78976 30252 58040 543516 0 0 02 4 1016 171 0 0 100 0

0 0 78976 30252 58048 543508 0 0 0166 1038 200 0 0 100 0

0 0 78976 30252 58048 543508 0 0 00 1012 184 0 0 99 0

0 0 78976 30252 58048 543508 0 0 024 1014 201 0 0 100 0

0 0 78976 30252 58052 543504 0 0 030 1015 194 0 0 100 0

0 0 78976 30316 58052 543764 0 0 048 1021 181 0 0 100 0

0 0 78976 30252 58060 543756 0 0 032 1015 198 1 0 100 0

0 0 78976 30252 58068 543748 0 0 032 1015 213 0 0 100 0

and top command also shows up free space

2.to check free disk space

ORACLE:/home/oracle>df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda1 20161172 7177644 11959388 38% /

/dev/sda3 50394996 4415128 43419912 10% /d01

/dev/sda2 50394996 1476324 46358716 4% /d02

/dev/sda6 28344028 77888 26826316 1% /d03

none 511344 0 511344 0% /dev/shm

ORACLE:/home/oracle>df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 20G 6.9G 12G 38% /

/dev/sda3 49G 4.3G 42G 10% /d01

/dev/sda2 49G 1.5G 45G 4% /d02

/dev/sda6 28G 77M 26G 1% /d03

none 500M 0 500M 0% /dev/shm

3.uptime - Tell how long the system has been running.

ORACLE:/home/oracle>uptime

18:38:23 up 7:40, 9 users, load average: 0.28, 0.13, 0.10

4.Display Number of CPUs in Linux

To see the number of CPUs on a Linux server, you can cat the /proc/cpuinfo file.

cat /proc/cpuinfo grep processor

ORACLE:/home/oracle>cat /proc/cpuinfo

processor : 0

vendor_id : GenuineIntel

cpu family : 6

model : 7

model name : Intel(R) Core(TM)2 Duo CPU E8200 @ 2.66GHz

stepping : 6

cpu MHz : 2666.830

cache size : 6144 KB

physical id : 0

siblings : 2

core id : 0

cpu cores : 2

fdiv_bug : no

hlt_bug : no

f00f_bug : no

coma_bug : no

fpu : yes

fpu_exception : yes

cpuid level : 10

wp : yes

flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est tm2 xtpr

bogomips : 5336.09

which - The "which" command displays the full path of (shell) commands.

which zip
/oracle/db/11.2/bin/zip

cp - The "cp" command is used to copy files and directories

cp [option] SOURCE DEST

cp file1.txt file2.txt
cp * /archive (the source = * ---> from current directory; the destination = /archive )
cp /archive/* . (the source =/archive/* ;

the destination is = . ---> the current directory)

mv - The "mv" command is used to move or rename files and directories

mv file1.txt file2.txt
mv * /archive (the source = * ---> from current directory; the destination = /archive )
mv /archive/* . (the source =/archive/* ;

the destination is = . ---> the current directory)

rm - The "rm" command is used to delete files and directories

ls
Dir1 file1.txt
rm file1.txt
ls
Dir1
rm -R Dir1
ls

You can use -R in order to remove a directory.

cd - The "cd" command is used to change directories

/home/oracle/myDir>pwd
/home/oracle/myDir
/home/oracle/myDir>cd ../
/home/oracle>cd myDir
/home/oracle/myDir>

mkdir - The "mkdir" command is used to create new directories

mkdir Mydir

find - The "find" command can be used to find the location of specific files

find $ORACLE_HOME -name listener.ora
/oracle/db/11.2/network/admin/samples/listener.ora

umask - The "umask" command can be used to set the creation file permisions for an (OS) user

The umask value is subtracted from the default permissions (666) to give the final/ real permission:

666 : Default permission
002 : - umask value
664 : final permission

chmod - The "chmod" command can be used to change the file permisions

chmod 777 myfile.txt

Owner      Group      The others    Permission

   7               7               7 read + write + execute
   6               6               6 write + execute
   5               5               5 read + execute
   4               4               4 read only
   2               2               2 write only
   1               1               1 execute only

chown - The "chown" command is used to change the ownership of files

chown oracle:dba tnsnames.ora

chown -R oracle:dba /oracle/base

ps - The "ps" command lists current process information

ps -ef | grep smon

kill - The "kill" command stop a specific OS process

kill -9 3009

3009 is the process ID

scp secure copy

ORACLE:/home/oracle>scp $ORACLE_HOME/dbs/initstby.ora oracle@10.11.12.7:/db/oracle/product/10.2.0/dbs/initstby.ora

oracle@10.11.12.7's password:

initstby.ora 100% 1120 1.1KB/s 00:00

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 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 022

The umask value is subtracted from the default permissions (666) to give the final permission.

666 : Default permission

022 : - umask value

644 : final permiss
i

The "chmod" command is used to alter file permissions after the file has been created.

root> chmod 777 *.log

wner Group World Permission

O========= ========= ========= ======================

7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute

) 5 (g+Rx) 5 (o+Rx) read + execute

4 (u+r)

6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute

5 (u+R
x 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

ora

root> who | wc -l

root> who | grep -i

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 }'

adb01.lynx.co.uk

oradb01.lynx.co.uk

root> hostname

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

E_SUFFIX=`date +"%y""%m""%d""%H""%M"`

T /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log

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

ORACLE_SID=LIN1; export ORACLE_SID

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

EOF

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

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 995244 24392 799656 0 0 6302 160 1221 1962 10 10 62 18 0

0 0 0 1060608 24372 739080 0 0 1334 63 1018 1571 14 11 66 10
0

0 0 0 992376 24400 799784 0 0 1 28 992 1886 3 2 95 0 0

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

0289148

-/+ buffers/cache: 2627948 5550936

Swap: 10289148 0

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

Device: tps kB_read/s kB_wrtn/s kB_

2.02 0.23 0.51 0.78 0.00 96.46

read kB_wrtn

-0 13.60 100.31 62.99 1792386 1125524

sda 9.23 100.55 62.99 1796672 1125538

d
mm-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

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

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

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

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

002: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

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

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

0Average: all 1.48 0.00 1.04 0.14 0.00 97.34

mpstat

Reports processor related statistics.

# mpstat 10 2

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

1:59:57 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s

002:00:07 PM all 1.21 0.00 0.90 0.20 0.00 0.00 0.00 97.69 980.50

verage: all 0.95 0.00 0.65 0.10 0.00 0.05 0.00 98.24 977.14

02:00:17 PM all 0.70 0.00 0.40 0.00 0.00 0.10 0.00 98.79 973.77

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

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

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

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

oracle 20 0 605m 34m 30m S 9.8 1.7 0:00.30 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

293
3888 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

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

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 oracl
ee

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

4 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ks

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
/oftirqd/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,

spid,

a.program,
sid,

a.serial#

$process b

WHERE a

FROM v$session a,

v.paddr = b.addr

id';

AND spid = '&
p

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:

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

----- --------------

0-23

day of mo

minute 0-59

hour
nth 1-31

month 1-12

are Sunday)

user Valid OS user

day of week 0-7 (both 0 and

7mmand 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 (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

"$(/bin/hostname -s)" ]] && exit 0

exit 1

[[ "$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.

Samba/CIFS Mount (Linux)

Create a directory to use for the mount point.

# mkdir /host

Add the following line to the "/etc/fstab" file.

//192.168.0.4/public /host cifs rw,credentials=/root/.smbcred,uid=500,guid=500 0 0

Create a file called "/root/.smbcred" with the following contents.

username=myuser

password=mypassword

Change the permissions on the credentials file.

# chmod 600 /root/.smbcred

Mount the share.

# mount /host

PC 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>:0

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

Sujeet APPS DBA

LINUX / UNIX Command for DBA

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database