Hemant
LOB is not involvedhow to check the deferred/corrupt rollback segment
Sanjay
On Thursday, November 17, 2016 10:01 PM, "Chitale, Hemant K"
<Hemant-K.Chitale@xxxxxx> wrote:
#yiv4296118734 #yiv4296118734 -- _filtered #yiv4296118734
{font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv4296118734
{panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4296118734
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv4296118734
{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} _filtered #yiv4296118734
{panose-1:2 11 5 2 4 2 4 2 2 3;}#yiv4296118734 #yiv4296118734
p.yiv4296118734MsoNormal, #yiv4296118734 li.yiv4296118734MsoNormal,
#yiv4296118734 div.yiv4296118734MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv4296118734 a:link,
#yiv4296118734 span.yiv4296118734MsoHyperlink
{color:blue;text-decoration:underline;}#yiv4296118734 a:visited, #yiv4296118734
span.yiv4296118734MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv4296118734
p.yiv4296118734MsoAcetate, #yiv4296118734 li.yiv4296118734MsoAcetate,
#yiv4296118734 div.yiv4296118734MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv4296118734
p.yiv4296118734msoacetate, #yiv4296118734 li.yiv4296118734msoacetate,
#yiv4296118734 div.yiv4296118734msoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4296118734
p.yiv4296118734msonormal, #yiv4296118734 li.yiv4296118734msonormal,
#yiv4296118734 div.yiv4296118734msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4296118734
p.yiv4296118734msochpdefault, #yiv4296118734 li.yiv4296118734msochpdefault,
#yiv4296118734 div.yiv4296118734msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4296118734
span.yiv4296118734msohyperlink {}#yiv4296118734
span.yiv4296118734msohyperlinkfollowed {}#yiv4296118734
span.yiv4296118734emailstyle17 {}#yiv4296118734 p.yiv4296118734msonormal1,
#yiv4296118734 li.yiv4296118734msonormal1, #yiv4296118734
div.yiv4296118734msonormal1
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv4296118734
span.yiv4296118734msohyperlink1
{color:blue;text-decoration:underline;}#yiv4296118734
span.yiv4296118734msohyperlinkfollowed1
{color:purple;text-decoration:underline;}#yiv4296118734
p.yiv4296118734msoacetate1, #yiv4296118734 li.yiv4296118734msoacetate1,
#yiv4296118734 div.yiv4296118734msoacetate1
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv4296118734
span.yiv4296118734emailstyle171 {color:#1F497D;}#yiv4296118734
p.yiv4296118734msochpdefault1, #yiv4296118734 li.yiv4296118734msochpdefault1,
#yiv4296118734 div.yiv4296118734msochpdefault1
{margin-right:0in;margin-left:0in;font-size:10.0pt;}#yiv4296118734
span.yiv4296118734EmailStyle29 {color:#1F497D;}#yiv4296118734
span.yiv4296118734BalloonTextChar {}#yiv4296118734 .yiv4296118734MsoChpDefault
{font-size:10.0pt;} _filtered #yiv4296118734 {margin:1.0in 1.0in 1.0in
1.0in;}#yiv4296118734 div.yiv4296118734WordSection1 {}#yiv4296118734 ORA-01555
is also reported in cases of LOB corruption. Are you querying a LOB column ?
Is it possibly trying to read data from a deferred or corrupt rollback (undo)
segment ? Hemant K Chitale From: Sanjay Mishra
[mailto:smishra_97@xxxxxxxxx]
Sent: Friday, November 18, 2016 10:53 AM
To: Chitale, Hemant K; Oracle-L Freelists
Subject: Re: Oracle - Undo questions Hemant In the alert log ? Did you
check Database A as well ? ==> Yes checked both Database A and Database B and
none reported ORA-0155 error and only reported in app logs Does the operation
join tables from A with the tables from B ? ==> No in Select statement it is
all Database B tables but with various subquery Does the target table have an
ON INSERT trigger that does some lookup/validation as well ? ==> No Trigger on
target table Is there a Materialized View that joins the target table with some
other table, that needs to be refreshed ? ==> No Few question 1. Undo
Segment_name for RAC vs non-RAC ==> Is Undo for non-RAC are like _SYSSMU10$ and
RAC are like _SYSSMU10_121212121$ ? 2. Application is showing
_SYSSMU10_121212121$ everytime it failed but database RAC is not showing this
name for Rollback Segment 10. 3. Increased the Undo for all RAC instance by
doubling it and undo retention also to 10hr in sec and monitoring was not
showing that undo usage goes more than even a Gig where Undo is 30G for all
instance. 4. Run Oracle Healthcheck as per Doc 1579035.1 and it was not
showing Undo usage to anywhere close to actual size SQL run for 1-2 hr and
then give errors ORA-01555 and now it is frequently coming on each run.
thanks for any suggestion to be looked into. TIA Sanjay On Thursday,
November 17, 2016 8:44 PM, "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx> wrote:
>> There was no error reported in Database B In the alert log ? Did you
check Database A as well ? Does the operation join tables from A with the
tables from B ? Does the target table have an ON INSERT trigger that does
some lookup/validation as well ? Is there a Materialized View that joins the
target table with some other table, that needs to be refreshed ? Hemant K
Chitale From:oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sanjay Mishra
Sent: Friday, November 18, 2016 12:51 AM
To: mark.powell2@xxxxxxx; Oracle-L Freelists
Subject: Re: Oracle - Undo questions Mark Sorry my mistake and error was
ORA-01555 as that is why mentioned to get help on UNDO details. There was no
error reported in Database B for this error but App team running the query
reported the error. So that is why looking to see if this has to increase the
size on UNDO in Database B or Database A. Database A is the place where data
is inserted Database B is the place from where query initiated from Database A
using Database link is reading the data. Rgds Sanjay On Thursday, November
17, 2016 11:38 AM, "Powell, Mark" <mark.powell2@xxxxxxx> wrote: Sanjay, I
would expect the full error stack to identify where the error occurred but if
you do not have access to the actual error message display then check the alert
log for both databases. There should be an entry for the ORA-01555 error.
From:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf of
Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx>
Sent: Thursday, November 17, 2016 11:25:53 AM
To: Oracle-L Freelists
Subject: Oracle - Undo questions I have a ver big Insert using APPENd hint
running on Database A and it is selecting data from Database B using Dblink
from multiple Tables with lots of joins/subqueries and inserting several
million rows (around 5-10 Mil). It failed few time with ORA-0155 error. 1.
Does that mean issue with UNDO on Database A or B? 2. Is there way to
troubleshoot and get some sizing estimate for UNDO on Database A or Database
B(Based on Point 1) that give some clear picture like quering v$undostat or so
if the issue happened today and Undostat have anyway data for few days.
Thanks for the time Sanjay
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html ;
This email and any attachments are confidential and may also be privileged. If
you are not the intended recipient, please delete all copies and notify the
sender immediately. You may wish to refer to the incorporation details of
Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
