Oracle Database Review
Beware: Need to monitor disk space


One thing that is annoying is the need to monitor the disk space available. In Oracle, the DBMS can monitor the utilization of a data file(s) but when the data files become full, the DBMS cannot just add a new data file and continue. Everything comes to a deadly halt. This approach makes no sense because the solution is just to add a new data file. So all that Oracle has to provide for are the parameters we want for all data files that are added "automagically" and we do not then run into such deadly issues.

Disclosure: I am a real user, and this review is based on my own experience and opinions.

10 Comments

it_user98628Real UserTOP 20LEADERBOARD

Have you used "Autoextend" which will not add another datafile but extend existing datafile?

21 March 14
Amin AdatiaConsultantTOP 20POPULAR

AutoExtend will work to the point when the data file size is reached. On AIX and Linux installation we had/have, the file size limit is 32GB. The max file size depends on the OS. However, there is now the BIGFILE option for a tablespace and the data file can be set for unlimited size; except this means that there can be a rouge process that can take up all of the available disk space. In the next incarnation of Oracle I think the option to automatically add a data file of a certain size will materialize.

22 March 14
James LuiReal UserTOP 5POPULAR

Also with modern NAS systems, counting free space is no longer a simple equation of dr'ing the OS filesystem. This is why Oracle has left the datafile addition management up to OEM. When tablespaces reach the desired threshold, it kicks off an incident handling process (including the alert), which can include assessment of available space, expansion of managed LUNs, calculated datafile additions based upon statistical growth, and using OMF (Oracle Managed Files) to keep the names distinct. Basically, that's what's happening inside an Exadata box under the same conditions - it's just been pre-configured that way.

27 March 14
Amin AdatiaConsultantTOP 20POPULAR

James Lui
Are you saying that there is an option in OEM to automatically add data file(s) based on how much free space is available? Or is it just ayet another report/chart showing you the usage which some person has to "action"? We have Exadata and we have " issues" about data files. Nobody from the DBA Group has ever mentioned this auto-magic management of data files.

27 March 14
James LuiReal UserTOP 5POPULAR

Depending a little on which version you're using, in 12c, you have the Tablespace threshold alert:
1) Raise an incident with alert
2) Decide on a particular strategy to deal with the incident.

You have both:
Note 1540562.1: 12c Cloud Control: Steps to configure OS Command Notification Method to Execute a Custom OS Script
Note 1542585.1: 12c Cloud Control: Steps to configure PL/SQL Notification Method to Execute a Custom PL/SQL procedure

Either one can be used to execute the appropriate steps to evaluate remaining free space, add an OMF datafile (or more than one, depending on growth rate), then e-mail the results back to you.

It's never been pre-configured out of the box that way because of the simple danger of too many variables in every customer's environment being different.

28 March 14
Amin AdatiaConsultantTOP 20POPULAR

Hi James Lui

Does the OEM 12c also work the same way against 11gR2 database or is all of this available only in Oracle Database 12c?

Just curious though why Oracle did not put the feature parameters at the Tablespace definition step instead of having these work via OEM 12c. If they were able to do the auto-extend on the single data file then why not for the Tablespace itself?

28 March 14
it_user98628Real UserTOP 20LEADERBOARD

Amin,

Great question!
Attempting to answer in simple way after thinking how it would have been made possible.

Tablespace is logical and derives its space from datafiles [which are operating system datafiles made of physical data blocks].

Autoextend means allocating more blocks to datafiles available on the file system / storage.

So if Autoextend was available at the tablespace level, Oracle database would need to
1. analyse each datafile in the tablespace to find out the current size, free space as well as free space on the file system / logical volume / volume group / physical volume etc.
2. Then pick the data file most suitable for extending.

Step-1 might be little complex at its needs lot of work at OS level. In addition, there are other factors, that DBAs do, like standardizing and limiting data file size, spreading files on different filesystems / disks etc. These were things of the past, but I am sure, large population might be still using traditional filesystem supported by SAN or DAS.

On the other hand, in my more than 11 years experience as DBA, I have never encountered a situation where I had to add datafile on urgent basis. I always employed best practices derived based on the experience. DBA must know profile of its database and application, just like family doctor knows about his patient.

Cheers
Sharad

28 March 14
Amin AdatiaConsultantTOP 20POPULAR

Hi Sharad
I know we usually dont have to add data files in an emergency. 95% full over a long weekend is pretty close though.
If OEM can now figure out all those complex queries to send you an email or generate the script and execute the command (s) then surely it cannot be that difficult to have the database do it. After all the database knows when to invoke auto-extend. So how much more difficult could it be to have "alter table space add datafile blah blah"?

Regards

28 March 14
kapilmalik1983ConsultantTOP REVIEWER

Do you have any monitoring tool or script in place which keep on tell how much space is remaining in datafile now other than OEM?

26 October 14
Amin AdatiaConsultantTOP 20POPULAR

We have a script which monitors space left based on what the user_segments reports as used. We have also started to use bigfIle tablespaces.

26 October 14
Guest
Why do you like it?

Sign Up with Email