Starting from Oracle Database 12.2, it is now possible with TDE to encrypt and decrypt tablespaces online. Here are 2 quick examples using OMF and non-OMF tablespaces in Oracle Database version 19.3.
Example using OMF
db_create_file_dest parameter points to an ASM location :
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
In PDB with GUID=902E95E2CB363FC8E053344EA8C088BB, let’s create a new unencrypted tablespace :
SQL> CREATE TABLESPACE chocolate ;
Tablespace created.
ASMCMD shows that a new file has been created in the right place :
# asmcmd
ASMCMD> cd +DATA/EASTER/902E95E2CB363FC8E053344EA8C088BB/DATAFILE
ASMCMD> ls
[...]
CHOCOLATE.320.1037636677
[...]
When this tablespace is being encrypted with the new ENCRYPTION ONLINE clause … :
ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT ;
...
… a new file apprears in the ASM directory :
# asmcmd
ASMCMD> cd +DATA/EASTER/902E95E2CB363FC8E053344EA8C088BB/DATAFILE
ASMCMD> ls
[...]
CHOCOLATE.317.1037636697
CHOCOLATE.320.1037636677
[...]
At the end of the encyption process, the old file is removed and the new file remains :
# asmcmd
ASMCMD> cd +DATA/EASTER/902E95E2CB363FC8E053344EA8C088BB/DATAFILE
ASMCMD> ls
[...]
CHOCOLATE.317.1037636697
[...]
View v$encrypted_tablespaces shows the tablespace has been encrypted :
SQL> SELECT t.name, e.encryptedts, e.status FROM v$tablespace t, v$encrypted_tablespaces e WHERE t.ts#=e.ts#;
NAME ENC STATUS
------------------------------ --- ----------
CHOCOLATE YES NORMAL
This view integrates a new column STATUS, with possible values including ENCRYPTING and DECRYPTING to indicate if an encryption/decryption operation is ongoing.
Now let’s create a new table in this encrypted tablespace :
SQL> CREATE TABLE countries (
country_name VARCHAR2(50) NOT NULL
) TABLESPACE chocolate;
Table created.
SQL> insert into countries values ('Belgium');
1 row created.
SQL> insert into countries values ('Switzerland');
1 row created.
SQL> insert into countries values ('France');
1 row created.
SQL> insert into countries values ('Italy');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from countries ;
COUNTRY_NAME
--------------------------------------------------
Belgium
Switzerland
France
Italy
If the keystore is closed, it is of course impossible to read the content of the table :
SQL> administer key management set keystore close identified by i_love_unicorns;
keystore altered.
SQL> select * from countries ;
select * from countries
*
ERROR at line 1:
ORA-28365: wallet is not open
By re-opening the keystore, the content of the table is readable again. And it is now also possible to decrypt the tablespace.
SQL> administer key management set keystore open identified by i_love_unicorns ;
keystore altered.
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE DECRYPT ;
...
Like during the encryption process, a new ASM file is created during the decryption process and then the old file is replaced :
# asmcmd
ASMCMD> cd +DATA/EASTER/902E95E2CB363FC8E053344EA8C088BB/DATAFILE
ASMCMD> ls
[...]
CHOCOLATE.317.1037636697
CHOCOLATE.320.1037636843
[...]
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE DECRYPT ;
Tablespace altered.
SQL> select name, encryptedts from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;
no rows selected
# asmcmd
ASMCMD> cd +DATA/EASTER/902E95E2CB363FC8E053344EA8C088BB/DATAFILE
ASMCMD> ls
[...]
CHOCOLATE.320.1037636843
[...]
Example using non-OMF
db_create_file_dest is not specified :
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
There are two options when using non-OMF tablespaces :
Without new FILE_NAME_CONVERT clause
Like in the previous example, an intermediate file with suffix _new will be generated during the online encryption and then automatically renamed with the original name :
SQL> create tablespace chocolate datafile '/some/data/location/chocolate_01.dbf' size 1M;
# ls /some/data/location/chocolate_01.dbf
[...]
chocolate_01.dbf
[...]
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT ;
...
# ls /some/data/location/chocolate_01.dbf
[...]
chocolate_01.dbf
chocolate_01.dbf_new
[...]
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT ;
Tablespace altered.
# ls /some/data/location/chocolate_01.dbf
[...]
chocolate_01.dbf
[...]
With new FILE_NAME_CONVERT clause
This clause allows to explicitly convert the name of the underlying datafiles :
# ls /some/data/location/chocolate_01.dbf
[...]
chocolate_01.dbf
[...]
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('chocolate', 'good_chocolate');
...
# ls /some/data/location/chocolate_01.dbf
[...]
chocolate_01.dbf
good_chocolate_01.dbf
[...]
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('chocolate', 'good_chocolate');
Tablespace altered.
# ls /some/data/location/chocolate_01.dbf
[...]
good_chocolate_01.dbf
[...]
It is even possible to move datafiles to another location while changing their name, using FILE_NAME_CONVERT :
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('/some/data/location/good_','/some/new/data/location/');
Tablespace altered.
# ls /some/new/data/location/
[...]
chocolate_01.dbf
[...]
About this new FILE_NAME_CONVERT clause, official documentation states :
If you are using non-Oracle-managed file mode and if you omit the FILE_NAME_CONVERT clause, then Oracle Database internally assigns an auxiliary file name, and then later renames it back to the original name. This enables the encryption process to use the name that you had originally given the file to be encrypted. The renaming operation is effectively creating another copy of the file, hence it is slower than explicitly including the FILE_NAME_CONVERT clause. For better performance, include the FILE_NAME_CONVERT clause.
If you try to use FILE_NAME_CONVERT clause while using OMF in the database, you’ll get the following error :
SQL> ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('chocolate', 'good_chocolate');
ALTER TABLESPACE chocolate ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('chocolate', 'good_chocolate')
*
ERROR at line 1:
ORA-28437: unexpected FILE_NAME_CONVERT clause with Oracle Managed Files
Before Oracle 12.2, it is not possible to simply decrypt a tablespace. It is possible to use Data Pump to export data unencrypted and then re-import them in a new database or an unencrypted tablespace. There is also the possibility to move all the objects to a new unencrypted tablespace.
With this new feature, it is now way easier to decrypt a tablespace if necessary.
Floo Bar 
Thankyou for the blog. — I had the issue and the fix was not documented.
LikeLike