Forensic SGA capture

This script can help you in gathering artifacts for an Oracle SGA and deliver it in such a way that its integrity can be verified (written for Oracle Database Server on Linux). Please find a demo video below the code.

#!/bin/bash
if [ $# -ne 2 ]; then
echo "Usage: $0 <output filename> <seconds to read (first thousand characters) from shared SQL area>"
exit 1
fi
if [[ "$2" != +([0-9]) ]]
then
echo "Usage:       $0 <output filename> <seconds to read (first thousand characters) from shared SQL area>"
echo "For example: $0 case_suspicion 120"
exit 1
fi
STARTTIME=$(date +'%Y%m%d %H:%M:%S.%N')
FILE=$1
echo "**************************************************************" > "$FILE.log"
echo "" >> "$FILE.log"
echo "- Start at $STARTTIME " >> "$FILE.log"
STARTRUN=$(date +'%Y%m%d %H:%M:%S.%N')
STARTTOTAL=`date +%s`
SECS=$2
SECONDS=0
while (( $SECONDS < $SECS )); do
sqlplus -s /nolog  <<EOF connect / as sysdba alter session set nls_date_format='YYYYMMDDHH24MISS'; alter session set nls_timestamp_format='YYYYMMDDHH24MISSFF'; spool $FILE.dsv append -- ----------------------------------------------------------------------------- -- WWW.PETEFINNIGAN.COM LIMITED -- ----------------------------------------------------------------------------- -- Script Name : sga.sql -- Author : Pete Finnigan -- Date : September 2017 -- ----------------------------------------------------------------------------- -- Description : get SQL Text from the SGA -- ----------------------------------------------------------------------------- -- Maintainer : Pete Finnigan (http://www.petefinnigan.com) -- Copyright : Copyright (C) 2007 to 2017 PeteFinnigan.com Limited. All rights -- reserved. All registered trademarks are the property of their -- respective owners and are hereby acknowledged. -- ----------------------------------------------------------------------------- -- License : This software is licensed to you by PeteFinnigan.com Limited. All -- rights and ownership and copyright in the software are retained by -- PeteFinnigan.com Limited in all possible cases. Posession of this -- software does not infer any additonal rights. If you receive this -- software without these copyright notices and license text, this text, -- license and copyright still applies. This text, copyright and license -- must not be removed under any circumstances. This applies to all -- text displayed as comments up until and including the version history, -- This text also applies to any copyright, banner or other text -- refering to PeteFinnigan.com Limited ownership that is output by -- the program. -- -- This software is free to use BUT it is NOT open source and NOT GPL -- or any similar license and it is NOT in the public domain. -- -- You are permitted to use this software commercially or privately -- provided these notices or banners as described are not removed. You -- may modify the software and use it internally but this does not -- infer any additonal rights in the software. i.e. if you delete -- some of our code or change variable names or add features that does -- not make it your code and does not give you the right to remove -- our ownership in this software shown in these notices. This software must -- not be made available or published in anyway, any language, any -- modified form or original form except by PeteFinnigan.com Limited. -- You must not incorporate this software into any free or commercial -- product or software and you must not sell or give away any software that -- includes this sofware. -- -- -- In short this text is not written by a lawyer so please respect the -- intent that you can use or modify it freely but not give it away -- yourself or take away our right to attribution. -- If someone else needs a copy please ask them to come to PeteFinnigan.com -- Limited and we will happily let them also have a free copy. Also -- as you may expect freely use does not include adding this software to -- a commercial or free product (without our permission) but you -- can use it internally in projects. We put our time into the free scripts -- on our website or training courses and give these tools away for free and -- in return we expect our copyright and ownership to always remain. -- We like to help people but we also want to benefit from the fact -- our name becomes known through these scripts and tools and software -- that we make. We hope this makes sense. -- -- -- ----------------------------------------------------------------------------- -- Version History -- =============== -- -- Who version Date Description -- === ======= ====== ====================== -- P.Finnigan 1.0 Sep 2017 First Issue. set pages 0 set lines 2000 select sql_id ||'|'||sql_text ||'|'||first_load_time ||'|'||parsing_user_id ||'|'||parsing_schema_id ||'|'||service ||'|'||module ||'|'||action ||'|'||last_load_time ||'|'||last_active_time from gv$sql; exit EOF done ENDTIME=$(date +'%Y%m%d %H:%M:%S.%N') ENDTOTAL=`date +%s` RUNTIME=$((ENDTOTAL-STARTTOTAL)) echo "- End at $ENDTIME " >> "$FILE.log"
echo "" >> "$FILE.log"
echo "SHA512 message digest:" >> "$FILE.log"
/usr/bin/sha512sum $FILE.dsv >> "$FILE.log"
echo "" >> "$FILE.log"
echo "User defined seconds to read (first thousand characters) from shared SQL area was $SECS seconds" >>"$FILE.log"
echo "Script runtime duration was $RUNTIME seconds" >>"$FILE.log"
echo "" >> "$FILE.log"
echo "- Make a working copy of $FILE.dsv and store the original, including "$FILE.log", as evidence. ">> "$FILE.log"
echo "- Use e.g. Elastic Stack or Splunk to import $FILE.dsv">> "$FILE.log"
echo "- Or use spreadsheet tool to import $FILE.dsv with delimiter sign '|'">> "$FILE.log"
echo "   For example in Microsoft Excel 2010:">> "$FILE.log"
echo "   Open Microsoft Excel.">> "$FILE.log"
echo "   Click on the Data tab.">> "$FILE.log"
echo "   In the Get External Data group, click From Text.">> "$FILE.log"
echo "   Double-click $FILE.dsv in the Import Text File dialogue box.">> "$FILE.log"
echo "   Click Import.">> "$FILE.log"
echo "   Select Delimited and click Next.">> "$FILE.log"
echo "   Uncheck all and select Other and enter '|'">> "$FILE.log"
echo "   Click Next.">> "$FILE.log"
echo "**************************************************************">> "$FILE.log"
if [ -f "$FILE.log" ]
then
cat "$FILE.log"
else
echo "$FILE.log not found."
fi

What’s in security – October 2009

Last Thursday I read that Oracle postpones the upcoming CPU patches. They state:

There is a change in the previously announced release date of the October 2009 Critical Patch Update.

Since many Oracle customers with responsibility for deploying the Critical Patch Update within their respective organizations will be attending Oracle OpenWorld October 11-15, 2009, the October 2009 Critical Patch Update originally scheduled to be published on Tuesday, October 13th 2009, will be released on October 20th 2009.

Please note: this date change only impacts the October 2009 Critical Patch Update. As usual, Oracle will issue a pre-release announcement on the Thursday before the publication of the Critical Patch Update (Thursday, October 15th). All other aspects of the Critical Patch Update (where to find the documentation, how to download the patches, etc.) remain the same.

(More info: see Metalink

A good reason would be that there are no important patches.. aren’t there..?