In this Fourth part of a series of blogs on ZDLRA/RA backup storage/space, I am going to share some of my insights into analyzing Total 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 Total Storage On RA:

Now that we have reviewed Backup Storage within the RA, let us review data related to backup storage as captured in the RA Views, that can help drive a sizing analysis. Figure 4-1 below shows summary of total databases that have backups on the ZDLRA and the ones that do not. The databases not having backups on RA can either be Old Databases (CREATION_TIME) that need RA cleanup (DBMS_RA.DELETE_DB) or databases where backups did not get initiated to RA as seen in the below query output. This helps make sure we have up to date details of all databases being backed up, before we start looking at other data points. Queries for below can be found under “[[QUERY: STORAGE SUMMARY ANALYSIS]]” and “[[QUERY: DATABASE WITH NO BACKUPS]]”

Figure 4-1 Reviewing Databases Backups

Having validated the databases as above and taken necessary action, to enable backups to the RA as in the above use case. Next would be to check backups for all database are at RWG Figure 5-3, as this helps understand the relationship and values between the columns in Figure 4-2.

Now we can review the overall storage settings. Figure 4-2 below showcases:

TOTAL_SPACE - Total usable space on the RA
ESTIMATED RECOVERY WINDOW SPACE - What RA estimates you will need for current RWG
USED_SPACE - Space Currently in Use
DISK RESERVED SPACE - Space Allocated to meet the RWG

While many comparisons can be derived for Figure 4-2, we will keep it simple for this blog:

Is my “Total Reserved Space” Below “Total Space
How does RA Estimated Space compare to Used Space? If higher what is consuming more used space i.e. Max Disk Retention Window, KEEP BACKUPS etc.? Do I need to revisit these backup requirements?
Is my reserved Space set too high over the RA Estimated Space or Used Space (check 2 above)?

Figure 4-2 Storage comparison

In Figure 4-3 we look at the Free Space requirements that include total Free Space available on the RA and Total Freespace Goal needed for incoming backups as discussed earlier in “Attributes Driving Storage (1of 6)”.

Figure 4-3 Free Space

[[QUERY: STORAGE SUMMARY ANALYSIS]]

/* %%%%%%%%%%% STORAGE SUMMARY ANALYSIS QUERY %%%%%%%%%%% */

set line 200

col total_databases_added format 9999999999 Heading "TOTAL|DATABASES|ADDED To RA"

col total_databases_with_backups format 9999999999 Heading "TOTAL|DATABASES|Having Backups|On RA"

col STORAGE_LOCATION format a20

col total_space format 999999999.999 Heading "TOTAL_SPACE|(GB)"

col freespace format a20 Heading "FREESPACE|(GB)|[[ % of |Total Space ]]"

col used_space format a20 Heading "USED_SPACE|(GB)|[[ % of |Total Space ]]"

col total_disk_reserved_space format a20 Heading "TOTAL DISK|RESERVED SPACE|(GB)|[[ % of|Total Used Space ]]"

col freespace_goal format 999999999.999 Heading "FREESPACE_GOAL|(GB)"

col SYSTEM_PURGING_SPACE format 999999999.999 Heading "SYSTEM_PURGING_SPACE|(GB)"

col total_recovery_window_space format a20 Heading "RA ESTIMATED|RECOVERY WINDOW|SPACE (GB)|[[ % of |Total Used Space ]]"

--col PCT_FREE for 999.9 heading "FREE|SPACE|(%)"

--col PCT_USED for 999.9 heading "USED|SPACE|(%)"

select

total_databases_added, -- Total Databases Added To ZDLRA

total_databases_with_backups, -- Total Databases Having Backup On ZDLRA

name "STORAGE_LOCATION", -- The Recovery Appliance storage location name.

total_space, -- The maximum amount of storage (in GB) that the Recovery Appliance storage location can use for backup data.

round(used_space,3)||' ['||round( (used_space*100/total_space) , 2)||'%]' used_space, -- The amount of space (in GB) currently used in the Recovery Appliance storage location. AND Used Space %

round(ra.total_recovery_window_space,3)||' ['||round( ((total_recovery_window_space*100)/used_space), 2)||'%]' total_recovery_window_space, -- The TOTAL/SUM of amount of estimated disk space (in GB) needed to meet Rwcovery Window Goal of ALL Databases

round(ra.total_disk_reserved_space,3)||' ['||round( ((total_disk_reserved_space*100)/used_space), 2)||'%]' total_disk_reserved_space, -- The TOTAL/SUM of amount of disk space (in GB) reserved for the exclusive use of ALL Databases

round(freespace,3)||' ['||round( ((freespace*100)/total_space), 2)||'%]' freespace, -- the amount of space (in gb) available for immediate use. AND Free Space %

