ls -lt | grep "^-" | awk '{
if($8 ~ /:/)
   $8=2011
Date_count=$6" "$7", "$8
freq[Date_count]++}
END {for (date in freq) printf "%s\t%d\n", date, freq[date] }' | sort

I often use this script when oracle archive volume fills up and I want to locate number of archive generated every day.

OUTPUT

Oct 1, 2011     3
Oct 2, 2011     3
Oct 3, 2011     4
Oct 4, 2011     24
Oct 5, 2011     48
Oct 6, 2011     101
Oct 7, 2011     39

I normally have access to unix/linux systems through ssh (port 22) but firewall access is often disabled for ports like TNS (1521/1526), Emagent(1158), Grid  (5500). How do you connect to those ports if you don’t have direct access through firewall ? … Use SSH tunneling.

1.  Locate Source (Windows Desktop), Destination  Unix server IP address and port number you wish to connect at Destination

I am trying to connect to destination oracle server (192.168.1.100) using TNS port 1529

2. Open putty and add following configuration

Add server and port details

C:\>telnet localhost 1529
Connecting To localhost…Could not open connection to the host, on port 1529: Connect failed

3. Now startup putty session and ssh login at Remote server.

run “netstat -an” on windows desktop to see any sessions with port 1529 are listed

Now try “telnet localhost 1529” to see if it’s all working as it should.

4. Configure connection detail to port on your local machine which will create a tunnel to the destination server.

I am using TORA so I have directed port 1529 at my local machine , this is very important.

I am now able to connect to Target Oracle database thru my ssh tunnel successfully.

5. Let’s  use “netstat -anp”  at server to see connections from source desktop.

192.168.1.100 (Remote Oracle Server)  &  192.168.1.121 (my Desktop)

[oracle@~]$ netstat -anp | grep 192.168.1.121
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.168.1.100:22    ::ffff:192.168.1.121:3970    ESTABLISHED –
tcp        0      0 ::ffff:192.168.1.100:22    ::ffff:192.168.1.121:3807    ESTABLISHED –

6. At desktop run “netstat -an”  to see established sessions.

p=$(lsof -t -i :1159); echo $p $(tr ‘\0’ ‘\n’ < /proc/$p/cmdline | tail -1)
4066 /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_Server1.oracledbasupport.co.uk_TEST/config/server.xml

p=$(lsof -t -i :1160); echo $p $(tr ‘\0’ ‘\n’ < /proc/$p/cmdline | tail -1)
8121 /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_Server1.oracledbasupport.co.uk_Dev/config/server.xml

I need to remind myself on what machine & directory I am working on constantly. The easiest way to do so would be setting PS1 prompt under bash shell.

For example,
Name of Server : Server1
Pwd : /opt/IBM/WebSphere/AppServer/profiles/Profile01/Node
TAG : [Dev] Uatf]

export TAG=Dev/Uatf/Test

export PS1=‘${HOSTNAME:0:10}-$(echo $PWD|awk “{print substr(\\$1,length(\\$1)-24,25)}”)-${TAG:0:4}# ‘

returned  Server1-/logs/uatf_server_member1-Dev#

To Display HOSTNAME In caps do

export PS1=’$(echo ${HOSTNAME:0:10}|tr “[a-z]” “[A-Z]”)-$(echo $PWD|awk “{print substr(\\$1,length(\\$1)-24,25)}”)-${TAG:0:4}# ‘

returned SERVER1-/logs/uatf_server_member1-Dev#

Comparing Files is one of very common task as a DBA, System Administrator. There are tonnes of Oracle,Websphere,linux configuration files. Often I have to compare one server to another and locate changes between environments.

Recently one of my websphere server broke down. Despite my good efforts I couldn’t revive it so I had to restore it from a backup.  Then came the task to compare the websphere confiuration between good and bad. When I looked at $WAS_HOME/bin/backupconfig , it backed up more than 400 files and carrying one to one comparison is no way possible.  I used following script to locate the difference.

#!/usr/bin/perl
# file_compare.pl
# Purpose: compare two files and show differences
# usage: file_compare.pl filename1 filename2

