ADO/ILM policy

[Roshan]

Oracle Database 12.2.0
RHEL 7.4

Hi Erman,

I would like to have some guidance on ILM/ADO
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/ilm-strategy-heatmap-ado.html#GUID-DE4C9EB5-FBDC-4621-AA7B-17EBD11FBF27 

I would like to implement compression and moving to low cost storage tier.

Suppose I want to implement the following policies:

/* Add a segment level compression policy for data after 12 months of no access */
SQL> ALTER TABLE BIMOBSMS MODIFY PARTITION SYS_P2280 ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 2 MONTHS OF NO ACCESS;
ALTER TABLE BIMOBSMS MODIFY PARTITION SYS_P2280 ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 2 MONTHS OF NO ACCESS
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for
tablespaces on this storage type

/* Add storage tier policy to move old data to a different tablespace */
/* that is on low cost storage media */
SQL> ALTER TABLE BIMOBSMS MODIFY PARTITION SYS_P2280 ILM ADD POLICY TIER TO archive_data;

Table altered.

When I query dba_segments:
select * from dba_segments where partition_name='SYS_P2280';
I see that PARTITION SYS_P2280 has not been moved to archive_data.

Also, my current ASM structure is shown as attached below
sizing.xlsx

If I create a tablespace where it's datafiles reside on low cost storage tier(+DATARC), then I compress and move the data/partition to this tablespace(using the above 2 policies), is this strategy ok?


Thanks,
Roshan

[ErmanArslansOracleBlog]

Did you read the manual?
There it says :

For TIER TO tablespace

Data will be migrated when data usage of the tablespace quota reaches the percentage defined by TBS_PERCENT_USED. The database will make a best effort to migrate enough data so that the amount of free space within the tablespace quota reaches the percentage defined by TBS_PERCENT_FREE. Refer to Oracle Database PL/SQL Packages and Types Reference for more information on TBS_PERCENT_USED and TBS_PERCENT_FREE, which are constants in the DBMS_ILM_ADMIN package.

Ref: https://docs.oracle.com/database/121/SQLRF/statements_3001.htm

[Roshan]

The blog below is also helpful. It clarifies my requirement
https://blogs.oracle.com/dbstorage/ado-automating-storage-tiering-for-information-lifecycle-management

"when a tablespace reaches the used threshold (85%) defined by the user, the
database will automatically move the coldest table/partition(s) in the
tablespace to the target tablespace until the tablespace quota has at least 25
percent free. Of course this only applies to tables and partitions that have a
"TIER TO" ADO policy defined. "

The "TIER TO" policy can be implemented as shown below
http://blog.sqlora.com/en/ilm-storage-tiering/