freespace_goal, -- *** the expected free space requirement (in gb) based on usage history. purges may occur to meet this goal.

system_purging_space -- the amount of space (in gb) reserved for purging operations. Must be always free.

from ra_storage_location,

(

Select

count(DB_UNIQUE_NAME) total_databases_added,

sum(disk_reserved_space) total_disk_reserved_space,

sum(recovery_window_space) total_recovery_window_space

From ra_database

) ra,

(

Select

count(DB_UNIQUE_NAME) total_databases_with_backups

From ra_database_storage_usage

) rau

;

[[QUERY: DATABASES WITH NO BACKUP]]

/* %%%%%%%%%%%%%% DB WITH NO BACKUPS QUERY %%%%%%%%%%%%% */

-- --------------- QUERY : DB ADDED BUT NOT BACKED UP

alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';

col CREATION_TIME for a35

select

ra.DB_UNIQUE_NAME "DB_ADDED", ra.CREATION_TIME, rau.DB_UNIQUE_NAME "DB_BACKUP_UP"

from ra_database ra

FULL OUTER JOIN ra_database_storage_usage rau using(DB_KEY)

;

Finally, in Figure 4-4 a sample query to help generate alerts bases on some thresholds. It is however recommended as a best practice to use Enterprise Manager Cloud Control (EMCC) for all alerting and management of ZDLRA/RA. The sample query provided can be found under “[[QUERY: STORAGE ALERT]]”.

Figure 4-4 Setting up thresholds for Alerts.

[[QUERY: STORAGE ALERT QUERY]]

/* %%%%%%%%%%%%%%%% STORAGE ALERT QUERY %%%%%%%%%%%%%% */

col current_date format a30 new_value current_date noprint;

select sysdate current_date from dual;

col current_host format a30 new_value current_host noprint;

select host_name current_host from v$instance;

col description format a30 new_value ra_description noprint

select description from ra_server;

set line 200 heading off feedback off

alter session set nls_date_format='dd-MON-YYYY HH24:MI:SS';

/* *** SET THRESHOLDS *** */

define USED_SPACE_WARNING=80;

define USED_SPACE_CRITICAL=90;

define FREESPACE_WARNING=30;

define FREESPACE_CRITICAL=10;

define DISK_RESERVED_SPACE_WARNING=20;

define DISK_RESERVED_SPACE_CRITICAL=25;

select

'TOTAL_DATABASES_ADDED ='||chr(9)||chr(9)||chr(9)||total_databases_added ||chr(10)||

'TOTAL_DATABASES_HAVING_BACKUPS ='||chr(9)||total_databases_with_backups ||chr(10)||

'STORAGE_LOCATION ='||chr(9)||chr(9)||chr(9)||name ||chr(10)||

'TOTAL_SPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(total_space,3)||' (GB)' ||chr(10)||

'TOTAL_RECOVERY_WINDOW_SPACE ='||chr(9)||chr(9)||round(ra.total_recovery_window_space,3)||' (GB)'||

' ['||round( ((total_recovery_window_space*100)/used_space), 2)||'%]' ||chr(10)||

'KEEP_SPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(keep_space,3)||' (GB)'||chr(10)||

CASE -- VALIDATE for THRESHOLDS

WHEN round( (used_space*100/total_space) , 2) >= &USED_SPACE_CRITICAL

THEN

'USED_SPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(used_space,3)||' (GB)'||

' ['||round( (used_space*100/total_space) , 2)||'%]' ||chr(9)||chr(9)||'*** USED_SPACE THRESHOLD CRITICAL > &USED_SPACE_CRITICAL %'

WHEN round( (used_space*100/total_space) , 2) >= &USED_SPACE_WARNING

THEN

'USED_SPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(used_space,3)||' (GB)'||

' ['||round( (used_space*100/total_space) , 2)||'%]' ||chr(9)||chr(9)||'*** USED_SPACE THRESHOLD WARNING > &USED_SPACE_WARNING %'

ELSE

'USED_SPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(used_space,3)||' (GB)'||

' ['||round( (used_space*100/total_space) , 2)||'%]'

END ||chr(10)||

CASE -- VALIDATE for THRESHOLDS

WHEN round( ((total_disk_reserved_space*100)/used_space), 2) >= &DISK_RESERVED_SPACE_CRITICAL

THEN

'TOTAL_DISK_RESERVED_SPACE ='||chr(9)||chr(9)||round(ra.total_disk_reserved_space,3)||' (GB)'||

' ['||round( ((total_disk_reserved_space*100)/used_space), 2)||'%]' ||chr(9)||chr(9)||'*** DISK_RESERVED_SPACE THRESHOLD CRITICAL > &DISK_RESERVED_SPACE_CRITICAL %'

WHEN round( ((total_disk_reserved_space*100)/used_space), 2) > &DISK_RESERVED_SPACE_WARNING

THEN

'TOTAL_DISK_RESERVED_SPACE ='||chr(9)||chr(9)||round(ra.total_disk_reserved_space,3)||' (GB)'||

' ['||round( ((total_disk_reserved_space*100)/used_space), 2)||'%]' ||chr(9)||chr(9)||'*** DISK_RESERVED_SPACE THRESHOLD WARNING > &DISK_RESERVED_SPACE_WARNING %'

ELSE

'TOTAL_DISK_RESERVED_SPACE ='||chr(9)||chr(9)||round(ra.total_disk_reserved_space,3)||' (GB)'||

' ['||round( ((total_disk_reserved_space*100)/used_space), 2)||'%]'

END ||chr(10)||

CASE -- VALIDATE for THRESHOLDS

WHEN round( ((freespace*100)/total_space), 2) <= &FREESPACE_CRITICAL

THEN

'FREESPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(freespace,3)||' (GB)'||

' ['||round( ((freespace*100)/total_space), 2)||'%]' ||chr(9)||chr(9)||'*** FREESPACE THRESHOLD CRITICAL > &FREESPACE_CRITICAL %'

WHEN round( ((freespace*100)/total_space), 2) <= &FREESPACE_WARNING

THEN

'FREESPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(freespace,3)||' (GB)'||

' ['||round( ((freespace*100)/total_space), 2)||'%]' ||chr(9)||chr(9)||'*** FREESPACE THRESHOLD WARNING > &FREESPACE_WARNING %'

ELSE

'FREESPACE ='||chr(9)||chr(9)||chr(9)||chr(9)||round(freespace,3)||' (GB)'||

' ['||round( ((freespace*100)/total_space), 2)||'%]'

END ||chr(10)||

'FREESPACE_GOAL ='||chr(9)||chr(9)||chr(9)||round(freespace_goal,3)||' (GB)' ||chr(10)||

'SYSTEM_PURGING_SPACE ='||chr(9)||chr(9)||chr(9)||system_purging_space||' (GB)'

from ra_storage_location,

(

Select

count(DB_UNIQUE_NAME) total_databases_added,

sum(disk_reserved_space) total_disk_reserved_space,

sum(recovery_window_space) total_recovery_window_space,

sum(keep_space) keep_space

From ra_database

) ra,

(

Select

count(DB_UNIQUE_NAME) total_databases_with_backups

From ra_database_storage_usage

) rau

;

prompt '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

prompt '%%%%%%%%% Recovery Appliance BACKUP STORAGE %%%%%%%%%%%%';

prompt '%%%% DATE :: &current_date

prompt '%%%% HOST :: &current_host

prompt '%%%% Recovery Appliance :: &ra_description

prompt '%%%%%%%%%%%%%%%%%%%% DESCRIPTION %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

prompt '% TOTAL_DATABASES_ADDED -- Databases Added to RA %';

prompt '% TOTAL_DATABASES_HAVING_BACKUPS -- Databases having backups on RA %';

prompt '% STORAGE_LOCATION -- Storage Location Identifier on RA %';

prompt '% TOTAL_SPACE -- Total Usable Storge Capacity on this RA %';

prompt '% TOTAL_RECOVERY_WINDOW_SPACE -- RA Estimates Spcce Needed for RWG %';

prompt '% KEEP SPACE -- Storage used by backups with KEEP Option %';

prompt '% USED_SPACE -- Total Storage Used on this RA %';

prompt '% TOTAL_DISK_RESERVED_SPACE -- Total Storage Reserved for database backups %';

prompt '% FREESPACE -- Free Storage Available for Use %';

prompt '% FREESPACE_GOAL -- Storage required for expected current/next backups %';

prompt '% SYSTEM_PURGING_SPACE -- System Processing Storage %';

prompt '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

set verify off

set verify on

prompt '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

prompt '%%%%%%%%%%%%%%%%%%%%%% END %%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

prompt '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%';

set line 200 heading on feedback on

Summary:

Now that we have reviewed the Overall Backup Storage Summary withing the RA hopefully this helps identify areas to review capacity before we do a deeper dive on better tuning storage needs in terms of storage usage, allocation etc. can be done. Out next Part in this series will cover some of these aspects.

Search This Blog

Exchanging Snippets and Ideas on Oracle and Other Technologies.

Managing Space - Recovery Appliance / ZDLRA / RA - 4 of 6 - Analyzing Total Storage

Content [Click On Link]:

Analyzing Total Storage On RA:

Figure 4-1 Reviewing Databases Backups

Figure 4-2 Storage comparison

Figure 4-3 Free Space

Figure 4-4 Setting up thresholds for Alerts.

Summary:

Comments

Post a Comment

Popular posts from this blog

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

Oracle RAC - Visualizing "crsstat"