Unix for the DBA

How to kill all similar processes with single command (in this case opmn)

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory

find . -print |grep -i test.sql

Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

Display the number of CPU’s in Solaris

psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l
Aix : lsps -a

Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''

Show mount points for a disk in AIX

lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM

45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)

15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)

15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Real Application Clusters (RAC) Pocket Reference

Real Application Clusters (RAC) Pocket Reference Guide

By ckim

Get the latest relevant RAC information for Viskosity Technologies’ Real Application Clusters (RAC) Pocket Reference Guide.
Provided by Charles Kim, Oracle ACE Director

ASM Pocket Reference

ASM Pocket Reference Guide

By ckim

Get the latest relevant ASM information from Viskosity Technologies’ Automatic Storage Management (ASM) Pocket Reference Guide.

Provided by Charles Kim, Oracle ACE Director

srvctl commands

SRVCTL:

srvctl command target [options]

commands: enable|disable|start|stop|relocate|status|add|remove|

modify|getenv|setenv|unsetenv|config

targets: database/db|instance/inst|service/serv|nodeapps|asm|listener

srvctl -help or srvctl -v

srvctl -V -- prints version

srvctl version: 10.2.0.0.0 (or) srvctl version: 11.0.0.0.0

srvctl -h -- print usage

srvctl status service –h

Database:

srvctl add database -d db_name -o ORACLE_HOME [-m domain_name][-p spfile] [-A name|ip/netmask]

[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}]

[-s start_options] [-n db_name] [-y {AUTOMATIC|MANUAL}]

srvctl remove database -d db_name [-f]

srvctl start database -d db_name [-o start_options] [-c connect_str | -q]

srvctl start database -d db_name [-o open]

srvctl start database -d db_name -o nomount

srvctl start database -d db_name -o mount

srvctl start db -d prod

srvctl stop database -d db_name [-o stop_options] [-c connect_str | -q]

srvctl stop database -d db_name [-o normal]

srvctl stop database -d db_name -o transactional

srvctl stop database -d db_name -o immediate

srvctl stop database -d db_name -o abort

srvctl status database -d db_name [-f] [-v] [-S level]

srvctl status database -d db_name -v service_name

srvctl status database -d hrms

srvctl enable database -d db_name

srvctl disable database -d db_name

srvctl config database

srvctl config database -d db_name [-a] [-t]

srvctl modify database -d db_name [-n db_name] [-o ORACLE_HOME] [-m domain_name] [-p spfile]

[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}] [-s start_options] [-y {AUTOMATIC|MANUAL}]

srvctl modify database -d hrms -r physical_standby

srvctl modify db -d RAC -p /u03/oradata/RAC/spfileRAC.ora -- moves parameter file

srvctl getenv database -d db_name [-t name_list]

srvctl setenv database -d db_name {-t name=val[,name=val,...]|-T name=val}

srvctl unsetenv database -d db_name [-t name_list]

Instance:

srvctl add instance –d db_name –i inst_name -n node_name

srvctl remove instance –d db_name –i inst_name [-f]

srvctl start instance -d db_name -i inst_names [-o start_options] [-c connect_str | -q]

srvctl start instance –d db_name –i inst_names [-o open]

srvctl start instance –d db_name –i inst_names -o nomount

srvctl start instance –d db_name –i inst_names -o mount

srvctl start instance –d prod -i prod3

srvctl stop instance -d db_name -i inst_names [-o stop_options] [-c connect_str | -q]

srvctl stop instance –d db_name –i inst_names [-o normal]

srvctl stop instance –d db_name –i inst_names -o transactional

srvctl stop instance –d db_name –i inst_names -o immediate

srvctl stop instance –d db_name –i inst_names -o abort

srvctl stop inst –d prod -i prod6

srvctl status instance –d db_name –i inst_names [-f] [-v] [-S level]

srvctl status inst –d racdb -i racdb2

srvctl enable instance –d db_name –i inst_names

srvctl disable instance –d db_name –i inst_names

srvctl modify instance -d db_name -i inst_name {-s asm_inst_name|-r} -- set a dependency of instance to ASM

