Re: transaction or savepoint rollback required
Date: Tue, 16 May 2017 19:07:50 +0000
Message-ID: <DF4PR84MB01242FE88C4E1FC65B2F19DCCCE60_at_DF4PR84MB0124.NAMPRD84.PROD.OUTLOOK.COM>
What changed?
What is the version of each database in the distributed transaction?
Do any of the table have triggers on them that were changed recently or had FK constraints added?
If triggers exist, were any stored procedures called by the trigger modified recently?
Again were you able to manually run the failing SQL so you can compare the error stack returned in SQLPlus verse what you have?
If code which has been working for a while suddenly breaks then almost always some part of the process has in fact changed or you have a data issue.
Mark Powell
Database Administration
(313) 592-5148
From: Sanjay Mishra <smishra_97_at_yahoo.com> Sent: Tuesday, May 16, 2017 2:48:15 PM
To: Powell, Mark; dedba_at_tpg.com.au; dmarc-noreply_at_freelists.org; mark_at_bobak.net Cc: Oracle-L Freelists
Subject: Re: transaction or savepoint rollback required
Mark
After Tracing SQL is identified and it is insert into remote table using select from primary Database table join and it is also failing with same error.
Tx
Sanjay
On Tuesday, May 16, 2017 2:45 PM, "Powell, Mark" <mark.powell2_at_dxc.com> wrote:
If you run the SQL that is failing directly in a SQLPlus window do you get any more information back?
Double check that both DBA_2PC_PENDING and DBA_2PC_NEIGHBORS return "no rows" on all involved servers.
There are only a few things that cause an update to fail such as trying to store a value that exceeds the target column capacity, you try to update a not NULL column to NULL, violate a FK constraint check, or the target row is in use and you get a distributed timeout. I would think each of these would produce an error that would be passed back so the suggestion above.
Mark Powell
Database Administration
(313) 592-5148
From: Sanjay Mishra <smishra_97_at_yahoo.com> Sent: Tuesday, May 16, 2017 12:07:57 PM
To: dedba_at_tpg.com.au; dmarc-noreply_at_freelists.org; mark_at_bobak.net Cc: Powell, Mark; Oracle-L Freelists
Subject: Re: transaction or savepoint rollback required
Tony
There is no database as well as table trigger on Optic based Database link on Remote database. Not able to understand as how to resolved it as eveh bounceing didn;t helped.
Sanjay
On Monday, May 15, 2017 6:52 PM, "dedba_at_tpg.com.au" <dedba_at_tpg.com.au> wrote:
Hi Sanjay
It sounds as if the OPTIC database closes the connection on logon. That could happen if there is a database trigger that fires on logon and raises an exception.
Cheers,
Tony
On 16 May 2017 8:18:41 AM AEST, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote: Mark
AS per error and the dblink OPTIC, the current as well as remote database is not showing any error in alert log. I saw in listener log that request reached OPTIC database link based server.
What is best way on backend to trace distributed query as I tried to trace session but it is not providing any details. This process was working fine but suddently got this issue and now failing continously afterward. I even had bounced the databases but still no success.
TIA
Sanjay
On Monday, May 15, 2017 8:44 AM, Mark J. Bobak <mark_at_bobak.net> wrote:
Ok, so go to the alert.log of the db that is hitting end of fie on communication channel. See if you can determine the root cause of that error.
-Mark
On Fri, May 12, 2017 at 3:39 PM, Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote: Mark
Here is what is getting now
SQL> exec Webprocess_mkinv('14003331');
BEGIN Webprocess_mkinv('14003331')
*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required ORA-02068: following severe error from OPTIC ORA-03113: end-of-file on communication channel ORA-06512: at "invuser.pnttbl", line 31 ORA-06512: at "invuser.pntfail", line 89 ORA-06512: at line 1
Or
ERROR at line 1:
ORA-02067: transaction or savepoint rollback required ORA-06512: at "invuser.pnttbl", line 12 ORA-06512: at "invuser.pntfail", line 52 ORA-06512: at "invuser.pnttrial", line 88 ORA-02055: distributed update operation failed; rollback required ORA-02068: following severe error from OPTIC ORA-03113: end-of-file on communication channel ORA-06512: at "invuser.pntfail" ORA-06512: at line 1
Sanjay
On Friday, May 12, 2017 2:16 PM, "Powell, Mark" <mark.powell2_at_dxc.com<mailto:mark.powell2_at_dxc.com>> wrote:
Sanjay, when a distributed transaction fails it has always been the responsibility of the calling program to issue an explicit rollback command. Fix the program code to do so then determine why the transaction is failing to begin with.
Did you list the full error stack returned to the program?
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> > on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> Sent: Friday, May 12, 2017 11:04:18 AM
To: Oracle-L Freelists
Subject: transaction or savepoint rollback required
Has anyone experienced ths issue with distributed query where I am running a stored procedure but will have mutliple remote database link used including some insert remotely. Working fine but suddenly stopped working and has no savepoint in the code and getting error
ORA-02067: transaction or savepoint rollback required ORA-02055: distributed update operation failed; rollback required
There is no error on existing as well as on remote database in dba_2pc_pending. Any other way to check and rollback or so as it is now constantly generating the same error with every execution
Any help is appreciated
Tx
Sanjay
-- Sent from my Android device with K-9 Mail. Please excuse my brevity. -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 16 2017 - 21:07:50 CEST