use strict;
use warnings;

my $file1 = shift or die “filename missing \n”;
my $file2 = shift or die “filename missing \n”;

open (FILE1, “< $file1”) or die “Can not read file $file1: $! \n”;
my @file1_contents = <FILE1>; # read entire contents of file
close (FILE1);

open (FILE2, “< $file2”) or die “Can not read file $file2: $! \n”;
my @file2_contents = <FILE2>; # read entire contents of file
close (FILE2);

my $length1 = $#file1_contents; # number of lines in first file
my $length2 = $#file2_contents; # number of lines in second file

if ($length1 > $length2) {
# first file contains more lines than second file
my $counter2 = 0;
foreach my $line_file1 (@file1_contents) {
chomp ($line_file1);

if (defined ($file2_contents[$counter2])) {
# line exists in second file
chomp (my $line_file2 = $file2_contents[$counter2]);

if ($line_file1 ne $line_file2) {
print “\nline ” . ($counter2 + 1) . ” \n”;
print “< $line_file1 \n” if ($line_file1 ne “”);
print “— \n”;
print “> $line_file2 \n\n” if ($line_file2 ne “”);
}
}
else {
# there is no line in second file
print “\nline ” . ($counter2 + 1) . ” \n”;
print “< $line_file1 \n” if ($line_file1 ne “”);
print “— \n”;
print “> \n”; # this line does not exist in file2
}
$counter2++; # point to the next line in file2
}
}
else {
# second file contains more lines than first file
# or both have equal number of lines
my $counter1 = 0;
foreach my $line_file2 (@file2_contents) {
chomp ($line_file2);

if (defined ($file1_contents[$counter1])) {
# line exists in first file
chomp (my $line_file1 = $file1_contents[$counter1]);

if ($line_file1 ne $line_file2) {
print “\nline ” . ($counter1 + 1) . ” \n”;
print “< $line_file1 \n” if ($line_file1 ne “”);
print “— \n”;
print “> $line_file2 \n” if ($line_file2 ne “”);
}
}
else {
# there is no line in first file
print “\nline ” . ($counter1 + 1) . ” \n”;
print “< \n”; # this line does not exist in file1
print “— \n”;
print “> $line_file2 \n” if ($line_file2 ne “”);
}
$counter1++; # point to next line in file1
}
}

Output

$perl compare_files.pl notworking.lst working.lst  | more

line 1
< 4     notworking/Cell/pmirm.xml
—
> 4     working/Cell/pmirm.xml
line 2
< 4     notworking/Cell/resources-pme.xml
—
> 4     working/Cell/resources-pme.xml
line 3
< 32    notworking/Cell/resources.xml
—
> 32    working/Cell/resources.xml

Objectives

The objectives of this document are to:

Record the setup and configuration of the 9i Oracle Standalone environment.

Read more…

Table of Contents
1.1 Objectives
1.2 Scope
2 System Configuration
2.1 Architecture
3 System Configuration
3.1 Machine Configuration
3.2 External/Shared Storage
4 Oracle Pre-Installation tasks
4.1 Redhat Pre-Requisite
4.2 Copy Oracle 10.2.0.1 software onto server
4.3 Unpack Files
4.4 Download Patches
4.5 Check kernel and update rpm files
4.6 Creating Required Operating System Groups and Users
4.7 Oracle required directory creation
4.8 Set Kernel Parameters
4.9 Create Oracle Profile
5 Oracle Software Configuration
5.1 Directory Structure
5.2 Download ASM packages
5.3 ASM package install
5.4 Configuring and Loading ASM
5.5 Creating ASM Disks
5.6 Installation
5.6.1 Database Only Installation
5.6.2 Installing the Listener
5.6.3 DBCA : Creating an ASM /Database Instance

Read more…

netstat displays the  contents  of  various  network-related  data structures in  depending on the options selected.

netstat  <option/s>

multiple options can be given at one time.

Options

-a – displays the state of all sockets.
-r – shows the system routing tables
-i – gives statistics on a per-interface basis.
-m – displays information from the network memory buffers. On Solaris, this shows statistics
         forSTREAMS
