Introduction to Information Life Cycle Management
1. Concept
The concept of ILM (Information Life Cycle Management) was born in the 1960s and 1970s. It was proposed by StorageTek (acquired by Oracle in 2010). EMC is a comprehensive management method from the generation and initial storage stage of information system data and related metadata to the last outdated deletion.
According to various data standards (common use, importance, etc.), the data can be automatically grouped into different layers, and the data can be automatically moved between different layers.
2. Background
- Information explosion growth
- Cost Limitation
- Information Value
- Laws and regulations require that ____________
ILM in Oracle
1. Implementation before 12C
(1) Implementation steps
- Define data hierarchies and determine partitioning strategies -- layered by usability, privacy, importance, etc.
- Determining Storage Layer Policies for Data Levels
- Creating Data Access and Mobility Policies
- Define and implement complex strategies
Data Retention
Immutability
Privacy (Data Privacy -- VPD, Data Encryption)
Auditing (Standard Auditing, Fine-grained Auditing)
Expiration
(2) Disadvantages
- When categorizing by common use, it is up to the DBA to determine which cold data is.
- Implementation of Partition Mobility strategy is more complex
- Partition mobility does not support online operations
- Partition maintenance (move,drop,truncate partition, etc.) can lead to index failure
(3) Implementing testing
Creating table spaces CREATE TABLESPACE ILM_HIGH_COST DATAFILE '/data/ilm_high_cost.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 30G; CREATE TABLESPACE ILM_LOW_COST DATAFILE '/data/ilm_low_cost.dbf' SIZE 1G AUTOEXTEND ON NEXT 10G MAXSIZE 30G; //Create test users and authorize create user ilmtest identified by ilmtest default TABLESPACE ILM_HIGH_COST; grant connect,resource to ilmtest; ALTER USER ilmtest QUOTA UNLIMITED ON ILM_HIGH_COST; ALTER USER ilmtest QUOTA UNLIMITED ON ILM_LOW_COST; //Establishment of partition tables CREATE TABLE ilmtest.ilm_table ( ID VARCHAR2(120) primary key, time NUMBER(18,0) ) partition by range(time) interval (86400000) --one day ( partition p_day_1 values less than (1535731200000) --2018-09-01 00:00:00 ); //Indexing CREATE INDEX ilmtest.ilm_table_N2 ON ilmtest.ilm_table(time); //Import test data //View partitions select table_name,partition_name from dba_tab_partitions where table_name='ILM_TABLE'; //Move the partition manually. Note that 11g locks the table and the index will fail after move. ALTER TABLE ilmtest.ilm_table MOVE PARTITION SYS_P929 TABLESPACE ilm_low_cost COMPRESS; //Creating stored procedures and job periodically moving partitions //slightly
2. 12c Implementation Method
Improvement of 12c
- Thermograph HEAT MAP
- ADO for Automatic Data Optimization
- Online move partition (supports online move operation and index does not invalidate after move)
(2) Thermograph
Tracking Oracle DB data access (segment level) and modifications (row level and segment level)
Characteristic:
- Easy to start, no need to restart
ALTER SYSTEM SET HEAT_MAP = ON;
When enabled, data access will be tracked (except for objects in system and sysaux), hot maps will be enabled/disabled, and ADO will be enabled/disabled at the same time.
- Thermal Map Tracking
Tracking data segment query and modification time
Tracking block modification time
- Overall
Distinguishing index lookup access from full table scan access
Automatic exclusion of maintenance task access: statistical information collection, DDL, backup, table redefinition, etc.
- High Performance
No Cost at Object Level
Block cost < 5%
Relevant Views:
V$HEAT_MAP_SEGMENT - Shows real-time segment access information
ALL_,DBA_, USER_HEAT_MAP_SEGMENT - Shows the latest access information (V $view is written every hour)
ALL_, DBA_,USER_HEAT_MAP_SEG_HISTOGRAM - Show segment access information
DBA_HEATMAP_TOP_OBJECTS - Shows the most active objects
DBA_HEATMAP_TOP_TABLESPACES - Shows the most active table spaces
The DBMS_HEAT_MAP package provides more flexible information display, supports viewing block, region, segment, object and table space-level thermal map information, and ADO uses it to filter "hot" blocks.
SELECT * FROM TABLE(DBMS_HEAT_MAP.BLOCK_HEAT_MAP('SH','SALES')) WHERE ROWNUM < 10;
SELECT SUBSTR(tablespace_name,1,16) Tblspace, min_writetime, min_ftstime
FROM TABLE(DBMS_HEAT_MAP.TABLESPACE_HEAT_MAP('EXAMPLE'));
SELECT relative_fno, block_id, blocks, TO_CHAR(min_writetime, 'mm-dd-yy hh-mi-ss') Mintime,
TO_CHAR(max_writetime, 'mm-dd-yy hh-mi-ss') Maxtime,
TO_CHAR(avg_writetime, 'mm-dd-yy hh-mi-ss') Avgtime
FROM TABLE(DBMS_HEAT_MAP.EXTENT_HEAT_MAP('SH','SALES')) WHERE ROWNUM < 10;
SELECT SUBSTR(owner,1,10) Owner, SUBSTR(segment_name,1,10) Segment,
SUBSTR(partition_name,1,16) Partition, SUBSTR(tablespace_name,1,16) Tblspace,
segment_type, segment_size FROM TABLE(DBMS_HEAT_MAP.OBJECT_HEAT_MAP('SH','SALES'));
(3) ADO for automatic data optimization
ADO automatically compresses and moves data between different levels of storage according to user-defined policies based on information gathered from thermal maps
ADO Strategy
create table t1 (C1 number, C2 varchar2(9)) partition by list(C2)
(partition p1 values('clerk', 'salesman') ilm add policy column
store compress for archive high segment after 3 months of creation);
ALTER TABLE sales MODIFY PARTITION sales_q1_2002
ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW
AFTER 30 DAYS OF NO MODIFICATION;Storage levels are moved by default by table space usage (85%, 25%).
ALTER TABLE sales MODIFY PARTITION sales_q1_1999 ILM ADD POLICY TIER TO my_low_cost_sales_tablespace;
When the user-defined table space usage rate moves
BEGIN DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED, 85): DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE, 25): END;
In the previous example, when the table space reaches a user-defined threshold (85%), the database automatically moves the extremely cold tables/partitions in the table space to the target table space (without checking the remaining storage space in the target table space) until the table space quota is at least 25% available.
Definable move by function strategy
CREATE OR REPLACE FUNCTION NOT_MODIFIED_CUSTOM (objn IN NUMBER) RETURN BOOLEAN IS days number:=0; begin select max(sysdate - SEGMENT_WRITE_TIME) into days from user_heat_map_segment where subobject_name = (select subobject_name from user_objects where object_id=objn); -- For DEBUG purposes only -- insert into ado_table select object_id,object_name,subobject_name,days, sysdate from user_objects where object_id=objn; -- commit; if (days>1) then return true; else return false; end if; end; / ALTER TABLE t MODIFY PARTITION P1 ILM ADD POLICY TIER TO ILM_LOW_COST ON NOT_MODIFIED_CUSTOM;
Execution schematic diagram
Since HEAP MAP's data in memory is written to disk every hour, it is generally delayed to view DBA_HEAT_MAP_SEGMENT. Actual data is stored on the HEAT_MAP_STAT $dictionary base table
3.12.2 New Characteristics
ADO supports In-Memory storage
ALTER TABLE sales_2015 ILM ADD POLICY NO INMEMORY AFTER 7 DAYS OF NO ACCESS;
(2) ADO and heat map support multi-tenant architecture, which can be set separately in PDB
https://oracle-base.com/articles/12c/heat-map-ilm-ado-12cr2
(3) ADO supports tier policy assignment according to time conditions (but table space must be read-only)
ALTER TABLE invoices MODIFY PARTITION invoices_2016_q4 ILM ADD POLICY TIER TO slow_storage_ts READ ONLY SEGMENT AFTER 6 MONTHS OF NO ACCESS;
4. Disabling and deleting ILM ADO policy
/* You can disable or delete an ADO policy in a table with the following */ ALTER TABLE sales_ado ILM DISABLE POLICY P1; ALTER TABLE sales_ado ILM DELETE POLICY P1; /* You can disable or delete all ADO policies in a table with the following */ ALTER TABLE sales_ado ILM DISABLE_ALL; ALTER TABLE sales_ado ILM DELETE_ALL; /* You can disable or delete an ADO policy in a partition with the following */ ALTER TABLE sales MODIFY PARTITION sales_q1_2002 ILM DISABLE POLICY P2; ALTER TABLE sales MODIFY PARTITION sales_q1_2002 ILM DELETE POLICY P2; /* You can disable or delete all ADO policies in a partition with the following */ ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DISABLE_all; ALTER TABLE sales MODIFY PARTITION sales_q1_2000 ILM DELETE_ALL;
5. ADO Enhancement
Implement ADO Policy Immediately
declare v_executionid number; begin dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA, execution_mode => dbms_ilm.ilm_execution_online, task_id => v_executionid); end; /
6. ADO Relevant Views
• (USER/DBA)_ILMPOLICIES – ADO policies and their status
• (USER/DBA)_ILMDATAMOVEMENTPOLICIES – Details of all ADO policies
• (USER/DBA)_ILMOBJECTS – Policy associations with objects, inheritance
information, and the status
• (USER/DBA)_ILMEVALUATION_DETAILS -- Results of evaluation are recorded
• (USER/DBA)_ILMTASKS – Tasks and their status. A task ID tracks an ADO evaluation/execution instance
• (USER/DBA)_ILMEVALUATIONDETAILS – Evaluation details for each task .
• (USER/DBA)_ILMRESULTS – Status and results of every ADO job.
(USER/DBA)_ILMPARAMETERS--ILM related parameters
7. Use restrictions
- Partition-level ADO and compression are supported for Temporal Validity except for row-level ADO policies that would compress rows that are past their valid time(access or modification).
- Partition-level ADO and compression are supported for in-database archiving if partitioned on the ORA_ARCHIVE_STATE column.
- Custom policies (user-defined functions) for ADO are not supported if the policies default at the tablespace level.
- ADO does not perform checks for storage space in a target tablespace when using storage tiering.
- ADO is not supported on tables with object types or materialized views.
- ADO is not supported with index-organized tables or clusters.
- ADO concurrency (the number of simultaneous policy jobs for ADO) depends on the concurrency of the Oracle scheduler. If a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later.
- ADO has restrictions related to moving tables and table partitions