UNIX for DBAs and Oracle APPS DBAs

UNIX Commands	Description : UNIX for DBAs Basic and Advance commands example	Example : *** Few of the command here are really dangerous , Please be very careful to run these commands**** UNIX Command for Expert users ***
tar	Create tape archives and add or extract files Note: There is no "untar" linux / unix command. Backup Oracle applications file system In this example , I am using tar command to create the zipped archive of Oracle applications file system (Including appltop,common top, ias oracle home, 806 oracle home So many ways to use it.	This is how you can create tape archives , (tar + gzip saves lot of space) tar -cvf - 8.0.6 |gzip -c > 8.0.6.tar.gz tar -cvf - appl |gzip -c > appl.tar.gz tar -cvf - comn |gzip -c > comn.tar.gz tar -cvf - iAS |gzip -c > iAS.tar.gz This is how you can restore the files to normal files from archives. nohup gunzip -c 806.tar.gz |tar -xvf - >806.out & nohup gunzip -c comn.tar.gz |tar -xvf - >comn.out & nohup gunzip -c iAS.tar.gz | tar -xvf - > ias.out& nohup gunzip -c appl.tar.gz | tar -xvf - >appl.out & If ssh is enables between the servers then you can can use this command nohup tar -cf - 10.2.0.1| ssh server "(cd /opt/oracle/product/10.2.0.1 && tar -xvf –)" & tar cf - home|(cd /u01/oracle/product ; tar xvf – ) If remsh is enabled nohup tar -cf - . | remsh server -l username 'cd /u01/oracle/11.5.10; tar -xf -' & cd fromdir ; tar cf - . | ( cd todir ; tar xf – ) If rsh is enabled. /usr/sbin/tar -cEpf - .| /usr/bin/rsh -l username remoteserver "(cd /db1/oracle/appltop && /usr/sbin/tar -xEvpf -)" /usr/bin/tar -cEpf - . | /usr/bin/rsh -l username remoteserver "(cd /oracle/erpapp && /usr/bin/tar -xEvpf -)"
find		Create  a backup with .bak and search and replace the occurrence of sun with night find . -exec perl -p -i.bak -e 's/sun/night/g' {} \;  find . -name '*.bak' -exec rm {} \; To remove all the files under /usr/oracle which end with .tmp  find /usr/oracle -name "*.tmp" -print -exec rm -f {} \; To list all files under /usr/oracle which are modified within a week.  find /usr/oracle -mtime -7 -print To compress all files which end with .dmp and are more than 1 MB.  find . -size +1073741824c -name "*.*" –print find . -name "*.log" -size +1048576c -mtime +7 -print -exec compress {} \;
kill –9	Killing all applmgr process in a single command	ps -ef | grep applmgr | grep -v grep | awk '{print $2}' | xargs kill -9 {}\
crontab	This is utility at UNIX which helps you to scheduling the jobs in UNIX	############################################################################### #    crontab guide: #             *  *  *  *  * #             |  |  |  |  | #             |  |  |  |  day of week (0=Sunday  ==>  6=Saturday) #             |  |  |  month of year  (1=January ==> 12=December) #             |  |  day of month      (1         ==> 31         ) #             |  hour of day          (0=12AM    ==> 23=11PM    ) #             minute of hour          (0         ==> 59         ) ################################################################################
ls	ls is very basic command in UNIX can be used to list the file List filename with extension .trc and created on May 22 , Be careful to run this from the correct directory	rm `ls -tlr *.trc | grep "May 22"|awk '{print $9}'`
UNIX Commands	Basic UNIX command ls : List file and directory cd : Change Directory cp : Copy files mv : To move the file rm : to remove the files pwd : to find out present working directory cat: The cat utility reads each file in sequence and writes it on the standard output. cut: The cut utility to cut  out  columns  from  a  table  or fields  from  each line of a file head: The head utility copies the first number of  lines  of  each  filename  to  the  standard output. tail: The tail utility copies the named file to the standard  output  beginning  at  a designated place. echo: The echo utility writes its arguments, separated  by  BLANKs and  terminated  by  a  NEWLINE, to the standard output.  exit: exit will cause the calling shell or shell  script  to  exit with  the  exit  status  specified by n. break- shell built-in function to escape from  within  a controlling while, for, foreach, or until loop wc: The wc utility  reads  one  or  more  input  files  and,  by default,  writes the number of newline characters, words and bytes contained in each input file to the standard output. expr: The expr utility evaluates the  expression  and  writes  the result  to  standard  output. Unix commands continued grep: The grep utility searches  text  files  for  a  pattern  and prints  all lines that contain that pattern. read: The read utility will  read  a  single  line  from  standard input. sort: The sort command sorts lines of all the named files together and writes the result on the standard output. test: The test utility evaluates the condition and  indicates  the result  of the evaluation by its exit status. exit status 0=true and 1=false uniq: The uniq utility will read an input file comparing  adjacent lines,  and write one copy of each input line on the output. touch: The touch utility sets the access and modification times  of each  file.  The  file  operand  is  created  if it does not already exist.  date: The date utility writes the date and time to standard output or attempts to set the system date and time. By default, the current date and time will be written. crontab: The crontab utility manages a user's access with  cron by copying, creating, listing, and removing cron- tab files.	ls –ltr ,ls –la , ls –l cd /abc/xyz cp /abc/xyz.txt  /abc/xyz1.txt  , cp –R /abc/* /xyz/ mv abc xyz rm abc.txt , rm –rf  (Recurcive and force) pwd cat abc.txt cut head –10 alert.log tail –50 alert.log , tail –f echo $ORACLE_HOME exit break ls | wc –l ps –ef | grep oracle du -sk *|sort –n
ps	man ps on UNIX/LINUX for more info	Best of PS command. 1. Load test: ps -ef|grep applmgr|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE do print $LINE | awk '{ print $3 }' > abc.txt echo $CPUTIME done ***with*** prstat -u applmgr -n 10 -s cpu prstat -u applmgr -n 10 -s cpu | cut -c52-60 prstat -u applmgr -n 10 -s cpu -P 1,2 1 1 | cut -c54-58 |q 2. We were recently working on a project to which requires me to identify Oracle Home and Listener Home used by the instance. Server was hosting around 8 databases and one ASM instance and was installed with 6 Oracle Homes of same version. You may think that why these many oracle home, answer is business can't afford a downtime but they can afford extra space. They asked us to create four node RAC which would spread across two different physical location and were connected through ASM and high end optic fibre network. To restart / reconfigure any of the instances / listener / asm instance we need to target the right oracle home, but since the processes were already started, identifying the oracle home was biggest challenge. Here is a command to find out oracle home or asm home or listener home used by oracle process. ps -ef|grep pmon|grep -v grep|awk {'print $2'}|xargs -t -I {} ps eauwww {} ps -ef|grep tns|grep -v grep|awk {'print $2'}|xargs -t -I {} ps eauwww {} ps -ef|grep cssd|grep -v grep|awk {'print $2'}|xargs -t -I {} ps eauwww {} 3.  export APP_PASS=password ps -auxwwee | grep APP_PASS ps can dump the ENVIRONMENT as well. very handy trick to know, can be useful to see a processes environment 4 ps -ef | grep applmgr | grep -v grep | awk '{print $2}' | xargs kill -9 {}\ 5. List listeners with lot of other information including ports netstat -a | grep -i listen | egrep "com.15[2-9][0-9]|com.16[0-2][0-9]" | cut -d . -f 1,5 | awk '{print substr($1,1,8) "\t" substr($1,10,4)-1521}' 6. ps –ef | grep inherit  : to list all the lsiteners of the server 7 ps –ef | grep FNDLIBR : list all concurrent manager processes ps –ef | grep CPMGR ps –ef | grep http ps –ef | grep oracle ps –ef | grep applmgr 8. ps -ef  grep "/OAS10g/developemnt" awk -F " " '{print "kill -9 " $2}' > a.sh 9. To find %CPU use by a user : e.gUsage  cpu.sh applmgr #!/usr/bin/ksh #name : cpu.sh for para1 in $* do ps -afe -o user -o pcpu -o comm -o args grep "$para1\ " >>aa.lst if [ "$para1" = "all" ] then tcpu=`ps -afe -o user -o pcpu -o comm -o args sort -k2n awk '{print $2}' grep -v 0.0 sed 's/$/+/g' xargs echo sed 's/+$//' xargs echo 0 bc` else tcpu=`ps -afe -o user -o pcpu -o comm -o args grep "$para1\ " grep -v grep sort -k2n awk '{print $2}' grep -v 0.0 sed 's/$/+/g' xargs echo sed 's/+$//' xargs echo 0 bc` fi echo "Total % of Cpu consumed by $para1 = $tcpu" done
unzip	to unzip the file	unzip -t \*.zip >integrity.txt , To check if the .zip is in correct format
find	find files	find *.out | xargs rm , find . –name *.out | xargs rm find *.log | xargs rm
How can I SELECT information into my vi-file?	vi editor	How can I SELECT information into my vi-file? When using vi to edit SQL*Plus or Pro*C code, sometimes one need to insert a table definition or data values into the file. You can simply open a new line, put 'desc EMP' (or a SELECT statement) into it and type:         :.,.!sqlplus -s system/password ... automatically output from your statement arrives in the vi buffer for cutting and pasting. You might even like to consider piping the output through grep, awk, sed or perl.
rsync	rsync for appltop	rsync -vrl --rsh=ssh --size-only --rsync-path=/opt/local/bin/rsync --exclude-from=rsync_exclude_file $1 $2 Exclude file: cat rsync_exclude_file ### Log files ### + /log/ - **/log/** - **.log ## out files ### + /out/ - **/out/** - **.out ### req files ### + /req/ **.req ### Old files ### + /old/ - **.old +/bak/ - **.bak - /core

0403-057 Syntax error at line 24 : `<' is not matched.

Error:   0403-057 Syntax error at line 24 : `<' is not matched.

Shell script :
.
.
.
sqlplus  << EOJ
.
.
.  
"some space "EOJ

Fix :

Verify that there is no space before before the  'EOJ'  or 'EOF' (Closing side)...it works.

Hiding password in shell Scripts requires oracle connection.

Hiding password in shell Scripts requires oracle connection.


1. User identified externally

SQL> show parameter prefix

NAME TYPE VALUE
------------------------------------ ----------- -----
os_authent_prefix string OPS$
SQL>

SQL> create user ops$sandeep identified externally;
User created.
SQL>

User sandeep will be able to connect as
sqlplus /

You can use the OS user to connect to Database.

2. Use Oracle wallet.

Oracle gives us the option of using a secure external password store where the Oracle login credentials are stored in a client-side Oracle wallet. This allows scripts to contain connections using the "/@db_alias" syntax.

Create a wallet and wallet entry for database connection.
Identify the wallet location
set the wallet location as TNS_ADMIN
export TNS_ADMIN=
create the sqlnet.ora file
Create the tnsnames.ora file with the wallet entries

learn about mkstore to create wallet.


3. print password | sqlplus apps

4. Put the passwords in some OS file with 700 permissions. You can have you scripts read the password from script.
cat passfile.sh
MYLOGIN=username/password

cat myshell.sh
#!/usr/bin/ksh
ksh passfile.sh
sqlplus << EOF
$MYLOGIN
select sysdate from dual;
exit
EOF



Oracle Wallet is my favourite one out of these.

All About UNIX vi command

a. cursor movements (items below are sometimes called objects):
h - left one character
l - right one character
j - down one line
k - up one line
w - right one word
b - back one word
$ - to the end of line
0 - to the beginning of the line
) - right one sentence
( - left one sentence
} - right one paragraph
{ - left one paragraph

Ctrl-F - forward one page
Ctrl-B - back one page
G - go to (without arguments, go to end of file)

b. deleting:
d - delete
then add one of the cursor movement symbols to
show what should be deleted, i.e.:
d$ - delete to end of line
d0 - delete to the beginning of the line
d} - delete to the end of paragraph
dd - delete delete (delete the whole line)
x - delete character cursor is on
c. other basic commands:
r - replace one character
ZZ - save and exit (hold down shift and press "z" twice)
y - yank (copy into temporary buffer)
then add cursor movement symbol to show what should be
copied, for example: y) - copy to the end of sentence
Y - yank line cursor is on
p - paste below cursor line (deleted or copied text)
P - paste above cursor line
u - undo last editing command
/sometext - search for "sometext"
d. any command can take numeric argument before the name of "object", .e.:
5dd - delete 5 lines beginning with cursor line (or) d5d - same
2dw - delete two words (or) d2w - delete two words
c3w - change 3 words
3Ctrl-B - move up three pages
1G - go to the first line
e. external commands can be performed on the selected text (in lines)
if command is started with "!", i.e.:
!}fmt - reformat paragraph to 72 columns
f. command line (sometimes called "ex mode"):
:
g. from the command line a "set" command can be executed to
customize editing environment, i.e.:
:set all - will show the state of all options
:set number - will show on the screen numbers of all lines
:set autoindent // obvious
h. from the command line operations can be performed on the range of ines,
i.e.:
:18,24 del - delete from line 18 to line 24
:23,48 copy 17 - block from line 23 to 48 copy to line 17
:2,17 move 92 - block from line 2 to 17 move to line 92
i. from the command line any external UNIX command can be performed on
the range of lines if line range is superseded by "!":
:11,16! sed -e "s/^/\/\*/" -e "s/$/\*\//"
(the command above wraps the block of text with
"C" style comments - /* text */. It can be done
easier, but this is an example)
:14,19! sort -r +3
(sort the table in reverse order by fourth column)
j. file manipulation from the command line:
:r somefile - read in "somefile"
:x - save and exit (if file is "Read Only", this command will
exit without saving)
:wq - write and quit (same as above)
:w - write (save) if the file permissions allow it
:w! - save file even if it is read-only as long as we own it
:w somefile - save this file as "somefile"
:q - quit without saving
:q! - quit without saving if changes were made
k. text input commands (all require "Esc" to terminate):
i - insert text before the character cursor is on
I - insert text at the beginning of the line
a - append (insert text after the character cursor is on)
A - append text to the end of the line
c - change (replace previous text with new one)
takes arguments just like the delete command - it is
a fast and powerful way of changing original text -
much more so than typical "overwrite"
R - start overwriting text
o - start entering text at the beginning of the new line
below the cursor
O - start entering text at the beginning of the new line
above the cursor
l. if in doubt, press "Esc"