-p [proto] – retrieves statistics for the specified protocol
  -s – shows per-protocol statistics. (some implementations allow -ss to remove fileds with a value of 0 (zero) from the display.)
-D – display the status of DHCP configured interfaces.
-n do not lookup hostnames, display only IP addresses.
-d (with -i) displays dropped packets per interface.
-I [interface] retrieve information about only the specified interface.
-v be verbose

This shows the output on a Solaris machine who’s IP address is 192.168.1.11 with a default router at 192.168.1.1

Results and Solutions:

A.) Network availability

The command as above is mostly useful in troubleshooting network accessibility issues . When  outside network is not accessible from a machine check the following

1. if the default router ip  address is correct

2.  you can ping it from your machine.

3. If router address is incorrect  it can be changed  with route add  commnad . See man route  for more info .

route command examples:
$route add default <hostname>
$route add 192.0.2.32  <gateway_name>

If the router address is correct but still you can’t ping it  there may be some  network cable /hub/switch problem  and you have to try and eliminate the faulty component .

B.) Network Response

$ netstat -i
Name     Mtu     Net/Dest     Address     Ipkts     Ierrs     Opkts     Oerrs     Collis     Queue
lo0     8232     loopback     localhost     77814     0     77814     0     0     0
hme0     1500     server1     server1     10658566     3     4832511     0     279257     0

This option is used to diagnose the network problems when  the connectivity is there but  it is slow in response .

Values to look at:

    * Collisions (Collis)
    * Output packets (Opkts)
    * Input errors (Ierrs)
    * Input packets (Ipkts)

The above values will give information to workout

i.  Network collision rate as follows :

Network collision rate = Output collision counts / Output packets

Network-wide collision rate greater than 10 percent  will indicate

    *  Overloaded network,
    *  Poorly configured network,
    *  Hardware problems. 

ii.  Input packet error rate as follows :

Input Packet Error Rate = Ierrs / Ipkts.

If the input error rate is high (over 0.25 percent), the host is dropping packets. Hub/switch cables etc needs to be checked for potential problems.

C.  Network socket &  TCP Cconnection state

Netstat gives important   information about network socket and tcp state . This is very useful in
finding out the open , closed and  waiting network tcp connection .

Network states returned by  netstat are following :

     CLOSED               —-  Closed.  The socket  is  not  being used.
     LISTEN                 —-  Listening for incoming connections.
     SYN_SENT           —-  Actively trying to  establish  connection.
     SYN_RECEIVED  —- Initial synchronization of the connection under way.
     ESTABLISHED     —-  Connection has been established.
     CLOSE_WAIT      —-  Remote shut down; waiting  for  the socket to close.
     FIN_WAIT_1        —-  Socket closed; shutting  down  connection.
     CLOSING             —-  Closed,   then   remote   shutdown; awaiting acknowledgement.
     LAST_ACK          —-   Remote  shut  down,  then   closed ;awaiting acknowledgement.
     FIN_WAIT_2        —-  Socket closed; waiting for shutdown from remote.
     TIME_WAIT         —-  Wait after close for  remote  shutdown retransmission.

Example: #netstat -a

if  you see a lots of connections in FIN_WAIT state  tcp/ip parameters   have to be tuned  because the connections  are not being closed and they gets accumulating . After some time system may run out of resource . TCP parameter can be tuned to define a time out so that connections can be released and used by new connection.  

vmstat –  vmstat reports virtual memory statistics of   process, virtual memory, disk, trap, and CPU activity.

On multicpu systems , vmstat averages the number of CPUs  into  the  output. For per-process statistics .Without options, vmstat displays a one-line summary  of  the  virtual memory activity since the system was booted.

Basic synctax is vmstat  <options>   interval  count

option – let you specify the type of information needed such as paging  -p , cache   -c ,.interrupt -i  etc.

if no option is specified  information about   process , memory , paging , disk ,interrupts & cpu  is displayed  .

