*********************************************************************************************
* Description: TDE tablespace with Lob data type via online redefinition without db downtime
* Date: 05:37 PM EST, 08/11/2017
*********************************************************************************************



<1> Step 1 - Create target TDE tablespace with secure file, and add data files to gaurantee the space would be sufficient enough:
     |
     |__ SQL> CREATE TABLESPACE BTB_CLT_DATA_SEC DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);             
     |
     |__ SQL> ALTER TABLESPACE BTB_CLT_DATA_SEC ADD DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M MAXSIZE UNLIMITED;
              ALTER TABLESPACE BTB_CLT_DATA_SEC ADD DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M MAXSIZE UNLIMITED;
              ALTER TABLESPACE BTB_CLT_DATA_SEC ADD DATAFILE '+DATA' SIZE 32767M AUTOEXTEND ON NEXT 1056M MAXSIZE UNLIMITED;

			  

			  
			  
<2> Step 2 - Prework to collect essential information for comparison purpose afterwards:
     |
     |__ SQL> select count(*) from APP_CLT.GPSDATA;
     |
     |
     |                    COUNT(*)
     |                    ----------
     |                    3245630
     |	  
     |
     |
     |__ SQL> select a.segment_name, sum(b.bytes)/1024/1024 from dba_lobs a, dba_segments b where a.segment_name = b.segment_name and a.table_name='GPSDATA' group by a.segment_name;
     |          	 
     |          	 
     |                    SEGMENT_NAME                            Table Lob Segment Original Size Before TDE in MB
     |                    --------------------------------------- ------------------------------------------------
     |                    SYS_LOB0000053103C00004$$                                                         290194	  
     |
     |
     |
     |__ SQL> select OWNER,SEGMENT_NAME, BYTES/1024/1024 as MB from dba_segments where owner='APP_CLT' and SEGMENT_NAME='GPSDATA';			 
     |	 
     |	 
     |                     OWNER            SEGMENT_NAME           Regular Table Data Size Before TDE in MB
     |                     ---------------- ---------------------- ----------------------------------------
     |                     APP_CLT          GPSDATA                                                     608	 
     |	  
     |
     |
     |__ SQL> select owner, TABLESPACE_NAME, SEGMENT_NAME, segment_type from dba_segments where TABLESPACE_NAME='BTB_CLT_DATA'; --< How many segments under source tablespace >
     |
     |
     |                     OWNER           TABLESPACE_NAME                SEGMENT_NAME                    SEGMENT_TYPE
     |                     --------------- ------------------------------ ------------------------------- ------------------
     |                     APP_CLT         BTB_CLT_DATA                   SYS_IL0000053103C00004$$        LOBINDEX
     |                     APP_CLT         BTB_CLT_DATA                   SYS_LOB0000053103C00004$$       LOBSEGMENT			  
     |	
     |
     |
     |__ SQL> select owner, object_name, object_type from dba_objects where owner in ('APP_CLT') and status='INVALID'; --< Check if any invalid objects >
	 
                           no row selected		 
			  
			 
			 
			 
<3> Step 3 - Confirm wallet, that contains master encryption key, is open for TDE:
     |
     |__ SQL> select * from v$encryption_wallet;
		
		
                          WRL_TYPE             WRL_PARAMETER                                      STATUS
                          -------------------- -------------------------------------------------- ------------------
                          file                 /u01/app/oracle/admin/${ORACLE_SID}/wallet         OPEN	 
			  
			  

<4> Step 4 - Get DDL of orignial table: 			  
     |
     |__ SQL> select dbms_metadata.get_ddl('TABLE','GPSDATA','APP_CLT') from dual;
	
                  CREATE TABLE "APP_CLT"."GPSDATA"
                    (    "ID" NUMBER NOT NULL ENABLE,
                         "TYPE" NUMBER,
                         "TRANSACTION_ID" NUMBER,
                         "DATA" CLOB,
                          CONSTRAINT "GPSDATA_PK" PRIMARY KEY ("ID")
                   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                   STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                   TABLESPACE "AIA_COLLECT_INDX"  ENABLE,
                          CONSTRAINT "GPSDATA_FK1" FOREIGN KEY ("TRANSACTION_ID")
                           REFERENCES "APP_CLT"."TRANSACTION" ("ID") ENABLE
                    ) SEGMENT CREATION IMMEDIATE
                   PCTFREE 10 PCTUSED 75 INITRANS 1 MAXTRANS 255
                  NOCOMPRESS LOGGING
                   STORAGE(INITIAL 1048576 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
                   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                   TABLESPACE "BTB_CLT_DATA02"
                  LOB ("DATA") STORE AS BASICFILE (
                   TABLESPACE "BTB_CLT_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
                   NOCACHE LOGGING
                   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));	 
			  
			  
			  
			  
<5> Step 5 - Redefine the DDL as intermin table with secure tablespace and  secure data file:		  
     |
     |__ SQL> CREATE TABLE "APP_CLT"."GPSDATA_SEC"
                (    "ID" NUMBER NOT NULL ENABLE,
                     "TYPE" NUMBER,
                     "TRANSACTION_ID" NUMBER,
                     "DATA" CLOB,
                      CONSTRAINT "GPSDATA_SEC_PK" PRIMARY KEY ("ID")
               USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
               STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
               PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
               BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
               TABLESPACE "AIA_COLLECT_INDX_SEC"  ENABLE,
                      CONSTRAINT "GPSDATA_SEC_FK1" FOREIGN KEY ("TRANSACTION_ID")
                       REFERENCES "APP_CLT"."TRANSACTION" ("ID") ENABLE
                ) SEGMENT CREATION IMMEDIATE
               PCTFREE 10 PCTUSED 75 INITRANS 1 MAXTRANS 255
              NOCOMPRESS LOGGING
               STORAGE(INITIAL 1048576 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
               PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
               BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
               TABLESPACE "BTB_CLT_DATA02_SEC"
              LOB ("DATA") STORE AS SECUREFILE (
               TABLESPACE "BTB_CLT_DATA_SEC" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
               NOCACHE LOGGING
               STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
               PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
               BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));	 
			  
			  
			  
			  
