Managing Space - Recovery Appliance / ZDLRA / RA - 5 of 6 - Analyzing Individual Database Storage

In this Fifth Part of a series of blogs on ZDLRA/RA backup storage/space, I am going to share some of my insights into a slightly deeper analysis of Individual Database Backup Storage on Oracle "Zero Data Loss Recovery Appliance" (ZDLRA) also referred to as the “Recovery Appliance” (RA). To keep it simple RA or ZDLRA are used through out this blog.  All references to backups are related to Oracle Database backups only. This blog also assumes you have a basic understand of Oracle Database Backup and Recovery and RA/ZDLRA Administration.

This blog is specific to understanding, where opportunities exist over and above the storage management automation integrated into the RA, and when needed, help the RA Admin better understand overall backup storage on the RA and manage storage from a database backup perspective. With this in view the content herein has been kept at a higher level of analysis and limited to known tuning capabilities available to an RA Admin.

While I have tried to share my personal insights in this blog, Oracle Documentation and MOS (My Oracle Support) Notes, should still be your primary source of content and references on this subject. Any queries provided in this blog are not supported by Oracle.

What is not covered in this blog, is REPLICATION or SBT USAGE and Enterprise Manager Cloud control (EMCC) RA Storage management capabilities.

Content in the series will be covered under the following with the highlighted focused in current blog.

Content [Click On Link]:

Attributes Helping Define Storage (1 of 6)

Storage Purge Rules (2 of 6)

Planning for Storage Capacity (3 of 6)

Analyzing Total Storage On RA (4 of 6)

Analyzing Storage for Individual Database Backup (5 of 6)

Managing Storage Attributes & Workload Balance (6 of 6)

 

Analyzing Storage for Individual Database Backup:

Now that we have checked out the overall storage on the RA and some aspects to manage that storage it becomes essential to review individual database backups to ensure that each database backup is correctly setup to converge into the overall RA Storage for Capacity Planning. 

Figure 5-1 below shows the state of a database backup with reference to its existence on the RA. If the database is being Deleted “YES” that references to all backups of the databases are in the process of being deleted from the RA. It is a good cautionary practice to validate the number and volume/size of database backups being deleted before collecting Capacity Planning metrics. Supporting sample query can be found under “[[DATABASE - IDENTIFY PLANNED REMOVAL from RA]]”.

Figure 5-1 Database - Planned Removal from RA

 

[[DATABASE - IDENTIFY PLANNED REMOVAL from RA QUERY]]

/* %%%%%% IDENTIFY DATABASES being DELETED from RA QUERY %%%%%%%% */

alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';
set    line 300 pagesize 200 trimspool on
col    DB_UNIQUE_NAME for a15
col    DBID for 999999999999
col    DELETING for a8
col    STORAGE_LOCATION for a17
col    RECOVERY_WINDOW_GOAL for 9999999999 heading "--[RWG]--|RECOVERY|WINDOW|GOAL|(Days)"
col    CURRENT_RECOVERY_WINDOW for a16 heading "--[RWG]--|CURRENT|RECOVERY|WINDOW|GOAL|(Days)"
col    MAX_RETENTION_WINDOW for 9999999999 heading "--[RETW]--|MAX|RETENTION|WINDOW|(Days)"
col    RETENTION_ABOVE_RECOVERY for a17 heading "**REVIEW**|--[RETW VS RWG]--|'RETW' Above Or|Below 'RWG'|[* Check Best|Practices]|Days ('X' Times)"
col    RETENTION_ALERT for 9999999999.9 heading "**REVIEW**|RETENTION|(Allocation|'X' Times |Recovery Window|Goal)|[* Check Best|Practices]"
col    RECOVERY_WINDOW_SPACE for 999999.999 heading "--[RWG]--|ESTIMATED|RECOVERY|WINDOW SPACE|(GB)"
col    SIZE_ESTIMATE for 999999.999 heading "DB|ESTIMATE|SIZE|(GB)"
col    DISK_RESERVED_SPACE for 9999999.999 heading "DISK|RESERVED|SPACE|(GB)"
col    RESERVED_SPACE_ALERT for 999999.9 heading "**REVIEW**|RESERVED SPACE|(Allocated Compared|With MAX of|'DB ESTIMATE SIZE'|OR |'BACKUP SPACE_USAGE')|[* Check Best|Practices]|(%)"
col    SPACE_USAGE for 999999.999 heading "DB|BACKUP|SPACE_USAGE|(GB)"
col    KEEP_SPACE for 999999.999 heading  "KEEP_SPACE|(GB)"
col    CUMULATIVE_USAGE for 9999999999.999 heading "CUMULATIVE_USAGE|(GB)"
With storage_analysis AS
(
Select
    DB_UNIQUE_NAME,        -- The unique name of this protected database.
    DBID,            -- The DBID for this protected database.
    DELETING,        -- YES if this database is currently being deleted.
    STORAGE_LOCATION,    -- The name of the Recovery Appliance storage location used by this protected database.
    EXTRACT(Day from RECOVERY_WINDOW_GOAL) "RECOVERY_WINDOW_GOAL",
    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,' Days ',NULL)||
        EXTRACT(Hour From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,':',NULL)||
        ltrim(to_char(EXTRACT(Minute From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')),'09'))||NVL2(HIGH_TIME,' Hrs',NULL) "CURRENT_RECOVERY_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) "MAX_RETENTION_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) - EXTRACT(Day from RECOVERY_WINDOW_GOAL) || NVL2(MAX_RETENTION_WINDOW,' Days',NULL) ||
    NVL2(MAX_RETENTION_WINDOW,' (',NULL) || ltrim(to_char(EXTRACT(Day from MAX_RETENTION_WINDOW) / EXTRACT(Day from RECOVERY_WINDOW_GOAL),'999.9'))||NVL2(MAX_RETENTION_WINDOW,'x)',NULL)    "RETENTION_ABOVE_RECOVERY",
    SIZE_ESTIMATE    "SIZE_ESTIMATE", -- The estimated space (in GB) consumed by the entire protected database. (DB SIZE without L1 or ARCHIVE LOGS)
    RECOVERY_WINDOW_SPACE "RECOVERY_WINDOW_SPACE",    -- The estimated space (in GB) that is needed to meet the recovery window goal.
    SPACE_USAGE,        -- The amount of disk space (in GB) currently used by this protected database.
    DISK_RESERVED_SPACE,    -- The amount of disk space (in GB) reserved for the exclusive use of this database
CASE   
    WHEN    SPACE_USAGE >= SIZE_ESTIMATE
    THEN    100-(SPACE_USAGE * 100 / DISK_RESERVED_SPACE)
    WHEN    SIZE_ESTIMATE >= SPACE_USAGE
    THEN    100-(SIZE_ESTIMATE * 100 / DISK_RESERVED_SPACE)
    ELSE    100-NVL(SIZE_ESTIMATE,0) * 100 / DISK_RESERVED_SPACE
END    "RESERVED_SPACE_ALERT",
    KEEP_SPACE,        -- The space used to hold KEEP backups for the database. -- "Note" that this column is available only with ZDLRA software update 12.1.1.1.8 and later.
    CUMULATIVE_USAGE    -- The cumulative amount of disk space (in GB) allocated for all backups received for this database. (Always Increases)
From     RA_DATABASE RA_DB   
    LEFT OUTER JOIN RA_RESTORE_RANGE USING (DB_KEY)
)
Select * from storage_analysis;

Earlier in the series while analyzing Total Storage we had discussed to validate that all databases backup was at RWG while reviewing “RA Estimated Recovery Window Space” compared to “Used Space”.  Figure 5-2 below showcases:

1.  Preset RWG compared to the Current RWG attained
2. Max Disk Retention Window setting and overage above the RWG

This helps identify database that are falling behind the RWG and address any backup challenges (beyond the scope of the blog) that may need addressed. The output also identifies database which have “Max Disk Retention” set too high i.e. 2x Defaults so there can be corrected.

Figure 5-2 Database Storage – Recovery VS Retention

Figure 5-3 below narrows the above view to only list databases backups falling below the RWG (new backup still rolling up to RWG) or last backup received over 1 Day old which may need attention. Supporting sample query provided under “[[QUERY: DATABASE BACKUP Below RWG]]” and “[[QUERY: DATABASE BACKUP Below RWG - Detailed]]”.

Figure 5-3 Database Storage – Backups < RWG

 

[[QUERY: DATABASE BACKUP Below RWG]]

/* %%%%% IDENTIFY DATABASES BACKUPS falling Below RWG QUERY %%%%%% */
alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';
set    line 300 pagesize 200 trimspool on
col    DB_UNIQUE_NAME for a15
col    DBID for 999999999999
col    DELETING for a8
col    STORAGE_LOCATION for a17
col    RECOVERY_WINDOW_GOAL_DAYS for 9999999999 heading "--[RWG]--|RECOVERY|WINDOW|GOAL|(Days)"
col    CURRENT_RECOVERY_WINDOW for a16 heading "--[RWG]--|CURRENT|RECOVERY|WINDOW|GOAL|(Days)"
col    MAX_RETENTION_WINDOW for 9999999999 heading "--[RETW]--|MAX|RETENTION|WINDOW|(Days)"
col    RETENTION_ABOVE_RECOVERY for a17 heading "**REVIEW**|--[RETW VS RWG]--|'RETW' Above Or|Below 'RWG'|[* Check Best|Practices]|Days ('X' Times)"
col    RETENTION_ALERT for 9999999999.9 heading "**REVIEW**|RETENTION|(Allocation|'X' Times |Recovery Window|Goal)|[* Check Best|Practices]"
col    RECOVERY_WINDOW_SPACE for 999999.999 heading "--[RWG]--|ESTIMATED|RECOVERY|WINDOW SPACE|(GB)"
col    SIZE_ESTIMATE for 999999.999 heading "DB|ESTIMATE|SIZE|(GB)"
col    DISK_RESERVED_SPACE for 9999999.999 heading "DISK|RESERVED|SPACE|(GB)"
col    RESERVED_SPACE_ALERT for 999999.9 heading "**REVIEW**|RESERVED SPACE|(Allocated Compared|With MAX of|'DB ESTIMATE SIZE'|OR |'BACKUP SPACE_USAGE')|[* Check Best|Practices]|(%)"
col    SPACE_USAGE for 999999.999 heading "DB|BACKUP|SPACE_USAGE|(GB)"
col    KEEP_SPACE for 999999.999 heading  "KEEP_SPACE|(GB)"
col    CUMULATIVE_USAGE for 9999999999.999 heading "CUMULATIVE_USAGE|(GB)"
With storage_analysis AS
(
Select
    DB_UNIQUE_NAME,        -- The unique name of this protected database.
    DBID,            -- The DBID for this protected database.
    DELETING,        -- YES if this database is currently being deleted.
    STORAGE_LOCATION,    -- The name of the Recovery Appliance storage location used by this protected database.
    RECOVERY_WINDOW_GOAL    "RECOVERY_WINDOW_GOAL",
    HIGH_TIME        "HIGH_TIME",
    LOW_TIME        "LOW_TIME",
    EXTRACT(Day from RECOVERY_WINDOW_GOAL) "RECOVERY_WINDOW_GOAL_DAYS",
    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,' Days ',NULL)||
        EXTRACT(Hour From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,':',NULL)||
        ltrim(to_char(EXTRACT(Minute From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')),'09'))||NVL2(HIGH_TIME,' Hrs',NULL) "CURRENT_RECOVERY_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) "MAX_RETENTION_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) - EXTRACT(Day from RECOVERY_WINDOW_GOAL) || NVL2(MAX_RETENTION_WINDOW,' Days',NULL) ||
    NVL2(MAX_RETENTION_WINDOW,' (',NULL) || ltrim(to_char(EXTRACT(Day from MAX_RETENTION_WINDOW) / EXTRACT(Day from RECOVERY_WINDOW_GOAL),'999.9'))||NVL2(MAX_RETENTION_WINDOW,'x)',NULL)    "RETENTION_ABOVE_RECOVERY",
    SIZE_ESTIMATE    "SIZE_ESTIMATE", -- The estimated space (in GB) consumed by the entire protected database. (DB SIZE without L1 or ARCHIVE LOGS)
    RECOVERY_WINDOW_SPACE "RECOVERY_WINDOW_SPACE",    -- The estimated space (in GB) that is needed to meet the recovery window goal.
    SPACE_USAGE,        -- The amount of disk space (in GB) currently used by this protected database.
    DISK_RESERVED_SPACE,    -- The amount of disk space (in GB) reserved for the exclusive use of this database
CASE   
    WHEN    SPACE_USAGE >= SIZE_ESTIMATE
    THEN    100-(SPACE_USAGE * 100 / DISK_RESERVED_SPACE)
    WHEN    SIZE_ESTIMATE >= SPACE_USAGE
    THEN    100-(SIZE_ESTIMATE * 100 / DISK_RESERVED_SPACE)
    ELSE    100-NVL(SIZE_ESTIMATE,0) * 100 / DISK_RESERVED_SPACE
END    "RESERVED_SPACE_ALERT",
    KEEP_SPACE,        -- The space used to hold KEEP backups for the database. -- "Note" that this column is available only with ZDLRA software update 12.1.1.1.8 and later.
    CUMULATIVE_USAGE    -- The cumulative amount of disk space (in GB) allocated for all backups received for this database. (Always Increases)
From     RA_DATABASE RA_DB   
    LEFT OUTER JOIN RA_RESTORE_RANGE USING (DB_KEY)
)
Select   
    DB_UNIQUE_NAME,       
    DBID,           
    DELETING,       
    STORAGE_LOCATION,   
    RECOVERY_WINDOW_GOAL_DAYS,
    CURRENT_RECOVERY_WINDOW,
    MAX_RETENTION_WINDOW,
    RETENTION_ABOVE_RECOVERY,
    SIZE_ESTIMATE,
    RECOVERY_WINDOW_SPACE,   
    SPACE_USAGE,       
    DISK_RESERVED_SPACE,   
CASE   
    WHEN    SPACE_USAGE >= SIZE_ESTIMATE
    THEN    100-(SPACE_USAGE * 100 / DISK_RESERVED_SPACE)
    WHEN    SIZE_ESTIMATE >= SPACE_USAGE
    THEN    100-(SIZE_ESTIMATE * 100 / DISK_RESERVED_SPACE)
    ELSE    100-NVL(SIZE_ESTIMATE,0) * 100 / DISK_RESERVED_SPACE
END    "RESERVED_SPACE_ALERT",
    KEEP_SPACE,        -- The space used to hold KEEP backups for the database. -- "Note" that this column is available only with ZDLRA software update 12.1.1.1.8 and later.
    CUMULATIVE_USAGE
From storage_analysis
Where    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) < EXTRACT(Day from RECOVERY_WINDOW_GOAL)
And    HIGH_TIME < sysdate-1
;

[[QUERY: DATABASE BACKUP Below RWG - Detailed]]

/* %%% IDENTIFY DATABASES BACKUPS falling Below RWG - Detailed QUERY %%% */
alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';
set    line 300 pagesize 200 trimspool on
col    NZDL_ACTIVE for a8 heading "Redo|Shipping"
col    Required_RWG for 999999999 heading "--[RWG]--|RECOVERY|WINDOW|GOAL|(Days)"
col    Current_Days for 999999.99 heading "--[RWG]--|Recovery|Window|Current|(days)"
col    low_time for a22 heading "low_time|OLDEST|RECEIVED BACKUP"
col    high_time for a22 heading "high_time|LATEST|RECEIVED BACKUP"
col    Oldest_Backup for 9999999999 heading "OLDEST|RECOVERY POSSIBLE|(Days Old)"
col    Latest_Backup for 9999999999 heading "LATEST|RECOVERY POSSIBLE|(Days Old)"
select
    DB_UNIQUE_NAME,        -- The unique name of this protected database.
    DBID,            -- The DBID for this protected database.
    RA_DB.NZDL_ACTIVE    "NZDL_ACTIVE",
    low_time,
    high_time,
    sysdate,
    EXTRACT(Day from RECOVERY_WINDOW_GOAL)     "Required_RWG",
    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY'))  "Current_Days",
    round(sysdate-low_time,0)        "Oldest_Backup",
    round(sysdate-high_time,0)        "Latest_Backup"
From     RA_DATABASE RA_DB   
    JOIN RA_RESTORE_RANGE USING (DB_KEY)
Where    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) < EXTRACT(Day from RECOVERY_WINDOW_GOAL)
And    HIGH_TIME < sysdate-1
;

Figure 5-4 below narrows databases that have a Max Retention Window (RETW) greater than 2x RWG. The query can be tweaked to set this to “n”Days above RWG i.e. an example of RWG @ 7 Days and RETW @ 10 Days. Sample query under “[[QUERY: DATABASE BACKUP Retention Window > 2x RWG]]”.

Figure 5-4 Database Storage – RETENTION > x Days of RWG

 

[[QUERY: DATABASE BACKUP Retention window > 2x RWG]]

/* %%%%% IDENTIFY DATABASES BACKUP RETENTION > 2x RWG QUERY %%%% */
alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';
set    line 300 pagesize 200 trimspool on
col    DB_UNIQUE_NAME for a15
col    DBID for 999999999999
col    DELETING for a8
col    STORAGE_LOCATION for a17
col    RECOVERY_WINDOW_GOAL for 9999999999 heading "--[RWG]--|RECOVERY|WINDOW|GOAL|(Days)"
col    CURRENT_RECOVERY_WINDOW for a16 heading "--[RWG]--|CURRENT|RECOVERY|WINDOW|GOAL|(Days)"
col    MAX_RETENTION_WINDOW for 9999999999 heading "--[RETW]--|MAX|RETENTION|WINDOW|(Days)"
col    RETENTION_ABOVE_RECOVERY for a17 heading "**REVIEW**|--[RETW VS RWG]--|'RETW' Above Or|Below 'RWG'|[* Check Best|Practices]|Days ('X' Times)"
col    RETENTION_ALERT for 9999999999.9 heading "**REVIEW**|RETENTION|(Allocation|'X' Times |Recovery Window|Goal)|[* Check Best|Practices]"
col    RECOVERY_WINDOW_SPACE for 999999.999 heading "--[RWG]--|ESTIMATED|RECOVERY|WINDOW SPACE|(GB)"
col    SIZE_ESTIMATE for 999999.999 heading "DB|ESTIMATE|SIZE|(GB)"
col    DISK_RESERVED_SPACE for 9999999.999 heading "DISK|RESERVED|SPACE|(GB)"
col    RESERVED_SPACE_ALERT for 999999.9 heading "**REVIEW**|RESERVED SPACE|(Allocated Compared|With MAX of|'DB ESTIMATE SIZE'|OR |'BACKUP SPACE_USAGE')|[* Check Best|Practices]|(%)"
col    SPACE_USAGE for 999999.999 heading "DB|BACKUP|SPACE_USAGE|(GB)"
col    KEEP_SPACE for 999999.999 heading  "KEEP_SPACE|(GB)"
col    CUMULATIVE_USAGE for 9999999999.999 heading "CUMULATIVE_USAGE|(GB)"
With storage_analysis AS
(
Select
    DB_UNIQUE_NAME,        -- The unique name of this protected database.
    DBID,            -- The DBID for this protected database.
    DELETING,        -- YES if this database is currently being deleted.
    STORAGE_LOCATION,    -- The name of the Recovery Appliance storage location used by this protected database.
    EXTRACT(Day from RECOVERY_WINDOW_GOAL) "RECOVERY_WINDOW_GOAL",
    EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,' Days ',NULL)||
        EXTRACT(Hour From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,':',NULL)||
        ltrim(to_char(EXTRACT(Minute From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')),'09'))||NVL2(HIGH_TIME,' Hrs',NULL) "CURRENT_RECOVERY_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) "MAX_RETENTION_WINDOW",
    EXTRACT(Day from MAX_RETENTION_WINDOW) - EXTRACT(Day from RECOVERY_WINDOW_GOAL) || NVL2(MAX_RETENTION_WINDOW,' Days',NULL) ||
    NVL2(MAX_RETENTION_WINDOW,' (',NULL) || ltrim(to_char(EXTRACT(Day from MAX_RETENTION_WINDOW) / EXTRACT(Day from RECOVERY_WINDOW_GOAL),'999.9'))||NVL2(MAX_RETENTION_WINDOW,'x)',NULL)    "RETENTION_ABOVE_RECOVERY",
    SIZE_ESTIMATE    "SIZE_ESTIMATE", -- The estimated space (in GB) consumed by the entire protected database. (DB SIZE without L1 or ARCHIVE LOGS)
    RECOVERY_WINDOW_SPACE "RECOVERY_WINDOW_SPACE",    -- The estimated space (in GB) that is needed to meet the recovery window goal.
    SPACE_USAGE,        -- The amount of disk space (in GB) currently used by this protected database.
    DISK_RESERVED_SPACE,    -- The amount of disk space (in GB) reserved for the exclusive use of this database
CASE   
    WHEN    SPACE_USAGE >= SIZE_ESTIMATE
    THEN    100-(SPACE_USAGE * 100 / DISK_RESERVED_SPACE)
    WHEN    SIZE_ESTIMATE >= SPACE_USAGE
    THEN    100-(SIZE_ESTIMATE * 100 / DISK_RESERVED_SPACE)
    ELSE    100-NVL(SIZE_ESTIMATE,0) * 100 / DISK_RESERVED_SPACE
END    "RESERVED_SPACE_ALERT",
    KEEP_SPACE,        -- The space used to hold KEEP backups for the database. -- "Note" that this column is available only with ZDLRA software update 12.1.1.1.8 and later.
    CUMULATIVE_USAGE    -- The cumulative amount of disk space (in GB) allocated for all backups received for this database. (Always Increases)
From     RA_DATABASE RA_DB   
    LEFT OUTER JOIN RA_RESTORE_RANGE USING (DB_KEY)
)
Select * from storage_analysis
Where    MAX_RETENTION_WINDOW / RECOVERY_WINDOW_GOAL > 2    -- List All Databases having RETENTION Days > 2x
;

Next Figure 5-5 in the checklist is the Reserved Space check. In this case the query compares the RA “Estimated Recovery Space”, Used and reserved space for individual databases. Same rules as discussed earlier (RA Storage Summary Overview) apply when comparing values these for each database. Sample Query under “[[QUERY: DATABASE BACKUP Analysis]]”

Figure 5-5 Database Storage – Used VS Reserved

 

Next Figure 5-6 in the checklist is the Reserved Space check. In this case the query compared the reserved space to the RA “Estimated Recovery Space”. As discussed Reserved Space must always be same or higher (Recommended) than RWG i.e. Reserved Space is set too high for one and too low for another. Sample query under “[[QUERY: DATABASE BACKUP Reserved Space > "n"% Over Estimated RWG OR < Estimated RWG]]”.

Figure 5-6 Database Storage – Reserved > “n”% OR < RWG

 

 
[[QUERY: DATABASE BACKUP Reserved Space > "n"% Over Estimated RWG OR < Estimated RWG]]

/* %%%% DATABASES BACKUPS RESERVED SPACE > "n"% (USER ENTERED) of Estimated RWG OR < RWG QUERY %%% */
accept pct_reserve_over_rwg number FORMAT '999' DEFAULT '0' PROMPT 'ENTER RESERVED SPACE for Database Storage as PERENT (i.e. 20 for 20%) :: '

alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';
set    line 300 pagesize 200 trimspool on
col    DB_UNIQUE_NAME for a15
col    DBID for 999999999999
col    DELETING for a8
col    STORAGE_LOCATION for a17
col    RECOVERY_WINDOW_GOAL for 9999999999 heading "--[RWG]--|RECOVERY|WINDOW|GOAL|(Days)"
col    CURRENT_RECOVERY_WINDOW for a16 heading "--[RWG]--|CURRENT|RECOVERY|WINDOW|GOAL|(Days)"
col    MAX_RETENTION_WINDOW for 9999999999 heading "--[RETW]--|MAX|RETENTION|WINDOW|(Days)"
col    RETENTION_ABOVE_RECOVERY for a17 heading "**REVIEW**|--[RETW VS RWG]--|'RETW' Above Or|Below 'RWG'|[* Check Best|Practices]|Days ('X' Times)"
col    RETENTION_ALERT for 9999999999.9 heading "**REVIEW**|RETENTION|(Allocation|'X' Times |Recovery Window|Goal)|[* Check Best|Practices]"
col    RECOVERY_WINDOW_SPACE for 999999.999 heading "--[RWG]--|ESTIMATED|RECOVERY|WINDOW SPACE|(GB)"
col    SIZE_ESTIMATE for 999999.999 heading "DB|ESTIMATE|SIZE|(GB)"
col    DISK_RESERVED_SPACE for 9999999.999 heading "DISK|RESERVED|SPACE|(GB)"
col    RESERVED_SPACE_ALERT for 999999.9 heading "**REVIEW**|RESERVED SPACE|(Allocated Compared|With MAX of|'DB ESTIMATE SIZE'|OR |'BACKUP SPACE_USAGE')|[* Check Best|Practices]|(%)"
col    SPACE_USAGE for 999999.999 heading "DB|BACKUP|SPACE_USAGE|(GB)"
col    KEEP_SPACE for 999999.999 heading  "KEEP_SPACE|(GB)"
col    CUMULATIVE_USAGE for 9999999999.999 heading "CUMULATIVE_USAGE|(GB)"

With storage_analysis AS
(
Select
DB_UNIQUE_NAME,        -- The unique name of this protected database.
DBID,            -- The DBID for this protected database.
DELETING,        -- YES if this database is currently being deleted.
STORAGE_LOCATION,    -- The name of the Recovery Appliance storage location used by this protected database.
EXTRACT(Day from RECOVERY_WINDOW_GOAL) "RECOVERY_WINDOW_GOAL",
EXTRACT(Day From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,' Days ',NULL)||
EXTRACT(Hour From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')) ||NVL2(HIGH_TIME,':',NULL)||
ltrim(to_char(EXTRACT(Minute From NUMTODSINTERVAL(HIGH_TIME-LOW_TIME, 'DAY')),'09'))||NVL2(HIGH_TIME,' Hrs',NULL) "CURRENT_RECOVERY_WINDOW",
EXTRACT(Day from MAX_RETENTION_WINDOW) "MAX_RETENTION_WINDOW",
EXTRACT(Day from MAX_RETENTION_WINDOW) - EXTRACT(Day from RECOVERY_WINDOW_GOAL) || NVL2(MAX_RETENTION_WINDOW,' Days',NULL) ||
NVL2(MAX_RETENTION_WINDOW,' (',NULL) || ltrim(to_char(EXTRACT(Day from MAX_RETENTION_WINDOW) / EXTRACT(Day from RECOVERY_WINDOW_GOAL),'999.9'))||NVL2(MAX_RETENTION_WINDOW,'x)',NULL)    "RETENTION_ABOVE_RECOVERY",
SIZE_ESTIMATE    "SIZE_ESTIMATE", -- The estimated space (in GB) consumed by the entire protected database. (DB SIZE without L1 or ARCHIVE LOGS)
RECOVERY_WINDOW_SPACE "RECOVERY_WINDOW_SPACE",    -- The estimated space (in GB) that is needed to meet the recovery window goal.
SPACE_USAGE,        -- The amount of disk space (in GB) currently used by this protected database.
DISK_RESERVED_SPACE,    -- The amount of disk space (in GB) reserved for the exclusive use of this database
CASE 
--    WHEN SPACE_USAGE >= SIZE_ESTIMATE
--    THEN 100-(SPACE_USAGE * 100 / DISK_RESERVED_SPACE)
--    WHEN SIZE_ESTIMATE >= SPACE_USAGE
--    THEN 100-(SIZE_ESTIMATE * 100 / DISK_RESERVED_SPACE)
    WHEN DISK_RESERVED_SPACE >= RECOVERY_WINDOW_SPACE
    THEN 100-(RECOVERY_WINDOW_SPACE * 100 / DISK_RESERVED_SPACE)
    WHEN RECOVERY_WINDOW_SPACE >= DISK_RESERVED_SPACE
    THEN 100-(RECOVERY_WINDOW_SPACE * 100 / DISK_RESERVED_SPACE)
    ELSE 100-NVL(SIZE_ESTIMATE,0) * 100 / DISK_RESERVED_SPACE
END    "RESERVED_SPACE_ALERT",
KEEP_SPACE,        -- The space used to hold KEEP backups for the database. -- "Note" that this column is available only with ZDLRA software update 12.1.1.1.8 and later.
CUMULATIVE_USAGE    -- The cumulative amount of disk space (in GB) allocated for all backups received for this database. (Always Increases)
From     RA_DATABASE RA_DB 
LEFT OUTER JOIN RA_RESTORE_RANGE USING (DB_KEY)
)
Select * from storage_analysis
Where    RESERVED_SPACE_ALERT > &pct_reserve_over_rwg OR RESERVED_SPACE_ALERT < 0                -- List All Databases having RESERVED SPACE > 20% OR < RWG Space
;

Next Figure 5-7 & Figure 5-8 show one use case on the impact of not setting or reviewing Reserved Space correctly for some types of backups i.e. Backups using the KEEP UNTIL clause that fall outside the traditional RA backup.

Figure 5-7 Database Storage – KEEP SPACE

 

Figure 5-8 Database Storage – Effect of Reserved Space Set on KEEP BACKUP

 

                          NOTE:   Refer to the ZDLRA Administrative Guide for more scenarios.                

Summary:

It can be a lot to cover in a single blog but hopefully this blog has been able to point you in the right direction.

To bring out journey to a conclusion, we will cover storage management implementation/automation and workload balancing aspects on RA in the next blog in this series.