interval – is time period in seconds between two samples . vmstat   4  will give data at each 4 seconds interval.

count  – is the number of times the data is needed . vmstat 4   5   will give data at 4 seconds interval   5

             times.

The following command displays a summary of what the  system is doing every five seconds.

     example% vmstat 5

     procs  memory          page             disk      faults        cpu
     r b w swap  free re mf pi p fr de sr s0 s1 s2 s3  in  sy  cs us sy id
     0 0 0 11456 4120 1  41 19 1  3  0  2  0  4  0  0  48 112 130  4 14 82
     0 0 1 10132 4280 0   4 44 0  0  0  0  0 23  0  0 211 230 144  3 35 62
     0 0 1 10132 4616 0   0 20 0  0  0  0  0 19  0  0 150 172 146  3 33 64
     0 0 1 10132 5292 0   0  9 0  0  0  0  0 21  0  0 165 105 130  1 21 78

The fields of vmstat’s display are
    procs
            r     in run queue
            b     blocked for resources I/O, paging etc.
           w     swapped

    memory (in Kbytes)
             swap –  amount  of  swap   space   currently   available               
             free   – size of the free list

    page ( in units per second).
          re    page reclaims –  see  -S  option  for  how  this field is modified.
          mf    minor faults –  see  -S  option  for  how    this field is modified.
          pi    kilobytes paged in
          po    kilobytes paged out
          fr    kilobytes freed
          de    anticipated short-term memory shortfall (Kbytes)
          sr    pages scanned by clock algorithm

    disk  ( operations per second )
          There are  slots for up to four disks, labeled with a single letter and number.
          The letter indicates  the  type  of  disk  (s = SCSI, i = IPI, etc) . The number is 
          the logical unit number.

    faults
           in    (non clock) device interrupts
          sy    system calls
          cs    CPU context switches

    cpu –   breakdown of percentage usage of CPU  time.  On multiprocessors  this is an a
               verage across all processors.
          us    user time
          sy    system time
          id    idle time

Results and Solutions:

A.   CPU issues:

Following columns has to be watched to determine if there is any cpu issue

Processes in the run queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle time (cpu id)

     procs      cpu
     r b w    us sy  id
     0 0 0    4  14  82
     0 0 1    3  35  62
     0 0 1    3  33  64
     0 0 1    1  21  78

Problem symptoms:
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .
2.) if  this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.
3.) If  the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us)  system is facing shortage of CPU resources.
Resolution :
Resolution to these kind of issues involves tuning of application procedures  to make efficient use of cpu  and as a last resort increasing the cpu power or adding more cpu to the system.  

B.   Memory Issues:
Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage.
Resolution :
1. Tune the applications & servers to make  efficient use of memory and cache.
2. Increase system memory .
3. Implement priority paging in s in pre solaris 8 versions by adding line “set priority paging=1” in
    /etc/system. Remove this line if upgrading from Solaris 7 to 8 & retaining old /etc/system file.

./runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.11 or B.11.23.    Actual B.11.11
Passed

Checking swap space: must be greater than 500 MB.   Actual 32768 MB    Passed
Checking temp space: 48 MB available, 250 MB required.    Failed <<<<

Set a TEMP space variable to point at /u02/stage/tmpdir volume
$ echo $TMPDIR
/u02/stage/tmpdir
$ echo $TMP
/u02/stage/tmpdir
TMPDIR & $TMP Didn’t WORK

$ export TEMP=/u02/stage/tmpdir — Worked

$ ./runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.11 or B.11.23.    Actual B.11.11   Passed

Checking swap space: must be greater than 500 MB.   Actual 32768 MB    Passed
Checking Temp space: must be greater than 250 MB.   Actual 198722 MB    Passed

All installer requirements met.

 iostat   reports terminal and disk  I/O  activity and  CPU utilization.  The first line of output is for the  time period  since boot  &  each subsequent line is for  the  prior  interval . Kernel maintains  a number of counters to keep track of  the  values.

iostat’s activity class options default  to  tdc  (terminal,  disk, and CPU). If any other option/s are specified,  this  default is completely overridden i.e.  iostat -d will report only statistics about the disks.

