oci360 2b.53. Total Volume Backups Cost Forecast - Avg (OCI360_BACKUPS)


Notes:
1) drag to zoom, and right click to reset
2) up to 31 days of awr history were considered

3) Future costs are estimations based on previous backups size.
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 TYPE                                                                                         VARCHAR2(16)
 VOLUME_ID                                                                                    VARCHAR2(128)
 KMS_KEY_ID                                                                                   VARCHAR2(4)
 SIZE_IN_GBS                                                                                  NUMBER
 SIZE_IN_MBS                                                                                  NUMBER
 SOURCE_TYPE                                                                                  VARCHAR2(8)
 SYSTEM_TAGS$ORCL_CLOUD$FREE_TIER_RETAINED                                                    VARCHAR2(4)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(16)
 TIME_CREATED                                                                                 VARCHAR2(32)
 FREEFORM_TAGS$BACKUP                                                                         VARCHAR2(16)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 EXPIRATION_TIME                                                                              VARCHAR2(4)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 UNIQUE_SIZE_IN_GBS                                                                           NUMBER
 UNIQUE_SIZE_IN_MBS                                                                           NUMBER
 TIME_REQUEST_RECEIVED                                                                        VARCHAR2(32)
 SOURCE_VOLUME_BACKUP_ID                                                                      VARCHAR2(4)


WITH
days_in_future as (
SELECT vdate,
       decode(to_number(to_char (vdate, 'd', 'nls_date_language=english')),1,1,0) is_sunday,
       decode(vdate,trunc(vdate,'MM'),1,0)                                        is_month_first_day,
       decode(vdate,trunc(vdate,'YYYY'),1,0)                                      is_year_first_day
FROM   (SELECT  trunc(sysdate) + rownum vdate
        FROM    dual
        CONNECT BY LEVEL <= 366*6 /*  6 Years */
       )
),
days_in_future_period as (
SELECT vdate,
       case
         when is_sunday+is_month_first_day+is_year_first_day = 0
           then 'ONE_DAY'
         when is_sunday=1 and is_month_first_day+is_year_first_day = 0
           then 'ONE_WEEK'
         when is_month_first_day=1 and is_year_first_day = 0
           then 'ONE_MONTH'
         else 'ONE_YEAR'
         end period
FROM   days_in_future
),
bkp_policies as (
SELECT distinct
       id policy_id,
       display_name policy_name,
       schedules$period period,
       schedules$backup_type type,
       schedules$retention_seconds retention_seconds
FROM   OCI360_BKP_POLICY
),
bkps_in_future as (
select t2.policy_name,
       t2.policy_id,
       t1.period,
       t2.type,
       t1.vdate creation_date,
       t1.vdate+ (t2.retention_seconds/(60*60*24)) expire_date
FROM   days_in_future_period t1,
       bkp_policies t2
WHERE  t1.period=t2.period
)
,
bkp_size_estimation as (
select   DECODE(type,'INCREMENTAL',avg(unique_size_in_gbs),'FULL',max(unique_size_in_gbs)) est_unique_size_in_gbs,
         type,
         volume_id
FROM     OCI360_BACKUPS
WHERE    lifecycle_state = 'AVAILABLE'
GROUP BY type,
         volume_id
),
bkps as (
select tas.creation_date,
       tas.expire_date,
       bse.est_unique_size_in_gbs unique_size_in_gbs,
       tas.type
from   bkps_in_future tas,
       OCI360_VOLUMES jt,
       OCI360_BKP_POLICY_ASSIGN tbkppolassign,
       bkp_size_estimation bse
WHERE  jt.ID = tbkppolassign.ASSET_ID
AND    tbkppolassign.POLICY_ID = tas.POLICY_ID
AND    jt.ID = bse.volume_id (+)
AND    tas.type = bse.type (+)
UNION ALL
SELECT cast(to_timestamp_tz(t1.time_created,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') AT LOCAL AS DATE) creation_date,
       nvl(cast(to_timestamp_tz(t1.expiration_time,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') AT LOCAL AS DATE),sysdate+10000) expire_date,
       unique_size_in_gbs,
       t1.type
FROM   OCI360_BACKUPS t1
WHERE  t1.lifecycle_state = 'AVAILABLE'
),
bkps_sum_day as (
select vdate,
       type,
       SUM(CASE WHEN vdate BETWEEN creation_date AND expire_date THEN unique_size_in_gbs ELSE 0 END) TOTAL
from   bkps,
       ( SELECT  trunc(sysdate) + ((ROWNUM-1)*10) vdate /*  (10 in 10 days) */
         FROM    dual
         CONNECT BY LEVEL <= (366*6) /*  6 Years */
       ) days
where  vdate <= ADD_MONTHS(sysdate,12*6) /*  6 Years */
group by vdate, type
),
bkps_sum_month as (
select   trunc(vdate,'mm') vdate,
         type,
         max(total) total
from     bkps_sum_day
group by trunc(vdate,'mm'), type
),
result as (
SELECT rank() over (order by vdate asc)                                      snap_id,
       TO_CHAR(vdate, 'YYYY-MM-DD HH24:MI:SS')                               begin_time,
       TO_CHAR(ADD_MONTHS(vdate,1),'YYYY-MM-DD HH24:MI:SS')                  end_time,
       ROUND(SUM(total)*MF,2)                                                line1,
       ROUND(SUM(CASE WHEN type = 'INCREMENTAL' THEN total ELSE 0 END)*MF,2) line2,
       ROUND(SUM(CASE WHEN type = 'FULL'        THEN total ELSE 0 END)*MF,2) line3
FROM   bkps_sum_month,
       "OCI360_PRICING"
WHERE  subject = 'STORAGE'
AND    inst_type = 'Object Storage' /*  Backups are encrypted and stored in Oracle Cloud Infrastructure Object Storage */
GROUP  BY vdate, MF
)
select snap_id,
       begin_time,
       end_time,
       line1,
       line2,
       line3,
       0 dummy_04,
       0 dummy_05,
       0 dummy_06,
       0 dummy_07,
       0 dummy_08,
       0 dummy_09,
       0 dummy_10,
       0 dummy_11,
       0 dummy_12,
       0 dummy_13,
       0 dummy_14,
       0 dummy_15
from   result
order by snap_id;


73 rows selected.

oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T15:52:55