{"id":10762,"date":"2018-01-11T18:07:44","date_gmt":"2018-01-11T17:07:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/"},"modified":"2023-06-09T16:52:48","modified_gmt":"2023-06-09T14:52:48","slug":"automatic-data-optimization-part-ii-storagetiering","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/","title":{"rendered":"Automatic Data Optimization Part II : StorageTiering"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

In first a previous blog<\/a>, we saw how Automatic Data Optimization can be used to compress data under predefined conditions. In this blog we will see that another possible action with ADO is to move data to another storage. Indeed we will demonstrate how it is possible to move a table to another tablespace based on predefined conditions.
\nWe are using oracle a 12.1.0.2 non-CDB database.
\nLet\u2019s considerer following table ARTICLE in the tablespace USERS
\n
\nSQL> col TABLE_NAME for a20
\nSQL> select table_name,tablespace_name from user_tables where table_name='ARTICLE';
\nTABLE_NAME TABLESPACE_NAME
\n-------------------- ------------------------------
\nARTICLE USERS
\n<\/code>
\nNow let\u2019s create a target tablespace named COOLTAB. The table ARTICLE will be moved to this new COOLTAB tablespace depending on conditions we will define.
\n
\nSQL> create tablespace COOLTAB datafile '\/u01\/app\/oracle\/oradata\/NONCONT\/cooltab01.dbf' size 50M;
\nTablespace created.
\n<\/code>
\nAs specified in the previous blog<\/a>, the Heat Map must be enabled by setting the parameter HEAT_MAP to ON.
\n
\nSQL> show parameter heat_map;
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nheat_map string ON
\n<\/code>
\nWe can verify that heat map statistics are collected for table ARTICLE. It can be useful to gather statistics for ARTICLE using DBMS_STATS.
\n
\nSQL> SELECT OBJECT_NAME, to_char(TRACK_TIME,'DD-MON-YYYY HH24:MI:SS'), SEGMENT_WRITE \"Seg_write\",SEGMENT_READ \"Seg_read\", FULL_SCAN, LOOKUP_SCAN FROM v$heat_map_segment WHERE object_name='ARTICLE';
\nOBJECT_NAME TO_CHAR(TRACK_TIME,'DD-MON-YY Seg Seg FUL LOO
\n------------------------------ ----------------------------- --- --- --- ---
\nARTICLE 10-JAN-2018 09:40:48 NO NO YES NO
\n<\/code>
\nBefore creating the storage tiering policy, let\u2019s verify the status of our tablespaces. We can see that all the tablespaces are online
\n
\nSQL> select tablespace_name,status from dba_tablespaces;
\nTABLESPACE_NAME STATUS
\n------------------------------ ---------
\nSYSTEM ONLINE
\nSYSAUX ONLINE
\nUNDOTBS1 ONLINE
\nTEMP ONLINE
\nUSERS ONLINE
\nEXAMPLE ONLINE
\nCOOLTAB ONLINE
\n7 rows selected.
\n<\/code>
\nNow we are going to define a tiering policy which will move table ARTICLE from tablespace USERS to COOLTAB if there is no access to ARTICLE in the last 30 days. The policy will also put the tablespace COOLTAB in READ ONLY status.
\n
\nSQL> ALTER TABLE ARTICLE ILM ADD POLICY TIER TO COOLTAB READ ONLY SEGMENT AFTER 30 DAYS OF NO ACCESS;
\nTable altered.
\n<\/code>
\nIf we query the dba_ilmparameters we can see that for the demonstration the policy_time is set to 1. This means that 30 days are considered as 30 seconds due to the policy time
\n
\nSQL> select * from dba_ilmparameters;
\nNAME VALUE
\n-------------------- ----------
\nENABLED 1
\nRETENTION TIME 30
\nJOB LIMIT 2
\nEXECUTION MODE 2
\nEXECUTION INTERVAL 15
\nTBS PERCENT USED 85
\nTBS PERCENT FREE 25
\nPOLICY TIME 1
\n8 rows selected.
\n<\/code>
\nWe can verify that the policy is created
\n
\nSELECT policy_name, action_type, condition_type, condition_days FROM user_ilmdatamovementpolicies where action_type='STORAGE';
\nPOLICY_NAM ACTION_TYPE CONDITION_TYPE CONDITION_DAYS
\n---------- ----------- ---------------------- --------------
\nP65 STORAGE LAST ACCESS TIME 30
\n<\/code>
\nAnd that the policy is enabled
\n
\nSQL> SELECT policy_name, object_name, enabled FROM user_ilmobjects where policy_name='P65';
\nPOLICY_NAM OBJECT_NAME ENA
\n---------- --------------- ---
\nP65 ARTICLE YES
\n<\/code>
\nBy default ADO policies are automatically triggered during maintenance window. But in this demonstration we are going to manually execute the policy without waiting the maintenance window. So after 30 days of non-access (in fact 30 seconds) let’s trigger the policy
\n
\nSQL> EXEC dbms_ilm.flush_all_segments;
\nPL\/SQL procedure successfully completed.
\n.
\nDECLARE
\nv_executionid number;
\nBEGIN
\ndbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
\nexecution_mode => dbms_ilm.ilm_execution_offline,
\ntask_id => v_executionid);
\nEND;
\n\/
\nPL\/SQL procedure successfully completed.
\n<\/code>
\nLet\u2019s verify the result of the job. We can see that the job was successful
\n
\nSQL> SELECT task_id, job_name, job_state, completion_time completion FROM user_ilmresults ORDER BY 1 ;
\nTASK_ID
\n----------
\nJOB_NAME
\n--------------------------------------------------------------------------------
\nJOB_STATE
\n-----------------------------------
\nCOMPLETION
\n---------------------------------------------------------------------------
\n86
\nILMJOB232
\nCOMPLETED SUCCESSFULLY
\n10-JAN-18 02.28.31.506590 PM
\n<\/code>
\nAnd if we query again the USER_TABLES, we can see that the table ARTICLE were moved from tablespace USERS to tablespace COOLTAB.
\n
\nSQL> col TABLE_NAME for a20
\nSQL> select table_name,tablespace_name from user_tables where table_name='ARTICLE';
\nTABLE_NAME TABLESPACE_NAME
\n-------------------- ------------------------------
\nARTICLE COOLTAB
\n<\/code>
\nAnd if we query the dba_tablespaces, we can see that now the tablespace COOLTAB is now in READ ONLY STATUS
\n
\nSQL> select tablespace_name,status from dba_tablespaces;
\nTABLESPACE_NAME STATUS
\n------------------------------ ---------
\nSYSTEM ONLINE
\nSYSAUX ONLINE
\nUNDOTBS1 ONLINE
\nTEMP ONLINE
\nUSERS ONLINE
\nEXAMPLE ONLINE
\nCOOLTAB READ ONLY
\n7 rows selected.
\n<\/code>
\nConclusion<\/strong>: We have seen how ADO can help us to move data. Note that this movement can also be based on the fullness threshold of the source tablespace. One condition should be for example to move the table if the source tablespace is full at 65%.<\/p>\n","protected":false},"excerpt":{"rendered":"

By Mouhamadou Diaw In first a previous blog, we saw how Automatic Data Optimization can be used to compress data under predefined conditions. In this blog we will see that another possible action with ADO is to move data to another storage. Indeed we will demonstrate how it is possible to move a table to […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1251,1252,1262,96,1263],"type_dbi":[],"class_list":["post-10762","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-ado","tag-automatic-data-optimization","tag-ilm","tag-oracle","tag-storage-tiering"],"acf":[],"yoast_head":"\nAutomatic Data Optimization Part II : StorageTiering - dbi Blog<\/title>\n<meta name=\"description\" content=\"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automatic Data Optimization Part II : StorageTiering\" \/>\n<meta property=\"og:description\" content=\"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-11T17:07:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-09T14:52:48+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Automatic Data Optimization Part II : StorageTiering\",\"datePublished\":\"2018-01-11T17:07:44+00:00\",\"dateModified\":\"2023-06-09T14:52:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\"},\"wordCount\":410,\"commentCount\":0,\"keywords\":[\"ADO\",\"Automatic Data Optimization\",\"ILM\",\"Oracle\",\"Storage Tiering\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\",\"name\":\"Automatic Data Optimization Part II : StorageTiering - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-01-11T17:07:44+00:00\",\"dateModified\":\"2023-06-09T14:52:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automatic Data Optimization Part II : StorageTiering\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Automatic Data Optimization Part II : StorageTiering - dbi Blog","description":"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/","og_locale":"en_US","og_type":"article","og_title":"Automatic Data Optimization Part II : StorageTiering","og_description":"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle","og_url":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/","og_site_name":"dbi Blog","article_published_time":"2018-01-11T17:07:44+00:00","article_modified_time":"2023-06-09T14:52:48+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Automatic Data Optimization Part II : StorageTiering","datePublished":"2018-01-11T17:07:44+00:00","dateModified":"2023-06-09T14:52:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/"},"wordCount":410,"commentCount":0,"keywords":["ADO","Automatic Data Optimization","ILM","Oracle","Storage Tiering"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/","url":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/","name":"Automatic Data Optimization Part II : StorageTiering - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-01-11T17:07:44+00:00","dateModified":"2023-06-09T14:52:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"ILM, Storage Tiering, Automatic Data Optimization, ADO, Oracle","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/automatic-data-optimization-part-ii-storagetiering\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Automatic Data Optimization Part II : StorageTiering"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10762","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10762"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10762\/revisions"}],"predecessor-version":[{"id":25761,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10762\/revisions\/25761"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10762"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}