?Oracle Database 12c????Information Lifecycle Management ILM?Storage Enhancements
- by Liu Maclean(???)
Oracle Database 12c????Information Lifecycle Management ILM ?????????Storage Enhancements ????????
Lifecycle Management ILM ????????? Automatic Data Placement ??????, ??ADP?
?????? 12c???????Datafile??? Online Move Datafile, ????????????????datafile???????,???????????????
????(12.1.0.1)Automatic Data Optimization?heat map????????:
????????? (CDB)?????Automatic Data Optimization?heat map
Row-level policies for ADO are not supported for Temporal Validity. Partition-level ADO and compression are supported if partitioned on the end-time columns.
Row-level policies for ADO are not supported for in-database archiving. Partition-level ADO and compression are supported 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 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.
Policies for ADO are only run in the Oracle Scheduler maintenance windows. Outside of the maintenance windows all policies are stopped. The only exceptions are those jobs for rebuilding indexes in ADO offline mode.
ADO has restrictions related to moving tables and table partitions.
??????row,segment???????????ADO??,?????create table?alter table?????? ????ADO??,??????????????,???????????????? storage tier , ?????????storage tier?????????, ??????????????ADO??????????? segment?row??group?
?CREATE TABLE?ALERT TABLE???ILM???,??????????????????ADO policy? ??ILM policy???????????????? ??????? ????ADO policy, ?????alter table ???????,??????????????
CREATE TABLE sales_ado
(PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL )
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
AFTER 6 MONTHS OF NO ACCESS;
SQL> SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled
2 FROM USER_ILMPOLICIES;
POLICY_NAME POLICY_TYPE ENABLED
-------------------- -------------------------- --------------
P41 DATA MOVEMENT YES
ALTER TABLE sales MODIFY PARTITION sales_1995
ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT
AFTER 6 MONTHS OF NO ACCESS;
SELECT SUBSTR(policy_name,1,24) AS POLICY_NAME, policy_type, enabled
FROM USER_ILMPOLICIES;
POLICY_NAME POLICY_TYPE ENABLE
------------------------ ------------- ------
P1 DATA MOVEMENT YES
P2 DATA MOVEMENT YES
/* You can disable an ADO policy with the following */
ALTER TABLE sales_ado ILM DISABLE POLICY P1;
/* You can delete an ADO policy with the following */
ALTER TABLE sales_ado ILM DELETE POLICY P1;
/* You can disable all ADO policies with the following */
ALTER TABLE sales_ado ILM DISABLE_ALL;
/* You can delete all ADO policies with the following */
ALTER TABLE sales_ado ILM DELETE_ALL;
/* You can disable an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DISABLE POLICY P2;
/* You can delete an ADO policy in a partition with the following */
ALTER TABLE sales MODIFY PARTITION sales_1995 ILM DELETE POLICY P2;
ILM ???????:
?????ILM ADP????,???????:
?????? ???? activity tracking, ????2????????,???????????????????:
SEGMENT-LEVEL????????????????????
ROW-LEVEL????????,???????
????????:
1??????? SEGMENT-LEVEL activity tracking
ALTER TABLE interval_sales ILM ENABLE ACTIVITY TRACKING SEGMENT ACCESS
???????INTERVAL_SALES??segment level activity tracking,??????????????????
2? ???????????
ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING (CREATE TIME , WRITE TIME);
3?????????
ALTER TABLE emp ILM ENABLE ACTIVITY TRACKING (READ TIME);
?12.1.0.1.0?????? ??HEAT_MAP??????????, ?????system??session?????heap_map?????????????
?????????HEAT MAP??,?
ALTER SYSTEM SET HEAT_MAP = ON;
?HEAT MAP??????,?????????????????????????? ??SYSTEM?SYSAUX?????????????
???????HEAT MAP??:
ALTER SYSTEM SET HEAT_MAP = OFF;
????? HEAT_MAP????, ?HEAT_MAP??? ??????????????????????
?HEAT_MAP?????????Automatic Data Optimization (ADO)??? ??ADO??,Heat Map ??????????
????V$HEAT_MAP_SEGMENT ??????? HEAT MAP??
SQL> select * from V$heat_map_segment;
no rows selected
SQL> alter session set heat_map=on;
Session altered.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from v$heat_map_segment;
OBJECT_NAME SUBOBJECT_NAME OBJ# DATAOBJ# TRACK_TIM SEG SEG FUL LOO CON_ID
-------------------- -------------------- ---------- ---------- --------- --- --- --- --- ----------
EMP 92997 92997 23-JUL-13 NO NO YES NO 0
??v$heat_map_segment???,?v$heat_map_segment??????????????X$HEATMAPSEGMENT
V$HEAT_MAP_SEGMENT displays real-time segment access information.
Column
Datatype
Description
OBJECT_NAME
VARCHAR2(128)
Name of the object
SUBOBJECT_NAME
VARCHAR2(128)
Name of the subobject
OBJ#
NUMBER
Object number
DATAOBJ#
NUMBER
Data object number
TRACK_TIME
DATE
Timestamp of current activity tracking
SEGMENT_WRITE
VARCHAR2(3)
Indicates whether the segment has write access: (YES or NO)
SEGMENT_READ
VARCHAR2(3)
Indicates whether the segment has read access: (YES or NO)
FULL_SCAN
VARCHAR2(3)
Indicates whether the segment has full table scan: (YES or NO)
LOOKUP_SCAN
VARCHAR2(3)
Indicates whether the segment has lookup scan: (YES or NO)
CON_ID
NUMBER
The ID of the container to which the data pertains. Possible values include:
0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
1: This value is used for rows containing data that pertain to only the root
n: Where n is the applicable container ID for the rows containing data
The Heat Map feature is not supported in CDBs in Oracle Database 12c, so the value in this column can be ignored.
??HEAP MAP??????????????????,????DBA_HEAT_MAP_SEGMENT???????? ???????HEAT_MAP_STAT$??????
??Automatic Data Optimization??????:
????1:
SQL> alter system set heat_map=on;
??????
????????????? scott??
http://www.askmaclean.com/archives/scott-schema-script.html
SQL> grant all on dbms_lock to scott;
?????
SQL> grant dba to scott;
?????
@ilm_setup_basic C:\APP\XIANGBLI\ORADATA\MACLEAN\ilm.dbf
@tktgilm_demo_env_setup
SQL> connect scott/tiger ;
????
SQL> select count(*) from scott.employee;
COUNT(*)
----------
3072
??? 1 ??
SQL> set serveroutput on
SQL> exec print_compression_stats('SCOTT','EMPLOYEE');
Compression Stats
------------------
Uncmpressed : 3072
Adv/basic compressed : 0
Others : 0
PL/SQL ????????
???????3072??????
????????? ????policy ????????????
alter table employee ilm
add policy row store compress advanced row
after 3 days of no modification
/
SQL> set serveroutput on
SQL> execute list_ilm_policies;
--------------------------------------------------
Policies defined for SCOTT
--------------------------------------------------
Object Name------ : EMPLOYEE
Subobject Name--- :
Object Type------ : TABLE
Inherited from--- : POLICY NOT INHERITED
Policy Name------ : P1
Action Type------ : COMPRESSION
Scope------------ : ROW
Compression level : ADVANCED
Tier Tablespace-- :
Condition type--- : LAST MODIFICATION TIME
Condition days--- : 3
Enabled---------- : YES
--------------------------------------------------
PL/SQL ????????
SQL> select sysdate from dual;
SYSDATE
--------------
29-7? -13
SQL> execute set_back_chktime(get_policy_name('EMPLOYEE',null,'COMPRESSION','ROW','ADVANCED',3,null,null),'EMPLOYEE',null,6);
Object check time reset ...
--------------------------------------
Object Name : EMPLOYEE
Object Number : 93123
D.Object Numbr : 93123
Policy Number : 1
Object chktime : 23-7? -13 08.13.42.000000 ??
Distnt chktime : 0
--------------------------------------
PL/SQL ????????
?policy?chktime???6??, ????set_back_chktime???????????????“????”?,?????????,????????
??????
alter system flush buffer_cache;
alter system flush buffer_cache;
alter system flush shared_pool;
alter system flush shared_pool;
SQL> execute set_window('MONDAY_WINDOW','OPEN');
Set Maint. Window OPEN
-----------------------------
Window Name : MONDAY_WINDOW
Enabled? : TRUE
Active? : TRUE
-----------------------------
PL/SQL ????????
SQL> exec dbms_lock.sleep(60) ;
PL/SQL ????????
SQL> exec print_compression_stats('SCOTT', 'EMPLOYEE');
Compression Stats
------------------
Uncmpressed : 338
Adv/basic compressed : 2734
Others : 0
PL/SQL ????????
??????????????? Adv/basic compressed : 2734 ???????
SQL> col object_name for a20
SQL> select object_id,object_name from dba_objects where object_name='EMPLOYEE';
OBJECT_ID OBJECT_NAME
---------- --------------------
93123 EMPLOYEE
SQL> execute list_ilm_policy_executions ;
--------------------------------------------------
Policies execution details for SCOTT
--------------------------------------------------
Policy Name------ : P22
Job Name--------- : ILMJOB48
Start time------- : 29-7? -13 08.37.45.061000 ??
End time--------- : 29-7? -13 08.37.48.629000 ??
-----------------
Object Name------ : EMPLOYEE
Sub_obj Name----- :
Obj Type--------- : TABLE
-----------------
Exec-state------- : SELECTED FOR EXECUTION
Job state-------- : COMPLETED SUCCESSFULLY
Exec comments---- :
Results comments- :
---
--------------------------------------------------
PL/SQL ????????
ILMJOB48?????policy?JOB,?12.1.0.1??J00x????
?MMON_SLAVE???M00x???15?????????
select sample_time,program,module,action from v$active_session_history where action ='KDILM background EXEcution' order by sample_time;
29-7? -13 08.16.38.369000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.17.38.388000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.17.39.390000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.23.38.681000000 ?? ORACLE.EXE (M002) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.32.38.968000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.33.39.993000000 ?? ORACLE.EXE (M003) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.33.40.993000000 ?? ORACLE.EXE (M003) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.36.40.066000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.37.42.258000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.37.43.258000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.37.44.258000000 ?? ORACLE.EXE (M000) MMON_SLAVE KDILM background EXEcution
29-7? -13 08.38.42.386000000 ?? ORACLE.EXE (M001) MMON_SLAVE KDILM background EXEcution
select distinct action from v$active_session_history where action like 'KDILM%'
KDILM background CLeaNup
KDILM background EXEcution
SQL> execute set_window('MONDAY_WINDOW','CLOSE');
Set Maint. Window CLOSE
-----------------------------
Window Name : MONDAY_WINDOW
Enabled? : TRUE
Active? : FALSE
-----------------------------
PL/SQL ????????
SQL> drop table employee purge ;
?????
???? ?????
spool ilm_usecase_1_cleanup.lst
@ilm_demo_cleanup ;
spool off
