6 Encryption Conversions for Tablespaces and Databases

You can perform encryption operations on both offline and online tablespaces and databases.

6.1 About Encryption Conversion for Tablespaces and Databases

The CREATE TABLESPACE SQL statement can be used to encrypt new tablespaces. ALTER TABLESPACE can encrypt existing tablespaces.

In addition to encrypting new and existing tablespaces, you can encrypt full databases, which entails the encryption of the Oracle-managed tablespaces (in this release, the SYSTEM, SYSAUX, TEMP, and UNDO tablespaces). An Oracle-supplied tablespace contains information necessary for the correct functioning (confidentiality, integrity, and availability) of the database system. This information includes the system's data dictionary, the system's temporary sort area, the system's undo segment, and the system's auxiliary data. This information is only expected to be updated internally by the Oracle database server itself, and does not normally be updated directly by users.

To encrypt a full database, you use the ALTER TABLESPACE statement, not ALTER DATABASE, to encrypt the Oracle-managed tablespaces.

The following table compares the differences between an offline and an online encryption conversion of tablespaces and databases.

Table 6-1 Offline and Online Tablespace and Database Encryption Conversions

Functionality Offline Conversion Online Conversion

Release with minimum conversion capability

Oracle Database 11g release 2 (11.2)

Oracle Database 12c release 2 (12.2) and later

What can be backported?

The ability to encrypt or decrypt a data file with the AES128 algorithm (using
ALTER DATABASE DATAFILE data_file ENCRYPT
or DECRYPT) can be used in Oracle Database releases 12.1.0.2 and 11.2.0.4.

No

Algorithms supported

All symmetric encryption algorithms that TDE supports.

All symmetric encryption algorithm that TDE supports.

When can the conversion be run?

When the tablespace is offline or the database is in the mount stage.

When the tablespace is online and database is open in read/write mode.

Is auxiliary space required for the conversion?

No

Yes.

Oracle Data Guard conversion guidelines

Convert both the primary and standby manually. Convert the standby first and then switch over to minimum downtime

After you convert the primary, the standby conversion takes place automatically. You cannot perform an online conversion directly on the standby.

Encrypt the SYSTEM, SYSAUX, and UNDO tablespaces (database conversion)

Oracle Database 12c release 2 (12.2) and later only. You must set COMPATIBILITY to 12.2.0.0.

Oracle Database 12c release 2 (12.2) and later only. You must set COMPATIBILITY to 12.2.0.0.

Can an existing TEMP tablespace be converted?

No, but you can create an encrypted TEMP tablespace in Oracle Database 12c release 2 (12.2) and later, make it the default temporary tablespace, and then drop the original TEMP tablespace.

No, but you can create an encrypted TEMP tablespace in Oracle Database 12c release 2 (12.2) and later, make it the default temporary tablespace, and then drop the original TEMP tablespace.

Can an existing tablespace be decrypted?

You only can decrypt a tablespace or data file that was previously encrypted by an offline encrypt operation. Oracle does not recommend that you decrypt the UNDO tablespace once it is encrypted.

Yes, but Oracle does not recommend that you decrypt the UNDO tablespace once it is encrypted.

Can encryption keys be rekeyed?

No, but after the tablespace is encrypted, you can then use online conversion to rekey in Oracle Database 12c release 2 (12.2) compatibility.

Yes

Can encryption operations be run in parallel?

You can run parallel encryption conversions at the data file level with multiple user sessions running.

You can run parallel encryption conversions at the tablespace level with multiple user sessions running.

What to do if an encryption conversion SQL statement fails to complete?

Re-issue the encryption or decryption SQL statement to ensure that all the data files within the tablespace are consistently either encrypted or decrypted.

Rerun the SQL statement but use the FINISH clause.

6.2 Impact of a Closed TDE Keystore on Encrypted Tablespaces

A TDE keystore can be closed or migrated when an Oracle-managed tablespace is encrypted, and the database system itself must be shut down to disallow operations on an Oracle-managed tablespace.

A closed TDE keystore has no impact on operations that involve an encrypted Oracle-managed tablespace (in this release, the SYSTEM, SYSAUX, TEMP, and UNDO tablespaces). This enables operations that are performed by background processes (for example, the log writer) to continue to work on these tablespaces while the TDE keystore is closed. If you want to disallow operations on an encrypted Oracle-managed tablespace, then you must shut down the database.

With regard to user-created tablespaces, a closed TDE keystore causes operations such as rotating a key or decrypting the tablespace to fail with an ORA-28365 wallet is not open error, just as it did in earlier releases. If you want to disallow operations on the user-created tablespace, then close the TDE keystore (or shut down the database).

User-created data can be copied into an encrypted Oracle-managed tablespace (for example, by an internal process such as DBMS_STATS statistics gathering) from a user-created tablespace while the TDE keystore is open. Closing the keystore does not prevent a user from viewing this data afterward, when the TDE keystore is in the CLOSED state at the time that you query the V$ENCRYPTION_WALLET view. Access to the original data by attempting to query an encrypted user-created tablespace will fail, resulting in an ORA-28365 wallet is not open error.