Array in shell script.

Creating number of directories and subdirectories is kind of often required. Setting up Array in shell scripts really helps..how ?

Here is it.

set -A ARR a b c d e f g h i j k l m n o p
i=0
while [ $i -lt 12 ]
do
curdir=${ARR[$i]}
cd $curdir
cd scripts
print " Doind something" $curdir
mkdir/any other unix command..which you want to do in all sub directories
cd ../..
(( i=i+1 ))
done

trace os process

Tracing executables has helped me a lot in the past in some difficult scenarios. Tracing is really helpful. This is interesting to read this.


The following platforms support a trace utility that can be used to
identify what a process is doing:

O/S Version Trace Utility
-----------
-------------------------------------------------------
Sun Solaris 2.x, truss, e.g.:
Unixware 7.0
$ truss -aefo

HP/UX 11 tusc, e.g.:
@ (Download via:
http://eigen.ee.ualberta.ca/hppd/hpux/Sysadmin/ )

$ tusc -afpo

IBM AIX 4.x trace, e.g.:
Log on as root.
# trace -a -L 4000000 -T 2000000 -o ./trace.out;
# owsctl start admin
# trcstop
# trcrpt -O exec=on,pid=on trace.out > trcrpt.1

Or if present

sctrace, e.g.:
$ sctrace -Amo

$ prpt -p

Note: 'sctrace' has been discontinued by Veritas
Software
Corporation Inc. (formerly The Kernel
Group Inc)
as off Jan. 8, 2002.

Linux strace, ktrace/kdump, ltrace e.g.:

$ strace -fo

SGI IRIX 6.x par, e.g.:

$ par -siSSo

Compaq Tru64 Unix trace, e.g.:
@ (Download via
ftp://ftp.digital.com/pub/Digital/alpha-trace-1.25.tar.Z )

$ trace -fo

Sequent Dynix/PTX truss, e.g.:

$ truss -aefo

Data General DG/UX dg_trace, e.g.:
R4.20MU06
$ dg_strace -fxto

[] [-p
]




Related metalink documents

<1812.1>Getting a Stack Trace from a CORE file
<28588.1>Using Truss / Trace on Unix