Error
Error Code: ORA-28431

Oracle ORA-28431: Double Encryption Attempt

📦 Oracle Database
📋

Description

The ORA-28431 error occurs in Oracle Database when attempting to encrypt a data file that already resides within an encrypted tablespace. This usually happens during database administration tasks related to encryption.
💬

Error Message

ORA-28431: cannot encrypt an already encrypted data file string
🔍

Known Causes

3 known causes
⚠️
Encrypted Tablespace Datafile
The data file is already part of a tablespace that is configured for transparent data encryption (TDE).
⚠️
Accidental Redundancy
A script or process is unintentionally trying to encrypt the same data file multiple times.
⚠️
Configuration Error
Incorrect parameters or settings are being used during the encryption process, leading to the attempt to double-encrypt.
🛠️

Solutions

3 solutions available

1. Identify and Remove Existing Encryption medium

Locate the data file that is already encrypted and remove its encryption before attempting to re-encrypt.

1
Determine which data file is causing the ORA-28431 error. The error message typically includes the file name.
ORA-28431: cannot encrypt an already encrypted data file '/path/to/your/datafile.dbf'
2
Connect to the Oracle database as a user with SYSDBA or appropriate privileges.
sqlplus / as sysdba
3
Query the V$ENCRYPTED_TABLESPACES view to confirm if the tablespace associated with the data file is encrypted.
SELECT tablespace_name, encrypted FROM v$encrypted_tablespaces WHERE tablespace_name IN (SELECT tablespace_name FROM dba_data_files WHERE file_name = '/path/to/your/datafile.dbf');
4
If the tablespace is encrypted, you need to decrypt it. This is a destructive operation and requires careful planning. Ensure you have a backup.
ALTER TABLESPACE <tablespace_name> ENCRYPTION OFFLINE DECRYPT USING '<cipher_algorithm>' IDENTIFIED BY '<master_key_password>';
5
Once the tablespace is decrypted, you can proceed with your intended encryption operation on the data file.
ALTER TABLESPACE <tablespace_name> ENCRYPTION ONLINE USING '<cipher_algorithm>' IDENTIFIED BY '<master_key_password>';

2. Verify Encryption Status Before Operation easy

Implement checks before executing encryption commands to prevent accidental double encryption.

1
Before attempting to encrypt a data file or tablespace, query the `dba_data_files` view to check the `encryption_type` column. A non-NULL value indicates encryption.
SELECT file_name, encryption_type FROM dba_data_files WHERE file_name = '/path/to/your/datafile.dbf';
2
If `encryption_type` is not NULL, do not proceed with encryption. Investigate why it's already encrypted.
NULL
3
If `encryption_type` is NULL, you can safely proceed with your encryption command.
NULL

3. Recreate Data File After Decryption advanced

As a more drastic but effective measure, decrypt the tablespace, drop the problematic data file, and recreate it.

1
Identify the data file and its associated tablespace as described in Solution 1.
ORA-28431: cannot encrypt an already encrypted data file '/path/to/your/datafile.dbf'
2
Connect to the Oracle database as SYSDBA.
sqlplus / as sysdba
3
Decrypt the tablespace.
ALTER TABLESPACE <tablespace_name> ENCRYPTION OFFLINE DECRYPT USING '<cipher_algorithm>' IDENTIFIED BY '<master_key_password>';
4
Take a full backup of your database before proceeding.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
5
Drop the problematic data file from the tablespace.
ALTER TABLESPACE <tablespace_name> DROP DATAFILE '/path/to/your/datafile.dbf';
6
Add a new data file to the tablespace. This new file will not be encrypted unless you explicitly encrypt it during creation or immediately after.
ALTER TABLESPACE <tablespace_name> ADD DATAFILE '/new/path/to/your/datafile.dbf' SIZE <size> AUTOEXTEND ON NEXT <increment> MAXSIZE <max_size>;
7
Now, you can encrypt the tablespace with the new data file.
ALTER TABLESPACE <tablespace_name> ENCRYPTION ONLINE USING '<cipher_algorithm>' IDENTIFIED BY '<master_key_password>';