This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Oracle 11g Transparent Data Encrpytion (TDE) Tablespace Conversion Script

After needing to configure and setup an Oracle tablespace in an 11g database so that it was encrypted with Transparent Data Encryption (TDE), I devised a scripted method of doing this quickly and simply.

The script is hardcoded to only work on a tablespace called “USERS_TDE” at the moment, but if you find and replace this for your specific tablespaces. I’ve even included commented out code showing how to do more than one tablespace at a time.

You should also change the disk locations within the script for the location of the export/import dump files and the wallet file location.
As per the Oracle docs, the wallet file is stored outside of the Oracle home in /etc/ORACLE.

The basic process is:

  • Create wallet dir.
  • Adjust sqlnet.ora.
  • Create dpump dir.
  • Export tablespace data.
  • Export tablespace DDL metadata.
  • Adjust tablespace DDL to include TDE commands.
  • Enable TDE encryption at database level (sets the enc key).
  • Offline & drop existing tablespace.
  • Create TDE version of tablespace.
  • Import tablespace data.

!/bin/bash
#############################################################################
# Author: D.Griffiths
# Script: oracle_TDE_encrypt.sh
# Params: None.
# RunAs:  Oracle home owner.
# Desc:   Script to automatically export a tablespace called USERS_TDE

#         (change as you wish), drop it, re-create it ENCRYPTED with TDE
#         then re-import the data objects back into the tablespace.
#
# HISTORY ###################################################################
# v1.0, Created.
#############################################################################

# Variable definitions.
wallet_dir="/etc/ORACLE/WALLETS/$ORACLE_SID"
walletpw=""
dp_dir="/data/backup/tblspace_export_pre_TDE/$ORACLE_SID" # Datapump export/import dir.

# Check required variables.
if [ -z "$ORACLE_SID" -o `grep -c "^$ORACLE_SID:" /etc/oratab` -ne 1 ] ; then
   echo "ERROR: Invalid ORACLE_SID."
   exit 1;
fi

if [ -z "$ORACLE_HOME" ] ; then
   export ORACLE_HOME = "`awk '/'$ORACLE_SID'/ { split($0,a,":"); print a[2] }' /etc/oratab`"

   if [ -z "$ORACLE_HOME" ] ; then
      echo "ERROR: Failed to set ORACLE_HOME."
      exit 1;
   fi
fi

# Show setup.
echo "------------------------------------------"
echo "Starting TDE setup script."
date
echo "The following has been defined:"
echo " ORACLE_SID : $ORACLE_SID"
echo " ORACLE_HOME : $ORACLE_HOME"
echo " WALLET DIR : $wallet_dir"
echo " DATAPUMP DIR: $dp_dir"
echo "------------------------------------------"
echo " "

