Disk full problem of Oracle database

Dear all,

Right now our Oracle system suddently ran quicklky out of space and it is too late for us to add disk space, someone suggest the ASH, may I know what it is and how can I use this tools to fix this ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
You have to add physically new hard disk to the OS and then create tablespace and add new DBF files to the database.

Read
http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmbysql.htm#OSTMG13980
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Or find something to delete to get some free space and expand the existing tablespace files as a short-term fix. E.g. old archive logs. Maybe you can delete data, or move index or tables to other tablespaces. But you'll need to add physical disk space ASAP.
0
johnsoneSenior Oracle DBACommented:
What tablespace caused the issue?  Was it TEMP?  If it is a temporary tablespace, I would suggest that you drop and recreate with the old sizes and turn autoextend off for TEMP.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
did you fill up SYSAUX due to auditing? if so, you can copy that data off somewhere else and then truncate that table.

some other SYSAUX objects have cleanup routines associated with them, like awr and statistic history.

is the problem even inside the database or is it outside?

How you address the problem will depend on what the problem is.
0
marrowyungSenior Technical architecture (Data)Author Commented:
HuaMinChen,

but how about existing data?  that one has nothing to do with existing data, right?

Qlemo,

"Or find something to delete to get some free space and expand the existing tablespace files as a short-term fix. E.g. old archive logs. "

you mean use RMAN to backupo archive log and remove the old one ? assuming there are no unnecessary item here as it will be a long time to find them all out !

johnsone,

" turn autoextend off for TEMP. "

it means TEMP will dead later on as it can't expand by itself ? no it is user data table space.

I am not sure why ASH can fix it, please suggest.

sdstuber,

"is the problem even inside the database or is it outside?"

inside the database.
0
Geert GOracle dbaCommented:
find out what is causing the most redo.
that should give a clue where to look

this is an ancient article, but still valid for items like this:
http://blog.tanelpoder.com/2008/05/30/oracle-troubleshooting-with-snapper-detecting-whos-causing-excessive-redo-generation/
0
marrowyungSenior Technical architecture (Data)Author Commented:
so ASH provide no help on this ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Geert Gruwez,

why you think about redo log ?
0
johnsoneSenior Oracle DBACommented:
Autoextend on for TEMP is dangerous in my opinion.  One person who writes a bad careteasian product and all your disk space is gone.  It should be sized properly for your application.

Personally, I don't like autoextend for anything.  Takes away the control of where space is allocated.  I can spread data better across disk controllers myself.  Autoextend keeps putting the data in to the same place.


The comments about redo are to try to determine what is using the space and who (which user) is causing it to happen.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Autoextend on for TEMP is dangerous in my opinion. "

if the transaction was completed, the temp storage should be release, right? if it has a limited, then when it reached that limit, error prompts anyway and application stop working, right? still got into trouble!

"Autoextend keeps putting the data in to the same place.
"
Good point but this is because it is the same tablespace? then why not creating the skethed tablespace under ASM and this can do the same works, right?

"The comments about redo are to try to determine what is using the space and who (which user) is causing it to happen. "

only one way? just like checking the resource toke by real time query ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
any query can find that out instead of that  Snapper ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, after I run that snapper script, where can I find it from Oracle developer ? or Toad for Oracle ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
when I executing the Snapper,  it keep asking for vaule, I don't know how to handle it.

it is a temporary script and it leave nothting on the database side ? what is the name of it from within Oracle ?

I can't find it from within toad under my schema and procedure session.
0
johnsoneSenior Oracle DBACommented:
After a transaction completes, the space within the TEMP tablespace is released to be reused, however the disk space is not.  The disk space will remain allocated to TEMP and not released unless an admin intervenes.

Autoextend keeps putting space in the same place because it is the same datafile.  I can have more than one file in a tablespace and direct data to span physical hardware that you cannot do with autoextend.

I cannot comment on the Snapper utility that someone has mentioned.  Never used it and I'm not going to try to figure it out.  I use LogMiner to fish through archive logs.  It does not leave anything in the database and it doesn't have to be run against production, it can be run against any database of the same version.  Documentation here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1553
0
marrowyungSenior Technical architecture (Data)Author Commented:
"After a transaction completes, the space within the TEMP tablespace is released to be reused, however the disk space is not.  The disk space will remain allocated to TEMP and not released unless an admin intervenes."

this is amazing, is this mean TEMP tablespace will only getting bigger and bigger ?

"Autoextend keeps putting space in the same place because it is the same datafile.  I can have more than one file in a tablespace and direct data to span physical hardware that you cannot do with autoextend."

can't understand that. you mean adding more temp table space file to more disk and they will create equal size of each temp table space and therefore better than autoextend?

but keep addding disk need money ! not a very good choice ! if it keep full each time we need to add more disks ?

so again, ASH utility do nothing on fixing disk space ? what is that for ?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes, TEMP tablespace will never shrink unless you do yourself.
0
johnsoneSenior Oracle DBACommented:
Autoextend only adds space to existing files.  It does not create new ones.  By creating new files on different physical hardware you can spread the I/O load across physical boundaries.  Autoextend cannot do that.  Most people do not plan for that or attempt to do it.  It is more of a dying art because of striping and other means of chunking up disk.  However if you know your administrators (system and disk) and they know what they are doing, you can get advantages of physically separating large tables across physical boundaries.  You can also allocate physical drives in such a way that more accessed data files are on faster parts of the disk (i.e. inner cylinders have faster seek times, outer cylinders spin faster).
0
slightwv (䄆 Netminder) Commented:
>>this is amazing, is this mean TEMP tablespace will only getting bigger and bigger ?

