Linux Command for Oracle DBA

General Linux Commands

uptime : amount of time system has been up and load average for last 5, 10, 15 minutes.

last : log of system shutdowns

memory :
free -m : note : linux uses available memory for buffer and cache

cat /proc/meminfo

ipcs -m : shared memory segments.

ipcrm -M : deletes shared memory segments.

lsof -p PID : gives details of open files, ports, sharedmem for a process.

System Monitor

OSWatcher :
start : ./startOSW.sh
stop : ./stopOSW.sh

graph the data:
export PATH=$ORACLE_HOME/jdk/bin:$PATH
java -jar oswg.jar -i archive
where archive is archive directory where all the traces went.


top : gives snapshot of the system every 2 seconds: CPU usage, memory and swap usage.
provides load avg for last 1, 5 and 15 minutes.

load_avg should be less than number of CPU on the system.

vmstat : usage of virtual memory.
vmstat 3 10 ( every 3 seconds 10 times == virtual memory status for last 30 seconds )

strace : reports system calls and signals of a process
strace -p 1234 -o 1234.txt

netstat -i : interface statistics

iostat -p sda 30 : read/write stats for /dev/sda at every 30 secs forever.

mpstat 3 10 : Look at iterrupts and cpu usage.

ifconfig eth[n] : provides dropped packets, HW address

netstat -s : summary statistics

netstat -a : lists all established and listening socket related information.
--inet -a option will only give network related information

/etc/services lists ports for well-known services.

ss : socket statistics.

nmon : performance monitoring tool from IBM.

tcpdump : It allows you to look at network traffic.
tcpdump -D : Lists interfaces
tcpdump -i 1 -w tcp.dump.file

tcpdump -r tcp.dump.file

sar -A -o sar.txt 10 30 : Capture all system related info. in sar.txt.
sar -f sar.txt : reads the sar.txt file
sar -u : reports cpu
sar -n : networks statistics
sar -b : IO statistics
(ksar is a popular graphing sar data tool).
.



Software Installation and Machine Info

To find the hostname and OS version:
uname -a
.
To find where a module is installed :
rpm -q _module_name_ { e.g. rpm -q libaio }
rpm -qa | grep _module_name_ { if you dont know the exact name of the module }
.
To install a module :
rpm -ivh _module_name_
for example:
rpm -ivh oracleasm* ( Installs ASMLIB RPM packages).
.
To list loaded device drivers:
lsmod
driver information is also provided in /var/log/messages.
.
To get a file from ftp server:
# wget ftp://ftp.mogumbo.com/pub/dest/of/file
.
TO install via yum ( works for centos/Red Hat, yum == yellow dog Updater, Modified ):
yum list mozilla
yum install mozilla
.

Installation commands:

To create a group:
# groupadd -g 4500 dba
# groupadd -g 4501 oinstall

To add oracle user:
# useradd -g oinstall -G dba -d /home/vkshukla -p passwd vkshukla

To verify id of a user:
# id vkshukla

To change passwd:
# passwd vkshukla

To change system configuration for Oracle
edit /etc/sysctl.conf
# sysctl -p

Important kernel parameters for oracle:
SHMMAX : max. size of one shared mem segment
SHMMNI : max. # of shared mem segments allowed in the system.
SHMSEG : max. # of shared mem segments one process can attach
SEMMNS : max # of semaphores in the system
.
file-max : maximum # of files for the entire system.
.
Network buffersize parameters:
net.core.rmem_default #bytes of the socket receive buffer
net.core.wmem_default #bytes of the socket send buffer
net.core.rmem_max # Maximum socket receive buffer size
net.core.wmem_max # Maximum socket send buffer size
.
Limit of open files per user:
/etc/security/limits.conf
.
To view the limits for the user:
ulimit -a
memlock : maximum amount of memory user can lock in memory.
.
Maximum asynchronous I/O kernel parameter :
/proc/sys/fs/aio-nr
.
To view shared memory or semaphores:
ipcs -m|sb
To remove semaphores:
ipcrm -s
To remove shared memory:
ipcrm -m
.
To create a directory:
# mkdir -p /u01/app/oracle
change ownership
# chown -R root:oinstall /u01
change security ( umast 002 )
# chmod -R 775 /u01
.
To set environment variable on login:
edit ~/.bash_profile where ~ stands for HOME directory (e.g. /home/vkshukla )
for example : PATH=$PATH:$HOME/bin
.
To set alias and function ( this gets executed when you open another Xterm )
edit ~/.bashrc

Important environment variables:

ORACLE_HOME: Oracle software install location
ORACLE_SID: system identifier for instance
ORA_CRS_HOME: clusterware software install location
TNS_ADMIN: network admin location - tnsnames.ora, listener.ora
PATH: must include $ORACLE_HOME/bin and $ORA_CRS_HOME/bin
NLS_LANG: AMERICAN_AMERICA.AL32UTF8
ORA_NLS10: $ORACLE_HOME/nls/data
LD_LIBRARY_PATH : $ORACLE_HOME/lib, $ORA_CRS_HOME/lib
( runtime shared library loader searches for shared libraries. )
CLASSPATH: ( Java loader searches List of directories and class libraries. )
JAVE_HOME, JRE_HOME : needed for jdbc and sqlj
JAVA_HOME: $ORACLE_HOME/jdk
JRE_HOME: $ORACLE_HOME/jre/bin
ORACLE_PATH, SQL_PATH: directory which sql*plus searched for sql scripts.
DISPLAY : servername:screen_number ( X windows display ).
TEMP, TMPDIR: /tmp where installation logs etc go.
.
Example .bash_profile which sets the above env variable:
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/8.1.7; export ORACLE_HOME
ORACLE_SID=prod1ora; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/8.1.7/bin; export PATH
CLASSPATH=.:/u01/app/oracle/product/8.1.7/jdbc/lib/classes111.zip; export CLASSPATH
LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.7/lib; export LD_LIBRARY_PATH
LD_ASSUME_KERNEL=2.2.5; export LD_ASSUME_KERNEL
umask 022
----End of File------
export ensures that all child shells inherit these environment variable.
.
umask 022 sets all file read-write permission to be 755(rwxr_xr_x).

