OCP 12C – Information Lifecycle Management and Storage Enhancements

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

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 :
    • You can manually evaluate an ILM policy by using :

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.