Table 6-2 describes the operations that are necessary to disallow or allow operations on encrypted data in user-created tablespaces and Oracle-managed tablespaces. For example, in the first scenario, both the user-created tablespaces and the Oracle-managed tablespaces are encrypted. In this case, for the encrypted data in the encrypted user-created tablespace, an administrator can close or open keystores, and shut down and open a database with an encrypted user-created tablespace. When an encrypted Oracle-managed tablespace is configured, the administrator can disallow operations by shutting down the database, and can allow operations by starting up in mount mode, opening the TDE keystore, and then opening the database. (It is necessary to open the TDE keystore before opening the database because the system may need the TDE master encryption key to decrypt the bootstrap dictionary tables, which are located in the encrypted Oracle-managed tablespace.) The N/A flags in this table refer to non-encrypted data, which you can always operate on, unless the instance is shut down.

Table 6-2 Necessary Commands to Disallow or Allow Operations on Encrypted Data

Tablespace Encryption Scenarios Commands to Disallow Operations on Encrypted User-Created Tablespace Data Commands to Disallow Operations on Encrypted Oracle-Managed Tablespace Data Commands to Allow Operations on Encrypted User-Created Tablespace Data Commands to Allow Operations on Encrypted Oracle-Managed Tablespace Data
Both user-created and Oracle-managed tablespaces encrypted
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY password;
  • SHUTDOWN
SHUTDOWN
  • STARTUP MOUNT;
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
  • ALTER DATABASE OPEN
  • STARTUP MOUNT;
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
  • ALTER DATABASE OPEN
User tablespace encrypted; Oracle-managed tablespace not encrypted
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY password;
  • SHUTDOWN
N/A
  • STARTUP MOUNT;
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
  • ALTER DATABASE OPEN
N/A
User tablespace not encrypted; Oracle-managed tablespace encrypted N/A SHUTDOWN N/A
  • STARTUP MOUNT;
  • ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
  • ALTER DATABASE OPEN
Neither user nor Oracle-managed tablespaces encrypted N/A N/A N/A N/A

6.3 Restrictions on Using Transparent Data Encryption Tablespace Encryption

You should be aware of restrictions on using Transparent Data Encryption when you encrypt a tablespace.

Note the following restrictions:

  • Transparent Data Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as opposed to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.

  • To perform import and export operations, use Oracle Data Pump.

6.4 Creating an Encrypted New Tablespace

When you create a new tablespace, you can configure its encryption settings during the creation process.

6.4.1 Step 1: Set the COMPATIBLE Initialization Parameter for Tablespace Encryption

You must set the COMPATIBLE initialization parameter before creating an encrypted tablespace.

6.4.1.1 About Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption

A minimum COMPATIBLE initialization parameter setting of 11.2.0.0 enables the full set of tablespace encryption features.

Setting the compatibility to 11.2.0.0 enables the following functionality:

  • The 11.2.0.0 setting enables the database to use any of the four supported algorithms for data encryption (3DES168, AES128, AES192, and AES256).

  • The 11.2.0.0 setting enables the migration of a key from a software keystore to an external keystore (ensure that the TDE master encryption key was configured for the external keystore)

  • The 11.2.0.0 setting enables rekeying the TDE master encryption key

Be aware that once you set the COMPATIBLE parameter to 11.2.0.0, the change is irreversible. To use tablespace encryption, ensure that the compatibility setting is at the minimum, which is 11.2.0.0.

6.4.1.2 Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption

To set the COMPATIBLE initialization parameter, you must edit the initialization parameter file for the database instance.

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted administrative privileges.

    For example:

    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password
  2. Check the current setting of the COMPATIBLE parameter.
    For example:
    SHOW PARAMETER COMPATIBLE
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      12.2.0.0
    noncdbcompatible                     BOOLEAN     FALSE
    
  3. If you must change the COMPATIBLE parameter, then complete the remaining steps in this procedure.
    The value should be 11.2.0.0 or higher.
  4. From the command line, locate the initialization parameter file for the database instance.
    • UNIX systems: This file is in the ORACLE_HOME/dbs directory and is named initORACLE_SID.ora (for example, initmydb.ora).
    • Windows systems: This file is in the ORACLE_HOME\database directory and is named initORACLE_SID.ora (for example, initmydb.ora).
  5. Edit the initialization parameter file to use the new COMPATIBLE setting.
    For example:
    compatible=20.1.0.0.0
  6. Connect as a user who has the SYSDBA administrative privilege, and then restart the database.
    • From the CDB root:
      SHUTDOWN IMMEDIATE
      STARTUP
      
    • From a PDB:
      ALTER PLUGGABLE DATABASE pdb_name CLOSE IMMEDIATE;
      ALTER PLUGGABLE DATABASE pdb_name OPEN;
  7. If tablespace encryption is in use, then open the keystore at the database mount. The keystore must be open before you can access data in an encrypted tablespace.
    For example:
    STARTUP MOUNT;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
    ALTER DATABASE OPEN;

6.4.2 Step 2: Set the Tablespace TDE Master Encryption Key

You should ensure that you have configured the TDE master encryption key.

  • Set the TDE master encryption key for either software TDE master encryption keys or hardware TDE master encryption keys, using the same steps that you would use for united mode or isolated mode.

6.4.3 Step 3: Create the Encrypted Tablespace

After you have set the COMPATIBLE initialization parameter, you are ready to create the encrypted tablespace.

6.4.3.1 About Creating Encrypted Tablespaces

To create an encrypted tablespace, you can use the CREATE TABLESPACE SQL statement.

You must have the CREATE TABLESPACE system privilege to create an encrypted tablespace.

