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
Best Answer
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