Re: Oracle - Undo questions

Hemant 
as per dba_segments the only segment_type found linked to undo are ROLLBACK and TYPE2 UNDO and not DEFERRED Is there anyway we can check if the Undo segment name reported in some old AWR table  Sanjay

    On Thursday, November 17, 2016 10:04 PM, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:  

 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_at_sc.com> wrote:  

 #yiv9604201532 -- filtered {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;}#yiv9604201532 filtered {panose-1:2 4 5 3 5 4 6 3 2 4;}#yiv9604201532 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv9604201532 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv9604201532 filtered {panose-1:2 11 5 2 4 2 4 2 2 3;}#yiv9604201532 p.yiv9604201532MsoNormal, #yiv9604201532 li.yiv9604201532MsoNormal, #yiv9604201532 div.yiv9604201532MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv9604201532 a:link, #yiv9604201532 span.yiv9604201532MsoHyperlink {color:blue;text-decoration:underline;}#yiv9604201532 a:visited, #yiv9604201532 span.yiv9604201532MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv9604201532 p.yiv9604201532MsoAcetate, #yiv9604201532 li.yiv9604201532MsoAcetate, #yiv9604201532 div.yiv9604201532MsoAcetate {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv9604201532 p.yiv9604201532msoacetate, #yiv9604201532 li.yiv9604201532msoacetate, #yiv9604201532 div.yiv9604201532msoacetate {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv9604201532 p.yiv9604201532msonormal, #yiv9604201532 li.yiv9604201532msonormal, #yiv9604201532 div.yiv9604201532msonormal {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv9604201532 p.yiv9604201532msochpdefault, #yiv9604201532 li.yiv9604201532msochpdefault, #yiv9604201532 div.yiv9604201532msochpdefault {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv9604201532 span.yiv9604201532msohyperlink {}#yiv9604201532 span.yiv9604201532msohyperlinkfollowed {}#yiv9604201532 span.yiv9604201532emailstyle17 {}#yiv9604201532 p.yiv9604201532msonormal1, #yiv9604201532 li.yiv9604201532msonormal1, #yiv9604201532 div.yiv9604201532msonormal1 {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv9604201532 span.yiv9604201532msohyperlink1 {color:blue;text-decoration:underline;}#yiv9604201532 span.yiv9604201532msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv9604201532 p.yiv9604201532msoacetate1, #yiv9604201532 li.yiv9604201532msoacetate1, #yiv9604201532 div.yiv9604201532msoacetate1 {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv9604201532 span.yiv9604201532emailstyle171 {color:#1F497D;}#yiv9604201532 p.yiv9604201532msochpdefault1, #yiv9604201532 li.yiv9604201532msochpdefault1, #yiv9604201532 div.yiv9604201532msochpdefault1 {margin-right:0in;margin-left:0in;font-size:10.0pt;}#yiv9604201532 span.yiv9604201532EmailStyle29 {color:#1F497D;}#yiv9604201532 span.yiv9604201532BalloonTextChar {}#yiv9604201532 .yiv9604201532MsoChpDefault {font-size:10.0pt;}#yiv9604201532 filtered {margin:1.0in 1.0in 1.0in 1.0in;}#yiv9604201532 div.yiv9604201532WordSection1 {}#yiv9604201532 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_at_yahoo.com] 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_at_sc.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sanjay Mishra Sent: Friday, November 18, 2016 12:51 AM To: mark.powell2_at_hpe.com; 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_at_hpe.com> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org> 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        

--
http://www.freelists.org/webpage/oracle-l