You can import data into an encrypted tablespace by using Oracle Data Pump. You can also use a SQL statement such as CREATE TABLE...AS SELECT... or ALTER TABLE...MOVE... to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT... statement creates a table from an existing table. The ALTER TABLE...MOVE... statement moves a table into the encrypted tablespace.

For security reasons, you cannot encrypt a tablespace with the NO SALT option.

You can query the ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES data dictionary views to verify if a tablespace was encrypted.

6.4.3.2 Creating an Encrypted Tablespace

To create an encrypted tablespace, you must use the CREATE TABLESPACE statement with the ENCRYPTION USING clause.

Run the CREATE TABLESPACE statement, using its encryption clauses.

For example:

CREATE TABLESPACE encrypt_ts
  DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M
  ENCRYPTION USING 'AES256' ENCRYPT;

In this specification:

  • ENCRYPTION USING 'AES256' ENCRYPT specifies the encryption algorithm and the key length for the encryption. The ENCRYPT clause encrypts the tablespace. Enclose this setting in single quotation marks (' '). The key lengths are included in the names of the algorithms. If you do not specify an encryption algorithm, then the default encryption algorithm, AES128, is used.

6.4.3.3 Example: Creating an Encrypted Tablespace That Uses AES192

You can use the CREATE TABLESPACE SQL statement to create an encrypted tablespace.

Example 6-1 creates a tablespace called securespace_1 that is encrypted using the AES192 algorithm.

Example 6-1 Creating an Encrypted Tablespace That Uses AES192

CREATE TABLESPACE securespace_1
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION USING 'AES192' ENCRYPT;
6.4.3.4 Example: Creating an Encrypted Tablespace That Uses the Default Algorithm

You can use the CREATE TABLESPACE SQL statement to create an encrypted tablespace that uses the default algorithm.

Example 6-2 creates a tablespace called securespace_2. Because no encryption algorithm is specified, the default encryption algorithm (AES128) is used. The key length is 128 bits.

You cannot encrypt an existing tablespace.

Example 6-2 Creating an Encrypted Tablespace That Uses the Default Algorithm

CREATE TABLESPACE securespace_2
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION ENCRYPT;

6.5 Setting the Tablespace Encryption Default Algorithm

The TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter defines the default encryption algorithm for future tablespace creation operations.

TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM applies to both offline and online tablespace encryption operations. It also applies to future encrypted tablespaces, set by the ENCRYPT_NEW_TABLESPACES parameter. In a multitenant environment, you can set TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM in the root or in individual PDBs.
  • Enter the following ALTER SYSTEM statement:
    ALTER SYSTEM SET TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM = value SCOPE=BOTH;

    In this specification, value can be one of the following encryption algorithms: AES128, AES192, AES256, 3DES168, ARIA128, ARIA192, ARIA256, SEED128, and GOST256. The default value is AES128.

6.6 Encrypting Future Tablespaces

You can configure Oracle Database to automatically encrypt future tablespaces that you will create.

6.6.1 About Encrypting Future Tablespaces

The ENCRYPT_NEW_TABLESPACES dynamic database initialization parameter controls if future tablespaces are encrypted.

By default, all Oracle Cloud databases are encrypted. If you install an off-the-shelf application into such a database, its installation scripts most likely do not have the encryption syntax. In this case, because ENCRYPT_NEW_TABLESPACES is set to CLOUD_ONLY, those tablespaces would be created encrypted regardless.

In an Oracle Cloud environment, the following scenarios may occur when you create encrypted tablespaces in Oracle Cloud and on-premises environments:

  • You create a test database in Oracle Cloud and the tablespaces were encrypted by using when the ENCRYPT_NEW_TABLESPACE parameter has been set to automatically create the Cloud database as encrypted. However, you may not have the intention or even an Advanced Security Option license to bring the encrypted database back on premises. For this use case, Oracle Recovery Manager (Oracle RMAN) provides the option to duplicate or restore AS DECRYPTED.

  • You create a hybrid environment where the primary database is on premises and the standby database is on Oracle Cloud. If a switchover operation takes place, then the new primary is on Oracle Cloud. If a new tablespace is transparently encrypted, then a similar scenario to the first item in this list may occur. For example, suppose you do not have an Advanced Security Option (ASO) license, and you have an automatically encrypted tablespace in the Oracle Cloud. The standby database on premises is also automatically encrypted. In this case, you either need an Advanced Security license (which includes Transparent Data Encryption and Data Redaction) for the on-premises standby database, or you cannot use the standby database.

6.6.2 Setting Future Tablespaces to be Encrypted

You can set the ENCRYPT_NEW_TABLESPACES database initialization parameter to automatically encrypt future tablespaces that you create.

  • Enter the following ALTER SYSTEM statement:
    ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = value;

    In this specification, value can be:

    • CLOUD_ONLY transparently encrypts the tablespace in the Cloud using the tablespace encryption default algorithm if you do not specify the ENCRYPTION clause of the CREATE TABLESPACE SQL statement, or to the algorithm specified by the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter. It applies only to an Oracle Cloud environment. If you create the tablespace on premises, then it will follow the CREATE TABLESPACE statement specification that you enter. For example, if you omit the ENCRYPTION clause, then the tablespace is created unencrypted. If you include this clause and use a different algorithm, then the tablespace will use that algorithm. CLOUD_ONLY is the default.

    • ALWAYS automatically encrypts the tablespace using the tablespace encryption default algorithm, or to the algorithm specified by the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter, if you omit the ENCRYPTION clause of CREATE TABLESPACE, for both the Cloud and premises scenarios.

      If you do provide the ENCRYPTION clause, however, the algorithm that you specify takes precedence over the tablespace encryption default algorithm.
    • DDL encrypts the tablespace using the specified setting of the ENCRYPTION clause of CREATE TABLESPACE, for both Oracle Cloud and on-premises environments.

