An existing tablespace cannot be encrypted. To apply TDE to an existing Star Schema:
- Create tablespaces that are encrypted. For more information, see Encrypting Tablespaces.
- Move data from existing tablespaces to the new ones using table redefinition. The files that are used are preferences.txt, grants_before_tde.sql, migrate.sql, and grants_after_tde.sql.
- Edit the preferences.txt file so that it matches your environment and security requirements:
alg varchar2(7 char) NOT NULL := 'AES128';
owner varchar2(30 char) NOT NULL := 'STARUSER';
source_dir varchar2(128 char) NOT NULL := '/u01/opt/oradata/orcl/';
target_dir varchar2(128 char) NOT NULL := '/u02/opt/oradata/orcl/';
- Replace 'STARUSER' with the user name applicable to your environment.
Note:
- source_dir is the folder that contains the current clear text application tablespaces.
- Target_dir can point to the same or another folder or partition which allows you to relocate the encrypted tablespaces.
- grants_before_tde.sql grant the required privileges to STARUSER (change STARUSER to your schema user in case it is different). Run the script as sys user.
- migrate.sql migrates all objects in the given clear text tablespace into its encrypted counterpart. Run this script from command prompt for both Star_Dat1 and Star_Hst1 tablespaces as staruser.
- migrate_tables_part.sql migrates the remaining tables that have row level security enabled. Run this script as staruser and post migrate.sql finishes.
- grants_after_tde.sql will revoke all the privileges that were provided to staruser for creating and migrating data to encrypted tablespaces. Run the script as sys user.
Post-migration steps
Once the migration of all tablespaces is complete, manually complete the following steps:
- Rename all tablespaces and take the clear text tablespaces offline (as 'STARUSER'):
alter tablespace STAR_DAT1 rename to STAR_DAT1_backup;
alter tablespace STAR_DAT1_ENC rename to STAR_DAT1;
alter tablespace STAR_DAT1_backup offline normal;
alter tablespace STAR_HST1 rename to STAR_HST1_backup;
alter tablespace STAR_HST1_ENC rename to STAR_HST1;
alter tablespace STAR_HST1_backup offline normal;
- After confirming that the ETL process and OBIEE reports runs seamlessly off encrypted tablespaces, the original clear-text tablespaces can now be deleted:
drop tablespace STAR_DAT1_backup including contents and datafiles;
drop tablespace STAR_HST1_backup including contents and datafiles;
- Log in as sysdba and run grants_after_tde.sql.