********************************************************************************************* * 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