Related Topics

6.7 Encrypted Sensitive Credential Data in the Data Dictionary

You can encrypt sensitive credential data in the SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL system tables.

By default, the credential data in the SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL system tables is obfuscated. However, because of the availability of many types of de-obfuscation algorithms, Oracle recommends that you encrypt this sensitive credential data. To check the status the data dictionary credentials, you can query the DICTIONARY_CREDENTIALS_ENCRYPT data dictionary view.

The encryption of sensitive credential data in these two system tables uses Transparent Data Encryption. Encryption of credential data uses the AES256 algorithm. To encrypt credential data, you do not need an Oracle Advanced Security Option license, but you must be granted the SYSKM administrative privilege and the database must have an open keystore.

6.8 Encryption Conversions for Existing Offline Tablespaces

You can perform offline encryption conversions by using the ALTER TABLESPACE SQL statement OFFLINE, ENCRYPT, and DECRYPT clauses.

6.8.1 About Encryption Conversion for Existing Offline Tablespaces

You can encrypt or decrypt an existing data file of a user tablespace when the tablespace is offline or when the database is not open.

Use the offline encryption method if you do not plan to change the compatibility of your databases from Oracle Database 11c release 2 (11.2) or Oracle Database 12c release 1 (12.1) to release 18c, which is irreversible. The offline encryption method is also useful if you want to quickly make use of Transparent Data Encryption before you upgrade this database to release 18c. You can both encrypt and decrypt offline tablespaces.

Note the following:

  • If you want to encrypt the Oracle Database-supplied tablespaces (SYSTEM, SYSAUX, and UNDO) using the offline conversion method, then you must use the method that is recommended when you encrypt an existing database with offline conversion.

  • You can use the online method to rekey a tablespace that was previously encrypted with the offline method.

  • If you have configured Oracle Data Guard, you can minimize downtime by encrypting the tablespaces on the standby first, switching over to the primary, and then encrypting the tablespaces on the primary.

  • You can use the USING ... ENCRYPT clause to specify an encryption algorithm. Supported algorithms include AES128, AES192, AES256, and others, such as ARIA and GOST. To check the encryption key, query the ENCRYPTIONALG column in the V$DATABASE_KEY_INFO view.

  • To set the default encryption algorithm for future offline tablespace encryption operations, set the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter.

  • You can use the ALTER TABLESPACE statement to convert offline tablespaces in parallel by using multiple foreground sessions to encrypt different data files.

  • If you are using Oracle Data Guard, you can minimize the downtime by encrypting the tablespaces on the standby first, switching over, and then encrypting the tablespaces on the original primary next.

  • For Oracle Database 11g release 2 (11.2.0.4) and Oracle Database 12c release 1 (12.1.0.2), you cannot perform an offline encryption of the SYSTEM and SYSAUX tablespaces. Also, Oracle does not recommend encrypting offline the UNDO tablespace in these releases. Doing so prevents the keystore from being closed, and this prevents the database from functioning. In addition, encrypting the UNDO tablespace while the database is offline is not necessary because all undo records that are associated with any encrypted tablespaces are already automatically encrypted in the UNDO tablespace. If you want to encrypt the TEMP tablespace, you must drop and then recreate it as encrypted.

6.8.2 Encrypting an Existing User-Defined Tablespace with Offline Conversion

To encrypt an existing tablespace with offline conversion, you can use the ALTER TABLESPACE SQL statement with the OFFLINE and ENCRYPT clauses.

The procedure that is described in this section applies to the case where you want to encrypt individual user-created tablespaces within a database. These tablespaces can be encrypted offline. However, the Oracle Database-supplied SYSTEM and UNDO tablespaces cannot be brought offline. If you want to encrypt the tablespaces offline, then you must use the method that is described in Encrypting an Existing Database with Offline Conversion.
  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted administrative privileges.
    For example:
    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Bring the tablespace offline.
    ALTER TABLESPACE users OFFLINE NORMAL;
  3. Back up the tablespace.

    The offline conversion method does not use auxiliary disk space or files, and it operates directly in-place to the data files. Therefore, you should perform a full backup of the user tablespace before converting it offline.

  4. As a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege, open the software keystore.
    For example:
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password; 
  5. Encrypt the tablespace.
    For example, to encrypt an entire tablespace, include its data files:
    ALTER TABLESPACE users1 ENCRYPTION OFFLINE ENCRYPT;

    This example encrypts the tablespace with the default encryption algorithm, AES128. To use a different encryption algorithm, enter a statement similar to the following:

    ALTER TABLESPACE users2 ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
    To encrypt individual data files within a tablespace, use the ALTER DATABASE DATAFILE SQL statement. For example, to encrypt the data files user_01.dbf and user_02.dbf:
    ALTER DATABASE DATAFILE 'user_01.dbf' ENCRYPT;
    ALTER DATABASE DATAFILE 'user_02.dbf' ENCRYPT;

    In the same database session, these statements encrypt each of the data files in sequence, one after another. If you execute each statement in its own database session, then they will be executed in parallel.

    If the encryption process is interrupted, then rerun the ALTER TABLESPACE statement. The kinds of errors that you can expect in an interruption are general errors, such as file system or storage file system errors. The data files within the tablespace should be consistently encrypted. For example, suppose you offline a tablespace that has 10 files but for some reason, the encryption only completes for nine of the files, leaving one decrypted. Although it is possible to bring the tablespace back online with such inconsistent encryption if the COMPATIBLE parameter is set to 12.2.0.0 or higher, then it is not recommended to leave the tablespace in this state. If COMPATIBLE is less than 12.2.0.0, then it is not possible to bring the tablespace online if the encryption property is inconsistent across the data files.
  6. Bring the tablespace back online or open the database.
    • To bring the tablespace back online:
      ALTER TABLESPACE users ONLINE;
    • To open a database in a non-multitenant environment:

      ALTER DATABASE OPEN
    • In a multitenant environment, you can encrypt a data file or tablespace with the offline method if the root is open and the PDB is not open. For example, for a PDB named hr_pdb:

      ALTER PLUGGABLE DATABASE hr_pdb OPEN
  7. Perform a full backup of the converted tablespace.

