Tbs READ ONLY and Snapshot too old 2004-06-11 - By Goulet, Dick
Global Temp is available in 8i.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-- --Original Message-- --
From: NGUYEN Philippe (Cetelem) [mailto:philippe.nguyen@(protected)]
Sent: Friday, June 11, 2004 12:09 PM
To: 'oracle-l@(protected) '
Subject: RE: Tbs READ ONLY and Snapshot too old
Tahnks Dick, this application run on 8i database, is the Global Temp available ? or any equivalence ?
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____
Philippe Nguyen
CETELEM - Administration, Architecture D¨¦cisionnelle
Direction Customer Relationship Management
E-Mail : philippe.nguyen@(protected)
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-- --Message d 'origine-- --
De : oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]De la part de Goulet, Dick
Envoy¨¦ : vendredi 11 juin 2004 16:38
? : oracle-l@(protected)
Objet : RE: Tbs READ ONLY and Snapshot too old
Philippe,
If you look at the error your getting and the table/tablespace setup you 've described as well as the processing environment, it is immediately obvious that the database is NOT the problem, but the application. What you might do to band-aid their application is to take those small tables and turn them into global temporary tables. The real problem here is that the application is creating, modifying, and deleting data that other sessions are have an interest in. Couple that with those massive tables that you 've mentioned and getting an ORA-01555 (See ORA-01555.ora-code.com) under these circumstances is a guarantee. What is happening that I believe your duhvelopers can understand is that one session is modifying a block, not an insert in most cases, and has issued a commit. Now that block is resident in the SGA as a dirty block, but either the current session or another session is requesting data from those massive tables. So Oracle needs a place to put new data blocks and that modified one is low on the LRU list, so off to disk it goes & the rollback information heads for the trash. Another session now comes along to either modify or read that block, with an earlier SCN, and bingo, no rollback available, ORA-01555 (See ORA-01555.ora-code.com).
Therefore, possible band-aids:
1) Make the smaller tables Global Temps
2) Increase the db_block_buffers or their 9i equivalent.
3) If your on 9i or better increase Undo retention to some insane value like 24 hours. BTW: Increase your UNDO tablespace by a factor of 100. (That ought to get damanagements attention.)
4) Scan Metalink for ORA-01555 (See ORA-01555.ora-code.com). Note:269814.1 should be of help.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-- --Original Message-- --
From: NGUYEN Philippe (Cetelem) [mailto:philippe.nguyen@(protected)]
Sent: Friday, June 11, 2004 7:03 AM
To: 'oracle-l@(protected) '
Subject: RE: Tbs READ ONLY and Snapshot too old
thank Justin,
Errors occurs during the month but we have a very bad application that generate sql scripts with a lot of cursors opened on thoses big tables ( more than 20Go per tables) thoses script create small tables (10 - 200 Mo) with datas pick up from the different big tables.
The problem is that the generated script are often launch in the same time and I we can 't touch the code.
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____
Philippe Nguyen
CETELEM - Administration, Architecture D¨¦cisionnelle
Direction Customer Relationship Management
E-Mail : philippe.nguyen@(protected)
Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
-- --Message d 'origine-- --
De : oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]De la part de Justin Cave
Envoy¨¦ : vendredi 11 juin 2004 12:15
? : oracle-l@(protected)
Objet : RE: Tbs READ ONLY and Snapshot too old
ORA-01555 (See ORA-01555.ora-code.com) indicates that Oracle was attempting to reconstruct the state of a block and the earlier state was not available in the undo segments. By definition, you cannot get an ORA-01555 (See ORA-01555.ora-code.com) error when trying to access a read-only object.
Do you get these ORA-01555 (See ORA-01555.ora-code.com) errors shortly after doing the load or do you get them throughout the day? The only thing I can think is that these errors are caused by delayed block cleanout, but that should take care of itself soon after the load. If you get errors throughout the month, I would suspect that the big tables aren 't the cause.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com <http://www.ddbcinc.com/askDDBC > /askDDBC
__ __
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of NGUYEN Philippe (Cetelem)
Sent: Friday, June 11, 2004 4:15 AM
To: oracle-l@(protected)
Subject: Tbs READ ONLY and Snapshot too old
Hi List,
just a little question about read only tbs :
We have big tables that are loaded once and never updated during the month ,
so I think it 's a good thing to switch their tbs to read only mode.
Web have a lot of script that request thoses heavy tables to build small tables and we often encounter ORA-01555 (See ORA-01555.ora-code.com).
Do you think that "in general " this operation could enhance our ORA-01555 (See ORA-01555.ora-code.com) problems ?
TIA
Philippe
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD >
<META HTTP-EQUIV= "Content-Type " CONTENT= "text/html; charset=iso-8859-1 " >
<TITLE >Tbs READ ONLY and Snapshot too old </TITLE >
<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > </HEAD >
<BODY >
<DIV > <SPAN class=583431116-11062004 > <FONT face=Arial color=#0000ff size=2 >Global
Temp is available in 8i. </FONT > </SPAN > </DIV >
<DIV > </DIV >
<P > <FONT size=2 >Dick Goulet <BR >Senior Oracle DBA <BR >Oracle Certified 8i
DBA </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B > NGUYEN Philippe (Cetelem)
[mailto:philippe.nguyen@(protected)] <BR > <B >Sent: </B > Friday, June 11, 2004
12:09 PM <BR > <B >To: </B > 'oracle-l@(protected) ' <BR > <B >Subject: </B > RE: Tbs
READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=488540716-11062004 > <FONT face=Arial color=#0000ff
size=2 >Tahnks Dick, </FONT > </SPAN > <SPAN class=488540716-11062004 > <FONT
face=Arial color=#0000ff size=2 >this application run on 8i database, is the
Global Temp available ? or any equivalence ? </FONT > </SPAN > </DIV >
<DIV > </DIV >
<P > <FONT face=Arial color=#000000
size=2 >__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ <B > </B > </FONT > <B > </B >
<BR > <B > <FONT face=Arial color=#000000 >Philippe Nguyen </FONT > </B > <BR > <FONT
face=Arial color=#000000 size=2 >CETELEM - Administration, Architecture
D¨¦cisionnelle </FONT > <BR > <FONT face=Arial color=#000000 size=2 >Direction
Customer Relationship Management </FONT > <BR > <FONT face=Arial color=#000000
size=1 >E-Mail : philippe.nguyen@(protected) </FONT > <BR > <FONT face=Arial
color=#000000 size=1 >Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59
88 </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Message d 'origine-- -- <BR > <B >De : </B >
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B >De la
part de </B > Goulet, Dick <BR > <B >Envoy¨¦ : </B > vendredi 11 juin 2004
16:38 <BR > <B >? : </B > oracle-l@(protected) <BR > <B >Objet : </B > RE:
Tbs READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 >Philippe, </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >If you look at the error your getting and the
table/tablespace setup you 've described as well as the processing
environment, it is immediately obvious that the database is NOT the problem,
but the application. What you might do to band-aid their application
is to take those small tables and turn them into global temporary
tables. The real problem here is that the application is creating,
modifying, and deleting data that other sessions are have an interest
in. Couple that with those massive tables that you 've mentioned and
getting an ORA-01555 (See ORA-01555.ora-code.com) under these circumstances is a guarantee. What is
happening that I believe your duhvelopers can understand is that one session
is modifying a block, not an insert in most cases, and has issued a
commit. Now that block is resident in the SGA as a dirty block, but
either the current session or another session is requesting data from those
massive tables. So Oracle needs a place to put new data blocks and
that modified one is low on the LRU list, so off to disk it goes & the
rollback information heads for the trash. Another session now comes
along to either modify or read that block, with an earlier SCN, and bingo,
no rollback available, ORA-01555 (See ORA-01555.ora-code.com). </FONT > </SPAN > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > Therefore, possible band-aids: </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >1) Make the smaller tables Global
Temps </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >2) Increase the db_block_buffers or their 9i
equivalent. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial
color=#0000ff size=2 >3) If your on 9i or better increase Undo retention to
some insane value like 24 hours. BTW: Increase your UNDO tablespace by
a factor of 100. (That ought to get damanagements
attention.) </FONT > </SPAN > </DIV >
<DIV > <SPAN class=388403313-11062004 > <FONT face=Arial color=#0000ff
size=2 > 4) Scan Metalink for ORA-01555 (See ORA-01555.ora-code.com). <!--StartFragment -- > <FONT size=3 > <FONT
color=#000000 > <FONT face= "Times New Roman " > </FONT > </FONT > <FONT
size=2 >Note:269814.1 should be of help. </FONT > </FONT > </FONT > </SPAN > </DIV >
<P > <FONT size=2 >Dick Goulet <BR >Senior Oracle DBA <BR >Oracle Certified 8i
DBA </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B > NGUYEN Philippe
(Cetelem) [mailto:philippe.nguyen@(protected)] <BR > <B >Sent: </B > Friday, June
11, 2004 7:03 AM <BR > <B >To: </B > 'oracle-l@(protected) ' <BR > <B >Subject: </B >
RE: Tbs READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >thank Justin, </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >Errors occurs during the month but we have a very bad
application that generate sql scripts with a lot
of cursors opened on thoses big tables ( more than 20Go per tables)
thoses script create small tables (10 - 200 Mo) with datas pick up
from the different big tables. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 >The problem is that the generated script are often launch in the
same time and I we can 't touch the code. </FONT > </SPAN > </DIV >
<DIV > <SPAN class=569465010-11062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<P > <FONT face=Arial color=#000000
size=2 >__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ <B > </B > </FONT > <B > </B >
<BR > <B > <FONT face=Arial color=#000000 >Philippe Nguyen </FONT > </B > <BR > <FONT
face=Arial color=#000000 size=2 >CETELEM - Administration, Architecture
D¨¦cisionnelle </FONT > <BR > <FONT face=Arial color=#000000 size=2 >Direction
Customer Relationship Management </FONT > <BR > <FONT face=Arial color=#000000
size=1 >E-Mail : philippe.nguyen@(protected) </FONT > <BR > <FONT face=Arial
color=#000000 size=1 >Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39
59 88 </FONT > </P >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Message d 'origine-- -- <BR > <B >De : </B >
oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B >De la part de </B > Justin
Cave <BR > <B >Envoy¨¦ : </B > vendredi 11 juin 2004
12:15 <BR > <B >? : </B > oracle-l@(protected) <BR > <B >Objet : </B >
RE: Tbs READ ONLY and Snapshot too old <BR > <BR > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 >ORA-01555 (See ORA-01555.ora-code.com) indicates that Oracle was attempting
to reconstruct the state of a block and the earlier state was not
available in the undo segments. By definition, you cannot get an
ORA-01555 (See ORA-01555.ora-code.com) error when trying to access a read-only
object. </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 > </SPAN > </FONT > </DIV >
<DIV dir=ltr align=left > <FONT face=Arial color=#0000ff size=2 > <SPAN
class=359271110-11062004 >Do you get these ORA-01555 (See ORA-01555.ora-code.com) errors shortly after
doing the load or do you get them throughout the day? The only
thing I can think is that these errors are caused by delayed block
cleanout, but that should take care of itself soon after the load.
If you get errors throughout the month, I would suspect that the big
tables aren 't the cause. </SPAN > </FONT > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV >
<DIV align=left > <FONT face=Arial size=2 >Justin Cave </FONT > </DIV >
<DIV align=left > <FONT face=Arial size=2 >Distributed Database Consulting,
Inc. </FONT > </DIV >
<DIV align=left > <FONT face=Arial > <FONT size=2 > <A
href= "http://www.ddbcinc.com/askDDBC " >http://www.ddbcinc.com <SPAN
class=359271110-11062004 >/askDDBC </A > </SPAN > </FONT > </FONT > </DIV >
<DIV > <FONT face=Arial color=#0000ff size=2 > </FONT > </DIV > <BR >
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left >
<HR tabIndex=-1 >
<FONT face=Tahoma size=2 > <B >From: </B > oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B >On Behalf Of </B >NGUYEN
Philippe (Cetelem) <BR > <B >Sent: </B > Friday, June 11, 2004 4:15
AM <BR > <B >To: </B > oracle-l@(protected) <BR > <B >Subject: </B > Tbs READ ONLY
and Snapshot too old <BR > </FONT > <BR > </DIV >
<DIV > </DIV >
<P > <FONT size=2 >Hi List, </FONT > <BR > <FONT size=2 >just a little question
about read only tbs : </FONT > <BR > <FONT size=2 >We have big tables that
are loaded once and never updated during the month , </FONT > <BR > <FONT
size=2 >so I think it 's a good thing to switch their tbs to read only
mode. </FONT > <BR > <FONT size=2 >Web have a lot of script that request
thoses heavy tables to build small tables and we often encounter
ORA-01555 (See ORA-01555.ora-code.com). </FONT > <BR > <FONT size=2 >Do you think that "in general " this
operation could enhance our ORA-01555 (See ORA-01555.ora-code.com) problems ? </FONT > <BR > <FONT
size=2 >TIA </FONT > <BR > <FONT size=2 >Philippe </FONT >
</P > </BLOCKQUOTE > </BLOCKQUOTE > </BLOCKQUOTE > </BLOCKQUOTE > </BODY > </HTML >
|
|
