“This is a video that I created for the Palsit security conference in Ljubljana recently. It would have been nice to go to Slovenia and present in person or even have done it live over webex but due to the pandemic i was asked to pre-record so that it was easier for the organisers. So here it is. This is a talk that is an overview of Oracle security and securing an Oracle database in the current age.” (Pete Finnigan)
Source: https://www.youtube.com/watch?v=Ia80xehOuyY
© PeteFinnigan.com Limited

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.

if [ $# -ne 2 ]; then
echo "Usage: $0 <output filename> <seconds to read (first thousand characters) from shared SQL area>"
exit 1
if [[ "$2" != +([0-9]) ]]
echo "Usage:       $0 <output filename> <seconds to read (first thousand characters) from shared SQL area>"
echo "For example: $0 case_suspicion 120"
exit 1
STARTTIME=$(date +'%Y%m%d %H:%M:%S.%N')
echo "**************************************************************" > "$FILE.log"
echo "" >> "$FILE.log"
echo "- Start at $STARTTIME " >> "$FILE.log"
STARTRUN=$(date +'%Y%m%d %H:%M:%S.%N')
STARTTOTAL=`date +%s`
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" ]
cat "$FILE.log"
echo "$FILE.log not found."

Upgrade your database to 12c with RMAN Duplicate

If you duplicate a database to a higher version, you would restore a RMAN backup and upgrade that. Using duplicate would fail (ORA-39700) since it shall open the auxiliary with resetlogs.

In RMAN 12c, the “NOOPEN” option is available, requesting RMAN to complete the duplicate activity but NOT open the auxiliary database (this would allow for the ‘open upgrade’ option to be manually executed when going between versions).

Source: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2022820.1

RMAN duplicate fails to create BCT file despite fix of bug 11744544 ( RMAN-11003 ORA-19750 )

Remember https://oracle.powerbytes.nl/ever-ran-into-an-ora-19755-with-rman/ ?

RMAN duplicate fails to create BCT file despite fix of bug 11744544 !!


Created 09-03-2014, fixed in version 12.2

ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DATA/rac2prd/changetracking/ctf.2646.835861329'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/23/2016 21:44:21
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database reco
ver logfile '+DATA2/oracle/AUX/archivelog/2016_01_23/o1_mf_1_98404_urqs4ef3_.
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '+DATA/rac2prd/changetracking/ctf.2646.835861329'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solve with Patch 18371441

A workaround would be to disable change tracking before duplicating.

Ever ran into an ORA-19755 with RMAN?

Ever ran into an ORA-19755 with RMAN duplicate from target? When your source database uses block change tracking?

Try patch 11744544; it fixes the bug which could might cause block change tracking recreation fail in combination with OMF newnames.

A workaround would be to disable change tracking before duplicating.



WordPress – How to Fix File and Folder Permissions – reset_wp_perm.sh

echo "v1.1, 2014120301"
echo ""
echo "Current directory is:"
read -p "Make sure you are in your WordPress directory, are you ? (y/n) " -n 1 -r
echo ""
if [[ $REPLY =~ ^[Yy]$ ]]
echo ""
echo "... Setting chmod 775 for all directories"
find ./ -type d -exec chmod 775 {} \;
echo "... Setting chmod 664 for all files"
find ./ -type f -exec chmod 664 {} \;
echo "... But setting chmod 664 for wp-config.php, though, assuming chown is set to ft
p user"
chmod 664 wp-config.php
echo "Type username"
read user
echo "... Setting chown $user:www-data for *"
chown -R $user:www-data *
#echo "... Setting chown to :www-data for wp-content/uploads"
#chown -R :www-data wp-content/uploads
echo "... Setting chown :www-data for .htaccess"
chown :www-data .htaccess
echo "... Setting chmod 644 for .htaccess"
chmod 644 .htaccess
echo ""
echo "Done"
echo ""
read -p "Also run fix for WordPress upgrade and file permissions issue ? (y/n) " -n 1
echo ""
if [[ $REPLY =~ ^[Yy]$ ]]
echo ""
echo "... Changing wp-content group ownership to www-data"
chgrp -R www-data wp-content
echo "... Making wp-content and all of its sub-directories group-writable"
chmod -R g+w wp-content
echo "... Newly-created files to be group-owned"
chmod g+s wp-content
echo "If necessary, add the following to the bottom of wp-config.php"
##/* Force direct file updating
##- taken from http://www.charleshooper.net/blog/wordpress-auto-upgrade-and-dumb-permi
##For more information, take a look at wp-admin/includes/file.php’s function get_files
echo "define('FS_METHOD', 'direct');"


Backup recovery area (FRA) without a tape device

Backup to disk

RMAN> run {
allocate channel d1 type disk format='/tmp/%U';
backup tablespace sysaux;

Backup to a fake tape

The fake tape library will call the MML APIs, essentially similar to an MML backup, but will place the file on disk (RMAN disksbt library emulates a SBT library). It cannot be used for production backups but can be used for verification purposes such as these:

RMAN> run {
allocate channel t1 type 'SBT' PARMS 'SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/tmp)';
backup tablespace sysaux;

The fake tape backup comes in handy when one wants to backup the recovery area; the ‘backup recovery area’ command only works with SBT channels. FRA backup to disk does not work.