Breadcrumbs

Encrypting an Existing Tablespace with Online Conversion

To encrypt an existing tablespace with online conversion, use ALTER TABLESPACE with the ONLINE and ENCRYPT clauses.

  1. Login to DB instance as a system user.

›_ sqlplus console 

SQL> connect system/<password>;

  1. Open HSM Keystore.

›_ sqlplus console 

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_password>;

  1. Create a tablespace.

›_ sqlplus console 

SQL> create tablespace NONSECURE_TS DATAFILE '/u01/app/oracle/oradata/utimacodb/NONSECURETS_01.DBF' SIZE 10M;

  1. Create EMP table inside the NONSECURE_TS tablespace.

›_ sqlplus console 

SQL> create table EMP (EMPID NUMBER(4),NAME VARCHAR(100),SALARY NUMBER(6)) tablespace NONSECURE_TS;

  1. Insert data into EMP table.

›_ sqlplus console 

SQL> insert into EMP values (0001, 'Michael Jackson', 999999); SQL> insert into EMP values (0002, 'Lady Gaga', 888888); 

SQL> insert into EMP values (0003, 'Freddie Mercury', 777777); 

SQL> insert into EMP values (0004, 'Steven Tyler', 666666); SQL> commit;

  1. View the data from EMP table.

›_ sqlplus console 

SQL> select * from EMP;

  1. Check the COMPATIBLE parameter is set correctly according to DB version.

›_ sqlplus console 

SQL> show PARAMETER COMPATIBLE; 
Example 

SQL> show PARAMETER COMPATIBLE; 

NAME TYPE VALUE 
------------ ----------- ------
compatible string 19.0.0

You must change the COMPATIBLE parameter if value is not 19.0.0, then complete the remaining steps in this procedure. To change the COMPATIBLE parameter, edit the initialization parameter file to use the new COMPATIBLE setting.

  1. Encrypt the NONSECURE_TS tablespace.

›_ sqlplus console 

SQL> alter tablespace NONSECURE_TS ENCRYPTION ONLINE using 'AES192' ENCRYPT FILE_NAME_CONVERT = ('NONSECURETS_01.DBF','SECURETS_02.DBF');

  1. View the data from EMP table.

›_ sqlplus console 

SQL> select * from EMP;

  1. Verify the NONSECURE_TS tablespace got encrypted.

›_ sqlplus console 

SQL> Select TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

  1. Close the keystore.

›_ sqlplus console 

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY <hsm_password>;

Now try to view the contents of EMP table, nothing is displayed as the wallet is closed.

As the keystore is closed, you will get an error message “ORA-28365: wallet is not open” and hence you cannot view the data from EMP table.

  1. Open the keystore.

›_ sqlplus console 

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY <hsm_password>;

  1. Verify EMP table.

›_ sqlplus console 

SQL> select * from EMP;