2b.50. Total Volume Backup Forecast (By Volume) (OCI360_BACKUPS) 2b.50. Total Volume Backup Forecast (By Volume) (OCI360_BACKUPS)
| # | VOLUME_NAME | IS_H | SIZE_IN_GBS | TIME_CREATED | COMPARTMENT_NAME | LIFECYCLE_STATE | VOLU | AVAILABILITY_DOMAIN | TODAY | NEXT_1MONTH_BKPS | NEXT_2MONTH_BKPS | NEXT_3MONTH_BKPS | NEXT_YEAR_BKPS | NEXT_2YEAR_BKPS | NEXT_5YEAR_BKPS | ID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | test_vol | true | 50 | 2020-06-26T18:15:04.901000+00:00 | compt_dev_db | TERMINATED | CYtq:US-ASHBURN-AD-3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000711 | |
| 2 | test_vol | true | 50 | 2020-06-26T19:28:23.433000+00:00 | compt_dev_app | AVAILABLE | CYtq:US-ASHBURN-AD-3 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000710 | |
| 3 | oci360_u01 | true | 50 | 2020-05-20T15:05:40.997000+00:00 | compt_dev_app | AVAILABLE | CYtq:US-ASHBURN-AD-3 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ocid1.volume.oc1.iad.000000000000000000000000000000000000000000000000000000000712 |
#: click on a column heading to sort on it
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
)
,
bkps as (
select jt.DISPLAY_NAME VOLUME_NAME,
jt.IS_HYDRATED,
jt.SIZE_IN_GBS,
jt.TIME_CREATED,
tcomp.NAME COMPARTMENT_NAME,
jt.LIFECYCLE_STATE,
jt.VOLUME_GROUP_ID,
jt.AVAILABILITY_DOMAIN,
tas.creation_date,
tas.expire_date,
jt.ID
from bkps_in_future tas,
OCI360_VOLUMES jt,
OCI360_COMPARTMENTS tcomp,
OCI360_BKP_POLICY_ASSIGN tbkppolassign
WHERE jt.COMPARTMENT_ID = tcomp.ID
AND jt.ID = tbkppolassign.ASSET_ID (+)
AND tbkppolassign.POLICY_ID = tas.POLICY_ID (+)
UNION ALL
SELECT jt.DISPLAY_NAME VOLUME_NAME,
jt.IS_HYDRATED,
jt.SIZE_IN_GBS,
jt.TIME_CREATED,
tcomp.NAME COMPARTMENT_NAME,
jt.LIFECYCLE_STATE,
jt.VOLUME_GROUP_ID,
jt.AVAILABILITY_DOMAIN,
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,
jt.ID
FROM OCI360_BACKUPS t1,
OCI360_VOLUMES jt,
OCI360_COMPARTMENTS tcomp
WHERE t1.lifecycle_state = 'AVAILABLE'
AND t1.volume_id = jt.id (+)
AND t1.compartment_id = tcomp.id
)
select VOLUME_NAME,
IS_HYDRATED,
SIZE_IN_GBS,
TIME_CREATED,
COMPARTMENT_NAME,
LIFECYCLE_STATE,
VOLUME_GROUP_ID,
AVAILABILITY_DOMAIN,
SUM(CASE WHEN SYSDATE BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) TODAY,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,1) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_1MONTH_BKPS,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,2) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_2MONTH_BKPS,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,3) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_3MONTH_BKPS,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,12) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_YEAR_BKPS,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,24) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_2YEAR_BKPS,
SUM(CASE WHEN ADD_MONTHS(SYSDATE,60) BETWEEN creation_date AND expire_date THEN 1 ELSE 0 END) NEXT_5YEAR_BKPS,
ID
from bkps
GROUP BY VOLUME_NAME,
IS_HYDRATED,
SIZE_IN_GBS,
TIME_CREATED,
COMPARTMENT_NAME,
LIFECYCLE_STATE,
VOLUME_GROUP_ID,
AVAILABILITY_DOMAIN,
ID;
3 rows selected.