<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6603.0">
<TITLE>Radius Sending Rollback Message?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Arial">Radius Sending Rollback Message</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">A rather curious scenario of events:<BR>
</FONT>
<BR><FONT SIZE=2 FACE="Arial">In our setup of Radius, we use an Oracle database. Ordinarily, when NAS sends Accounting Start messages to Radius, Radius sends Update messages to Oracle. Periodically, something odd happens - Radius sends a Rollback message to Oracle which undoes the Update. This Rollback message is really not wanted as the Oracle DB doesnt get updated, but I havent got a clue why this happens. Please let me know if you've seen this before or if you know why this would happen. Help greatly appreciated.</FONT></P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Some more details are below.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial"><B></B></FONT><B> <FONT SIZE=2 FACE="Arial">(Radius-Oracle communicate with:</FONT></B>
<BR><B><FONT SIZE=2 FACE="Arial">Data Manipulation Language (DML) consists of UPDATEs, INSERTs and MERGEs.</FONT></B>
<BR><B><FONT SIZE=2 FACE="Arial">Transaction Control consists of COMMIT, SAVEPOINT, ROLLBACK and SET TRANSCATION.)</FONT></B>
</P>
<P><B><FONT SIZE=2 FACE="Arial">Here are the two failing sessions, (containing 4 UPDATE messages from the radius server) that did not successfully update the oracle DB.</FONT></B></P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">UPDATE radacct SET Status = 1, NASIPAddress = '10.10.32.4', AcctStartTime = to_d<BR>
ate('2005-11-18</FONT><B> <FONT COLOR="#008000" SIZE=2 FACE="Courier">14:12:21'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">,'YYYY-MM-DD HH24:MI:SS'), CalledStationId = 'wx', XDI<BR>
AL= '213.55.204.140', FramedIPAddress=</FONT><B> <FONT COLOR="#FF0000" SIZE=2 FACE="Courier">'10.12.33.152'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> WHERE CallingStationId =<BR>
'11118380323' <BR>
UPDATE radacct SET Status = 0 , AcctStopTime = to_date('2005-11-18</FONT><B> <FONT COLOR="#800080" SIZE=2 FACE="Courier">14:28:20'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">,'Y<BR>
YYY-MM-DD HH24:MI:SS') WHERE CallingStationId = decode('11118380323','', '11110<BR>
000000', '11118380323') <BR>
<BR>
UPDATE radacct SET Status = 1, NASIPAddress = '10.10.32.4', AcctStartTime = to_d<BR>
ate('2005-11-18</FONT><B> <FONT COLOR="#008000" SIZE=2 FACE="Courier">14:29:10'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">,'YYYY-MM-DD HH24:MI:SS'), CalledStationId = 'wx', XDI<BR>
AL= '213.55.204.140', FramedIPAddress=</FONT><B> <FONT COLOR="#FF0000" SIZE=2 FACE="Courier">'10.12.23.199'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> WHERE CallingStationId =<BR>
'11118380323' <BR>
UPDATE radacct SET Status = 0 , AcctStopTime = to_date('2005-11-18</FONT><B> <FONT COLOR="#800080" SIZE=2 FACE="Courier">15:13:04'</FONT></B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">,'Y<BR>
YYY-MM-DD HH24:MI:SS') WHERE CallingStationId = decode('11118380323','', '11110<BR>
000000', '11118380323')</FONT>
</P>
<BR>
<P><B><FONT SIZE=2 FACE="Arial">The Oracle DB listener trace for the first message shows the DML was received ok but the end of a trace shows a ROLLBACK (this undoes all the DMLs since the last COMMIT was sent). Details:</FONT></B></P>
<BR>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">UPDATE radacct SET Status = :"SYS_B_0", NASIPAddress = :"SYS_B_1",</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">AcctStartTime = to_date(:"SYS_B_2",:"SYS_B_3"), CalledStationId =</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">:"SYS_B_4", XDIAL= :"SYS_B_5", FramedIPAddress = :"SYS_B_6"</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">WHERE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> CallingStationId =:"SYS_B_7"</FONT>
</P>
<BR>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">call count cpu elapsed disk query current rows</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parse 1 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Execute 1 0.01 0.00 2 3 14 1</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Fetch 0 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">total 2 0.01 0.01 2 3 14 1</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Misses in library cache during parse: 1</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Optimizer goal: CHOOSE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parsing user id: 162</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Rows Row Source Operation</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ---------------------------------------------------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 1 UPDATE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 1 INDEX UNIQUE SCAN PK_RADACCT (object id 622971)</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">********************************************************************************</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">SELECT CallingStationId,</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> FramedIpAddress</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> from radius.radacct</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> where rowid = :b1</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> and Status = 1</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">call count cpu elapsed disk query current rows</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parse 1 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Execute 1 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Fetch 1 0.00 0.00 0 1 0 1</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">total 3 0.00 0.00 0 1 0 1</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Misses in library cache during parse: 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Optimizer goal: CHOOSE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parsing user id: 162 (recursive depth: 1)</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Rows Row Source Operation</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ---------------------------------------------------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 1 TABLE ACCESS BY USER ROWID RADACCT</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">********************************************************************************</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">UPDATE radius.radacct</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> set Status = 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> where Status = 1</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> and FramedIpAddress = :b2</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> and CallingStationId != :b1</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">call count cpu elapsed disk query current rows</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parse 1 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Execute 1 0.03 0.04 0 2066 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Fetch 0 0.00 0.00 0 0 0 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ------ -------- ---------- ---------- ---------- ---------- ----------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">total 2 0.03 0.04 0 2066 0 0</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Misses in library cache during parse: 0</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Optimizer goal: CHOOSE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Parsing user id: 162 (recursive depth: 1)</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">Rows Row Source Operation</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">------- ---------------------------------------------------</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 0 UPDATE</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 0 TABLE ACCESS BY INDEX ROWID RADACCT</FONT>
<BR><FONT COLOR="#0000FF" SIZE=2 FACE="Courier"> 2587 INDEX RANGE SCAN RADACCT_IX4 (object id 464104)</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">********************************************************************************</FONT>
</P>
<P><B><FONT COLOR="#0000FF" SIZE=2 FACE="Courier">rollback</FONT></B>
</P>
<P><FONT SIZE=2 FACE="Arial"> </FONT>
</P>
</BODY>
</HTML>