Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Disk full problem of Oracle database

Posted on 2014-12-04
29
Medium Priority
?
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 3
  • +4
29 Comments
 
LVL 11

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 71

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 35

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 74

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 38

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 35

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 35

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
 
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 71

Expert Comment

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

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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 500 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 71

Assisted Solution

by:Qlemo
Qlemo earned 500 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 35

Accepted Solution

by:
johnsone earned 500 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 35

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 35

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 77

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

618 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