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 logsDoes 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 tableIs there a Materialized View that joins the target table with some other table, that needs to be refreshed ?==> No Few question1. 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. TIASanjay

    On Thursday, November 17, 2016 8:44 PM, "Chitale, Hemant K" <Hemant-K.Chitale_at_sc.com> wrote:  

 #yiv8353634594 #yiv8353634594 -- _filtered #yiv8353634594 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv8353634594 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv8353634594 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv8353634594 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;} #yiv8353634594 #yiv8353634594 p.yiv8353634594MsoNormal, #yiv8353634594 li.yiv8353634594MsoNormal, #yiv8353634594 div.yiv8353634594MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;} #yiv8353634594 a:link, #yiv8353634594 span.yiv8353634594MsoHyperlink {color:blue;text-decoration:underline;} #yiv8353634594 a:visited, #yiv8353634594 span.yiv8353634594MsoHyperlinkFollowed {color:purple;text-decoration:underline;} #yiv8353634594 p.yiv8353634594MsoAcetate, #yiv8353634594 li.yiv8353634594MsoAcetate, #yiv8353634594 div.yiv8353634594MsoAcetate {margin:0in;margin-bottom:.0001pt;font-size:8.0pt;} #yiv8353634594 span.yiv8353634594EmailStyle17 {color:#1F497D;} #yiv8353634594 span.yiv8353634594BalloonTextChar {} #yiv8353634594 .yiv8353634594MsoChpDefault {font-size:10.0pt;} _filtered #yiv8353634594 {margin:1.0in 1.0in 1.0in 1.0in;} #yiv8353634594 div.yiv8353634594WordSection1 {} #yiv8353634594 >> 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    

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