<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0"
	xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:atom="http://www.w3.org/2005/Atom"
	xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
	>

<channel>
	<title>AppCrawler</title>
	<atom:link href="http://appcrawler.com/wordpress/?feed=rss2" rel="self" type="application/rss+xml" />
	<link>http://appcrawler.com/wordpress</link>
	<description>Tying business metrics to IT excellence</description>
	<pubDate>Thu, 02 Sep 2010 13:52:37 +0000</pubDate>
	<generator>http://wordpress.org/?v=2.7.1</generator>
	<language>en</language>
	<sy:updatePeriod>hourly</sy:updatePeriod>
	<sy:updateFrequency>1</sy:updateFrequency>
			<item>
		<title>A simple example of a custom component managed by CRS</title>
		<link>http://appcrawler.com/wordpress/?p=730</link>
		<comments>http://appcrawler.com/wordpress/?p=730#comments</comments>
		<pubDate>Fri, 27 Aug 2010 21:41:28 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Oracle]]></category>

		<category><![CDATA[RAC]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=730</guid>
		<description><![CDATA[You can add custom components to CRS if you have a need.  You basically need a program (perhaps as simple as a shell script) that accepts three command line arguments:

start
stop
check

Below is an example of a shell script that you can register.
#!/bin/sh

if [ "$1" = "start" ]; then
  /home/oracle/test &#38;
elif [ "$1" = "stop" [...]]]></description>
			<content:encoded><![CDATA[<p>You can add custom components to CRS if you have a need.  You basically need a program (perhaps as simple as a shell script) that accepts three command line arguments:</p>
<ul>
<li>start</li>
<li>stop</li>
<li>check</li>
</ul>
<p>Below is an example of a shell script that you can register.</p>
<pre>#!/bin/sh

if [ "$1" = "start" ]; then
  /home/oracle/test &amp;
elif [ "$1" = "stop" ]; then
  PID=$(ps -ef | grep "/home/oracle/test" | grep -v grep | awk '{print $2}')
  if [ $PID ]; then
    kill -9 $PID
  fi
  exit 0
elif [ "$1" = "check" ]; then
  ps -ef | grep "/home/oracle/test" | grep -v grep  | grep -v check &gt; /dev/null
  RETURN=$?
  exit $RETURN
fi</pre>
<p>&#8230;with the program below called by the script above&#8230;</p>
<pre>#!/bin/sh

while true; do
  echo 1
  sleep 5
done</pre>
<p>You can then register the program by creating the following resource profile in a file named oclc.linux1.test.cap in the current directory&#8230;</p>
<pre>NAME=oclc.linux1.test
TYPE=application
ACTION_SCRIPT=/home/oracle/test.sh
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=30
DESCRIPTION=test application
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=linux1
OPTIONAL_RESOURCES=
PLACEMENT=restricted
RESTART_ATTEMPTS=20
SCRIPT_TIMEOUT=30
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d</pre>
<p>&#8230;which you finally register with the clusterware as follows&#8230;</p>
<pre>crs_register oclc.linux1.test -dir $(pwd)</pre>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=730</wfw:commentRss>
		</item>
		<item>
		<title>Using awk with group by functionality</title>
		<link>http://appcrawler.com/wordpress/?p=773</link>
		<comments>http://appcrawler.com/wordpress/?p=773#comments</comments>
		<pubDate>Thu, 12 Aug 2010 18:33:48 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Shell scripting]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=773</guid>
		<description><![CDATA[You can use the following example if you need to total numbers over a group in a given file.
We first show our sample file&#8230;

14:30:57 oracle@emgrid01 ~ >cat list.txt
steve:61
steve:14
becky:57
steve:19
jenna:69
stephen:57
maddie:54
jenna:53
abby:41
jenna:21
jenna:66
jenna:64
stephen:53
stephen:26
jenna:77
steve:46
maddie:39
steve:32
abby:77
jenna:97

&#8230;and then show the totals using awk&#8230;

14:30:59 oracle@emgrid01 ~ >awk -F ":" '{a[$1]+=$2} END {for (i in a) {print i,a[i]}}' list.txt
maddie 93
abby 118
jenna 447
becky 57
steve 172
stephen 136
14:31:01 oracle@emgrid01 [...]]]></description>
			<content:encoded><![CDATA[<p>You can use the following example if you need to total numbers over a group in a given file.</p>
<p>We first show our sample file&#8230;</p>
<pre>
14:30:57 oracle@emgrid01 ~ >cat list.txt
steve:61
steve:14
becky:57
steve:19
jenna:69
stephen:57
maddie:54
jenna:53
abby:41
jenna:21
jenna:66
jenna:64
stephen:53
stephen:26
jenna:77
steve:46
maddie:39
steve:32
abby:77
jenna:97
</pre>
<p>&#8230;and then show the totals using awk&#8230;</p>
<pre>
14:30:59 oracle@emgrid01 ~ >awk -F ":" '{a[$1]+=$2} END {for (i in a) {print i,a[i]}}' list.txt
maddie 93
abby 118
jenna 447
becky 57
steve 172
stephen 136
14:31:01 oracle@emgrid01 ~ >
</pre>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=773</wfw:commentRss>
		</item>
		<item>
		<title>Python script to print out table data in name/value pairs</title>
		<link>http://appcrawler.com/wordpress/?p=769</link>
		<comments>http://appcrawler.com/wordpress/?p=769#comments</comments>
		<pubDate>Fri, 06 Aug 2010 18:26:13 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Python]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=769</guid>
		<description><![CDATA[Simple today.  I just wanted to ensure I had this in case I need to cut and paste it later  

#!/home/oracle/local/bin/python

import cx_Oracle, string

connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)

cursor = connection.cursor()
cnt=cursor.execute("select * from v$database")

colcount=len(cnt.description)

for row in cursor.fetchall():
  print "-----------------------------------------------"
  for col in range(colcount):
    print string.ljust(cursor.description[col][0],40) + str(row[col])
  print "-----------------------------------------------"

cursor.close()
connection.close()

]]></description>
			<content:encoded><![CDATA[<p>Simple today.  I just wanted to ensure I had this in case I need to cut and paste it later <img src='http://appcrawler.com/wordpress/wp-includes/images/smilies/icon_smile.gif' alt=':)' class='wp-smiley' /> </p>
<pre>
#!/home/oracle/local/bin/python

import cx_Oracle, string

connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)

cursor = connection.cursor()
cnt=cursor.execute("select * from v$database")

colcount=len(cnt.description)

for row in cursor.fetchall():
  print "-----------------------------------------------"
  for col in range(colcount):
    print string.ljust(cursor.description[col][0],40) + str(row[col])
  print "-----------------------------------------------"

cursor.close()
connection.close()
</pre>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=769</wfw:commentRss>
		</item>
		<item>
		<title>Redo on unchanged column values</title>
		<link>http://appcrawler.com/wordpress/?p=761</link>
		<comments>http://appcrawler.com/wordpress/?p=761#comments</comments>
		<pubDate>Fri, 06 Aug 2010 16:42:52 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Oracle]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=761</guid>
		<description><![CDATA[I recently spoke with an Oracle DBA.  He mentioned they had a third party application that was generating gobs of redo by updating rows to their existing values, i.e., update t set c = c.  
This bothered me, as my understanding was that other than recursive SQL such as extent allocation, block cleanout, [...]]]></description>
			<content:encoded><![CDATA[<p>I recently spoke with an Oracle DBA.  He mentioned they had a third party application that was generating gobs of redo by updating rows to their existing values, i.e., update t set c = c.  </p>
<p>This bothered me, as my understanding was that other than recursive SQL such as extent allocation, block cleanout, or undo segment management, redo should be very little when columns are updated to their existing value(s).</p>
<p>I decided to set up a test case so I could at least reproduce it and drill into it more.  This is the first part of the analysis.</p>
<p>We start by creating a 50 column table comprised of one numeric PK, and 49 columns of varchar2(30) datatype.  We then load the table with object names from dba_objects.</p>
<pre>
SQL> declare
  2    l_statement varchar2(32767) := 'create table t (c1 number primary key,';
  3  begin
  4    execute immediate 'drop table t';
  5    for i in 2..50 loop
  6      if i < 50 then
  7        l_statement := l_statement || 'c' || i || ' varchar2(30),';
  8      else
  9        l_statement := l_statement || 'c' || i || ' varchar2(30))';
 10      end if;
 11    end loop;
 12    execute immediate l_statement;
 13    l_statement := 'insert into t select object_id,';
 14    for i in 2..50 loop
 15      if i < 50 then
 16        l_statement := l_statement || ' object_name,';
 17      else
 18        l_statement := l_statement || ' object_name from dba_objects where object_id is not null';
 19      end if;
 20    end loop;
 21    execute immediate l_statement;
 22    for cur in (select * from t) loop
 23      null;
 24    end loop;
 25  end;
 26  /

PL/SQL procedure successfully completed.
</pre>
<p>Notice that at the end of the block above, we also scan the table to clean out all the blocks.  Finally, we log out so each test is performed from a separate session.</p>
<p>We then set up another block in a new session to update each row to its existing value.</p>
<pre>
SQL> variable total_redo number
SQL> declare
  2    l_statement varchar2(32767) := 'update t set ';
  3    l_variable_length number;
  4    l_value varchar2(30);
  5  begin
  6    for i in 2..50 loop
  7      if i < 50 then
  8        l_statement := l_statement || 'c' || i || ' = c' || i || ',';
  9      else
 10        l_statement := l_statement || 'c' || i || ' = c' || i;
 11      end if;
 12    end loop;
 13    execute immediate l_statement;
 14    select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';
 15    for cur in (select * from t) loop
 16      null;
 17    end loop;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL> print total_redo

      TOTAL_REDO
----------------
     167,985,928
</pre>
<p>We see that for about 59,000 rows, we generated about 160MB of redo.  Once again, we scan the table to ensure all blocks are cleaned out.  We do this after we determine our redo so the cleanout itself does not affect our analysis.</p>
<p>One again, in a new session, we then update our rows to a new changed value.  We also ensure our rows are about the same size as the initial rows.  We do this to ensure the size of the data doesn&#8217;t impact our analysis.</p>
<pre>
SQL> variable total_redo number
SQL> declare
  2    l_statement varchar2(32767) := 'update t set ';
  3    l_variable_length number;
  4    l_value varchar2(30);
  5  begin
  6    select avg(length(object_name))
  7      into l_variable_length
  8      from dba_objects;
  9    l_value := rpad('x',l_variable_length,'x');
 10    for i in 2..50 loop
 11      if i < 50 then
 12        l_statement := l_statement || 'c' || i || ' = ''' || l_value || ''',';
 13      else
 14        l_statement := l_statement || 'c' || i || ' = ''' || l_value || '''';
 15      end if;
 16    end loop;
 17    execute immediate l_statement;
 18    select value into :total_redo from v$mystat natural join v$statname where name = 'redo size';
 19    for cur in (select * from t) loop
 20      null;
 21    end loop;
 22  end;
 23  /

PL/SQL procedure successfully completed.

SQL> print total_redo

      TOTAL_REDO
----------------
     215,670,580
</pre>
<p>As you can see, we generated a little over 200MB for changed row values.  I would have expected the disparity (only about 40MB) to be much larger than it was.</p>
<p>I checked, and we don&#8217;t have any supplemental logging enabled.  The following are some values from v$database:</p>
<pre>

CREATED                                 2008-02-08 16:22:31
RESETLOGS_CHANGE#                       564488
RESETLOGS_TIME                          2008-02-08 16:22:33
PRIOR_RESETLOGS_CHANGE#                 1
PRIOR_RESETLOGS_TIME                    2006-12-19 04:16:53
LOG_MODE                                ARCHIVELOG
CHECKPOINT_CHANGE#                      6728756987948
ARCHIVE_CHANGE#                         6728756981942
CONTROLFILE_TYPE                        CURRENT
CONTROLFILE_CREATED                     2008-02-08 16:22:31
CONTROLFILE_SEQUENCE#                   2057989
CONTROLFILE_CHANGE#                     6728756989329
CONTROLFILE_TIME                        2010-08-06 11:32:43
OPEN_RESETLOGS                          NOT ALLOWED
VERSION_TIME                            2008-02-08 16:22:31
OPEN_MODE                               READ WRITE
PROTECTION_MODE                         MAXIMUM PERFORMANCE
PROTECTION_LEVEL                        MAXIMUM PERFORMANCE
REMOTE_ARCHIVE                          ENABLED
ACTIVATION#                             1827432451
SWITCHOVER#                             1827432451
DATABASE_ROLE                           PRIMARY
ARCHIVELOG_CHANGE#                      6728756987948
ARCHIVELOG_COMPRESSION                  DISABLED
SWITCHOVER_STATUS                       SESSIONS ACTIVE
DATAGUARD_BROKER                        DISABLED
GUARD_STATUS                            NONE
SUPPLEMENTAL_LOG_DATA_MIN               NO
SUPPLEMENTAL_LOG_DATA_PK                NO
SUPPLEMENTAL_LOG_DATA_UI                NO
FORCE_LOGGING                           NO
PLATFORM_ID                             13
PLATFORM_NAME                           Linux 64-bit for AMD
RECOVERY_TARGET_INCARNATION#            2
LAST_OPEN_INCARNATION#                  2
CURRENT_SCN                             6728756997178
FLASHBACK_ON                            NO
SUPPLEMENTAL_LOG_DATA_FK                NO
SUPPLEMENTAL_LOG_DATA_ALL               NO
STANDBY_BECAME_PRIMARY_SCN              0
FS_FAILOVER_STATUS                      DISABLED
FS_FAILOVER_CURRENT_TARGET              None
FS_FAILOVER_THRESHOLD                   0
FS_FAILOVER_OBSERVER_PRESENT            None
FS_FAILOVER_OBSERVER_HOST               None
</pre>
<p>Our next post will analyze the redo itself by dumping the logfile to see if we can determine the size of each change.</p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=761</wfw:commentRss>
		</item>
		<item>
		<title>Parsing hanganalyze output for blocking sessions</title>
		<link>http://appcrawler.com/wordpress/?p=752</link>
		<comments>http://appcrawler.com/wordpress/?p=752#comments</comments>
		<pubDate>Sat, 17 Jul 2010 19:45:26 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Oracle]]></category>

		<category><![CDATA[Python]]></category>

		<category><![CDATA[RAC]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=752</guid>
		<description><![CDATA[The hanganalyze command was introduced in Oracle 8i.  It is very useful, especially to mere mortals like us.  It is formatted in a human readable format.  In contrast, a systemstate dump normally contains a lot of memory addresses and offsets, which is useful to Oracle support, but not so much to us.
hanganalyze [...]]]></description>
			<content:encoded><![CDATA[<p>The hanganalyze command was introduced in Oracle 8i.  It is very useful, especially to mere mortals like us.  It is formatted in a human readable format.  In contrast, a systemstate dump normally contains a lot of memory addresses and offsets, which is useful to Oracle support, but not so much to us.</p>
<p>hanganalyze is *mandatory* to be generated in a cluster issue.  It can be obtained by issuing the following from any node in the cluster which you can access.</p>
<p><code><br />
sqlplus / as sysbda<br />
oradebug setmypid<br />
orabdeug –g all hanganalyze 3<br />
oradebug tracefile_name<br />
exit<br />
</code></p>
<p>Go to $ORACLE_BASE/udump and review the output by using the following python script:</p>
<pre>
#!/home/oracle/local/bin/python

import re, string, fileinput, sys

before = []

#------------------------------------------------------------------------------------

class blocker:
  def __init__(self, value):
    self.blockedIds = []
    self.blockerId = value
  def addBlocked(self,value):
    self.blockedIds.append(value)
  def getBlockerId(self):
    return self.blockerId

#------------------------------------------------------------------------------------

def parseFile(f, a):
  START_STATS=False
  FOUND_STATS=False
  for line in fileinput.input(f):
    if START_STATS == False:
      if re.match('^State of nodes', line):
        START_STATS = True
    elif FOUND_STATS == False:
      if re.match('^[[0-9]', line):
        FOUND_STATS = True
        a.append(line)
    elif FOUND_STATS == True:
      if re.match('^session', line):
        break
      else:
        a.append(line)
  fileinput.close()

#------------------------------------------------------------------------------------

parseFile(sys.argv[1], before)

blockers = []
arr = -1
found = False
HANG = False
for e in before:
  found = False
  j = 0
  BLOCKED=string.split(e,"/")[0]
  INST=int(string.split(e,"/")[1]) + 1
  SID=string.split(e,"/")[2]
  SERIAL=string.split(e,"/")[3]
  OSPID=string.split(e,"/")[5]
  STATE=string.split(e,"/")[6]
  BLOCKER=string.split(e,"/")[9]
  if string.find(STATE,"HANG") > -1:
    HANG = True
  if string.find(BLOCKER,"[") != -1:
    for i in range(len(blockers)):
      if blockers[i].getBlockerId() == BLOCKER:
        arr = i
        found = True
        break
      else:
        found = False
    if found != True:
      arr = arr + 1
      a = blocker(BLOCKER)
      a.addBlocked("SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")")
      blockers.append(a)
    else:
      a = blockers[arr]
      a.addBlocked("SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")")

if HANG == True:
  print "------------------------------------------------------------------------"
  print "\n\nW A R N I N G ! ! !  Possible True hang found\n\n"
  print "Search for the case sensitive word HANG in " + sys.argv[1] + "\n\n"

print "------------------------------------------------------------------------"
print "BLOCKING SESSION ANALYSIS:"
print "------------------------------------------------------------------------"

for e3 in blockers:
  for e in before:
    BLOCKED=string.split(e,"/")[0]
    if BLOCKED == e3.blockerId:
      INST=int(string.split(e,"/")[1]) + 1
      SID=string.split(e,"/")[2]
      SERIAL=string.split(e,"/")[3]
      OSPID=string.split(e,"/")[5]
      BLOCKER=string.split(e,"/")[9]
      if string.find(BLOCKER,"[") != -1:
        ALSO_BLOCKED = True
      else:
        ALSO_BLOCKED = False
      break
  print "------------------------------------------------------------------------"
  if ALSO_BLOCKED:
    print "SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ") - Also blocked"
  else:
    print "SID = " + str(SID) + " SERIAL# = " + str(SERIAL) + " on INST " + str(INST) + " (OSPID = " + str(OSPID) + ")"
  print "...is blocking..."
  for e4 in e3.blockedIds:
    print "\t" + e4
</pre>
<p>This will generate output similar to the following:</p>
<p><code><br />
C:\>parseHang.py wcprod1_diag_23622.trc<br />
------------------------------------------------------------------------<br />
BLOCKING SESSION ANALYSIS:<br />
------------------------------------------------------------------------<br />
------------------------------------------------------------------------<br />
SID = 1533 SERIAL# = 35901 on INST 1 (OSPID = 28371)<br />
...is blocking...<br />
        SID = 1524 SERIAL# = 252 on INST 1 (OSPID = 28407)<br />
------------------------------------------------------------------------<br />
SID = 1524 SERIAL# = 252 on INST 1 (OSPID = 28407) - Also blocked<br />
...is blocking...<br />
        SID = 1504 SERIAL# = 33 on INST 2 (OSPID = )<br />
        SID = 1507 SERIAL# = 81 on INST 2 (OSPID = )<br />
        SID = 1511 SERIAL# = 37 on INST 2 (OSPID = )<br />
        SID = 1516 SERIAL# = 44536 on INST 2 (OSPID = )<br />
        SID = 1517 SERIAL# = 9722 on INST 2 (OSPID = )<br />
        SID = 1525 SERIAL# = 32 on INST 2 (OSPID = )<br />
        SID = 1532 SERIAL# = 372 on INST 2 (OSPID = )<br />
        SID = 1534 SERIAL# = 45 on INST 2 (OSPID = )<br />
        SID = 1535 SERIAL# = 61522 on INST 2 (OSPID = )<br />
        SID = 1539 SERIAL# = 59 on INST 2 (OSPID = )<br />
        SID = 1543 SERIAL# = 8 on INST 2 (OSPID = )<br />
        SID = 1547 SERIAL# = 37 on INST 2 (OSPID = )<br />
        SID = 1555 SERIAL# = 45 on INST 2 (OSPID = )<br />
        SID = 1556 SERIAL# = 34 on INST 2 (OSPID = )<br />
        SID = 1557 SERIAL# = 37 on INST 2 (OSPID = )<br />
        SID = 1562 SERIAL# = 20988 on INST 2 (OSPID = )<br />
        SID = 1581 SERIAL# = 35597 on INST 2 (OSPID = )<br />
        SID = 1584 SERIAL# = 63056 on INST 2 (OSPID = )<br />
        SID = 1589 SERIAL# = 47551 on INST 2 (OSPID = )<br />
        SID = 1614 SERIAL# = 60597 on INST 2 (OSPID = )</p>
<p>C:\><br />
</code></p>
<p>You may be able to identify the session that is causing the issue using output similar to what is above.  It may be a background process on one of the instances, in which case you will have to terminate that instance.</p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=752</wfw:commentRss>
		</item>
		<item>
		<title>Converting SCN to decimal</title>
		<link>http://appcrawler.com/wordpress/?p=743</link>
		<comments>http://appcrawler.com/wordpress/?p=743#comments</comments>
		<pubDate>Tue, 15 Jun 2010 15:39:46 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Oracle]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=743</guid>
		<description><![CDATA[I found a slick little script at the following URL for converting an SCN to decimal.

http://www.bluegecko.net/oracle/converting-hexadecimal-oracle-scns-to-decimal/
You can take the raw SCN and substitute it below&#8230;
scn=0x0623.02021908;echo $((${scn%%.*}*4294967296+0x${scn##*.}))
]]></description>
			<content:encoded><![CDATA[<p>I found a slick little script at the following URL for converting an SCN to decimal.</p>
<p><a href=" http://www.bluegecko.net/oracle/converting-hexadecimal-oracle-scns-to-decimal/" target=_blank><br />
http://www.bluegecko.net/oracle/converting-hexadecimal-oracle-scns-to-decimal/</a></p>
<p>You can take the raw SCN and substitute it below&#8230;</p>
<p><code>scn=0x0623.02021908;echo $((${scn%%.*}*4294967296+0x${scn##*.}))</code></p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=743</wfw:commentRss>
		</item>
		<item>
		<title>Yet another reason to use Oracle&#8217;s JDBC connection manager</title>
		<link>http://appcrawler.com/wordpress/?p=725</link>
		<comments>http://appcrawler.com/wordpress/?p=725#comments</comments>
		<pubDate>Mon, 07 Jun 2010 15:51:27 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Java]]></category>

		<category><![CDATA[Oracle]]></category>

		<category><![CDATA[RAC]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=725</guid>
		<description><![CDATA[Over the weekend we had an odd issue.  
We have four nodes in a cluster, with two core services.  SERVICE_A runs on servers 1 and 2, while SERVICE_B runs on server 4.  Server 3 is effectively idle most of the time unless we need to allow for additional throughput.  Server 3 [...]]]></description>
			<content:encoded><![CDATA[<p>Over the weekend we had an odd issue.  </p>
<p>We have four nodes in a cluster, with two core services.  SERVICE_A runs on servers 1 and 2, while SERVICE_B runs on server 4.  Server 3 is effectively idle most of the time unless we need to allow for additional throughput.  Server 3 died on Friday afternoon, and after it came back up, the other three instances would not register anything about the services they were well, servicing, with the listener on server 3.  As a result, we began to see an increase in the number of TNS-12514 in the listener log on server 3.  While this is a problem in and of itself, the bigger issue that came to light was when developers began calling us and asking why they were getting TNS-12514.  My immediate question was, how do you know that?</p>
<p>The reason I asked is because Oracle&#8217;s connection time load balancing is based on each instance in the cluster telling the other listeners where they are.  So if I connect to the listener on server 3, it should tell me, &#8220;I can&#8217;t help you, but here is where that service is located on server 1&#8243;.  If the listener can&#8217;t provide that information (as it couldn&#8217;t in our case), it will simply throw a TNS-12514 back to the user.  However, if you use the Oracle JDBC connection manager, you will find it is smart enough to handle these exceptions and just get a connection from one of the other servers in the URL you supply.  Incidentally, this is why you want all servers in your URL.  </p>
<p>This is also why you should never see a TNS-12514 in your application if a case similar to what I described earlier occurs.</p>
<p>We can prove this with the code below&#8230;</p>
<pre>

import java.sql.*;
import java.io.*;
import java.util.*;

class testConn {

  public static void main(String args[]) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Properties p = new Properties();
      p.load(new FileInputStream(System.getProperty("propfile")));

      //we first build a connection manager, and load each physical connection into an array

      oracleConnMgr cm = new oracleConnMgr();
      ArrayList al = new ArrayList();
      for (int i = 0; i < 100; i++) {
        al.add(cm.getConnection());
      }

      //...and we then check the host to which we are connected.  problem instances where the listener does not
      //  know where the service is actually running should be ignored and result in no exceptions.
      //  we will still see tns-12514 errors in the listener log file on the offending node, but our application
      //  will continue to be unaware of any issue.

      for (int j = 0; j<100; j++) {
        Connection conn = (Connection)al.get(j);
        ResultSet rst = conn.createStatement().executeQuery("select sys_context('userenv','instance') from dual");
        while (rst.next()) {
          System.out.println(rst.getString(1));
        }
      }
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}
</pre>
<p>I should note that the properties file and oracleConnMgr class noted above simply contain the URL and number of connections to use (we had 100 in the file), as well as the code to fire up an OracleDataSource object, respectively.</p>
<p>Don&#8217;t roll your own connection manager.  Life is too short.</p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=725</wfw:commentRss>
		</item>
		<item>
		<title>How to &#8220;unregister&#8221; a service from a listener</title>
		<link>http://appcrawler.com/wordpress/?p=717</link>
		<comments>http://appcrawler.com/wordpress/?p=717#comments</comments>
		<pubDate>Sun, 06 Jun 2010 12:37:47 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Oracle]]></category>

		<category><![CDATA[RAC]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=717</guid>
		<description><![CDATA[If a service is registered with your listener outside of the clusterware, you can unregister it with the following call:

dbms_service.stop_service(service_name,instance_name)

I verified that this will not disconnect existing sessions in the instance on which the service is being stopped.
I did this by performing the following steps:
create 100 sessions to service and verify some are on the [...]]]></description>
			<content:encoded><![CDATA[<p>If a service is registered with your listener outside of the clusterware, you can unregister it with the following call:</p>
<pre>
dbms_service.stop_service(service_name,instance_name)
</pre>
<p>I verified that this will not disconnect existing sessions in the instance on which the service is being stopped.</p>
<p>I did this by performing the following steps:</p>
<p>create 100 sessions to service and verify some are on the instance on which you want to stop the service</p>
<pre>
exec dbms_service.stop_service(service_name,instance_name)
select count(*),inst_id from gv$session where service_name = '&amp;service_name' group by inst_id;
</pre>
<p>You will see the sessions are still on the instance on which you stopped the service.  However, if you issue the following:</p>
<pre>
lsnrctl service listener_name
</pre>
<p>&#8230;you will see the service no longer shows up in the list.</p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=717</wfw:commentRss>
		</item>
		<item>
		<title>Is memory allocated actually in use?</title>
		<link>http://appcrawler.com/wordpress/?p=697</link>
		<comments>http://appcrawler.com/wordpress/?p=697#comments</comments>
		<pubDate>Thu, 13 May 2010 13:14:26 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Linux]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=697</guid>
		<description><![CDATA[In an earlier article, we talked about actually initializing memory rather than just allocating it.  This post will show how we can look at what is allocated in Linux, as well as when it shows as being in use and when it doesn&#8217;t.
I found that memory will show up in pmap (really, pmap just [...]]]></description>
			<content:encoded><![CDATA[<p>In an earlier <a href="http://appcrawler.com/wordpress/?p=654" target="_blank">article</a>, we talked about actually initializing memory rather than just allocating it.  This post will show how we can look at what is allocated in Linux, as well as when it shows as being in use and when it doesn&#8217;t.</p>
<p>I found that memory will show up in pmap (really, pmap just reads /proc/pid/maps and formats it) if it has been malloc’d.  </p>
<p>It will *not* show up in free-m or /proc/meminfo unless it has been initialized/written to, i.e., calloc() or memset().</p>
<p>The JRE must malloc() 1.2GB of RAM per process, but not call memset().  Once again, in this case the memory will not show up as used in free –m if this is the case (as it is with us).  This is also what Oracle does with the SGA, which is why we don’t see free memory reduced by 8GB immediately after we start an instance, but only over time.</p>
<p>Using the C code below…</p>
<pre>
int main() {
  void *m = malloc(2000*1024*1024);
  memset(m,0,2000*1024*1024);
  sleep(30);
  return 0;
}
</pre>
<p>…we start with the server showing 0MB swap used, 5500MB of file buffers/cache, and 700MB free memory…</p>
<pre>
21:05:31 oracle@emgrid01 ~ >free -m
             total       used       free     shared    buffers     cached
Mem:          7987       7287        700          0        624       4902
-/+ buffers/cache:       1761       6226
Swap:         1004          0       1004
</pre>
<p>…I then malloc()/memset() the 2GB of memory…</p>
<pre>
21:05:33 oracle@emgrid01 ~ >./allocatemem &#038;
[1] 10616
</pre>
<p>…after which I show 2GB of memory as writable-private in my process…</p>
<pre>
21:05:39 oracle@emgrid01 ~ >pmap -d 10616 | tail -1
2048140K writable-private, 10580K readonly-private, and 0K shared
</pre>
<p>…so the server now shows we have 650MB less free memory, 1400MB less file buffer/cache used, and an increase of 80MB of swap used (why was the swap used rather than pulling from file cache??)…</p>
<pre>
21:05:46 oracle@emgrid01 ~ >free -m
             total       used       free     shared    buffers     cached
Mem:          7987       7940         47          0        302       3798
-/+ buffers/cache:       3839       4148
Swap:         1004         80        923
21:05:49 oracle@emgrid01 ~ >
</pre>
<p>…and when the process terminates, the OS dutifully cleans up behind me and we see the swap is still there, and the file buffers/cache that were in use at the start of our test have now been returned to the free pool…</p>
<pre>
[1]+  Done                    ./allocatemem
21:07:00 oracle@emgrid01 ~ >free -m
             total       used       free     shared    buffers     cached
Mem:          7987       5935       2052          0        301       3798
-/+ buffers/cache:       1835       6152
Swap:         1004         80        923
21:07:03 oracle@emgrid01 ~ >
</pre>
<p>If I run the test with the memset() call commented out, pmap –d will still show the 2GB as writable-private in my process, but the free server memory as shown by free –m will not be reduced, as it has only been allocated.</p>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=697</wfw:commentRss>
		</item>
		<item>
		<title>Huge pages</title>
		<link>http://appcrawler.com/wordpress/?p=686</link>
		<comments>http://appcrawler.com/wordpress/?p=686#comments</comments>
		<pubDate>Tue, 11 May 2010 16:48:30 +0000</pubDate>
		<dc:creator>Steve</dc:creator>
		
		<category><![CDATA[Linux]]></category>

		<guid isPermaLink="false">http://appcrawler.com/wordpress/?p=686</guid>
		<description><![CDATA[Huge Pages is a feature available in later Linux kernels that provides two important benefits:
1.	Locks the memory available to huge pages, so it cannot be paged to disk
2.	Make the TLB (translation lookaside buffer) much smaller on the processor, as the number of entries is much smaller.  This is due to the fact the standard [...]]]></description>
			<content:encoded><![CDATA[<p>Huge Pages is a feature available in later Linux kernels that provides two important benefits:</p>
<p>1.	Locks the memory available to huge pages, so it cannot be paged to disk<br />
2.	Make the TLB (translation lookaside buffer) much smaller on the processor, as the number of entries is much smaller.  This is due to the fact the standard page size in Linux is 4K, whereas a huge page is either 2MB of 4MB in size.  This makes managing virtual memory much more efficient, as the processor does not have to work as hard switching pages in and out.</p>
<p>The issue for databases such as Oracle, especially those with a large SGA, is that the sheer number of operations the processor is forced to perform can overwhelm the system.  With an SGA such as 32GB, it will need to manage over eight million pages, whereas with huge pages, only 8,192 pages will be managed in the TLB.  This is far less stress on the CPU.<br />
This explains why utilities such as sar can’t post their output during periods of high load when huge pages aren’t used, since the system CPU utilization goes through the roof.<br />
Below is a test case for the technology. See Appendix A for the source code used in the test.<br />
We first allocate our pages in the OS.</p>
<p><code><br />
sudo sysctl -w vm.nr_hugepages=50<br />
</code></p>
<p>Notice we first show we have 50 pages available to us.</p>
<pre>[root@linux5 ~]# gcc -o hugealloc hugealloc.c
[root@linux5 ~]# ./hugealloc

Huge pages prior to starting program...

HugePages_Total:    50
HugePages_Free:     50
HugePages_Rsvd:      0
Hugepagesize:     4096 kB
PageTables:       4584 kB</pre>
<p>We then issue a shmget() system call to allocate 100MB (25 pages * 4MB per page).  Notice the pages are merely reserved, and still part of the “free” pool.  Also notice that you cannot just add Free and Rsvd together, as this will total more than the Total.  Whenever you find this condition, you can look at it from the standpoint that while you have reserved the pages, they still show up in the free pool until you actually write into them.  You can also look at it from the view that the total minus what is free is currently in use.  The reserved pages are then added to this number to get what is currently allocated (either in use or just reserved).  Any difference between this number and the total means your application is not requesting all the memory for which you have configured huge pages.</p>
<pre>created huge pages shared memory segment (shmid 0xa000f in ipcs -m)

Showing huge pages memory usage...

HugePages_Total:    50
HugePages_Free:     50
HugePages_Rsvd:     25
Hugepagesize:     4096 kB
PageTables:       4584 kB</pre>
<p>We then issue a shmat() call, which attaches the memory to our process.  Notice the pages are still only reserved, and still part of the “free” pool.  Linux does this on the outside chance a process will request a ton of memory, but not actually use it.  It will allocate it on demand, as shown in the next section.</p>
<pre>Attached shared memory segment at address 0xb1800000...

Showing huge pages memory usage...

HugePages_Total:    50
HugePages_Free:     50
HugePages_Rsvd:     25
Hugepagesize:     4096 kB
PageTables:       4584 kB</pre>
<p>We now write data into our memory pages, and see the pages no longer show as reserved, but are actually used (the free pool has been reduced)</p>
<pre>Wrote data into shared memory segment...

Showing huge pages memory usage...

HugePages_Total:    50
HugePages_Free:     25
HugePages_Rsvd:      0
Hugepagesize:     4096 kB
PageTables:       4540 kB</pre>
<p>We finally delete our shared memory segment, which shows the pages as all available, and 0 reserved.</p>
<pre>Deleted shared memory segment...

Showing huge pages memory usage...
HugePages_Total:    50
HugePages_Free:     50
HugePages_Rsvd:      0
Hugepagesize:     4096 kB
PageTables:       4540 kB

[root@linux5 ~]#</pre>
<p>It should be noted that software can still function without huge pages.  When an strace is run against an Oracle instance at startup, you will see both the shmget() and shmat() calls, each with the SHM_HUGETLB  flag passed.  If the shmget() fails, another shmget() call is issued without the SHM_HUGETLB flag.  This allows the instance to start, although it may fail under load for the reasons noted earlier.</p>
<p>Our source code used below shows an example of this.</p>
<pre>#include
#include
#include
#include
#include
#include 

#ifndef SHM_HUGETLB
#define SHM_HUGETLB 04000
#endif

#define LENGTH (100UL*1024*1024)

#define ADDR (void *)(0x0UL)
#define SHMAT_FLAGS (0)

int main(void)
{
        int shmid;
        unsigned long i;
        char *shmaddr;

        printf("\n\nHuge pages prior to starting program...\n\n");
        system("/bin/grep -i huge /proc/meminfo");
        system("/bin/grep -i pagetable /proc/meminfo");
        printf("\n");

        if ((shmid = shmget(IPC_PRIVATE, LENGTH, SHM_HUGETLB | IPC_CREAT | SHM_R | SHM_W)) &lt; 0) {
          if ((shmid = shmget(IPC_PRIVATE, LENGTH, IPC_CREAT | SHM_R | SHM_W)) &lt; 0) {
            perror("shmget");
            exit(1);
          }
          else {
            printf("created normal shared memory segment, as we couldn't use huge pages.\n");
            printf("shmid: 0x%x\n", shmid);
          }
        }
        else {
          printf("created huge pages shared memory segment (shmid 0x%x in ipcs -m)\n", shmid);
        }

        printf("\n\nShowing huge pages memory usage...\n\n");
        system("/bin/grep -i huge /proc/meminfo");
        system("/bin/grep -i pagetable /proc/meminfo");
        printf("\n");

        shmaddr = shmat(shmid, ADDR, SHMAT_FLAGS);
        if (shmaddr == (char *)-1) {
                perror("Shared memory attach failure");
                shmctl(shmid, IPC_RMID, NULL);
                exit(2);
        }

        printf("\n\nAttached shared memory segment at address %p...\n",shmaddr);
        printf("\nShowing huge pages memory usage...\n\n");
        system("/bin/grep -i huge /proc/meminfo");
        system("/bin/grep -i pagetable /proc/meminfo");
        printf("\n");

        for (i = 0; i &lt; LENGTH; i++) {
          shmaddr[i] = (char)(i);
          /*
          if (!(i % (1024 * 1024)))
            printf(".");
          */
        }
        printf("\n");

        /*
        printf("Starting the Check...");
        for (i = 0; i &lt; LENGTH; i++)
                if (shmaddr[i] != (char)i)
                        printf("\nIndex %lu mismatched\n", i);
        printf("Done.\n");
        */

        printf("\n\nWrote data into shared memory segment...\n");
        printf("\nShowing huge pages memory usage...\n\n");
        system("/bin/grep -i huge /proc/meminfo");
        system("/bin/grep -i pagetable /proc/meminfo");
        printf("\n");
        if (shmdt((const void *)shmaddr) != 0) {
                perror("Detach failure");
                shmctl(shmid, IPC_RMID, NULL);
                exit(3);
        }

        shmctl(shmid, IPC_RMID, NULL);

        printf("\n\nDeleted shared memory segment...\n");
        printf("\nShowing huge pages memory usage...\n");
        system("/bin/grep -i huge /proc/meminfo");
        system("/bin/grep -i pagetable /proc/meminfo");
        printf("\n");

        return 0;
}</pre>
]]></content:encoded>
			<wfw:commentRss>http://appcrawler.com/wordpress/?feed=rss2&amp;p=686</wfw:commentRss>
		</item>
	</channel>
</rss>
