Friday, November 28, 2008

Encrypting Network Traffic Using Advanced Security - R12

To configure the E-Business Suite Release 12 to encrypt network traffic sent over the TNS protocol we use the Advanced Networking Option (ANO) that is part of the Advanced Security Option (ASO) of the Oracle database and included with the Release 12 E-Business Suite Technology Suite.

TNS (Transparent Networking Substrate) is an Oracle protocol running on top of a number of supported network protocols - typically TCP/IP. ANO/ASO encryption prevents sending TNS traffic "in-the-clear" over a network connection.
CERTIFICATION: This configuration is certified for Oracle Applications Release 12 using Forms listener Servlet (the default mode) on the following platforms: Linux-x86 Solaris-32 AIX4-32 Tru64 HP-UX Windows-32 .

Advanced security encryption can be configured, based on a combination of client and server configuration parameters as REJECTED, ACCEPTED, REQUESTED or REQUIRED.

Oracle has certified EBS Release 12 with the server parameter set to REQUIRED - this ensures that all EBS TNS network traffic is being encrypted. Although ANO/ASO supports a number of different encryption algorithms, the supported algorithms are version dependent.

For Release 12 certification the the server's preference is set to AES256, AES192, 3DES168.
Steps to enable the encryption in each of the different ORACLE_HOME’s in an EBS deployment.

Step 1 - Shutdown Middle Tier Server Processes and Database Listener

1. On the database server node, shut down the database listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh stop DEV

2. On each middle tier server, shut down all processes or services:
$ADMIN_SCRIPTS_HOME/adstpall.sh apps user / apps password

Step 2 - DB Tier Changes

1. Logon to the DB Tier server as the file system owner.

2. Source the DB Tier environment file located in Oracle Home directory.

3. Take a backup of the $TNS_ADMIN/sqlnet_ifile.ora file.

4. Open the $TNS_ADMIN/sqlnet_ifile.ora file with the editor of your choice and add the following lines replacing [crypto seed] with a string consisting of 10 - 70 alphanumeric characters of your choosing. The characters that form the value fo this parameter will be used when generating cryptographic keys. The more random the characters entered into this field are, the stronger the keys are.

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256, AES192, 3DES168) SQLNET.ENCRYPTION_SERVER=REQUIRED SQLNET.CRYPTO_SEED=[crytpo seed]

Note: Oracle Corporation recommends that you enter as many characters as possible, up to 70, for the crypto seed to make the resulting key more random and therefore stronger.

5. After the changes have been made, restart the listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh start DEV

Step 3 - Create $TNS_ADMIN/sqlnet.ora and sqlnet_ifile.ora files on each Middle Tier.

By default, the Oracle Applications Middle Tier installations do not have either a sqlnet.ora or sqlnet_ifile.ora file so we will need create these. We keep the ANO/ASO directives in the sqlnet_ifile.ora file to isolate it from any future autoconfig updates that affect the sqlnet.ora file.

1. Logon to the Middle Tier server as the file system owner.
2. Source your middle tier environment file (APPSDEV_tlsdev.env) located in the APPL_TOP directory.
3. Navigate to the $TNS_ADMIN directory.
4. Use the editor of you choice to create the sqlnet.ora file with the following lines:

#######################################################################
sqlnet.ora file for middle tier sqlnet encryption with Advanced SSL Configuration
######################################################################
IFILE = /sqlnet_ifile.ora
5. Use the editor of you choice to create the sqlnet_ifile.ora file with the following lines:

######################################################################### sqlnet_ifile.ora for middle tier sqlnet encryption with Advanced SSL Configuration
#######################################################################
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256, AES192, 3DES168)
SQLNET.ENCRYPTION_CLIENT=REQUIREDSQLNET.CRYPTO_SEED=
somelongandrandomstringfordeploymentUpTo70characters

Note: the SQLNET.CRYPTO_SEED does not need to be the same as used on the db tier.

Step 4 - Update the Context File

Use the Oracle Applications Manager (OAM) Context Editor to change the SSL related variables on each middle tier server as shown As Below:



Set s_custom_dbc_params value as ENCRYPTION_CLIENT=REQUIRED ENCRYPTION_TYPES_CLIENT=(3DES168)

Step 5 - Run Autoconfig 1. Run autoconfig on each middle tier server:
$ADMIN_SCRIPTS_HOME/adautocfg.sh appspass=apps password

Step 6 - Restart the Middle Tier Services1. On each middle tier server, restart all processes and services: $ADMIN_SCRIPTS_HOME/adstrtall.sh apps user / apps password

Ref: Metalink Doc no: 376700.1

How to DROP an Oracle Database?

You can do it at the OS level by deleting all the files of the database.
The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora.
Make sure that the oratab entry is also removed.
With dbca you can easily drop an oracle database.
However you may wish manually to delete a database. The following steps will help you to manually drop a database.

1)Set the Oracle SID of the database which you interested to drop.
On my system it is,

$export ORACLE_SID=dbase

2)Connect to SQL*Plus as SYSDBA privilege.

$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 02:41:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

3)Mount the database.

SQL> startup force mount

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 92274728 bytes
Database Buffers 67108864 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Enable restricted session.

SQL> alter system enable RESTRICTED session;
System altered.

5)Drop the database.

SQL> drop database;
Database dropped.