srvctl modify instance -d db_name -i inst_name -n node_name -- move the instance

srvctl modify instance -d db_name -i inst_name -r -- remove the instance

srvctl getenv instance –d db_name –i inst_name [-t name_list]

srvctl setenv instance –d db_name [–i inst_name] {-t "name=val[,name=val,...]" | -T "name=val"}

srvctl unsetenv instance –d db_name [–i inst_name] [-t name_list]

Service:

srvctl add service -d db_name -s service_name -r pref_insts [-a avail_insts] [-P TAF_policy]

srvctl add service -d db_name -s service_name -u {-r "new_pref_inst" | -a "new_avail_inst"}

srvctl add service -d RAC -s PRD -r RAC01,RAC02 -a RAC03,RAC04

srvctl add serv -d CRM -s CRM -r CRM1 -a CRM3 -P basic

srvctl remove service -d db_name -s service_name [-i inst_name] [-f]

srvctl start service -d db_name [-s service_names [-i inst_name]] [-o start_options]

srvctl start service -d db_name -s service_names [-o open]

srvctl start service -d db_name -s service_names -o nomount

srvctl start service -d db_name -s service_names -o mount

srvctl stop service -d db_name [-s service_names [-i inst_name]] [-f]

srvctl status service -d db_name [-s service_names] [-f] [-v] [-S level]

srvctl enable service -d db_name -s service_names [–i inst_name]

srvctl disable service -d db_name -s service_names [–i inst_name]

srvctl config service -d db_name [-s service_name] [-a] [-S level]

srvctl config service -d db_name -a -- -a shows TAF configuration

srvctl config service -d WEBTST -s webtest PREF:WEBTST1 AVAIL:WEBTST2

srvctl modify service -d db_name -s service_name -i old_inst_name -t new_inst_name [-f]

srvctl modify service -d db_name -s service_name -i avail_inst_name -r [-f]

srvctl modify service -d db_name -s service_name -n -i preferred_list [-a available_list] [-f]

srvctl modify service -d db_name -s service_name -i old_inst_name -a avail_inst -P TAF_policy

srvctl modify serv -d PROD -s SDW -n -i I1,I2,I3,I4 -a I5,I6

srvctl relocate service -d db_name -s service_name –i old_inst_name -t target_inst [-f]

srvctl getenv service -d db_name -s service_name -t name_list

srvctl setenv service -d db_name [-s service_name] {-t "name=val[,name=val,...]" | -T "name=val"}

srvctl unsetenv service -d db_name -s service_name -t name_list

Nodeapps:

#srvctl add nodeapps -n node_name -o ORACLE_HOME -A name|ip/netmask[/if1[|if2|...]]

#srvctl add nodeapps -n lnx02 -o $ORACLE_HOME -A 192.168.0.151/255.255.0.0/eth0

#srvctl remove nodeapps -n node_names [-f]

#srvctl start nodeapps -n node_name -- Starts GSD, VIP, listener & ONS

#srvctl stop nodeapps -n node_name [-r] -- Stops GSD, VIP, listener & ONS

#srvctl status nodeapps -n node_name

#srvctl config nodeapps -n node_name [-a] [-g] [-o] [-s] [-l]

#srvctl modify nodeapps -n node_name [-A new_vip_address]

#srvctl modify nodeapps -n lnx06 -A 10.50.99.43/255.255.252.0/eth0

#srvctl getenv nodeapps -n node_name [-t name_list]

#srvctl setenv nodeapps -n node_name {-t "name=val[,name=val,...]"|-T "name=val"}

#srvctl unsetenv nodeapps -n node_name [-t name_list]

ASM:

srvctl add asm -n node_name -i asminstance -o ORACLE_HOME [-p spfile]

srvctl remove asm -n node_name [-i asminstance] [-f]

srvctl start asm -n node_name [-i asminstance] [-o start_options] [-c connect_str | -q]

srvctl start asm -n node_name -i asminstance [-o open]

srvctl start asm -n node_name -i asminstance -o nomount

srvctl start asm -n node_name -i asminstance -o mount

srvctl stop asm -n node_name [-i asminstance] [-o stop_options] [-c connect_str | -q]