<6> Step 6 - Redefinition - feasibility verification:
     |
     |__ SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('APP_CLT', 'GPSDATA');	 
			  
              
			  
			  

<7> Step 7 - Redefinition - move data into intermin table:
     |
     |__ SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('APP_CLT', 'GPSDATA', 'GPSDATA_SEC');	 

	 
			  
	
	
<8> Step 8 - Redefinition - copy table dependency object:
     |
     |__ SQL> DECLARE
                 error_count pls_integer := 0;
			 
                 BEGIN
                     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('APP_CLT', 'GPSDATA', 'GPSDATA_SEC', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
                     DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
                  END;
                 /
	 
	 
                 ERROR at line 1: Ignorable since LOB segment index was copied along with table redefinition
                 ORA-01408: such column list already indexed
                 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1186
                 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1893
                 ORA-06512: at line 4
	 

	 
	 

<9> Step 9 - Redefinition - finish to swap name orignial table with intermin table: 
     |
     |__ o. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. 
     |      However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.
     |
     |__ SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('APP_CLT', 'GPSDATA', 'GPSDATA_SEC');	 
     |
     |__ SQL> select dbms_metadata.get_ddl('TABLE','GPSDATA','APP_CLT') from dual; 
              select dbms_metadata.get_ddl('TABLE','GPSDATA_SEC','APP_CLT') from dual;
	 
	 
	 
	 
	 
<10> Step 10 - Post-work to verify redefinition completed successfully:	 
     |
     |__ SQL> select count(*) from APP_CLT.GPSDATA;
     |
     |                  COUNT(*)
     |                ----------
     |                   3258772
     |
     |					
     |        select count(*) from APP_CLT.GPSDATA_SEC;	                	 
     |
     |                  COUNT(*)
     |                ----------
     |                   3258706		 
     |
     |
     |
     |__ SQL> select a.segment_name, sum(b.bytes)/1024/1024 from dba_lobs a, dba_segments b where a.segment_name = b.segment_name and a.table_name='GPSDATA' group by a.segment_name;
     |                
     |                SEGMENT_NAME                   SUM(B.BYTES)/1024/1024
     |                ------------------------------ ----------------------
     |                SYS_LOB0000147182C00004$$                  76739.1875	 
     |
     |	 
     |
     |__ SQL> select OWNER,SEGMENT_NAME, BYTES/1024/1024 as MB from dba_segments where owner='APP_CLT' and SEGMENT_NAME='GPSDATA';
     |
     |                OWNER            SEGMENT_NAME                 MB
     |                ---------------- -------------------- ----------
     |                APP_CLT          GPSDATA                     352
     |
     |
     |
     |__ SQL> select owner, TABLESPACE_NAME, SEGMENT_NAME, segment_type from dba_segments where TABLESPACE_NAME='BTB_CLT_DATA';	--< Garantee both src/tgt tbs has same segments >
     |
     |                 OWNER                 TABLESPACE_NAME                SEGMENT_NAME                        SEGMENT_TYPE
     |                 --------------------- ------------------------------ ----------------------------------- ------------------
     |                 APP_CLT               BTB_CLT_DATA                   SYS_IL0000053103C00004$$            LOBINDEX
     |                 APP_CLT               BTB_CLT_DATA                   SYS_LOB0000053103C00004$$           LOBSEGMENT
     |
     |				  
     |
     |__ SQL> select owner, TABLESPACE_NAME, SEGMENT_NAME, segment_type from dba_segments where TABLESPACE_NAME='BTB_CLT_DATA_SEC';
     | 
     |                OWNER                 TABLESPACE_NAME                SEGMENT_NAME                        SEGMENT_TYPE
     |                --------------------- ------------------------------ ---------------------------------- ------------------
     |                APP_CLT               BTB_CLT_DATA_SEC               SYS_IL0000147182C00004$$            LOBINDEX
     |                APP_CLT               BTB_CLT_DATA_SEC               SYS_LOB0000147182C00004$$           LOBSEGMENT
     |     
     |
     |	 
     |__ SQL> SELECT TABLESPACE_NAME  FROM DBA_TABLESPACES where ENCRYPTED='YES'
	 
	 
	 
	 
<11> Step 11 - Cleanup the old un-encrypted tablespace:	 
     |
     |__ SQL> drop table APP_CLT.GPSDATA_SEC;
     |
     |__ SQL> purge tablespace BTB_CLT_DATA; 
     |
     |__ SQL> select owner, TABLESPACE_NAME, SEGMENT_NAME, segment_type from dba_segments where TABLESPACE_NAME='BTB_CLT_DATA'; --< Should be no rows segment selected >
     |
     |__ SQL> drop tablespace BTB_CLT_DATA; 
	 
	 
	 
	 
	 
	 
	 
	 
			 
			 
			 
    
	

Your Comments