iostat  <options>   interval  count

option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t  or -tdc ) .  x options gives the extended statistics .

interval –  is time period in seconds between two samples . iostat  4  will give data at each 4 seconds interval.

count  – is the  number of times the data is needed .  iostat 4 5   will give data at 4 seconds interval   5 times

 $ iostat -xtc 5 2
                          extended disk statistics       tty         cpu
     disk r/s  w/s Kr/s Kw/s wait actv svc_t  %w  %b  tin tout us sy wt id
     sd0   2.6 3.0 20.7 22.7 0.1  0.2  59.2   6   19   0   84  3  85 11 0
     sd1   4.2 1.0 33.5  8.0 0.0  0.2  47.2   2   23
     sd2   0.0 0.0  0.0  0.0 0.0  0.0   0.0   0    0
     sd3  10.2 1.6 51.4 12.8 0.1  0.3  31.2   3   31

[oracle@]$ iostat -dn 5  | grep data
Device:                  rBlk_nor/s   wBlk_nor/s   rBlk_dir/s   wBlk_dir/s   rBlk_svr/s   wBlk_svr/s
netapp:/vol/test_data         0.02         0.15         0.00         0.00      5985.39      1694.18
netapp:/vol/test_c_data       0.00         0.00         0.00         0.00      3975.35      2160.74
netapp:/vol/streams_data      0.05         0.43         0.00         0.00       230.63        45.43
netapp:/vol/test_data         0.00         0.00         0.00         0.00     70004.41        59.32
netapp:/vol/test_c_data       0.00         0.00         0.00         0.00       160.32       102.61
netapp:/vol/streams_data      0.00         0.00         0.00         0.00     12710.22     20754.31

The fields have the following meanings:
      disk    name of the disk
      r/s     reads per second
      w/s     writes per second
      Kr/s    kilobytes read per second
      Kw/s    kilobytes written per second
      wait    average number of transactions waiting for service (Q length)

      actv    average number of transactions  actively being serviced (removed  from  the queue but not yet
              completed)
      %w      percent of time there are transactions  waiting for service (queue non-empty)
      %b      percent of time the disk is busy  (transactions in progress)

Find files older than 5 days:    find . -mtime +5 -print | xargs ls -l | more
Remove files older than 5 days :  find . -mtime +5 -print | xargs rm

List files named “trust.p12” with their attributes

$ find . -name “*trust.p12” -type f -ls
738579    4 -rw-rw-r–   1 was61    was61        1586 Oct 23 04:36 ./dmgr/etc/trust.p12
738623    4 -rw-rw-r–   1 was61    was61        2730 Oct 23 02:02 ./dmgr/config/cells/Cell/trust.p12
738625    4 -rw-rw-r–   1 was61    was61        2730 Oct 23 02:02 ./dmgr/config/cells/Cell/nodes/Node01/trust.p12
1098642    4 -rw-rw-r–   1 was61    was61         850 Jul 13  2010 ./Node/etc/trust.p12
1098644    4 -rw-rw-r–   1 was61    was61        2730 Oct  7  2010 ./Node/config/cells/Cell/trust.p12
1098647    4 -rw-rw-r–   1 was61    was61        2730 Oct  7  2010 ./Node/config/cells/Cell/nodes/Node01/trust.p12

Print the number of blocks used by each directory

find . -type d -exec du -s {} \;

Find directory size recursively

$du -h –max-depth=10

8.1G    ./current/ABLXPORA01
153M    ./current/BAK/ABMWPSQL01/BDY_TVP_P

$ du -a | sort -rn | head
9646324 .
8532176 ./IBM
4008132 ./IBM/WebSphere
3753856 ./IBM/WebSphere/AppServer
3321980 ./IBM/HTTPServer
3003896 ./IBM/HTTPServer/Plugins

$ du -sk * | sort -nk 1
228     cdump
2468    stage
3924    metadata
5328    alert
286480  trace
6757680 incident

Print names of all files over 5,000 blocks (2,560,000) bytes.

