APTARE IT Analytics Technical Notes

Oracle supports TDE with Oracle Database Enterprise Edition. To use TDE with APTARE IT Analytics complete the following steps on a Linux platform:

1. Create a keystore path. For example: /opt/aptare/oracle/wallet

   # su - aptare
   # mkdir /opt/aptare/oracle/wallet

2. Edit/create the sqlnet.ora file in the Oracle installation. The default location is <install location>/oracle/network/admin with following content:

   --------------------------------
   #cat /opt/aptare/oracle/network/admin/sqlnet.ora
   ENCRYPTION_WALLET_LOCATION=
   (SOURCE=
      (METHOD=FILE)
        (METHOD_DATA=
   	(DIRECTORY=<path where keystore to be created for example /opt/aptare/oracle/wallet >)))
   --------------------------------

3. Copy the APTARE IT Analytics portal software ISO contents to a directory in the system where the Portal will be installed.
4. Edit the tablespace creation sql file to support TDE:

   • Copy the database_release_10XXX.tar.gz from the ISO contents to a temp directory:

     # cp /portal/aptare/linux/database_release_10XXX.tar.gz /tmp/oracle_ee_tde/

   • Extract the tar:

     #cd /tmp/oracle_ee_tde
     # tar -xzvf database_release_10XXX.tar.gz

   • Replace file ora_scripts/create_tablespaces_encrypt.sql with ora_scripts/create_tablespaces.sql:

     #cp ora_scripts/create_tablespaces_encrypt.sql ora_scripts/create_tablespaces.sql

   • Edit the following lines in the file ora_scripts/create_tablespaces_encrypt.sql to replace the place holder strings for keystore password, keystore path (for example /opt/aptare/oracle/wallet/) and encryption standard (for example AES256):

     PROMPT Creating keystore
     ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<keystore path mentioned in sqlnet.ora>' IDENTIFIED BY <keystore password>;
     PROMPT Checking wallet status
     select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;
     PROMPT Opening Wallet...
     administer key management set keystore open identified by <keystore password>;
     PROMPT Checking wallet status
     select WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;
     PROMPT Setting Master key
     ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY <keystore password> WITH BACKUP USING 'aptare_scdb';
     PROMPT Setting Auto login
     ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE  '<Keystore path mentioned in sqlnet.ora >' IDENTIFIED BY <keystore password>;
     CREATE TABLESPACE aptare_tbs_data_1m
             DATAFILE '/data01/oradata/scdb/aptare_tbs_data_1m_01.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 20G
                      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ENCRYPTION USING '<encryption standard>' DEFAULT STORAGE (ENCRYPT);  ->

     Note:

     Every tablespace in the file has an <encryption standard> to replace. All must be modified.

5. Create the tar with the modified file and replace it in the ISO contents.

   #cd /tmp/test
   # rm -f database_release_10XXX.tar.gz
   # tar -cvf /tmp/database_release_10XXX.tar *
   # gzip /tmp/database_release_10XXX.tar
   #cp /tmp/database_release_10XXX.tar.gz /portal/aptare/linux/

6. Run the schema creation script create_aptare_database.sh

   #su - aptare /portal/ create_aptare_database.sh