Solved

Disk full problem of Oracle database

Posted on 2014-12-04
29
373 Views
Last Modified: 2014-12-17
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 ?
0
Comment
Question by:marrowyung
  • 13
  • 7
  • 3
  • +4
29 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40482345
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40482447
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40482606
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 40482743
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40486123
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40486320
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40486418
so ASH provide no help on this ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40486430
Geert Gruwez,

why you think about redo log ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40486512
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40490465
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 40490467
any query can find that out instead of that  Snapper ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40490471
one thing, after I run that snapper script, where can I find it from Oracle developer ? or Toad for Oracle ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40490484
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40491089
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:marrowyung
ID: 40495827
"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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40495902
Yes, TEMP tablespace will never shrink unless you do yourself.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40495995
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 40496030
>>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
 
LVL 1

Author Comment

by:marrowyung
ID: 40499888
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
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 125 total points
ID: 40499897
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 125 total points
ID: 40499926
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
 
LVL 34

Accepted Solution

by:
johnsone earned 125 total points
ID: 40500155
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40500257
"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
 
LVL 34

Expert Comment

by:johnsone
ID: 40500323
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40501945
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40501949
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40502299
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40502365
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40502477
>>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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now