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