Prev: Requesting to add my id to your yahoo im
Next: removing previous installations effects for Oracle 10-win32
From: ca111026 on 14 May 2010 17:52
I got strange error message in alert.log for 9.2.0.7 database (64-bit)
on AIX 5.3

ARC0: Completed archiving log 4 thread 1 sequence 123305
Fri May 14 21:05:55 2010
Failure to extend rollback segment 15 because of 1000 conditionFULL
status of rollback segment 15 set.
Fri May 14 21:05:59 2010

I am familiar with 30036 error, but not with 1000. In fact error 1000
has nothing to do with rollback/undo

oracle> oerr ora 30036
30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
// *Cause: the specified undo tablespace has no more space
available.
// *Action: Add more space to the undo tablespace before retrying
// the operation. An alternative is to wait until active
// transactions to commit.

oracle> oerr ora 1000
01000, 00000, "maximum open cursors exceeded"
// *Cause:
// *Action:

The database is configured with automatic undo management. This is
batch processing system, undo generation is stable at approximately
60,000 blocks per 10 min interval (as per V$UNDOSTAT), or 360,000
blocks per hour. Undo retention is set to 28,000 seconds, or 8 hours.
To keep 8 hours of undo we need approximately 3 million blocks
(2,880,000), or 24 GB as database uses 8K blocks. Undo tablespace is
60 GB, approx 50% free.
So everything seems to be configured correctly, why the error?


From: Steve Howard on 14 May 2010 19:07
On May 14, 5:52 pm, ca111026 <ca111...(a)gmail.com> wrote:
> I got strange error message in alert.log for 9.2.0.7 database (64-bit)
> on AIX 5.3
>
> ARC0: Completed archiving  log 4 thread 1 sequence 123305
> Fri May 14 21:05:55 2010
> Failure to extend rollback segment 15 because of 1000 conditionFULL
> status of rollback segment 15 set.
> Fri May 14 21:05:59 2010
>
> I am familiar with 30036 error, but not with 1000. In fact error 1000
> has nothing to do with rollback/undo
>
> oracle> oerr ora 30036
> 30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
> // *Cause:   the specified undo tablespace has no more space
> available.
> // *Action:  Add more space to the undo tablespace before retrying
> //           the operation. An alternative is to wait until active
> //           transactions to commit.
>
> oracle> oerr ora 1000
> 01000, 00000, "maximum open cursors exceeded"
> // *Cause:
> // *Action:
>
> The database is configured with automatic undo management. This is
> batch processing system, undo generation is stable at approximately
> 60,000 blocks per 10 min interval (as per V$UNDOSTAT), or 360,000
> blocks per hour. Undo retention is set to 28,000 seconds, or 8 hours.
> To keep 8 hours of undo we need approximately 3 million blocks
> (2,880,000), or 24 GB as database uses 8K blocks. Undo tablespace is
> 60 GB, approx 50% free.
> So everything seems to be configured correctly, why the error?

Perhaps this will help.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1441804355350
From: ca111026 on 15 May 2010 01:22
Thanks. Yes, I checked V$SESSTAT for STATISTIC# = 3 ('open cursors
current'). In this database parameter open_cursors is set to 2,000.

Most sessions had less than 10 opened cursors, with following
exceptions:
- one session with 1,999
- two sessions with 2,000
- one session with 32,430
- one session with 33,877

How a session could have 32,000 opened cursors when limit is 2,000?


From: Mladen Gogala on 15 May 2010 01:42
On Fri, 14 May 2010 22:22:15 -0700, ca111026 wrote:

> Thanks. Yes, I checked V$SESSTAT for STATISTIC# = 3 ('open cursors
> current'). In this database parameter open_cursors is set to 2,000.
>
> Most sessions had less than 10 opened cursors, with following
> exceptions:
> - one session with 1,999
> - two sessions with 2,000
> - one session with 32,430
> - one session with 33,877
>
> How a session could have 32,000 opened cursors when limit is 2,000?

It can't. There is probably a problem with the stats. Check the
V$OPEN_CURSOR table and do counts. Here is what the table looks like

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> desc v$open_cursor
Name Null? Type
----------------------------------------- --------
----------------------------
SADDR RAW(4)
SID NUMBER
USER_NAME VARCHAR2(30)
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_TEXT VARCHAR2(60)
LAST_SQL_ACTIVE_TIME DATE
SQL_EXEC_ID NUMBER
CURSOR_TYPE VARCHAR2(64)

SQL>


--
http://mgogala.byethost5.com
From: ca111026 on 15 May 2010 02:15
I already checked V$OPEN_CURSOR. It shows than no session has more
tha 200 cursors:


SQL> select
2 sid,
3 count(*) cnt
4 from
5 v$open_cursor
6 group by
7 sid
8 order by
9 2;

SID CNT
---------- ----------
18 1
43 1
87 1
101 1
119 1
157 1
179 1
183 1
206 1
286 1
274 1
260 1
256 1
240 1
210 1
204 1
164 1
412 1
396 1
113 1
144 2
369 2
315 2
417 2
220 3
383 4
84 6
186 7
15 8
394 9
60 12
74 13
88 14
132 16
110 17
182 17
190 18
409 18
127 19
68 20
249 20
36 21
111 21
57 24
147 24
129 31
203 31
12 38
167 38
263 39
308 42
64 61
316 61
75 65
104 75
314 77
259 78
292 79
184 80
415 81
169 82
187 83
271 83
247 83
28 84
268 84
78 84
39 84
96 85
207 85
267 85
294 85
309 85
162 86
337 86
398 87
42 88
126 88
251 88
254 88
141 88
11 89
389 89
339 89
281 89
261 89
153 89
100 89
19 90
105 90
382 90
176 90
229 90
130 90
125 90
117 90
102 91
108 91
166 91
145 91
89 92
224 92
50 93
250 93
295 93
192 93
30 94
152 94
118 94
399 95
307 96
38 97
47 100
82 105
328 108
252 112
154 122
77 138
401 138
194 144
426 146
112 151
244 153
387 171
390 175
85 176
282 178
143 179
368 179
98 181
159 187
24 188
245 194
133 195
17 196
62 198
21 200
395 200
65 200
290 200
333 200
228 200

142 rows selected.

 |  Next  |  Last
Pages: 1 2
Prev: Requesting to add my id to your yahoo im
Next: removing previous installations effects for Oracle 10-win32