du -sk * | sort -nk 1 | pg

List and sort out the files per their size

find $ORACLE_BASE -size +2000 -exec ls -s {} \; | sort -nr | more

Look for which big files have filled up a filesystem recently

find . -size +20000 -mtime -10 -ls  (10 here is days)

Find files which were modified either (<)  less than 1 week ago but more (>) than 2 weeks ago

find . \( -mtime +14 -o -mtime -7 \) -ls

Find and delete files older than 5 days

-rw-r—– 1 oracle oracle 2620416 Aug 30 23:16 1_2114_692896370.arc
-rw-r—– 1 oracle oracle 2620416 Sep  1 23:28 1_2161_692896370.arc
-rw-r—– 1 oracle oracle 2624512 Feb 14 00:18 1_21301_700768645.arc

find . -type f -mtime +15 -exec rm -f {} \;

-rw-r—– 1 oracle oracle 2624512 Feb 14 00:18 1_21301_700768645.arc

Find all files modified in last 10 days and  print them as <Size> <Filename>, I have used grep again to list only log files

$ find /opt -size +20000 -mtime -10 -ls |  awk ‘{print $7 ” ”  $11}’ | grep log
104857600 /opt/filestores/com.ibm.ws.sib/localhost_Node01.dev_server_member1-PRPC_Bus-1A01E8290042E159/log/Log
104857600 /opt/filestores/com.ibm.ws.sib/localhost_Node01.dev_server_member2-PRPC_Bus-DF5E2FA284B7341C/log/Log
147066880 /opt/logs/dev_server_member2/native_stderr.log

Find all files containing a string in filename only

$du -a | grep “string”

Find all files recursively with a “hostName” string:

grep -H -r “hostName=” /opt |  cut -d: -f1
/opt/IBM/WebSphere/localhost_Cell/nodes/localhost_Node01/serverindex.xml
/opt/IBM/WebSphere/localhost_Cell/nodes/localhost_Manager/serverindex.xml

Look for word 500 and print lines on screen  :

egrep -in “500” $HOME/today_access.log

Search for word “500” in files at /opt directory

find /opt | xargs grep -l 500

find . | xargs grep 'string'

I need to return only name of file from “ls -l”

-rwxr-xr-x 1 was61 web  4970 Jun 30  2009 startManager.sh
-rwxr-xr-x 1 was61 web  5026 Jun 30  2009 startNode.sh
ls -la | awk ‘{print $9}’
startManager.sh
startNode.sh

Get kill PID of sessions  : ps -ef | grep java | grep dev_server_member2 | awk ‘{print $2}’

This command will backup trace files and then delete them

tar -czvf /mnt/backup/Traces_`date +%Y%m%d_%H%M`.tgz   /opt/app/oracle/diag/rdbms/prod/trace/*.tr*   &&  find *.tr* -delete

Shell script to Find Files Recursively with a “string”

#!/bin/bash
#
# Author: Sagar PATIL
echo "Enter the FullPackage DIR path[/opt/IBM/WebSphere/AppServer/profiles/Profile01/Dmgr/config/cells......] "
read PKG_DIR
echo "Enter the Source Pattern to be replaced "
read SS
grep -H -r ${SS} ${PKG_DIR} |  cut -d: -f1

Shell script to Find & Replace a “string” Recursively

#!/bin/bash
# Author: Sagar PATIL

echo "Enter the FullPackage DIR path[/opt/IBM/WebSphere/AppServer/profiles/Profile01/Dmgr/config/cells......] "
read PKG_DIR
echo "Enter the Source Pattern to be replaced "
read SS
echo "Enter the Target Pattern "
read TS
list=`find $PKG_DIR -type f -name '*.xml'`  # I am only searching for files with extn xml, to search all files please remove name '*.xml'
for file in ${list}
do
#in the below command colon is a separator
sed "s:"${SS}":"${TS}":g" ${file} > /tmp/temp.txt
echo "File " ${file}
echo "Source Pattern " ${SS}
echo "Target Pattern " ${TS}
mv /tmp/temp.txt ${file}
done