Encrypting Tablespaces Using Transparent Data Encryption

Note: The procedure explained below to encrypt an existing database must be performed on the source environment before performing the lift.

This topic summarizes the procedure to enable Transparent Data Encryption (TDE) Tablespaces Offline Encryption for an Oracle PeopleSoft Applications database. This process is referred to as using the Fast Offline Conversion method to convert existing clear data (residing in non TDE encrypted tablespaces) to TDE encrypted tablespaces. In order to use this feature, the PeopleSoft Applications database requires downtime, as the tablespace(s) to be encrypted need to be temporarily offline. As the encryption is transparent to the application, code does not have to be rewritten, and existing SQL statements work as they are. Transparent also means that any authorized database session can read the encrypted data without any problem: the encryption only applies to data-at-rest, meaning the database data files and any backups of them.

Refer to the information on Transparent Data Encryption in the Oracle Database Advanced Security Guide for your Oracle Database version. See Oracle Database Documentation, https://docs.oracle.com/en/database/oracle/oracle-database/index.html.

  • This procedure can be used with Oracle PeopleSoft Applications Database on supported Oracle Database versions.

    See PeopleSoft Cloud Manager Home Page, My Oracle Support, Doc ID 2231255.2, for support information.

  • Understand TDE implications and restrictions and develop a process for maintaining wallets and keys. Refer to the Oracle Database Advanced Security Administrator's Guide for further details.

  • Ensure the compatible database parameter is set to the appropriate database version, 19c or later.

  • Always take a full backup of your database before starting the procedure.

The following restrictions apply to implementing Tablespace Encryption using Fast Offline Conversion:

  • It can only be performed for application tablespace data files. SYSTEM, SYSAUX, UNDO and TEMP tablespaces cannot be encrypted.

  • External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption because these files reside outside the database. PeopleSoft applications do not utilize BFILEs.

To perform TDE Tablespace Offline Encryption for an Oracle PeopleSoft Applications database, follow the steps below:

  1. Shut down application server processes.Shut down all Applications server processes and make sure all jobs are completed cleanly before continuing further. Users should be prevented from using the Applications database until the encryption process is completed.

  2. Source your Oracle PeopleSoft Applications Database Oracle Home.

  3. Create a wallet by specifying the wallet location in the sqlnet.ora file under the $TNS_ADMIN directory:

    1. Add the following entry to the sqlnet.ora:

      ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = $ORACLE_HOME/admin/TDE/$ORACLE_SID)))

    2. Create the corresponding directory manually:

      $ mkdir -p /$ORACLE_HOME/admin/TDE/$ORACLE_SID

    3. Check wallet location and status:

      $ sqlplus / as sysdba;SQL>select * from V$encryption_wallet;

  4. Create a Keystore in the wallet.

    SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/$ORACLE_HOME/ADMIN/tde/$ORACLE_SID’ IDENTIFIED BY "<Strong password>";

  5. Open the Keystore create in step 4. As we are in a multitenant environment, we have to specify CONTAINER=ALL in order to set the keystore in all the PDBs:

    SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY PASSWORD CONTAINER=ALL;

  6. Set the master encryption key:

    SQL>ADMINSTER KEY MANAGEMENT SET KEY IDENTIFIED by "<Strong password>" CONTAINER=ALL;

    Note: The password must be enclosed in double quotes as shown.

  7. Bounce the database:

    SQL> shutdown normal;SQL> exit;

  8. Start up the database normally, ensuring that the wallet is open:

    sqlplus "/ as sysdba"SQL>startup;  SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<Strong password>" CONTAINER=ALL;

  9. Switch to the PeopleSoft PDB.

    SQL> ALTER SESSION SET CONTAINER=<PDBNAME>;

  10. Identify all the temporary and undo tablespaces in the database:

    SQL>select tablespace_name from dba_tablespaces where contents='TEMPORARY' and STATUS='ONLINE';
SQL>select tablespace_name from dba_tablespaces where contents='UNDO' and STATUS='ONLINE';

  11. While still in the PDB, generate three scripts, which will be used perform the TDE offline data conversion.

    ALTDATAFILESOFFLINE.SQL

    ALTDATAFILESENCRYPT.SQL

    ALTDATAFILESONLINE.SQL

    1. Script One takes specific data files offline. Create a script file with the following statements and save file as generatealtdatafilesoffline.sql.

      sqlplus "/ as sysdba"
