0

My application was running without any problems for almost one year. But recently someone executed a big select for a report, and the Oracle´s TEMP datafile has grown from 5GB to 30GB. The server then ran out of space.

The users's tablespace/datafiles were not affected because I created the files large enough to fit all the data for a long time, in fact, we are still using about 15% of user tablespace space initially allocated. So the problem is not here I guess. No loss of data was reported or detected.

After the server ran out of space, I deleted some old backup files and did the following:

  • CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/db/temp02.dbf' SIZE 50m;
  • ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
  • DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
  • CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u02/oradata/db/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M;
  • ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
  • DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

One hour after that I started to receive calls from users saying that the application is very slow, they can´t log in, everything is slow, the application freezes. But the problem "solve" itself after 2-3 minutes. When I do some test, to check memory/io/cpu, everything is fine. I ask the user and he says that now everything is fine, and I did nothing.

I tried to rebuild index and recompute statistics. But the problem persists.

On the application side, I have a lot Oracle errors logged when the user calls me:

  • ORA-12571: TNS: packet writer failure
  • ORA-01012: not logged on
  • ORA-00028: your session has been killed
  • ORA-12170: TNS:Connect timeout occurred

After 2-3 minutes, no more errors and the performance is fine.

This is happening with 80 users logged during the day, and with 2 users logged at night with a very low usage of the system.

Oracle Release 11.2.0.1.0 Server Linux CentOS

Anyone have any ideas please?

Thank you!

Edgar

Improve this question
4
  • Do the problems coincide with the tempfile autoextending; or is there a big query running again that's consuming all 5GB of temp and causing other sessions to fail - perhaps with an ORA-01652 that's being lost? Is there anything in the alert log on the server?
    – Alex Poole
    Aug 18, 2011 at 14:47
  • Last night I recreated the tempfile with initial size very big 10GB (bigger than the maxsize of the previous I created), with next autoexend at 500mb and maximum size without limit. The file is no longer growing, it means that its size is enough. The previous temp had it´s maxsize 5GB, but the size of the file never passed 2,5GB. I got ORA-01652 when the server was out of space, but after I recreated the temp, it does not happen anymore. Thank you. No alert on the servers. I tried to reboot both the application server and the database server. Thank you!
    – Edgar
    Aug 18, 2011 at 20:49
  • having no limit on the maximum size leaves you open to the same problem you had originally, with a runaway query running out of disk space - so I'd have thought capping it at 5GB or 10GB might be safer. Since the previous tempfile had autoextended about 25 times, I wonder if the application problems did coincide with those extensions; but it shouldn't take long to add 100MB, and the application errors don't make sense. Unless there was a really small response timeout in the app, perhaps. Maybe the listener.ora or sqlnet.ora might reveal something? Or the listener log?
    – Alex Poole
    Aug 19, 2011 at 8:31
  • Well, we had this problem with TEMP files and the application became unstable. So my attention was on the database. I spent many hours recreating both the database and another application server, doing a lot of tests, including network traffic, and we realized that our application server was actually receiving some kind of attack, we just changed the IP of the application server and now everything is fine! What a bad luck having this network problem at the same time of temp file, I got my attention to the wrong place.
    – Edgar
    Aug 19, 2011 at 21:40

1 Answer 1

Reset to default
0

Well, we had this problem with TEMP files and the application became unstable. So my attention was on the database. I spent many hours recreating both the database and another application server, doing a lot of tests, including network traffic, and we realized that our application server was actually receiving some kind of attack, we just changed the IP of the application server and now everything is fine! What a bad luck to have this network problem at the same time of temp file, I got my attention to the wrong place.

Just to check, I used this IP address on a workstation/desktop, and the workstation started to loose network packets and became unstable.

@Alex-Poole, thank you very much for your attention!

Edgar

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .