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