# Check if DB already has wallet/encryption.
retval="`sqlplus -s "/ as sysdba" <<EOF
set head off
set newpage none
set feedback off
set trimout on
set tab on
select DECODE(count(status),0,'NONE','SOME') from v\$wallet;
EOF`"

if [ "$retval" != "NONE" ] ; then
   echo "ERROR: Encryption may already be enabled on this database."
   exit 1;
 else
   echo "Encryption is not already enabled on this database."
fi

# Check for existence of Wallet Dir.
if [ ! -d $wallet_dir ] ; then
   mkdir -p $wallet_dir
   if [ $? -ne 0 ] ; then
      echo "ERROR: Failed to create wallet dir $wallet_dir"
      exit 1;
   fi
   

   echo "WALLET directory created: $wallet_dir"
 else
   echo "WARNING: Wallet directory already exists:"
   echo "$wallet_dir"
   echo -n "Continue to use this? {Y|N} : "
   read reply
   if [ "$reply" != "Y" -a "$reply" != "y" ] ; then
      exit 1;
   fi
 

   echo "Using wallet dir: $wallet_dir"
fi
 

if [ ! -d $dp_dir ]; then
   mkdir -p $dp_dir;
   if [ $? -ne 0 ] ; then
      echo "ERROR: Failed to create datapump dir $dp_dir"
      exit 1;
   fi
 else
   echo "ERROR: Datapump dir already exists, files can not be overwritten."
   echo "Check: $dp_dir"
   exit 1;
fi

# Check sqlnet.ora file.
if [ "`grep -c '^ENCRYPTION_WALLET_LOCATION' $ORACLE_HOME/network/admin/sqlnet.ora`" -ne 1 ] ; then
   echo "Adding ENCRYPTION_WALLET_LOCATION to sqlnet.ora in $ORACLE_HOME/network/admin/sqlnet.ora"
   cat <<EOF >>$ORACLE_HOME/network/admin/sqlnet.ora
# Added WALLET location for TDE see 1228046.1.
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = ${wallet_dir}/)))
EOF

   if [ $? -ne 0 ] ; then
      echo "ERROR: Failed to update sqlnet.ora."
      exit 1;
   fi
 else
   echo "Section ENCRYPTION_WALLET_LOCATION already exists in sqlnet.ora"
fi

# Get list of unencrypted non-system tablespaces
list_tblspace="`sqlplus -s "/ as sysdba" <<EOF
ttitle off
btitle off
set newpage none
set feedback off
set head off
set wrap off
set trim on
set tab on
set linesize 30
set pagesize 1000
-- select rpad(UPPER(t.name),30)
-- from v$tablespace t
-- where t.name not in ('SYSTEM','SYSAUX','UNDO','TEMP')
-- and t.ts# not in (select ts# from v$encrypted_tablespaces);
select rpad('USERS_TDE',30) from dual;
EOF`"

# Confirm tablespaces.
echo " "
echo "The following non-system unencrypted tablespaces have been found:"
echo "$list_tblspace"
echo -n "Setup these for TDE? {Y|N} : "
read reply

if [ "$reply" != "Y" -a "$reply" != "y" ] ; then
   exit 1;
fi

# Check dbdump oracle directory doesnt exist.
retval="`sqlplus -s "/ as sysdba"<<EOF
set head off
set newpage none
set feedback off
set trimout on
set tab on
select trim(directory_path) from dba_directories
where directory_name='DPDUMP_BACKUP';
EOF`"

if [ -n "$retval" ] ; then
   echo "WARNING: Oracle directory DPDUMP_BACKUP already exists."
   echo "It is assigned to path: $retval"
   echo "OK to recreate it to $dp_dir"
   echo -n "Enter {Y|N} : "
   read reply
   if [ "$reply" != "Y" -a "$reply" != "y" ] ; then
      exit 1;
   fi
fi

# Create the datapump export/import dir in oracle.
sqlplus -s "/ as sysdba" <<EOF
create or replace directory dpdump_backup as '$dp_dir';
EOF

echo "------------------------------------------"
echo "Exporting tablespaces and generating DDL."

# Export the tablespaces to the backup directory location and generate DDL.
echo "$list_tblspace" | while read tablespace
do
   expdp userid="'/ as sysdba'" dumpfile=$tablespace.dmp directory=dpdump_backup logfile=${tablespace}_exp.log tablespaces="$tablespace"

   # Generate the tablespace creation DDL to the backup directory location.
   sqlplus -s "/ as sysdba" << EOF
SET LONG 10000
set head off
set newpage none
set trimspool on
spool $dp_dir/cre_$tablespace.sql
SELECT dbms_metadata.get_ddl('TABLESPACE','$tablespace') FROM DUAL;
SPOOL OFF
EOF

   # Adjust the DDL file to include the ENCRYPTION string.
   cat $dp_dir/cre_$tablespace.sql | grep -e '^.*[A-Z0-9]' > $dp_dir/cre_TDE_$tablespace.sql

   echo " ENCRYPTION using 'AES256'" >> $dp_dir/cre_TDE_$tablespace.sql
   echo " STORAGE (ENCRYPT)" >> $dp_dir/cre_TDE_$tablespace.sql
 

done

########### ENABLE ENCRYPTION FROM HERE ON IN ################################

while [ -z "$walletpw" ] ; do
   echo -n "Enter the wallet encryption key to use: "
   read walletpw
   echo -n " Re-enter the wallet encryption key: "
   read reply
   if [ "$walletpw" != "$reply" ] ; then
      echo "WARNING: Typed keys do not match."
      echo -n "Try again? {Y|N} : "
      read reply
      if [ "$reply" != "Y" -a "$reply" != "y" ] ; then
         exit 1;
      fi
      walletpw=""
   fi

done

# Alter the DB to enable encryption.
sqlplus "/ as sysdba" << EOF
alter system set encryption key identified by "$walletpw";
EOF

if [ $? -ne 0 ] ; then
   echo "WARNING: Enabling encryption may have failed in the DB."
   exit 1;
fi

# Change permissions on all files in the wallet dir.
chmod 600 "$wallet_dir/*"

# Offline and then drop the tablespace and datafiles.
# WARNING: You should have a backup at this point.
echo "WARNING: About to offline and drop tablespaces:"
echo "$list_tblspace"
echo "##################################################"
echo "You should check the expdp export logs in $dp_dir."
echo "##################################################"

echo -n "Continue? [Y|N]: "
read reply
if [ "$reply" != "Y" -a "$reply" != "y" ]; then
   echo "Cancelling."
   exit 1
fi

# Offline and drop each tablespace (including datafiles) then re-create and import export dump file.
echo "$list_tblspace" | while read tablespace
do
   sqlplus "/ as sysdba" << EOF
alter tablespace $tablespace offline;
drop tablespace $tablespace including contents and datafiles;
@$dp_dir/cre_TDE_$tablespace.sql
-- @$dp_dir/cre_$tablespace.sql <<-- Recreates original non-TDE tablespace.
/
EOF

   impdp userid="'/ as sysdba'" dumpfile=$tablespace.dmp directory=dpdump_backup logfile=${tablespace}_imp.log tablespaces="$tablespace"

done

echo "#######################################################"
echo "You should enable auto-open of the wallet if necessary."
date
echo "End of script."
############################################################################

HowTo: Script eMail With Attachment Direct via SMTP Server

Have you ever wondered how you can directly send an email with a file attachment, direct from a shell script via an SMTP server?
That is, without needing to rely on any mail or sendmail setup on the UNIX/Linux server.
Well, it’s simple.

Create a script that encodes the file you wish to attach, into BASE64, then type the following to send the file:

telnet somesmtpserver.net 25<<EOF
helo yourserver.net
mail from: noreply@mymail.net
rcpt to: noreply@mymail.net
data
Subject: Test attachment

Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=attachmentbound

--attachmentbound
The email body

--attachmentbound
Content-Type: application/octet-stream; name="attachmentfile"
Content-Disposition: attachment; filename="thefilename.txt"
Content-Transfer-Encoding: base64

VGVzdCBBdHRhY2htZW50     <<-- Insert your BASE64 content here!

.
quit
EOF

You need to have the content already encoded in BASE64.
For this you could use a Perl script using the MIME::Base64 module as shown below:


#!/usr/bin/perl
use MIME::Base64;
#printf ("%s", encode_base64(eval ""$ARGV[0]""));

## Check to see if each and every file exists ##
foreach $file (@ARGV)
{
if (not -e $file)
{
print "File "$file" does not existn";
exit( 1 );
}

# Convert file to BASE64
open( OUTFILE, ">$file.out") or die "Could not open outputfile: $!n";;
open( FILE, $file ) or die "Could not open inputfile: $!n";
{
local($/) = undef;
print OUTFILE encode_base64(<FILE>);
}
close( FILE );
close( OUTFILE );
}


Script Korn Shell to use Bold Output

Here’s how you can use a normal Korn shell script to put out bold text on the screen providing the emulator you are using supports it:

#!/bin/ksh
$ bold=`tput smso`
$ regular=`tput rmso`

$ echo "${bold}Please type in your name: c"
$ echo "${regular}OK"

See the man page for tput for a wild array of other capabilities.
It’s possible you can even use tput to draw in different parts of the screen.
This could be used to create a drop-down box or a menu select box in KSH.
It can be done!  Honest!

Windows Batch File to Install Windows Task

@cls
@rem ######################################################################
@rem # Script:  install_task.bat
@rem # Params:  none.
@rem # Desc:    This script installs the task that will run the
@rem #           start.bat on the required schedule.  WinXP or Win2k3
@rem # Author:  D.Griffiths
@rem # Version: 1.0
@rem # History:
@rem #          09-June-2008 – DMG – Created.
@rem #
@rem ######################################################################

@set TN=My_TaskName
@set DAYS=MON,TUE,WED,THU,FRI,SAT
@set TR=C:start.bat
@set ST=05:50:00

@set /P conf=Press return to install task “%TN%”:

@echo Tasks currently scheduled…
@schtasks /Query /FO TABLE

@echo.
@echo Removing existing task if already present…
@schtasks /Delete /TN %TN% /F

@echo.
@echo Installing new task “%TN%”…
@schtasks /Create /RU SYSTEM /SC WEEKLY /D %DAYS% /TN %TN% /TR %TR% /ST %ST%
@IF ERRORLEVEL 1 goto invalid_task

@echo.
@echo Task “%TN%” now scheduled.
@echo.
@echo Tasks currently scheduled…
@schtasks /Query /FO TABLE
@echo.
@echo.
@set /P conf=Press return to exit.
@exit

:invalid_task
@echo.
@echo.
@echo ERROR: There was a problem installing the task.
@echo ERROR: Please try again or check the command syntax.
@set /P conf=Press return to exit.