Determine the headroom between my current SCN and the maximum allowed SCN
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Example of Turning Shell Script Functionality Into a Stored Procedure
The origin of this entry was an attempt to determine why a 6 node RAC cluster was burning through SCN's an an accelerated rate such that it was eating through headroom between the current SCN and the maximum allowed SCN.

The first two scripts were written by Oracle support and provided Oracle's internal algorithm for this determination.
#!/bin/ksh
# -------------------------------------------------------------------------------------
# checks for SCN change to see the closing gap between LOCALDB with other DB (REMOTEDB)
# -------------------------------------------------------------------------------------
. $HOME/.prof_opm01p5
# -------------------------------------------------------------------------------------
sqlplus -s "/ as sysdba" << EOF
spool /appl/oracle/admin/opm01p/changes/SCN-uwclass1.log
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MM:SS') "DATE",
       TO_CHAR(dbms_flashback.get_system_change_number, 'xxxxxxxxxxxx') "SCN" FROM dual;
exit;
EOF

scn_trends_v3.ksh (utility to view the gathered log, SCN-localdb_v3.log)

#!/bin/ksh
#
# scn_trends_v3.ksh
#
# This script parses the monitoring logs, to print the hourly
# snapshots of the current scn with the maximum scn, and the
# delta, so the trends can be observed.
#
# The maximum valid scn on any oracle database is a function of the current
# date and time (eg MM/DD/YYYY HH:MN:SS) . The maximum scn is the
# current scn you would have in your oracle database if that database
# had been created on 1/Jan 1988 and had executed 16*1024 transactions
# per second since that time, with the assumption that all months are
# 31 days. So this is the formula used to calculate the maximum scn:
#
#((((((YYYY-1988)*12*31)+((31*(MM-1))+(DD-1)))*24*60*60)+((((HH*60)+MN)*60)+SS))
# *1024*16)
#
# Eg At Feb 7 20:21:30 the maximum scn allowed is:
#((((((2011-1988)*12*31)+((31*(02-1))+(07-1)))*24*60*60)+((((20*60)+21)*60)+30))# *1024*16)
# max scn = 0xB1071F28000 (12165259100160 decimal)
#
i=0
echo ""
if [ "$1" = "" ]
then
  echo "input: monitoring script output file"
  exit
fi
echo "This version of the script requires the SYSDATE (not SYSDATE+1) is "
echo " written to the monitoring logs"
echo "We are looking for the last column to go negative, that's when"
echo "the current_scn is lower than the maximum_scn"
echo ""
lines=`wc -l $1 | awk '{print $1}'`
echo "date time curr_scn max_scn cur-max cur-max(decimal)"
echo "---------- -------- ----------- ----------- -------- --------------"
while :
do
  read line rest
  if [ "$line" = "DATE" ]
  then
    read dashes
    read date time scn
    mm=`echo $date | awk 'BEGIN {FS="/";} {print $1}'`
    dd=`echo $date | awk 'BEGIN {FS="/";} {print $2}'`
    yyyy=`echo $date | awk 'BEGIN {FS="/";} {print $3}'`
    hh=`echo $time | awk 'BEGIN {FS=":";} {print $1}'`
    min=`echo $time | awk 'BEGIN {FS=":";} {print $2}'`
    ss=`echo $time | awk 'BEGIN {FS=":";} {print $3}'`
    max_scn=$(((((((($yyyy-1988)*12*31)+((31*($mm-1))+($dd-1)))*24*60*60)+(((($hh*60)+ $min)*60)+$ss))*1024*16)))

    # convert the max scn from decimal (max_scn) to hex (upper_hex_max_scn)
    echo "obase=16" > /tmp/bc
    echo "$max_scn" >> /tmp/bc
    upper_hex_max_scn=`bc < /tmp/bc`

    # convert current scn from lc hex (scn) to UC hex (upper_hex_scn)
    upper_hex_scn=`echo $scn | awk '{print toupper($1)}'`
    upper_hex_scn=`echo $scn | sed '/^\./!y/abcdef/ABCDEF/'`

    # convert current scn from UC hex (upper_hex_scn) to decimal (dec_curr_scn)
    echo "ibase=16" > /tmp/bc1
    echo "$upper_hex_scn" >> /tmp/bc1
    dec_curr_scn=`bc < /tmp/bc1`

    # calculate the difference (current minus max)
    delta=$(($dec_curr_scn-$max_scn))

    #convert the delta from decimal (delta) to hex (hex_delta)
    echo "obase=16" > /tmp/bc2
    echo "$delta" >> /tmp/bc2
    hex_delta=`bc < /tmp/bc2`
    echo "$date $time $scn $upper_hex_max_scn $hex_delta $delta"
  fi
  i=$(($i+1))
  if [ "$i" -gt "$lines" ]
  then
    break
  fi
done < $1
echo ""
This next bit of code I wrote in 5 minutes and performs the same functionality without requiring Korn shell access and a lot of other complexity.
set serveroutput on

DECLARE
 cur_yr NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'));
 cur_mo NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'MM'));
 cur_dy NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'DD'));
 cur_hr NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'HH24'));
 cur_mi NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'MI'));
 cur_ss NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'SS'));
 cur_scn NUMBER;
 max_scn NUMBER;
BEGIN
 cur_scn := dbms_flashback.get_system_change_number;
 max_scn := ((((((((cur_yr-1988)*12*31) + ((31*(cur_mo-1)) + (cur_dy-1)))*24*60*60) + ((((cur_hr*60) + cur_mi)*60) + cur_ss))*1024*16)));

 dbms_output.put_line('Timestamp: '   || TO_CHAR(SYSTIMESTAMP));
 dbms_output.put_line('Current SCN: ' || TO_CHAR(cur_scn));
 dbms_output.put_line('Maximum SCN: ' || TO_CHAR(max_scn));
 dbms_output.put_line('Headroom: '    || TO_CHAR(max_scn - cur_scn));
END;
/
Oracle spent a lot of money turning out an overly complex customer-unfriendly solution ... the lesson ... when you get something from Oracle for diagnostic purposes. Examine it and consider your options. Oracle is now distributing my solution as theirs.
 
Related Topics
DBMS_FLASHBACK
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved