OCP 12C – Information Lifecycle Management and Storage Enhancements

Latest posts by Cyrille Modiano (see all)

Information Lifecycle Management

Information Lifecycle Management

Before reading this flashcard I recommend you to read my article on ILM.

Automatic Data Optimization (ADO)

  • Policy based data management
  • Let you define policies at :
    • Tablespace level
    • Segment level
    • Row level
  • Policies can either compress data or move data to a different tablespace
  • Policies let you define when, what and where to move data
  • Heat Map collect statistics used to trigger ADO actions
  • Heat Map collects statistics on data utilization like:
    • When does this row was last updated
    • When does this table was last updated
  • Policies let you define what to do with :
    • Active data
    • Frequently accessed data
    • Infrequently accessed data
    • Dormant data
  • Policies let you also define what to do with data when you encounter space pressure in a tablespace.

Views related to Information Lifecycle Management

  • DBA_ILMDATAMOVEMENTPOLICIES view displays information specific to data movement related attributes of an ILM policy for ADO.
  • DBA_ILMTASKS view displays the task Ids of the procedure EXECUTE_ILM. This view contains information about all ILM tasks for ADO.
  • The DBA_ILMEVALUATIONDETAILS view displays details on policies considered for a particular task. It also shows the name of the job that executes the policy in case the policy was selected for evaluation.
  • The DBA_ILMOBJECTS view displays all the objects and policies for ADO in the database.
  • The DBA_ILMPOLICIES view displays details about all the policies for ADO in the database.
  • The DBA_ILMRESULTS view displays information about data movement-related jobs for ADO in the database.
  • The DBA_ILMPARAMETERS view displays information about ADO-related parameters.

Enable/Disable Heat Map

  • To enable Heat Map use : ALTER SYSTEM SET HEAT_MAP=ON;
  • To disable Heat Map use : ALTER SYSTEM SET HEAT_MAP=OFF;
  • By default Heat Map is disabled

Compression policies

  • ROW STORE COMPRESS BASIC : Rows are compressed when inserted or updated with the Basic table compression.
  • ROW STORE COMPRESS ADVANCED : This is for Advanced Row Compression
  • COLUMN STORE COMPRESS FOR QUERY LOW/HIGH : This is Columnar Compression, it provides good performance and is used for frequently queried but not frequently updated data.
  • COLUMN STORE COMPRESS FOR ARCHIVE LOW/HIGH : This is the highest level of compression which should be used for infrequently accessed or updated data

Custom Policies

  • You can create custom policies based on your business rules by creating custom PL/SQL function which will be executed to define if a policy should be executed or not.
  • Custom PL/SQL functions return a boolean (TRUE or FALSE)
    • On TRUE the policy executes
    • On FALSE nothing happens

Evaluation and Execution of ILM Policies

    • By default row-level ILM policies are evaluated every 15 minutes by MMON.
    • You can customize the interval by execution the following procedure :
      SQL> EXEC DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.EXECUTION_INTERVAL,5);
    • You can manually evaluate an ILM policy by using :
SQL> var V_TASKID NUMER
SQL> EXEC DBMS_ILM.EXECUTE_ILM_TASK(owner=>'SCOTT', OBJECT_NAME=>'EMPLOYEE', TASK_ID=>:V_TASKID_NUMBER);

Enable/Disable Policies

  • To disable all Information Lifecycle Management  policies on a table use the following:
    • ALTER TABLE MY_TABLE ILM DISABLE_ALL;
  • To enable all Information Lifecycle Management  policies on a table use the following:
    • ALTER TABLE MY_TABLE ILM ENABLE_ALL;
  • To enable or disable a specific policy use :
    • ALTER TABLE MY_TABLE ENABLE/DISABLE POLICY POLICY_NAME;
  • To delete a policy on a table use:
    • ALTER TABLE MY_TABLE DELETE POLICY POLICY_NAME;

 

 ADO AND HEAT MAP ARE NOT SUPPORTED IN MULTITENANT DATABASES.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.