Not really.  With autoextend on the TEMP data file will grow to the size that is necessary to complete the largest transaction.  Once that transaction is complete, the space for that is released back to the 'free' list.  The next transaction will reuse the previous allocated space and the datafile will not grow again unless a larger transaction fills up the available space.

What people are saying is that once the space has been allocated to a data file, the file doesn't automatically shrink once the space has been released.

>>so again, ASH utility do nothing on fixing disk space ? what is that for ?

I believe ASH was suggested to help find out what was using all the TEMP and causing it to grow.  It will not help resize the data file once it has grown.

Take a look at this presentation:
http://www.oracle.com/technetwork/database/manageability/ppt-active-session-history-129612.pdf
0
marrowyungSenior Technical architecture (Data)Author Commented:
Qlemo,

"Yes, TEMP tablespace will never shrink unless you do yourself. "

what command to do it ?

johnsone,
" You can also allocate physical drives in such a way that more accessed data files are on faster parts of the disk (i.e. inner cylinders have faster seek times, outer cylinders spin faster). "

how? by using ASM?

slightwv,

"Not really.  With autoextend on the TEMP data file will grow to the size that is necessary to complete the largest transaction.  Once that transaction is complete, the space for that is released back to the 'free' list.  The next transaction will reuse the previous allocated space and the datafile will not grow again unless a larger transaction fills up the available space.
"

so it just reuse the temp free list and it will be fast than if we shrink it and let it extend again?

"I believe ASH was suggested to help find out what was using all the TEMP and causing it to grow.  It will not help resize the data file once it has grown."

and how, this is what I want to know
0
Geert GOracle dbaCommented:
what size is your database in total ?
what size is the biggest table ?

temp is mostly used for sorting data
or storing tempfile data

for a 50Gb db i set temp to 2Gb fixed.
not much tempfile usage here

for 2TB db i've got temp set to 50Gb.
also fixed

make sure autoextend is off. some oracle versions have a bug with autoextend on for temp
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Shrinking a tablespace usually means to recreate it.
Extending a tablespace is a costly operation - most of the time reusing unallocated space in a tablespace datafile is much faster than having to physically allocate by extending.
0
johnsoneSenior Oracle DBACommented:
Spreading data across physical barriers is done manually.  There is no utility to do it.  You have to know your hardware.  When tablespace is getting full, add another data file with ALTER TABLESPACE.  We never set autoextend to on with any tablespace.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"Spreading data across physical barriers is done manually.  There is no utility to do it.  You have to know your hardware.  "

you mean physical RAID ? I still wondering how fast it can be done once we have already done  that ! disk is full and the result is we add disk ? but the point is it full suddenly !

"We never set autoextend to on with any tablespace. "

you ALSO mean autoextend is turn off by default ?
0
johnsoneSenior Oracle DBACommented:
We have large databases.  Even with RAID, there are multiple LUNs spread across multiple controllers and multiple access paths.  There is RAID, but we help it out even more by spreading our load as much as we can.  If I can have a table with 400 million records spread across 5 drives on one controller or spread across 15 drives on 3 controllers, which would you pick.

Autoextend is set when you create the datafile/tablespace.  The default (on or off) depends on how you create the file.  You can see the documentation here -> http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses004.htm#SQLRF01602
0
marrowyungSenior Technical architecture (Data)Author Commented:
johnsone,

"If I can have a table with 400 million records spread across 5 drives on one controller or spread across 15 drives on 3 controllers, which would you pick."

15 drives on 3 controllers! but cost will be much higher to get a better performance and how disk space, what I am wondering is what if aFTER that, disk full again, going to this approach seems costly and doesn't make more sense.

The good direction should be which query take up so much space and study why! e.g. let this query run less often or programmming differently.

"Autoextend is set when you create the datafile/tablespace. "

ok, tks.

but you all will set a maximum size of it, so that it can have a max sizes anyway ?  but what if that max size still reached ? Oracle still not operatable, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Geert Gruwez,

"for a 50Gb db i set temp to 2Gb fixed.
 not much tempfile usage here

 for 2TB db i've got temp set to 50Gb.
 also fixed
"
why have this setting/ratio, any best practice you follow?

"make sure autoextend is off. some oracle versions have a bug with autoextend on for temp "

tks for that and do you mean that we have to set autoextend to off for all tablespace? and we can only set autoextend on tablespace level ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Slightwv,


"I believe ASH was suggested to help find out what was using all the TEMP and causing it to grow.  It will not help resize the data file once it has grown.

 Take a look at this presentation:
http://www.oracle.com/technetwork/database/manageability/ppt-active-session-history-129612.pdf "

all of your suggest is telling me to check which tablespace is full, is ASH the tools or how can I find out which tablspace was full?

at that time Oracle should have no response at all, right?
0
johnsoneSenior Oracle DBACommented:
Like I said, it is a manual process to spread data across physical barriers.  Getting performance out of hardware and tuning it is not a one time process.  It is constant.  And capacity planning is part of it.  We never run out of disk space.  Based on trends we know how much we should use over the course of a given timeframe and we have some buffer.

We manage enterprise level databases.  Multi-terabyte databases spread across hundreds of disks.  Some we very carefully lay out for performance, others performance isn't as much of an issue so we let it fall wherever it wants to on disks.
0
slightwv (䄆 Netminder) Commented:
>>how can I find out which tablspace was full?

There are tons of scripts out there that show tablespace usage.  If you have OEM set up it is a few simple clicks.

I think the question you are asking is what actually filled it up.

I've not done anything with ASH but I believe it was suggested to help you figure out if your TEMP usage is 'normal' or just an 'accident' performed by someone that executed some bad SQL.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.