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