6.8.3 Decrypting an Existing Tablespace with Offline Conversion

To decrypt an existing tablespace with offline conversion, you can use the ALTER TABLESPACE SQL statement with the OFFLINE and DECRYPT clauses.

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA administrative privilege.
    For example:
    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Bring the tablespace offline.
    ALTER TABLESPACE users OFFLINE NORMAL;
  3. As a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege, open the keystore.
    For example:
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password; 
  4. Run the ALTER TABLESPACE SQL statement to perform the decryption.
    For example, for a tablespace called users:
    ALTER TABLESPACE users ENCRYPTION OFFLINE DECRYPT; 

    If the decryption process is interrupted, then rerun the ALTER TABLESPACE statement. The kinds of errors that you can expect in an interruption are general errors, such as file system or storage file system errors. The data files within the tablespace should be consistently decrypted. For example, suppose you offline a tablespace that has 10 files but for some reason, the decryption only completes for nine of the files, leaving one encrypted. Although it is possible to bring the tablespace back online with such inconsistent decryption if the COMPATIBLE parameter is set to 12.2.0.0 or higher, then it is not recommended to leave the tablespace in this state. If COMPATIBLE is less than 12.2.0.0, then it is not possible to bring the tablespace online if the encryption property is inconsistent across the data files.

  5. Bring the tablespace online.
    ALTER TABLESPACE users ONLINE;

6.9 Encryption Conversions for Existing Online Tablespaces

You can encrypt and decrypt an online existing tablespace by using the ALTER TABLESPACE SQL statement with the ONLINE and ENCRYPT or DECRYPT clauses.

6.9.1 About Encryption Conversion for Existing Online Tablespaces

You can encrypt, decrypt, or rekey existing user tablespaces, and the SYSTEM, SYSAUX, and UNDO tablespace when they are online.

However, you cannot encrypt, decrypt, or rekey a temporary tablespace online.

An online tablespace can be created by using the ONLINE clause of the CREATE TABLESPACE SQL statement. When you encrypt or rekey a tablespace online, the tablespace will have its own independent encryption keys and algorithms.

