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

SAP Netweaver 731 Oracle Create DB Statement

By default, when you use the Software Provisioning Manager (SWPM) to create a new NW731 Oracle database, it will generate and run an Oracle “CREATE DATABASE” statement as follows:

SQL> CREATE DATABASE DB1 CONTROLFILE REUSE 
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXLOGHISTORY 1000
MAXDATAFILES 1000
MAXINSTANCES 50
NOARCHIVELOG
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
DATAFILE '/oracle/DB1/sapdata1/system_1/system.data1' SIZE 350M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE PSAPTEMP TEMPFILE '/oracle/DB1/sapdata1/temp_1/temp.data1' SIZE 50M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
UNDO TABLESPACE PSAPUNDO DATAFILE '/oracle/DB1/sapdata1/undo_1/undo.data1' SIZE 100M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
SYSAUX DATAFILE '/oracle/DB1/sapdata1/sysaux_1/sysaux.data1' SIZE 200M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 10000M
LOGFILE GROUP 1 ('/oracle/DB1/origlogA/log_g11m1.dbf') SIZE 200M  REUSE ,
GROUP 2 ('/oracle/DB1/origlogA/log_g12m1.dbf') SIZE 200M  REUSE ;


Notice that both the character set and national character set are UTF8.

Kill All Oracle Sessions For Specific User – 11gR2

Here’s a small script to display, then kill all sessions in an Oracle 11gR2 database for a specific username.

First let’s check the sessions of the specific username:

NOTE: Change “<USER NAME HERE>” in both blocks of code, for the actual username to be killed.

SQL> set serveroutput on size 10000;
SELECT sid,serial#
  FROM v$session
WHERE username = '<USER NAME HERE>';

Now run the code to kill the user sessions:

SQL> DECLARE
   dummy NUMBER;
   c     NUMBER := dbms_sql.open_cursor;
BEGIN
   FOR c1_row IN (SELECT sid,serial#
                  FROM v$session
                  WHERE username = '<USER NAME HERE>') LOOP
      DBMS_OUTPUT.PUT_LINE('Killing session: '||c1_row.sid||' serial#: '||c1_row.serial#);
      DBMS_SQL.PARSE(c,'alter system kill session '||''''||c1_row.sid||','||c1_row.serial#||'''',dbms_sql.NATIVE);
      dummy := DBMS_SQL.EXECUTE(c);
      DBMS_OUTPUT.PUT_LINE('Session: '||c1_row.sid||' serial#: '||c1_row.serial#||' killed.');
   END LOOP;

   dbms_sql.close_cursor(c);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

You can now run the check code again to see that all sessions for the user have been killed.

Find RMAN Backup Statistics

You can query the view V$BACKUP_SYNC_IO (for synchronous tape devices) to obtain the average MB transfer speed from RMAN to the tape device (or intermediary software if using the obk interface):

SQL> select avg(EFFECTIVE_BYTES_PER_SECOND)/1024/1024 MB_per_s
       from V$BACKUP_SYNC_IO
      where DEVICE_TYPE='SBT_TAPE';

MB_PER_S
-----------
16.1589822

HowTo: Use Oracle BBED to adjust DB Name in File Headers

HowTo: Use BBED to hack the database SID in the datafiles if you’ve gone and got them all mixed during a “CREATE CONTROLFILE” operation.

WARNING: Using BBED is not supoprted by Oracle unless you are asked to use it by Oracle Support themselves.

Use UNIX vi to create a text file that contains a line number, followed by the file name for the DB files that need changing:

# cat <<EOF > filelist.txt
1 /db/ora/system/system1.dbf
2 /db/ora/data1/data1.dbf
3 /db/ora/index1/index1.dbf
EOF

Save the file as “filelist.txt”.

Launch bbed (blockedit) as the Oracle DB UNIX owner.
Change the text “NEWID” for your new DB name in the “modify” line below.

$ bbed
BBED> set listfile 'filelist.txt'
BBED> set mode edit

# Dump the current block value for datafile #1 in your list file.
# exmaple: BBED> dump /v dba <file#>,<block> …

BBED> dump /v dba 1,1 offset 32 count 16

Make the swap:

BBED> modify /c NEWID file 1 block 1 offset 32

The checksum is now invalid:

BBED> sum file 1 block 1

Force save the new checksum:

BBED> sum file 1 block 1 apply

Verify the block:

BBED> verify file 1 block 1

Once you’ve done all your files:

BBED> quit;

Start the database with the CREATE CONTROLFILE SET DATABASE “NEWID”…

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