Oracle step by step rebuild encrypt tablespace (TDE) & SQLs

記錄一下重建加密 Tablespace 過程，但無重建 wallet

Step 1. Check TBS Encrypt Type

SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS, STATUS FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;

 

Step 2. Check wallet is OPEN

SELECT * FROM v$encryption_wallet;

 

Step 3. Drop old Tablespace

DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES;

Note:Error ORA-29857

Solution 1: (Google Online)

Oracle删除表空间遇到的问题及解决 https://www.linuxidc.com/Linux/2016-04/130313.htm

Solution 2: (My Problem)

step a. Drop user cascade

drop user username cascade;

step b. Query how many Remaining

select count(1) from dba_objects where owner = 'USERNAME';

step c. redoing Step 3.

 

Step 4. Create Encrypt Tablespace and modify datafile (The AES128 from Step 1 Query Return)

create tablespace tbs datafile '+DATADG/ORCL/DATAFILE/tbs_dat_01.dbf' size 100m encryption using 'AES128' default storage(encrypt);
alter database datafile '+DATADG/ORCL/DATAFILE/tbs_dat_01.dbf' size 10000m autoextend on next 100m maxsize 30000M;

 

Step 5. Add datafile (option)

alter tablespace TBS add datafile '+DATADG/ORCL/DATAFILE/tbs_dat_02.dbf' size 10000m autoextend on next 100m maxsize 30000M;

 

Step 6. Check Tablespace and Datafile encrypted or not

set linesize 150
set pagesize 150
column name format a70
select tablespace_name,name,encrypted from v$datafile_header;

col name format a40
select * from v$tablespace;

select tablespace_name, status, encrypted, plugged_in from dba_tablespaces; 

 

Step 7. Check Schema Default Tablespace (option)

SELECT username, default_tablespace, temporary_tablespace FROM dba_users WHERE username ='USERNAME';

 

Step 8. Change Default Tablespace if Default Tablespace is not TBS 

alter user username default tablespace TBS;