Monday, June 1, 2020

Encrypting / Decrypting Tablespaces Online / Offline

Oracle database allow the ability to encrypt (or decrypt) tablespace online (or offline). Online method requires twice the space as the tablespace been worked on as work (encryption or decryption) happens out of place. In the offline method the work happens inplace as such in case of failure would need to recover from a backup. This post shows the online and offline encryption and decryption of an existing tablespace. It is assumed master key is already created for the database. The options could be interchanged, i.e. online encrypted tablespace could be decrypted offline and vice versa.

Online Encryption
The tablespace name stbs was chosen for encryption. Currently it's not encrypted (only the tablespace in question is shown).
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
STBS
Enable the encryption for the tablespace with encryption online encrypt.
SQL> ALTER TABLESPACE stbs ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
Tablespace's encryption status will change to encryption.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
STBS                           AES256  YES ENCRYPTING
Once the encryption is done status will change to normal
NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
STBS                           AES256  YES NORMAL
The alert log will show a intermediate data file being created for encryption and switching to it in the end.
2020-04-22T22:20:44.920971+10:00
ALTER TABLESPACE STBS ENCRYPTION ONLINE USING 'AES256' ENCRYPT
2020-04-22T22:20:44.953241+10:00
About to encrypt tablespace stbs (tsn 0/6)
Rekeying datafile +DATA/TESTDB1/DATAFILE/stbs.273.1035989447 (6) to +DATA
2020-04-22T22:22:22.315005+10:00
Rekey operation committed for file +DATA/TESTDB1/DATAFILE/stbs.297.1038435645
2020-04-22T22:22:24.436978+10:00
About to zero out original file "+DATA/TESTDB1/DATAFILE/stbs.273.1035989447"
2020-04-22T22:22:34.359115+10:00
Successfully zero'ed out original file "+DATA/TESTDB1/DATAFILE/stbs.273.1035989447"
Successfully deleted original file "+DATA/TESTDB1/DATAFILE/stbs.273.1035989447"
Completed rekey for tablespace stbs (tsn 0/6) from key version 0 to 1.
Completed: ALTER TABLESPACE STBS ENCRYPTION ONLINE USING 'AES256' ENCRYPT

Online Decryption
The same tablespace is used for decrypting. Being the decryption by specifying encryption online and decrypt.
SQL>  ALTER TABLESPACE stbs ENCRYPTION online DECRYPT;
The tablespace status change to decrypting during this process.
NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
stbs                         NONE    NO  DECRYPTING
Alert log shows new datafile created and switched to at the end.
2020-04-22T22:36:42.446452+10:00
 ALTER TABLESPACE stbs ENCRYPTION online DECRYPT
2020-04-22T22:36:42.485372+10:00
About to decrypt tablespace stbs (tsn 0/6)
Rekeying datafile +DATA/TESTDB1/DATAFILE/stbs.297.1038435645 (6) to +DATA
2020-04-22T22:38:14.303037+10:00
Rekey operation committed for file +DATA/TESTDB1/DATAFILE/stbs.273.1038436603
2020-04-22T22:38:16.437063+10:00
About to zero out original file "+DATA/TESTDB1/DATAFILE/stbs.297.1038435645"
2020-04-22T22:38:26.331705+10:00
Successfully zero'ed out original file "+DATA/TESTDB1/DATAFILE/stbs.297.1038435645"
Successfully deleted original file "+DATA/TESTDB1/DATAFILE/stbs.297.1038435645"
Completed rekey for tablespace stbs (tsn 0/6) from key version 3 to 4.
Completed:  ALTER TABLESPACE stbs ENCRYPTION online DECRYPT


Offline Encryption
Offline encryption requires taking the tablespace offline and encrypting and brining it back online. As said earlier it doesn't require double the space as work happens inline.
SQL> alter tablespace stbs offline normal;

SQL>  ALTER TABLESPACE stbs ENCRYPTION OFFLINE  USING 'AES256' ENCRYPT;

SQL> alter tablespace stbs online;

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
stbs                           AES256  YES NORMAL
Alert log shows work happening inline.
2020-04-22T22:32:17.799065+10:00
alter tablespace stbs offline normal
Completed: alter tablespace stbs offline normal
2020-04-22T22:33:05.102693+10:00
 ALTER TABLESPACE stbs ENCRYPTION OFFLINE  USING 'AES256' ENCRYPT
2020-04-22T22:33:05.182112+10:00
About to offline encrypt tablespace 6 ..
2020-04-22T22:34:37.019647+10:00
Successfully encrypted tablespace 6 with database key.
Completed:  ALTER TABLESPACE stbs ENCRYPTION OFFLINE  USING 'AES256' ENCRYPT
2020-04-22T22:34:44.844771+10:00
alter tablespace stbs online
Completed: alter tablespace stbs online

Offline Decryption
Same as encryption to decrypt take the tablespace offline and decrypt.
SQL> alter tablespace stbs offline normal;

SQL> ALTER TABLESPACE stbs ENCRYPTION OFFLINE DECRYPT;

SQL> alter tablespace stbs online;

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
stbs
Alert log output for offline decryption.
2020-04-22T22:27:20.225977+10:00
alter tablespace STBS offline normal
Completed: alter tablespace stbs offline normal
2020-04-22T22:28:19.782418+10:00
ALTER TABLESPACE stbs ENCRYPTION OFFLINE DECRYPT
2020-04-22T22:28:19.860285+10:00
About to offline decrypt tablespace 6 ..
2020-04-22T22:29:47.629045+10:00
Successfully decrypted tablespace 6 with database key.
Completed: ALTER TABLESPACE stbs ENCRYPTION OFFLINE DECRYPT

Related Posts
19c Encryption
12c Encryption
Rekeying Master Key in a Data Guard Setup
Encrypting / Decrypting Tablespaces Online / Offline in a Data Guard Configuration
Posted by at
Labels: ,