Note the following:

  • If an offline tablespace has been encrypted, then you can rekey it online to use a different algorithm.

  • You can encrypt multiple tablespaces online in parallel by using multiple foreground sessions to encrypt different tablespaces. Within each tablespace, the data files are encrypted sequentially.

  • To set the default encryption algorithm for future online tablespace encryption operations, set the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter.

  • If the conversion is interrupted, then you can resume the process by issuing the FINISH clause of the ALTER TABLESPACE SQL statement.

  • A redo log is generated for each online tablespace conversion.

  • Do not encrypt the SYSTEM and UNDO tablespaces concurrently with other tablespaces.

  • You cannot use the transportable tablespace feature with Oracle Data Pump while you are encrypting a tablespace.

  • You cannot run the ALTER TABLESPACE statement concurrently with the following features:
    • ADMINSTER KEY MANAGEMENT SET KEY SQL statement

    • FLASHBACK DATABASE SQL statement

  • If you are using Oracle-managed files for the data files, then the encryption process rekeys the data files that are associated with the tablespace and then copies or moves them to the default Oracle-managed files location.

  • You can add new files to the tablespace after you have encrypted it. Oracle Database reformats the new file with the new encryption key. Blocks will be encrypted using the new key.

  • Previous operations that took place in the root or the PDB may require the control files to be cross-checked against the data dictionary before you can begin the online conversion process. An ORA-241 operation disallowed: control file is not yet checked against data dictionary error may occur. To resolve this problem, restart the root or PDB, and then try issuing the online conversion commands again.

  • For security reasons, once online conversion processes a data file, Oracle will zero out the original data file before deletion. This prevents the database from leaving ghost data on disk sectors. However, there is a known limitation that can occur if you are performing an online tablespace conversion at the same time that Oracle Recovery Manager (Oracle RMAN) is validating files. The online tablespace conversion processes each file one at a time. If Oracle RMAN is validating a file at the same time that it is being processed by the online tablespace conversion, then Oracle RMAN could report a corruption problem (ORA-01578: ORACLE data block corrupted (file # , block # )). It does this because it sees the blocks that comprise the file as zero. This is a false alarm and you can ignore the error. If this occurs, then try running the Oracle RMAN validation process again.

6.9.2 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. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA administrative privilege.

    For example:

    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0.

    You can use the SHOW PARAMETER command to check the current setting of a parameter.

  3. Ensure that the database is open in read-write mode.

    You can query the STATUS column of the V$INSTANCE dynamic view to find if a database is open and the OPEN_MODE column of the V$DATABASE view to find if it in read-write mode.

  4. If necessary, open the database in read-write mode.
    ALTER DATABASE OPEN READ WRITE;
  5. Ensure that the auxiliary space is at least the same size as the largest data file of this tablespace.

    This size requirement is because Oracle Database performs the conversion one file at a time. For example, if the largest data file of the tablespace is 32 GB, then ensure that you have 32 GB of auxiliary space. To find the space used by a data file, query the BYTES or BLOCKS column of the V$DATAFILE dynamic performance view.

  6. As a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege, create and open a master encryption key.

    For example:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password;
    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY software_keystore_password WITH BACKUP;
  7. Run the ALTER TABLESPACE statement using the ENCRYPTION and ENCRYPT clauses to perform the encryption.

    For example, for a non-Oracle managed files tablespace named users:

    ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES192' ENCRYPT FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');

    In this example:

    • ENCRYPTION ONLINE USING ‘AES192’ ENCRYPT sets the statement to encrypt the tablespace users while it is online and assigns it the AES192 encryption algorithm. If you omit the USING algorithm clause, then the default algorithm, AES128, is used. For the SYSTEM and UNDO tablespaces, you can use the ENCRYPT clause to encrypt the tablespace, but you cannot specify an encryption algorithm because they must be encrypted with the existing database key the first time. After encrypting the tablespace, use the REKEY clause to specify the algorithm.

    • FILE_NAME_CONVERT specifies one or more pairs of data files that are associated with the tablespace. The first name in the pair is an existing data file, and the second name is for the encrypted version of this data file, which will be created after the ALTER TABLESPACE statement successfully executes. If the tablespace has more than one data file, then you must process them all in this statement. Note the following:

      • Separate each file name with a comma, including multiple pairs of files. For example:

        FILE_NAME_CONVERT = ('users1.dbf', 'users1_enc.dbf', 'users2.dbf', 'users2_enc.dbf')
      • You can specify directory paths in the FILE_NAME_CONVERT clause. For example, the following clause converts and moves the matching files of the tablespace from the dbs directory to the dbs/enc directory:

        FILE_NAME_CONVERT = ('dbs', 'dbs/enc')
      • The FILE_NAME_CONVERT clause recognizes patterns. The following example converts the data files users_1.dbf and users_2.dbf to users_enc1.dbf and users_enc2.dbf:

        FILE_NAME_CONVERT = ('users', 'users_enc')
      • In an Oracle Data Guard environment, include the name of the standby database data file in the FILE_NAME_CONVERT settings.

      • If you are using Oracle-managed file mode, then the new file name is internally assigned, so this file name should not affect your site's file-naming standards. 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.

      • You can find the data files for a tablespace by querying the V$DATAFILE or V$DATAFILE_HEADER dynamic views.

      By default, data files are in the $ORACLE_HOME/dbs directory. If the data files are located there, then you do not have to specify a path.

After you complete the conversion, you can check the encryption status by querying the STATUS column of the V$ENCRYPTED_TABLESPACES dynamic view. The ENCRYPTIONALG column of this view shows the encryption algorithm that is used. If the conversion process was interrupted, then you can resume it by running ALTER TABLESPACE with the FINISH clause. For example, if the primary data file converts but the standby data file does not, then you can run ALTER TABLESPACE ... FINISH on the standby database for the standby data files.

6.9.3 Rekeying an Existing Tablespace with Online Conversion

To rekey an existing tablespace that is online, you can use the REKEY clause of the ALTER TABLESPACE SQL statement.

Before you perform a rekey operation, be aware of the following:
  • You cannot rekey the TEMP tablespace. If you want to assign a different encryption algorithm to a TEMP tablespace, then drop TEMP and recreate it with the correct encryption algorithm.

  • Do not perform an online tablespace rekey operation with a master key operation concurrently. To find if any tablespaces are currently being rekeyed, issue the following query to find the rekey status of encrypted tablespaces:

    SELECT TS#,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES;

    A status of REKEYING means that the corresponding tablespace is still being rekeyed. Do not rekey the master key while this status is in effect.

To rekey an existing tablespace with online conversion:

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA administrative privilege.

    For example:

    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Ensure that the following requirements are met:
    • The COMPATIBLE initialization parameter is set to 12.2.0.0.
    • The database is open and in read-write mode.
    • A master encryption key has been created and is open.
  3. Query the KEY_VERSION and STATUS columns of the V$ENCRYPTED_TABLESPACES dynamic view to find the current status of the encryption algorithm used by the master encryption key.
  4. Perform the rekey operation, based on the status returned by the V$ENCRYPTED_TABLESPACES dynamic view:
    • If the key version status of the tablespace is NORMAL, then specify the new algorithm of the online tablespace rekey.

      For example:

      ALTER TABLESPACE users ENCRYPTION USING 'AES192' REKEY FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');
    • If the key version status is ENCRYPTING, DECRYPTING, or REKEYING, then use the FINISH clause.

      For example:

      ALTER TABLESPACE users ENCRYPTION ONLINE FINISH REKEY FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');
  5. If the ORA-00241 operation disallowed: control file inconsistent with data dictionary error appears, then restart the CDB root and then retry Step 4.
If the conversion process was interrupted, then you can resume it by running ALTER TABLESPACE with the FINISH clause.

6.9.4 Decrypting an Existing Tablespace with Online Conversion

To decrypt an existing tablespace with online conversion, you can use the ALTER TABLESPACE SQL statement with DECRYPT clause.

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA administrative privilege.

    For example:

    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Ensure that the following requirements are met:
    • The COMPATIBLE initialization parameter is set to 12.2.0.0.
    • The database is open and in read-write mode.
    • A master encryption key has been created and is open.
    • There is enough auxiliary space to complete the decryption.
  3. Run the ALTER TABLESPACE SQL statement with the DECRYPT clause.

    For example:

    ALTER TABLESPACE users ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('users_enc.dbf', 'users.dbf');

    In this specification:

    • When you specify the files to decrypt, enter them in the reverse order in which they were originally encrypted. That is, first enter the name of the encrypted file (users_enc.dbf), followed by the data file (users.dbf).

    • Do not provide an algorithm key for the decryption.

If the conversion process was interrupted, then you can resume it by running ALTER TABLESPACE with the FINISH clause.

6.9.5 Finishing an Interrupted Online Encryption Conversion

If an online encryption process is interrupted, then you can complete the conversion by rerunning the ALTER TABLESPACE statement using the FINISH clause.

An interrupted encryption process (encryption, rekey, or decryption) can be, for example, an ORA-28425: missing a valid FILE_NAME_CONVERT clause error in the FILE_NAME_CONVERT clause of the ALTER TABLESPACE SQL statement. Other examples of interrupted processes are if the conversion skips a data file, which can happen if there is an error when an Oracle DataBase WRiter (DBWR) process offlines a data file, or if there is not enough space for the auxiliary file. The tablespace should be operational even if you do not rerun the ALTER TABLESPACE statement with the FINISH clause.

In addition to interrupted encryption processes, the tablespace encryption process can fail during the period when the status is ENCRYPTING. In this case, you can either decrypt the tablespace back to its original state, or you can resume the encryption by using the ENCRYPTION ONLINE FINISH ENCRYPT clause of ALTER TABLEPSPACE.

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA or SYSKM administrative privilege.
    For example:
    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Query the V$ENCRYPTED_TABLESPACES to check the STATUS column for the tablespace.

    If the STATUS column reports ENCRYPTING, DECRYPTING, or REKEYING, then re-run the ALTER TABLESPACE statement with the FINISH clause, as described in this procedure. If the STATUS reports NORMAL, then you can rerun ALTER TABLESPACE without the FINISH clause.

    You can find the tablespace name that matches the TS# and TABLESPACE_NAME columns by querying the V$DATAFILE_HEADER view.

  3. If necessary query the following additional views to find information about the tablespace whose online conversion was interrupted:
    • DBA_TABLESPACES to find if the STATUS of the tablespace indicates if it is online or offline.
    • V$ENCRYPTED_TABLESPACES to find if the STATUS of the tablespace indicates if it is encrypted, and what the KEY_VERSION of the encryption key is.
    • V$DATAFILE and V$DATAFILE_HEADER to find the data files that are associated with a tablespace.
  4. Run the ALTER TABLESPACE statement using the FINISH clause.
    Examples are as follows:
    • For an encryption operation:
      ALTER TABLESPACE users ENCRYPTION ONLINE FINISH ENCRYPT FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');
    • For a decryption operation:
      ALTER TABLESPACE users ENCRYPTION ONLINE FINISH DECRYPT FILE_NAME_CONVERT = ('users_enc.dbf', 'users.dbf');

      Note the order in which the files are specified: first, the name of the encrypted file, and then the name of the data file. (In the encryption operation, the name of the data file is specified first, followed by the name of the encrypted file.)

    • For a rekey operation:
      ALTER TABLESPACE users ENCRYPTION ONLINE FINISH REKEY FILE_NAME_CONVERT = ('users.dbf', 'users_enc.dbf');
      You cannot specify an algorithm when you use the FINISH clause in an ALTER TABLESPACE statement.
  5. To check the conversion, query the STATUS column of the V$ENCRYPTED_TABLESPACES view.
    The status should be NORMAL. In an Oracle Data Guard environment, if the database does not have NORMAL as the STATUS, then run the ALTER TABLESPACE ... FINISH statement on the primary or the standby data file that did not successfully convert.

6.10 Encryption Conversions for Existing Databases

You can encrypt both offline and online databases.

6.10.1 About Encryption Conversions for Existing Databases

The encryption conversion of an entire database encrypts all tablespaces, including the Oracle-supplied SYSTEM, SYSAUX, UNDO, and TEMP tablespaces.

Note the following:

  • If you are using Database Configuration Assistant (DBCA) to create or configure a database, then you can create a TDE wallet in the database as part of the creation or configuration process. When you drop a database by using DBCA, any TDE wallets that are in this database are also removed. Important: Before you drop a database by using DBCA, and if it has any local TDE wallets, then back these wallets up to a secure location. If the database has been migrated to use Oracle Key Vault, then be aware that its TDE encryption keys reside in the Oracle Key Vault server. It is the responsibility of the Oracle Key Vault administrator to back up Oracle Key Vault.

  • To perform the encryption, you can use the offline and online functionality of the tablespace encryption conversions.

  • You can encrypt any or all of the Oracle-supplied tablespaces, and in any order. The encryption of the Oracle-supplied tablespaces has no impact on the encryption of user-created tablespaces.

  • When you encrypt the Oracle-supplied tablespaces, Oracle Database prevents the keystore from being closed.

  • You cannot encrypt an existing temporary tablespace, but you can drop the existing temporary tablespace and then recreate it as encrypted.

  • The UNDO and TEMP metadata that is generated from sensitive data in an encrypted tablespace is already automatically encrypted. Therefore, encrypting UNDO and TEMP is optional.

  • Oracle recommends that you encrypt the Oracle-supplied tablespaces by using the default tablespace encryption algorithm, AES128. However, you can rekey any of these tablespaces afterwards to use a different encryption algorithm if you want. (To find the current encryption key for the current database, you can query the V$DATABASE_KEY_INFO dynamic view.)

  • The performance effect of encrypting all the tablespaces in a database depends on the workload and platform. Many modern CPUs provide built-in hardware acceleration, which results in a minimal performance impact.

  • In a multitenant environment, you can encrypt any tablespaces in any pluggable databases (PDBs), including the Oracle-supplied tablespaces. However, the keystore in the CDB root must be open at all times so that a PDB can open its keystore. You can check the status of whether a keystore is open by querying the STATUS column of the V$ENCRYPTION_WALLET view

6.10.2 Encrypting an Existing Database with Offline Conversion

When you encrypt an existing database with offline conversion, for the Oracle-managed tablespaces, you do not specify an encryption algorithm.

  1. Connect to the united mode CDB root or isolated mode PDB as a user who has been granted the SYSDBA administrative privilege.

    For example:

    CONNECT c##sec_admin AS SYSDBA (or CONNECT sec_admin@pdb_name AS SYSDBA)
    Enter password: password

    You must have the SYSDBA administrative privilege to work with the SYSTEM and SYSAUX tablespaces. Otherwise, connect with the SYSKM administrative privilege.

  2. Mount the database.
    STARTUP MOUNT
  3. As a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege, open the keystore.
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore_password;
  4. Run the ALTER TABLESPACE SQL statement to encrypt the SYSTEM, SYSAUX, and UNDO tablespaces. Do not encrypt the SYSTEM tablespace concurrently with the encryption of other tablespaces.

    For example, to encrypt the SYSTEM tablespace:

    ALTER TABLESPACE SYSTEM ENCRYPTION OFFLINE ENCRYPT;
  5. Open the CDB root or the PDB.
    • For a CDB, to open in read/write mode, for example:
      ALTER DATABASE OPEN READ WRITE;
    • For a PDB:
      ALTER PLUGGABLE DATABASE pdb_name OPEN READ WRITE;
  6. Run the ALTER TABLESPACE SQL statement to encrypt other user tablespaces.
    Alternatively, you can proceed to the next step and open the database first, and then perform the steps to encrypt an existing user-defined tablespace with offline conversion.
  7. Open the CDB root or the PDB.
    • For a CDB:
      ALTER DATABASE OPEN;
    • For a PDB:
      ALTER PLUGGABLE DATABASE pdb_name OPEN;
After you have encrypted the tablespace, if you want to use a different encryption algorithm (change the TDE master encryption key) for the SYSTEM, SYSAUX, and UNDO tablespaces, then you must use online conversion. In addition to AES128, supported encryption algorithms are AES192 and AES256, in addition to other algorithms such as ARIA and GOST.

6.10.3 Encrypting an Existing Database with Online Conversion

When you encrypt an existing database with online conversion, you do not specify an encryption algorithm.

The reason that you do not need to specify an encryption algorithm the first time you perform the encryption is that the tablespaces that you must use to encrypt the database are automatically encrypted with the database key. If you want to change the algorithm, then you can issue the ALTER TABLESPACE ENCRYPTION REKEY SQL statement after the initial encryption.
  1. Perform the following tasks, which are performed when encrypting an existing tablespace with online conversion:
    1. Connect as a user who has been granted the SYSDBA administrative privilege.
    2. Ensure that the COMPATIBLE parameter is set to 12.2.0.0.
    3. Ensure that the database is open in read-write mode.
    4. Ensure that you have enough auxiliary space to complete the encryption.
    5. Back up the tablespaces that you must encrypt.
    6. Open the keystore.
  2. Run the ALTER TABLESPACE SQL statement to encrypt the SYSTEM, SYSAUX, and UNDO tablespaces. Do not specify an algorithm, and do not encrypt the SYSTEM tablespace concurrently with the encryption of other tablespaces.

    For example, to encrypt the SYSTEM tablespace:

    ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE ENCRYPT 
    FILE_NAME_CONVERT=('system01.dbf','system01_enc.dbf');
  3. For a temporary tablespace, drop it and then recreate it as encrypted. Do not specify an algorithm.

    For example, for a user-created tablespace:

    DROP TABLESPACE temp_01;
    CREATE TEMPORARY TABLESPACE temp_01 
    TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON 
    ENCRYPTION ENCRYPT;

    You cannot drop the default TEMP tablespace. You must first create a new tablespace and make it the default before you can drop TEMP.

    For example:

    CREATE TEMPORARY TABLESPACE temp_01 
    TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON 
    ENCRYPTION ENCRYPT;
    
    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_01;
    
    DROP TABLESPACE TEMP;