Managing Space - Recovery Appliance ZDLRA / RA - 6 of 6 - Managing Backup Storage & Workloads

In this Sixth and Last Part of a series of blogs on ZDLRA/RA backup storage/space, I am going to share some of my insights into, how to setup Attribute Thresholds on storage to meet your Recovery needs for Individual Database Backup 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)

Managing Storage Attributes:

With proper capacity planning there should not be a need to engage in storage micromanagement, however as business drive backup workloads there is little control on the volume of incoming backups and it becomes a good practice for an RA Admin to be proactive to ensure the RA is provisioned to meet every recovery scenario for the Enterprise Oracle Databases. 

Managing database backup storage needs can be handles through Attributes, which can be set both from Enterprise Manager cloud Control (EMCC) and CLI/API as seen below in Figure 6-1 and Figure 6-2. For more details refer to Oracle ZDLRA Admin Guide. Please refer to the First Blog in this series for some defaults and best setup cases for attributes.

Figure 6-1 Storage Allocation Setting Thresholds

Figure 6-2 Storage Allocation Setting Thresholds

An example of setting Reserved Space to above the RWG as discussed in earlier blogs in this series is provided below in Figure 6-3 and can be used to setup some form of automation.Sample Query below under "[[QUERY: DATABASE - Update Reserved Space]]"

Figure 6-3 Setting Reserved Space

[[QUERY: DATABASE - Update Reserved Space]]

/* %%%%%%%%% DATABASES - UPDATE RESERVED SPACE 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 HH:MI:SS PM';

set  line 1000 pagesize 500 trimspool on heading off feedback off

col  DB_UNIQUE_NAME for a15

col  DBID for 999999999

col  DELETING for a8

col  STORAGE_LOCATION for a17

col  RESERVED_SPACE_ALERT for 999999.9 heading "**REVIEW**|RESERVED SPACE|('%' Storage Excess|Over MAX of|Extimate OR Used)|[* Check Best|Practices]"

col  RESET_RESERVED_SPACE for a200

With storage_analysis AS

(

Select

--  DB_UNIQUE_NAME,

--  DBID,

--  DELETING,

--  STORAGE_LOCATION,

  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",

  round(NVL(SIZE_ESTIMATE,0),1)  DB_SIZE_ESTIMATE,

  q'[Prompt *** RESETTING RESERVED SPACE On ]'||sysdate||q'[ For ::]'||

  q'[ DB_UNIQUE_NAME=]'||DB_UNIQUE_NAME   ||q'[ ]'||

  q'[ DBID=]'||DBID    ||q'[ ]'||

  q'[ STORAGE_LOCATION=]'||STORAGE_LOCATION  ||q'[ ]'||

  q'[ DB_SIZE_ESTIMATE=]'||round(SIZE_ESTIMATE*1000,3)||'M'  ||q'[ ]'||

  q'[ BACKUP_SPACE_USAGE=]'||round(SPACE_USAGE*1000,3)||'M'  ||q'[ ]'||

  q'[ CURRENT_DISK_RESERVED_SPACE =]'||round(DISK_RESERVED_SPACE,3)||q'[ (GB)]'  ||q'[ ]'||

  q'[ *** Calculating NEW_DISK_RESERVED_SPACE Using = MAX(DB_SIZE_ESTIMATE,BACKUP_SPACE_USAGE)+]'||&pct_reserve_over_rwg||q'[% ::>> ]'||chr(10)||

  q'[exec DBMS_RA.UPDATE_DB(db_unique_name => ']'||DB_UNIQUE_NAME||q'[',reserved_space => ']'||

  CASE 

  WHEN  SPACE_USAGE >= SIZE_ESTIMATE

  THEN  round((SPACE_USAGE + (&pct_reserve_over_rwg * SPACE_USAGE) / 100),3) * 1000

  WHEN  SIZE_ESTIMATE >= SPACE_USAGE

  THEN  round((SIZE_ESTIMATE + (&pct_reserve_over_rwg * SIZE_ESTIMATE) / 100),3) * 1000

  ELSE  NULL

  END  ||

  q'[M)';]'  "RESET_RESERVED_SPACE"

From   RA_DATABASE RA_DB 

  LEFT OUTER JOIN RA_RESTORE_RANGE USING (DB_KEY)

)

Select  RESET_RESERVED_SPACE

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

And  DB_SIZE_ESTIMATE > 0

;

set  line 200 trimspool on heading on feedback on

Planning Storage Workloads:

Now that we have walked through the storage from Total Storage to Per-Database Storage and some aspects to keep in mind and fix, it also help in understanding the overall Storage workload and better balance workload by eliminating pockets of inactivity with an even distribution of backup schedules. Figure 7-1 show the uneven impact of workloads from deletes/purges (discussed earlier) to backups. High number of Deletes of backup or large number of databases backed up during the same overlapping time are prime examples of inconsistent workloads. Sample query under “[[QUERY: DATABASE BACKUP Storage Workloads]]” can help identify and create a more balanced workload removing long pockets of inactivity.

Figure 7-1 Evaluating Storage Workloads

 

[[QUERY: DATABASE BACKUP Storage Workloads]]

/* %%%%%%%%% DATABASE BACKUP Storage Workload QUERY %%%%%%%%%% */
 

set  line 300 pagesize 200 trimspool on

col  NAME for a15

col  LAST_UPDATED for a36

col  USAGE heading "USAGE|(GB)"

col  OVER_PREVIOUS_PERIOD for 999999.999 heading "ALLOCATION|OVER|PREVIOUS PERIOD|(GB)"

Select

  NAME,  SL_KEY,   SLOT,  round(USAGE,3) "USAGE",  LAST_UPDATED,

  USAGE - NVL(Lag(USAGE) Over (Order by SLOT),USAGE) OVER_PREVIOUS_PERIOD

From  RA_STORAGE_HISTOGRAM

Where  usage != 0

Order  By LAST_UPDATED

;

Conclusion:

Hope these blogs have been helpful and do provide your constructive comments to make this a greater experience for all.