📋

Description

This error occurs when attempting to encrypt a data file belonging to the SYSTEM or SYSAUX tablespace in Oracle Database versions with compatibility settings lower than 12.2. Encrypting these critical tablespaces is restricted in older versions.

💬

Error Message

ORA-28436: cannot encrypt data file string in SYSTEM or SYSAUX tablespace

🔍

Known Causes

2 known causes

⚠️

Incompatible Compatibility Level

The database compatibility level is set to a version prior to 12.2, which does not support encrypting SYSTEM or SYSAUX tablespaces. ⚙

⚠️

Accidental Tablespace Target

The data file targeted for encryption is unintentionally located within the SYSTEM or SYSAUX tablespace. 💻

🛠️

Solutions

3 solutions available

1. Relocate Objects from SYSTEM/SYSAUX medium

Move objects out of SYSTEM/SYSAUX to a user-created, encrypted tablespace.

1

Identify objects residing in the SYSTEM or SYSAUX tablespace that are not essential system objects. You can use the following query to find objects owned by users.

SELECT owner, segment_name, segment_type FROM dba_segments WHERE tablespace_name IN ('SYSTEM', 'SYSAUX') AND owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP');

2

Create a new tablespace for your data and enable encryption for it.

CREATE TABLESPACE my_encrypted_tbs DATAFILE '/path/to/datafile/my_encrypted_tbs01.dbf' SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT USING 'AES256');
-- You might need to set a wallet first if not already done:
-- ADMINISTER KEY MANAGEMENT SET KEYSTORE 'file:/path/to/your/keystore' IDENTIFIED BY 'your_password';

3

Alter the identified objects to move them to the newly created encrypted tablespace. For tables, this involves an `ALTER TABLE ... MOVE` operation. For indexes, you might need to drop and recreate them or use `ALTER INDEX ... REBUILD`.

ALTER TABLE owner.table_name MOVE TABLESPACE my_encrypted_tbs;
-- For indexes associated with the table:
-- ALTER INDEX owner.index_name REBUILD TABLESPACE my_encrypted_tbs;

4

After successfully moving the objects and verifying their integrity, you can optionally drop the original data files from SYSTEM/SYSAUX if they are no longer needed for these objects. **Caution:** Be extremely careful with this step to avoid dropping essential system files.

ALTER TABLESPACE SYSTEM SHRINK SPACE KEEP 100M;
-- Or more granularly, if you can identify specific segments to drop:
-- DBMS_SPACE_ADMIN.SEGMENT_DEALLOCATE(...);

2. Utilize TDE for User Tablespaces easy

Focus on encrypting user-created tablespaces instead of SYSTEM/SYSAUX.

1

Ensure Transparent Data Encryption (TDE) is configured for your Oracle Database. This typically involves setting up a keystore and master encryption key.

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/path/to/your/keystore' IDENTIFIED BY 'your_password';
ADMINISTER KEY MANAGEMENT SET KEYSTORE 'file:/path/to/your/keystore' IDENTIFIED BY 'your_password';
ADMINISTER KEY MANAGEMENT ADD SECRET KEY keystore_name IDENTIFIED BY 'your_password' WITH BACKUP;

2

Create new tablespaces for your application data and specify encryption during creation.

CREATE TABLESPACE app_data_tbs DATAFILE '/path/to/datafile/app_data_tbs01.dbf' SIZE 200M ENCRYPTION USING 'AES128' DEFAULT STORAGE (ENCRYPT USING 'AES128');

3

Migrate your application's data from any existing user tablespaces to the new encrypted tablespace. This can be done through export/import (Data Pump) or by creating new tables in the encrypted tablespace and inserting data.

-- Example using Data Pump to export and import:
-- expdp system/password@your_sid schemas=your_schema directory=data_pump_dir dumpfile=app_data_export.dmp
-- impdp system/password@your_sid directory=data_pump_dir dumpfile=app_data_export.dmp remap_tablespace=old_tbs:app_data_tbs

3. Review Oracle's Best Practices for Encryption medium

Understand and adhere to Oracle's guidelines for TDE.

1

Consult the Oracle documentation for Transparent Data Encryption (TDE). Pay close attention to sections detailing the use of TDE with SYSTEM and SYSAUX tablespaces. Oracle explicitly states that direct encryption of SYSTEM/SYSAUX data files is not supported.

Search for 'Oracle Transparent Data Encryption Administrator's Guide' for your Oracle Database version.

2

Understand that SYSTEM and SYSAUX tablespaces contain critical database metadata and internal objects. Attempting to encrypt these directly can lead to database corruption and instability.

N/A

3

Focus your encryption efforts on user-created tablespaces that store sensitive application data. This is the intended and supported use of TDE.

N/A

Oracle Error ORA-28436: Tablespace Encryption Error