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. |
|