SET LINESIZE 256
SET HEADING OFF;
SET TERM OFF;
SET FEED OFF; 
SPOOL ALTDATAFILESOFFLINE.SQL
select 'alter database datafile '’’||b.file_name|| ’’’ offline;' 
from dba_tablespaces a, DBA_DATA_FILES b
where a.tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PSTEMP','PSGTT01')and a.tablespace_name=b.tablespace_name
;	
Spool off
Exit
If you call the generation script GENERATEALTDATAFILESOFFLINE.SQL using @ from SQLPLUS, then you will not have to do any additional editing of the generated script.
SQL>alter session set container=<PDBNAME>
System altered.
SQL>@generatealtdatafilesoffline.sql 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$

    2. Script Two offline encrypts data files offline. Create a script file with the following statements and save file as generatealtdatafilesencrypt.sql.

      sqlplus "/ as sysdba"
SET LINESIZE 256
SET HEADING OFF;
SET TERM OFF;
SET FEED OFF;
SPOOL altdatafilesencrypt.sql 
select 'alter database datafile ’’’||b.file_name|| ’’’ ENCRYPT;' 
from dba_tablespaces a, DBA_DATA_FILES b
where a.tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PSTEMP','PSGTT01')and a.tablespace_name=b.tablespace_name
;
Spool off
Exit
If you call the generation script GENERATEALTDATAFILESENCRYPT.SQL using @ from SQLPLUS, then you will not have to do any additional editing of the generated script.
SQL>alter session set container=<PDBNAME>;
System altered.
SQL>@generatealtdatfilesencrypt.sql 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$

    3. Script Three brings data files back online. Create a script file with the following statements and save file as generatealtdatafilesonline.sql.

      sqlplus "/ as sysdba"
SET LINESIZE 256
SET HEADING OFF;
SET TERM OFF;
SET FEED OFF;
SPOOL altdatafilesoonlineexec.sql 
select 'alter database datafile ’’’||b.file_name|| ’’’ online;' 
from dba_tablespaces a, DBA_DATA_FILES b
where a.tablespace_name not in ('SYSTEM','SYSAUX','TEMP','PSTEMP','PSGTT01')and a.tablespace_name=b.tablespace_name
;
Spool off
Exit

If you call the generation script GENERATEALTDATAFILESONLINE.SQL using @ from SQLPLUS, then you will not have to do any additional editing of the generated script.

SQL>alter session set container=<PDBNAME>;
System altered.
SQL>@generatealtdatafilesonline.sql 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$

    4. Then get back to root or the CDB level.

      SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

    5. Close the PDB. We want the state to be in 'MOUNT' mode.

      SQL> ALTER PLUGGABLE DATABASE <PDBNAME> CLOSE IMMEDIATE;

    6. Switch to the PeopleSoft PDB.

      SQL> ALTER SESSION SET CONTAINER=<PDBNAME>;

  12. Bring all the specified tablespaces offline by connecting to SQL*Plus as sysdba, and running the script altdatafilesoffline.sql.

    $ sqlplus / as sysdbaSQL> @altdatafilesoffline.sql;

  13. Encrypt your datafiles by running the altdatafilesencrypt.sql offline encryption script from SQL*Plus as sysdba:

    $ sqlplus / as sysdba
SQL>@altdatafilesencrypt.sql;

    Note: If you have a large number of data files, you can parallelize their encryption by creating sub-scripts and running the sub-scripts from parallel SQL*Plus sessions.

  14. Bring all the specified tablespaces online by connecting to SQL*Plus as sysdba, and running the script altdatafilesonline.sql.

    $ sqlplus / as sysdbaSQL> @altdatafilesonline.sql;

    Note: Some tablespaces may take time to show as online. These are probably tablespaces that are encrypted.

    Check the status of tablespace encryption by connecting to SQL*Plus / as sysdba and running the query shown:

    $ sqlplus / as sysdba
SQL>select tablespace_name, encrypted from dba_tablespaces;

    Note: Unless an auto login keystore is created, every time the database is started up, the wallet will need to be opened as in Step 8 above.

    To make the wallet auto login, run the following command:

    $ sqlplus / as sysdba$ administer key management create AUTO_LOGIN keystore from keystore "<Wallet Path>" identified by "<Wallet Password>";

    Bounce the database.