srvctl stop asm -n node_name -i asminstance [-o normal]

srvctl stop asm -n node_name -i asminstance -o transactional

srvctl stop asm -n node_name -i asminstance -o immediate

srvctl stop asm -n node_name -i asminstance -o abort

srvctl status asm -n node_name

srvctl enable asm -n node_name [-i asminstance]

srvctl disable asm -n node_name [-i asminstance]

srvctl config asm -n node_name

srvctl modify asm -n node_name -i asminstance [-o ORACLE_HOME] [-p spfile]

Listener:

srvctl add listener -n node_name -o ORACLE_HOME [-l listener_name]

-- 11g command

srvctl remove listener -n node_name [-l listener_name] -- 11g command

srvctl start listener -n node_name [-l listener_names]

srvctl stop listener -n node_name [-l listener_names]

srvctl config listener -n node_name

source and reference:
http://satya-racdba.blogspot.com/2009/12/srvctl-commands.html

RAC Cheatsheet

RAC Cheatsheet
By Charles kim

• EDOCPRD1 > cluvfy -h
• USAGE:
• cluvfy [ -help ]
• cluvfy stage { -list | -help }
• cluvfy stage {-pre|-post} [-verbose]
• cluvfy comp { -list | -help }
• cluvfy comp [-verbose]
• EDOCPRD1 > oifcfg -help
• Name:
• oifcfg – Oracle Interface Configuration Tool.
• Usage: oifcfg iflist [-p [-n]]
• oifcfg setif {-node | -global} {/:}…
• oifcfg getif [-node | -global] [ -if [/] [-type ] ]
• oifcfg delif [-node | -global] [[/]]
• oifcfg [-help]
• – name of the host, as known to a communications network
• – name by which the interface is configured in the system
• – subnet address of the interface
• – type of the interface { cluster_interconnect | public | storage }
• EDOCPRD1 > ocrconfig -help
• Name:
• ocrconfig – Configuration tool for Oracle Cluster Registry.
• Synopsis:
• ocrconfig [option]
• option:
• -export [-s online]
• – Export cluster register contents to a file
• -import – Import cluster registry contents from a file
• -upgrade [ []]
• – Upgrade cluster registry from previous version
• -downgrade [-version ]
• – Downgrade cluster registry to the specified version
• -backuploc – Configure periodic backup location
• -showbackup – Show backup information
• -restore – Restore from physical backup
• -replace ocr|ocrmirror [] – Add/replace/remove a OCR device/file
• -overwrite – Overwrite OCR configuration on disk
• -repair ocr|ocrmirror – Repair local OCR configuration
• -help – Print out this help information
• EDOCPRD1 > crs_stat -h
• Usage: crs_stat [resource_name [...]] [-v] [-l] [-q] [-c cluster_member]
• crs_stat [resource_name [...]] -t [-v] [-q] [-c cluster_member]
• crs_stat -p [resource_name [...]] [-q]
• crs_stat [-a] application -g
• crs_stat [-a] application -r [-c cluster_member]
• crs_stat -f [resource_name [...]] [-q] [-c cluster_member]
• crs_stat -ls [resource_name [...]] [-q]
• crs_register –u resname
• crs_profile
• crs_relocate
• crs_start
• crs_stop
• crs_unregister
• NGSDVA > clscfg -h
• clscfg: EXISTING configuration version 3 detected.
• clscfg: version 3 is 10G Release 2.
• clscfg — Oracle cluster configuration tool
• This tool is typically invoked as part of the Oracle Cluster Ready
• Services install process. It configures cluster topology and other
• settings. Use -help for information on any of these modes.
• Use one of the following modes of operation.
• -install – creates a new configuration
• -upgrade – upgrades an existing configuration
• -downgrade – downgrades an existing configuration
• -add – adds a node to the configuration
• -delete – deletes a node from the configuration
• -local – creates a special single-node configuration for ASM
• -concepts – brief listing of terminology used in the other modes
• -trace – may be used in conjunction with any mode above for tracing
• WARNING: Using this tool may corrupt your cluster configuration. Do not
• use unless you positively know what you are doing.
• NGSDVA > crsctl
• Usage: crsctl check crs – checks the viability of the CRS stack
• crsctl check cssd – checks the viability of CSS
• crsctl check crsd – checks the viability of CRS
• crsctl check evmd – checks the viability of EVM
• crsctl set css – sets a parameter override
• crsctl get css – gets the value of a CSS parameter
• crsctl unset css – sets CSS parameter to its default
• crsctl query css votedisk – lists the voting disks used by CSS
• crsctl add css votedisk – adds a new voting disk
• crsctl delete css votedisk – removes a voting disk
• crsctl enable crs – enables startup for all CRS daemons
• crsctl disable crs – disables startup for all CRS daemons
• crsctl start crs – starts all CRS daemons.
• crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
• crsctl start resources – starts CRS resources.
• crsctl stop resources – stops CRS resources.
• crsctl debug statedump evm – dumps state info for evm objects
• crsctl debug statedump crs – dumps state info for crs objects
• crsctl debug statedump css – dumps state info for css objects
• crsctl debug log css [module:level]{,module:level} …
• – Turns on debugging for CSS
• crsctl debug trace css – dumps CSS in-memory tracing cache
• crsctl debug log crs [module:level]{,module:level} …
• – Turns on debugging for CRS
• crsctl debug trace crs – dumps CRS in-memory tracing cache
• crsctl debug log evm [module:level]{,module:level} …
• – Turns on debugging for EVM
• crsctl debug trace evm – dumps EVM in-memory tracing cache
• crsctl debug log res turns on debugging for resources
• crsctl query crs softwareversion [] – lists the version of CRS software installed
• crsctl query crs activeversion – lists the CRS software operating version
• crsctl lsmodules css – lists the CSS modules that can be used for debugging
• crsctl lsmodules crs – lists the CRS modules that can be used for debugging
• crsctl lsmodules evm – lists the EVM modules that can be used for debugging
• If necesary any of these commands can be run with additional tracing by
• adding a “trace” argument at the very front.
• Example: crsctl trace check css
• Clusterware check: log files in $ORA_CRS_HOME/nodename
• Crsctl check crs
• Crsctl check cssd
• Crsctl check crsd
• Crsctl check evmd
• Crsctl query crs softwareversion
• Crsctl query crs softwareversion node2
• Crsctl start crs
• Crsctl stop crs
• Crsctl debug log res “resname:level”
• Interconnect check:
• Oifcfg getif
• olsnodes
• disable auto reboot of aix nodes /etc/init*
• EDOCPRD1 > ls -lrt /etc/init*
• lrwxrwxrwx 1 root system 14 Feb 20 11:18 /etc/init -> /usr/sbin/init
• -rw-r–r– 1 root system 2914 Feb 22 16:19 /etc/inittab.orig
• -r-xr-xr-x 1 root system 3194 Feb 22 16:19 /etc/init.evmd
• -r-xr-xr-x 1 root system 36807 Feb 22 16:19 /etc/init.cssd
• -r-xr-xr-x 1 root system 4854 Feb 22 16:19 /etc/init.crsd
• -r-xr-xr-x 1 root system 2226 Feb 22 16:19 /etc/init.crs
• -rw-r–r– 1 root system 3093 Feb 22 21:58 /etc/inittab
• OCR check:
• Ocrcheck
• Ocrconfig –export /tmp/dba/exp_ocr.dmp –s online
• Ocrconfig –showbackup
• ocrdump
• VIP:
• Ifconfig –a
• Ifconfig en8 delete host1-vip
• Ifconfig en8 delete host2-vip
• Command = /apps/oracle/product/10.2.0/CRS/bin/racgons add_config ictcdb621:6200 ictcdb622:6200
• Command = /apps/oracle/product/10.2.0/CRS/bin/oifcfg setif -global en8/10.249.199.0:public en9/172.16.32.0:cluster_interconnect
• EDOCPRD1 > srvctl -h
• Usage: srvctl [-V]
• Usage: srvctl add database -d -o [-m ] [-p ] [-A /netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-n ] [-y {AUTOMATIC | MANUAL}]
• Usage: srvctl add instance -d -i -n
• Usage: srvctl add service -d -s -r “” [-a ""] [-P ]
• Usage: srvctl add service -d -s -u {-r “” | -a “”}
• Usage: srvctl add nodeapps -n -o -A /netmask[/if1[|if2|...]]
• Usage: srvctl add asm -n -i -o [-p ]
• Usage: srvctl config database
• Usage: srvctl config database -d [-a] [-t]
• Usage: srvctl config service -d [-s ] [-a] [-S ]
• Usage: srvctl config nodeapps -n [-a] [-g] [-o] [-s] [-l]
• Usage: srvctl config asm -n
• Usage: srvctl config listener -n
• Usage: srvctl disable database -d
• Usage: srvctl disable instance -d -i “”
• Usage: srvctl disable service -d -s “” [-i ]
• Usage: srvctl disable asm -n [-i ]
• Usage: srvctl enable database -d
• Usage: srvctl enable instance -d -i “”
• Usage: srvctl enable service -d -s “” [-i ]
• Usage: srvctl enable asm -n [-i ]
• Usage: srvctl getenv database -d [-t ""]
• Usage: srvctl getenv instance -d -i [-t ""]
• Usage: srvctl getenv service -d -s [-t ""]
• Usage: srvctl getenv nodeapps -n [-t ""]
• Usage: srvctl modify database -d [-n ] [-m ] [-p ] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s ] [-y {AUTOMATIC | MANUAL}]
• Usage: srvctl modify instance -d -i -n
• Usage: srvctl modify instance -d -i {-s | -r}
• Usage: srvctl modify service -d -s -i -t [-f]
• Usage: srvctl modify service -d -s -i -r [-f]
• Usage: srvctl modify service -d -s -n -i [-a ] [-f]
• Usage: srvctl modify asm -n -i -p
• Usage: srvctl relocate service -d -s -i -t [-f]
• Usage: srvctl remove database -d [-f]
• Usage: srvctl remove instance -d -i [-f]
• Usage: srvctl remove service -d -s [-i ] [-f]
• Usage: srvctl remove nodeapps -n “” [-f]
• Usage: srvctl remove asm -n [-i ] [-f]
• Usage: srvctl setenv database -d {-t =[,=,...] | -T =}
• Usage: srvctl setenv instance -d [-i ] {-t “=[,=,...]” | -T “=”}
• Usage: srvctl setenv service -d [-s ] {-t “=[,=,...]” | -T “=”}
• Usage: srvctl setenv nodeapps -n {-t “=[,=,...]” | -T “=”}
• Usage: srvctl start database -d [-o ] [-c | -q]
• Usage: srvctl start instance -d -i “” [-o ] [-c | -q]
• Usage: srvctl start service -d [-s "" [-i ]] [-o ] [-c | -q]
• Usage: srvctl start nodeapps -n
• Usage: srvctl start asm -n [-i ] [-o ] [-c | -q]
• Usage: srvctl start listener -n [-l ]
• Usage: srvctl status database -d [-f] [-v] [-S ]
• Usage: srvctl status instance -d -i “” [-f] [-v] [-S ]
• Usage: srvctl status service -d [-s ""] [-f] [-v] [-S ]
• Usage: srvctl status nodeapps -n
• Usage: srvctl status asm -n
• Usage: srvctl stop database -d [-o ] [-c | -q]
• Usage: srvctl stop instance -d -i “” [-o ] [-c | -q]
• Usage: srvctl stop service -d [-s "" [-i ]] [-c | -q] [-f]
• Usage: srvctl stop nodeapps -n
• Usage: srvctl stop asm -n [-i ] [-o ] [-c | -q]
• Usage: srvctl stop listener -n [-l ]
• Usage: srvctl unsetenv database -d -t “”
• Usage: srvctl unsetenv instance -d [-i ] -t “”
• Usage: srvctl unsetenv service -d [-s ] -t “”
• Usage: srvctl unsetenv nodeapps -n -t “”
• source and reference:
• http://www.dbaexpert.com/blog/2007/07/rac-cheatsheet/