Monitor and administration commands for DBA:

TO swith user :
su - oracle ( it will prompt for passwd unless you are root ).
.
To mount usbdisk:
mount /media/usbdisk/
.
To create a file with dd command:
dd if=/dev/zero of=/file/name bs=1M count=1024
To list files in reverse order of time :
ls -ltr
.
To list files on a particular date with > 1MB size:
$ ls -tlr *.trc | grep "Apr 22" | awk '{ if ($5 > 1048576) { print } }'
-rw-rw-r-- 1 oracle dba 4231767 Apr 22 19:58 yama_lgwr_8846.trc
$
To see ORA- error and 2 lines above it( to see date and tracefile name ) in alert log:
grep -i -b2 ora- alert_yama.log ( here ORACLE_SID = yama ).
.
To list processes
ps -ef
.
To kill database process to cause instance crash:
ps -ef | grep lgwr | awk '{print $2}' | xargs kill -9
.
To find out OS level messages/info/alerts:
cat /var/log/messages | more
dmesg | more
.
List network interface:
ifconfig -a
.
List message queue:
#ipcs -lq
.
For disks being used by the system:
cat /proc/scsi/scsi or dmesg ( as root )
.
To partition the disk:
parted /dev/sda or use fdisk /dev/sda
.
To list partitions:
fdisk -l /dev/sda
.
To set env on Linux using oraenv
. oraenv ( dot space makes changes with parent shell rather than only in subshell )

Install software in Linux:

( # means root prompt on shell )
# rpm -q software
# rpm -ivh software.rpm
# service software status
# service software start
.
To start/stop/restart/status a daemon ( for example nfsd )
service nfsd start
service nfsd stop
service nfsd restart
service nfsd status

To find out space usage:
df -kh .
.
To list cpu details:
cat /proc/cpuinfo
.
To list memory details:
cat /proc/meminfo
.
To list semaphores and shared memory:
ipcs -a
.
To make Oracle binary:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ipc_g ioracle ( please note that RAC uses UDP for cache fusion ).
.
To list patches applied on Oracle Binary:
$ORACLE_HOME/OPatch/opatch lsinventory ( It lists details of bug-fixes put in the patch).
.
If there are > 1 instances, how to map shared memory to instances:
on sql*plus as sysdba:
oradebug setmypid
oradebug ipc
now, match shmid from trace to outout of ipcs -a

Storage
To find out storage ( generally Linux uses SCSI protocol to access the storage ):
cat /proc/scsi/scsi
.
To list configured block devices:
ls -ld /sys/block/sd*
.
TO list partitions ( This provides major number, minor number and #blocks ).
cat /proc/partitions
please note that minor 0 corresponds to complete disk, 1 onwards are different partitions.
.
To find out the I/O thruput via orion
$ORACLE_HOME/orion -run dss -testname star
where star.lun contains the list of block devices.
.

ADVANCED:

TO find I/O scheduling :
cat /sys/block/sda/queue/scheduler ( policy is listed in [ ] bracket ).
To renice the priority of a process :
ionice -c1 -n4 -p PID

To check and modify runlevels

chkconfig: It manages symbolic links from /etc/rc.d/rc[0-6].d to /etc/rc.d/init.d.
To start crond on levels 2,3,4,5
chkconfig --levels 2345 crond on
.
To check runlevel information of services:
chkconfig --list ipmi
.
To disable a daemon from starting on any level:
chkconfig --del sendmail
find /etc/rc.d -name '*sendmail' -print /etc/rc.d/init.d/sendmail
.
chkconfig --levels 2345 crond on
.
Find all files with name *crond in directory /etc/rc.d and run file command on each find.
find /etc/rc.d -name '*crond' -exec file {} \;
.
To reload a xinetd:
xinetd[x=inter(net) daemon] manages all internet services e.g. ftp, rsh, finger, rlogin.
/etc/init.d/xinetd reload
.
To add oracle startup script:
add these comments at the top of oracle_start_script:
#chkconfig: 2345 80 05
#description: Oracle 8 Server
.
/sbin/chkconfig --add oracle_start_script
/sbin/chkconfig --list | grep oracle
.
For remote monitoring of Linux machine:

IPMI == Intelligent Platform management Interface ( standard for remote server management )
BMC == Baseboard management Controller ( It is an independent microprocessor with its power supply and network connection ).
To start/stop/status ipmi
service ipmi start/stop/status


How to set SSH:

mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa
cd .ssh
cat id_rsa.pub id_dsa.pub > authorized_keys
concatenate authorized_keys from all nodes to one file and copy that on all nodes.
.
if passphrase specified during key generation:
ssh-agent $SHELL
ssh-add
.
To avoid warning for X11 forwarding:
change /etc/ssh/ssh_config ( ForwardX11 yes->no )
service sshd restart
or
create file config in ~/.ssh dir:
Host *
ForwardX11 no
.
To check if the processor supports virtualization:
egrep '(vmx|svm)' /proc/cpuinfo
.