Search This Blog

Tuesday, July 23, 2019

We're using up storage too fast! Where is all the growth coming from?

Technical challenges aside, its difficult to navigate the politics of being an Oracle DBA.  In today's world, data warehouses/data lakes are expected to ingest massive amounts of data and still perform well.  Ok, no problem...this can be done...but when the storage utilization spikes, the storage guys (who write the checks for the expensive storage arrays the data is on)...they want to know if/why they need to buy another storage array.  DBA's are expected to perform storage planning, but are rarely informed when the data owners increase utilization until after the fact.

This question comes up pretty often...why is the database growing so fast?  Coming in to a new environment I'm not familiar with...I have a bunch of scripts I usually run to get a profile of the databases.  Usually a non-technical manager wants to see a summary, so if this data can be presented graphically as part of a presentation, communication flows better, and it looks more professional.

All the storage utilization data is being kept in the history tables...its just a matter of pulling it .  This is a dynamic query that creates a query...the results of that can be put into Excel and graphed.  Once the results are graphed, things pretty clearly jump out at you.  So...

1. Run this query (if you aren't using 8k block size, change 8192 below to w/e you're using):

select stmnt from (
select 1 o1, 0 o2, 'with stacked as
select snap_id, tablespace_id, trunc(to_date(rtime,''MM/DD/YYYY HH24:MI:SS'')) rtime, (tablespace_size*8192)/1024/1024/1024 ts_size
from dba_hist_tbspc_space_usage tdu
where snap_id in (select max(snap_id) from dba_hist_tbspc_space_usage tdu2
                    where trunc(to_date(tdu.rtime,''MM/DD/YYYY HH24:MI:SS''))=trunc(to_date(tdu2.rtime,''MM/DD/YYYY HH24:MI:SS'')))
select distinct rtime,'  stmnt from dual
union all
select 2, TS#,'(select ts_size from stacked s2 where s2.tablespace_id='||TS#||' and s1.rtime=s2.rtime and rownum=1) as "'||NAME||'"'||decode((tsm-ts#),0,null,',') from v$tablespace ts1 , (select max(TS#) tsm from v$tablespace) ts2
union all
select 3,0,'from stacked s1
 where (select ts_size from stacked s2 where s2.tablespace_id=0 and s1.rtime=s2.rtime) is not null;' from dual
 ) order by o1,o2;

2. Take the output of that query, and run it.  It'll create a very wide data set with daily storage size data for each tablespace.

3. Cut/Paste and put that data into Excel.

4. Select the data columns in Excel and at the top, select insert, then on the "Charts" group, select your favorite chart type..."2-D, stacked area" works well for this data, IMHO.

You should now see something like this (GB on the Y axis): now its clear where the acceleration in growth came from.  The relatively new, orange "troublemaker_data" tablespace had a ~10TB growth spurt until May 1st, where it looks like it leveled off.  This empowers you to go after the data owners and ask them if this will continue, and you can tell the storage guys there was growth but it settled down and they don't need to panic and buy more storage.

I hope this helps you with your storage planning!

Monday, July 22, 2019

Watch out for the latest 12.1 PSU/proactive patches!

We all have horror stories about "That one time, when we restored and recovered the database, and the CIO was calling out managers every few minutes, reminding them the company is at a stand-still...this is costing us $XXX,XXX per minute!!!!!"  Now imagine after a few hours in to your restore you have to say, "We hit a bug. We have to open an SR, get a patch and start over."  Discovering you can't successfully restore/recover when the heat is on due to a bug would make a great horror movie...nothing is more scary.  Luckily I hit this bug during a restore I had some time to deal with it...but I thought I should let everybody know about it.

I know a lot of people are still running  The Jan and Apr 2019 PSU (the two latest PSU's) introduce a bug (25107334) that cause you to be unable to open the database.  I hit this 3 hours into a restore:

ORA-65108: invalid use of a cursor belonging to another container

The work around is that you manually put the $SEED database into read-only mode.  Simple enough...but if you're restoring/recovering a database, that's not a option.  There's now a patch (25107334), that solves the issue.  While waiting for MOS to come up with a solution, I used a 19.3 home to restore the database, and it worked that's an optional work-around too.

As people migrate to 19.3, there are earlier bugs that prevent 12.1 from working with a 19.3 GI...those bugs are fixed in these PSU' patching needs to be part of the 19.3 upgrade plan, and this patch needs to be part of the patch.

Hopefully this will be part of future PSU/proactive patch bundles...and we can keep the nightmares in the movies! 

/*+ UPDATE */
The July PSU is out ( and per (1924126.1), 25107334 is fixed in that PSU.

Upgrade to Oracle 19.3!

Oracle has postponed End of Support for many, many times, but no mas!  In fact, if you're running EBS, is STILL supported with extended support fees waived, almost 11 years after its initial release.  I know a couple of large EBS shops that are still running on  To upgrade the database for EBS is a huge its understandable why they'd put this off as long as possible...but time is running out. See 742060.1 for details.

Also, 12.1 premier support ran out almost a year ago, but I know there are a lot of people still running critical databases on that version.

At this point, everybody is aware that Oracle changed their naming you can think of 19.3 really being 12.2.X.  There are *many* great new features in 19.3...its the terminal release of Oracle 12.2.  Oracle recommends you not upgrade at this point to anything other that 19c.  I've heard rumors that the next db version that'll be supported for EBS is 19.3 (currently the max is 12.1...they're skipping 12.2 and 18.3.)

So its're going to go to 19c.  Here's are some things to keep in mind:

1. In order to upgrade your database to 19.3, you...of course...need to upgrade your ASM's Grid Infrastructure to 19.3

2. In order to upgrade your Grid Infrastructure to 19.3, (assuming you're on RHEL) you MUST be on RHEL 7.  18.X was fine with RHEL 6, but 19.3 requires RHEL 7.

3. There are a lot of changes in the OS requirements.  Make sure you review/verify the install documentation before you install it.

I'll post more about the changes in the 19.3 upgrade process later.