Solved

Problems with space, processes, and temp.dbf - ORACLE 8 (Solaris 8)

Posted on 2004-10-15
6
4,048 Views
Last Modified: 2013-12-05
Hi everybody

From some years back we worked with Solaris 8, we haven't had greater
problems, nevertheless a month back has appeared bug in the I/O related
to ORACLE 8.1.7.

The Log was the following one:
---------------------------------------------------------------------------------------------
DBD::Oracle::st execute failed: *ORA-27062:* skgfospo: could not find
pending async I/Os (DBD ERROR: OCIStmtExecute) at ./script11.pl line 72.
 
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing
(perhaps you need to call execute first) at ./script11.pl line 73.
 
DBD::Oracle::st execute failed: ORA-27062: skgfospo: could not find
pending async I/Os (DBD ERROR: OCIStmtExecute) at ./script11.pl line 80.
 
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing
(perhaps you need to call execute first) at ./script11.pl line 81.
---------------------------------------------------------------------------------------------

Looking for information in the SUN Web, the released patch exists,
*patch 112254-01* (http://sunsolve.sun.com/search/document.do?assetkey=1-21-112254-01-1).
the same one has been installed.  and it has not returned to happen the same problem.

After some weeks of operation without problem, one has appeared to see a first particular problem:

- In one first opportunity:
  several processes (15, o 16) /oracle remained in memory in STATUS "sleep"
      -----------------
  PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
.....
 6599 oracle    253M  229M sleep   59    0   0:00.06 0.6% oracle/11
 4858 oracle    253M  229M sleep   59    0   0:00.59 0.5% oracle/11
 5623 oracle    253M  229M sleep   59    0   0:00.21 0.5% oracle/11
 5232 oracle    253M  229M sleep   59    0   0:00.32 0.4% oracle/11
 3365 oracle    253M  229M sleep   59    0   0:01.05 0.3% oracle/11
 6109 oracle    253M  229M sleep   59    0   0:00.17 0.3% oracle/11
 6206 oracle    253M  229M sleep   59    0   0:00.13 0.2% oracle/11
 5998 oracle    253M  229M sleep   59    0   0:00.15 0.2% oracle/11
..........
      -----------------

and the partition where this The Data Base has begun to fill
(the normal is that in a month it gets to grow to the month approx.  in 0.0625 % (79688.93 KB)) the partition Data Base, but in this case it has arrived from a 90% at 96% in three hours

making a revision.. the listener.log, this has grown.
I did shutdown the Data Base, then I killed the processes,
I do a empty of the listener.log. obtaining more space, from the 96% at 87% of the particion Data Base

after a week reviewing the system, I have observed again several processes/oracle (up to 18)
in memory all in status "sleep" this time the partition where this Data Base (ORACLE) was in 100% full partition, and was not registering data.  
=== Log ====
DBD::Oracle::st execute failed: ORA-01652: unable to extend temp segment by 16 in tablespace TEMP
ORA-27059: skgfrsz: could not reduce file size
SVR4 Error: 28: No space left on device
Additional information: 2 (DBD ERROR: OCIStmtExecute) at
==========

Reviewing listener.log again, this it has not grown, either the datafiles, except by temp01.dbf
that increased from 736,25 MB to 1286,25 MB.  
The normal is that this file in one week does not grow anything.  
Because of this file it is that the partition has filled.
======
  PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
........
 2886 oracle    253M  229M sleep   59    0   0:00.18 3.1% oracle/11
 2139 oracle    253M  229M sleep   59    0   0:01.06 1.0% oracle/11
 4981 oracle    253M  229M sleep   59    0   0:00.14 0.9% oracle/11
 5452 oracle    253M  229M sleep   59    0   0:00.06 0.9% oracle/11
 2675 oracle    253M  229M sleep   59    0   0:00.35 0.8% oracle/11
 2427 oracle    253M  229M sleep   59    0   0:00.42 0.7% oracle/11
 4733 oracle    253M  229M sleep   59    0   0:00.15 0.6% oracle/11
 1206 oracle    253M  229M sleep   59    0   0:01.20 0.2% oracle/11
 3379 oracle    253M  229M sleep   59    0   0:00.18 0.2% oracle/11
  424 root     4056K 3560K sleep   59    0   0:00.00 0.1% mibiisa/12
  891 oracle    253M  230M sleep   59    0   0:01.10 0.1% oracle/11
 1899 oracle    253M  229M sleep   59    0   0:01.06 0.1% oracle/11
 4495 oracle    253M  229M sleep   59    0   0:00.15 0.1% oracle/11
 3964 oracle    253M  229M sleep   59    0   0:00.16 0.1% oracle/11
 3703 oracle    253M  229M sleep   59    0   0:00.17 0.1% oracle/11
  229 root     3784K 2984K sleep   59    0   0:00.00 0.1% nscd/9
 1477 oracle    253M  229M sleep   59    0   0:01.17 0.1% oracle/11
 3146 oracle    253M  229M sleep   59    0   0:00.19 0.0% oracle/11
  364 oracle    253M  226M sleep   59    0   0:00.02 0.0% oracle/11
........

======
I have released to space in that partition erasing other files no related with the Data Base,
I stared the Data Base, and until now this worked good, but the
greater preoccupation is that returns to happen the
same problem.

How to know the causes by which increased the size of temp01.dbf ?
why happened this problem?
Why there are several processes/oracle and they remain in memory ?

I hope can you help me

In fact thanks.

Jimmy

0
Comment
Question by:Jimmy_hg
  • 2
6 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12321288
>>>>>>>>>>>>>>>How to know the causes by which increased the size of temp01.dbf ?


temporary tablespace is an issue in oracle8i.
SMON is usually repsonsible for cleaning up temp tablespace and release the orphaned space.
if your system is busy at long interval, SMON may not be able to get around to clean it up and release the tablespac,e

so you might observe that your temp tablespace keep growing and growing ( have seen grow up to a few gigabytes).

>>>>>>>>>>.why happened this problem?


the cause for your problem is that your temp tablespace is running out of disk space


>>>>>>>>>>>>>.Why there are several processes/oracle and they remain in memory ?


there are always background processes alive residing in Memory, SMON, PMON, LGWR, ARCn, DBWR, Jnnn, etc.
0
 

Author Comment

by:Jimmy_hg
ID: 12324420
Firts, thank you for your answers

I understand that ORACLE raises several processes in memory to execute tasks.
I have  Scripts that make transactions at Data Base
These Scripts are execute every 10 ms, during that time several processes (4 or 5) is raises in memory, once finished the transaction is not left none processes /oracle in memory and if it is only left one processes ORACLE.

but..
¿  As it is that in same days (5 days only) the size of the file has gotten to grow from 736,25 MB to 1286,25 MB ¿?  :-S
*(This week (5 days) the datafile Temp has not grown anything.  the sizes of the file have stayed)
and the processes ORACLE resideng in memory were max. only 5

When the problem of the space has happened  I have observed many processes ORACLE residing in memory (16 processes ORACLE to be exact), and just in that moment is where it has happened the problems that I mentioned before.

Why many processes ORACLE reside in memory?

This week the operation DataBase this good, but the problem may be to return to happen any moment.

the last logs (alert_DBA0 ) generated before one filled the partition, are:
---------------------------------------------------
Fri Oct  8 13:12:46 2004
Thread 1 advanced to log sequence 31482
  Current log# 3 seq# 31482 mem# 0: /oracle/orahome/oradata/DBA0/redo03.log
Fri Oct  8 13:35:50 2004
Thread 1 advanced to log sequence 31483
  Current log# 1 seq# 31483 mem# 0: /oracle/orahome/oradata/DBA0/redo01.log
Fri Oct  8 14:05:44 2004
Thread 1 advanced to log sequence 31484
  Current log# 2 seq# 31484 mem# 0: /oracle/orahome/oradata/DBA0/redo02.log
Fri Oct  8 14:28:13 2004
Thread 1 advanced to log sequence 31485
  Current log# 3 seq# 31485 mem# 0: /oracle/orahome/oradata/DBA0/redo03.log
Fri Oct  8 14:50:52 2004
Thread 1 advanced to log sequence 31486
  Current log# 1 seq# 31486 mem# 0: /oracle/orahome/oradata/DBA0/redo01.log
Fri Oct  8 14:51:02 2004
ORA-1652: unable to extend temp segment by 16 in tablespace             TEMP
Fri Oct  8 14:51:05 2004
ORA-1652: unable to extend temp segment by 16 in tablespace             TEMP
Fri Oct  8 14:51:10 2004
ORA-1652: unable to extend temp segment by 16 in tablespace             TEMP
Fri Oct  8 14:51:14 2004
ORA-1652: unable to extend temp segment by 16 in tablespace             TEMP
Fri Oct  8 14:51:18 2004
---------------------------------------------------

thank you

Jimmy.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 12324816
jimmy:

change your TEMP tablespace to Locally managed tablespace, then the error mesg will be all gone.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

760 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

21 Experts available now in Live!

Get 1:1 Help Now