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]:



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.


Comments

  1. The best Article that I have never seen before with useful content and very informative.Thanks for sharing info. MS-300T04: Enabling Office 365 Workloads for Collaboration

    ReplyDelete

Post a Comment

Please let me know if this blog has been helpful.

Popular posts from this blog

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

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