Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, June 27, 2013

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."
############################################################################

Thursday, June 20, 2013

Oracle 11g Methods of Performance Tuning SQL


>90% of upgrade related problems are performance issues after an upgrade.

Source: Oracle Corp


Oracle tools for helping you tune the database:
  • Statspack - FREE - (See note 394937.1)





  • AWR - Diagnostics Pack & Tuning Pack license required.


  • Real Application Testing (Features: SQL Performance Analyser & Database Replay) - Tuning Pack license required.



Since 11g, Oracle recommend, instead of: storing outlines, fixing stats, using SQL hints, using the Rule Based Optimiser (desupported); you should use the SQL Plan Management tool along with SQL Profiling.

See spm_white_paper_ow07.pdf for more information.

Thursday, June 13, 2013

HowTo: Recover Oracle DB with ORA-01547 after RESETLOGS Failed

Scenario: You have restored a database from a backup but part way through recovery, you didn't have all the archive redo logs.
Your recovery is an automated script that then progressed on to OPEN RESTLOGS, which failed.
Now you have all the archive logs and you want to re-run recovery, but you get ORA-01547 plus ORA-01194.

If you are certain that you now have all the archive logs (maybe you forgot to copy all of them), you need to restart recovery:

SQL> shutdown abort;
SQL> startup mount exclusive;
SQL> recover automatic database using backup controlfile until cancel;


You now get the error:

ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3



The database is looking for redo information generated during the previous recovery session.
During a recovery, redo information is generated because the database is replaying the undo records of transactions to be rolled back, but these records may need to be rolled back. So you get redo records.

Unfortunately, because your database is in a precarious state, it doesn't quite know where to look for the redo records.
So, all you need to do is tell it where the redo logs are located (your database probably won't have performed a log switch yet).

First, cancel the recovery:

SQL> CANCEL

For checking purposes, it's good to confirm that only SYSTEM tablespace is needing recovery by querying the current SCNs across all datafiles:

SQL> select distinct fhscn SCN from x$kcvfh;

SCN
----------------
5996813573990


Again, for checking purposes, you can see that one of the datafiles has a status of != 4. (4= consistent):

SQL> select distinct fhsta STATUS from x$kcvfh;

STATUS
----------
4
8196


You can see that this is the SYSTEM datafile (filenumber =1):

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh where fhsta = 8196;

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 5996813573990 1



To be able to provide the first redo log to the recovery process, you should query your log members.
I have found that usually, the recovery only needs the very first log member listed when you run the following query (I guess this is the "active" log member at the time the RESETLOGS was called):

SQL> select member from v$logfile;
MEMBER
----------------------------------------------------
/oradata/PROD/log_a/log4a_PROD.log            <<-- This one.
/oradata/PROD/log_b/log4b_PROD.log
/oradata/PROD/log_a/log3a_PROD.log
/oradata/PROD/log_b/log3b_PROD.log
/oradata/PROD/log_a/log2a_PROD.log
/oradata/PROD/log_b/log2b_PROD.log
/oradata/PROD/log_a/log1a_PROD.log
/oradata/PROD/log_b/log1b_PROD.log

8 rows selected.


Now you can call the recovery process again.
This time, when you are prompted for the archive log name, enter the redo log member name & path you got from the previous SQL:

SQL> recover automatic database using backup controlfile until cancel;
 
ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oradata/PROD/log_a/log4a_PROD.log

Log applied.
Media recovery complete.



Now open your database:

SQL> alter database open resetlogs;
 

Database altered.

SQL> exit


You should now run an immediate FULL backup using your preferred method.

Thursday, June 06, 2013

Oracle Doc: EBS R12 Upgrade/Patch Forms and Reports

There's a nice Oracle doc on how to upgrade the Oracle AS 10g Forms & Reports component of an Oracle EBS R12 instance:
Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12 [ID 437878.1]

Once you've read it, you'll realise what a mammoth task it is.
It's interesting that out-of-the-box, the Forms & Reports upgrades don't work with EBS, you need the interoperability patches to be installed ontop.