Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Question: Can I decrypt a tablespace that has previously been encrypted? Can I disable TDE?

Answer:
In versions prior to 12.2, you cannot.
From Version 12.2, you can.

In Oracle Versions prior to 12.2, this is what Oracle writes about decrypting a tablespace?

"You cannot decrypt a tablespace that has been created encrypted. You must create an unencrypted tablespace and re-create the database objects in the unencrypted tablespace."

In version 12.2, Oracle has added support to decrypt both online and offline tablespaces.

The syntax for an offline tablespace decryption would be

administer key management set keystore open identified by software_keystore_password; 
alter tablespace TEST_TABSPC offline;
alter tablespace TEST_TABSPC encryption offline decrypt;
alter tablespace TEST_TABSPC online;

An online decryption would executed like this:

alter tablespace TEST_TABSPC encryption online decrypt file_name_convert = ('test_tabspc.dbf', 'test_tabspc_decrypted.dbf');

For the online method to work, compatible should be set to 12.2, and there must be enough storage available for the database server that you can hold a copy of the entire tablespace to be decrypted.

How to check the current kernel settings

sysctl -a 

or, to limit the search to kernel parameters that starts with "sem":

sysctl -a |grep shm

Example output:

[root@myserver ~]# sysctl -a | grep sem
kernel.sem = 250        32000   100     128

What is the Software keystore used by Oracle Transparent Data Encryption?

The Software keystore is the container that stores the TDE master key.
There is one keystore per database.

From the Oracle Documentation:

"A software keystore is a container that stores the Transparent Data Encryption master encryption key."

By "container", Oracle means either

* a software keystore
* a hardware module security (HSM) keystore
* Oracle Key Vault keystore (separately licenced)

Personally I have only used software keystores, which is the equivalent of keeping the keystore as a file on a file system, or a file on an ASM disk group.


How is the keystore located by by the database?

"the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file."

Example of sqlnet.ora from my own environment:

# WALLET_OVERRIDE is only applicable when using auto login, set to FALSE
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)

Now that the keystore is defined, you can log into the database to create and open the keystore.
Finally, you set the TDE master encryption key.

After that, you can encrypt the data.

See my post Quick guide to set up Transparent Data Encryption for a tablespace for examples on how to set it up.

What is Transparent Data Encryption and when should it be used?

Use TDE to protect data on disk, or "data at rest".
In other words, TDE adds another layer of security to your database setup, but is by no means sufficent to protect your data by itself.

Inside the database, Oracle uses different methods to protect the data against unauthorized access.

For example, you can set up auditing to trace Access to specific tables in the database.
Another example would be redaction, introduced in Oracle 12cR1, which can hide sensitive data from the users.

However, none of these methods would protect data on disk, or "data at rest" as Oracle calls it.

From the documentation:

"TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen."

and

"To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files."

How does Oracle prevent unauthorized decryption?

"To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore."

Source: Oracle Documentation

How to use yum to list installed packages

yum list package_name

For example:

[root@myserver ~]# yum list compat-libcap1
Loaded plugins: product-id, rhnplugin, search-disabled-repos, security
This system is receiving updates from RHN Classic or RHN Satellite.
Installed Packages
compat-libcap1.x86_64                                                   1.10-1                                                   @test-rhel-x86_64-server-6
Available Packages
compat-libcap1.i686                                                     1.10-1    

See also "yum command cheat sheet" from RH for a good overview.

How to clear your bash shell's history

history -c && history -w

How to truncate a partition

ALTER TABLE MYTABLE TRUNCATE PARTITION P_2017_01;

For more details, check the documentation