Though Oracle hasn’t provided straight forward method to disable TDE . But there is a work around for this.
NOTE – Don’t implement this on production database. Oracle Support/Development team will not help in resolving any issues arising due to such operations.
Follow Below steps
Find the encrypted table columns and modify them:
SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG ----- ------------ ------------ ----------------------------- RAJ EMPLOYEE SALARY AES 192 bits key SQL> alter table raj.employee modify( salary decrypt); Table altered. SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns; No rows selected
Now find the table under encrypted tablespace and move to normal tablespace:
SQL> select segment_name,tablespace_name from dba_segments where tablespace_name in ( select tablespace_name from dba_tablespaces where encrypted='YES'); SEGMENT_NAME TABLESPACE_NAME ---------------------------------------- ------------------------------ ENC_TABLE TEST_ENCRY SQL> alter table enc_table move tablespace users; Table altered. SQL> select segment_name,tablespace_name from dba_segments where tablespace_name in ( select tablespace_name from dba_tablespaces where encrypted='YES'); no rows selected SQL> drop tablespace test_encry including contents and datafiles; Tablespace dropped.
switch logfiles multiple times
SQL> alter system switch logfile; System altered. SQL> / / / System altered. SQL> System altered.
Close the wallet, move the wallet files and restart the database:
SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE close; cd /media/sf_stuff/wallet mv * backup/ SQL> startup force
Now TDE has been disabled. Now if you want to enable TDE again, then restore the wallet files and open the key.
Oracle Support indicates the you should never – ever remove a encryption wallet. It is married to the database forever.
Hi,
Yes correct. Once disabled you can move to a backup location , instead of removing .
Rajkishore