Forum Stats

  • 3,816,532 Users
  • 2,259,202 Discussions
  • 7,893,506 Comments

Discussions

ORA-02050: transaction 2.22.1104 rolled back, some remote DBs may be in-doubt in Linux OS

user12251389
user12251389 Member Posts: 333 Blue Ribbon
edited Apr 24, 2015 7:58AM in General Database Discussions

I am performing data extraction using oracle job scheduler from remote database into my local database using database link.The extraction works fine for the other remote database tables but when i try to insert to fetch the data from remote database [email protected]_RETAIL table and try to insert the data locally then i am getting an error as :

ORA-02050: transaction 2.22.1104 rolled back, some remote DBs may be in-doubt

ORA-03114: not connected to ORACLE

ORA-02063: preceding line from FONIC_RETAIL

ORA-03113: end-of-file on communication channel

ORA-02063: preceding line from FONIC_RETAIL

ORA-03113: end-of-file on communication channel

ORA-02063: preceding line from FONIC_RETAIL

I think this issue only happening when i am using Oracle in Linux OS. In window i used Oracle for the same procedure,scheduling job and it works absolutely fine without causing any issue for several days.This error are occured occasionally. The extraction for [email protected]_RETAIL runs fine for sometimes and generates the error again and then it will continue to give the same error. This is serious issue i am facing and did not find any way from this. The [email protected]_RETAIL has REQUEST_XML,RESPONSE_XML columns which contains large xml string value and it has CLOB datatype. And i think the problem is because of this 2 columns as it is clob data type and contains large xml strings and clob has problems when working with remote database. But i am not sure about this issue. Here is my procedure:

PROCEDURE "EXT_SOAP_MONITORING" AS

LAST_SM_ID Number := 0;

BEGIN

--DELETE DATA FROM TEMP_SOAP_MONITORING

EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SOAP_MONITORING';

-- first retrieve the last id (of the newest record) which has been imported at last extraction

SELECT LAST_TASK_ID INTO LAST_SM_ID FROM CAPTURING where DB_TABLE='TEMP_SOAP_MONITORING';

-- retrieve all new records from remote [email protected]_RETAIL and insert it into TEMP_SOAP_MONITORING using MERGE statement

-- MERGE statement is able to retrieve CLOB fields:

merge into TEMP_SOAP_MONITORING TSM

using (

   select * from

(select DISTINCT(ID),REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE from

[email protected]_RETAIL WHERE WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' and ID > LAST_SM_ID order by ID desc) where rownum <=1000

) data

ON (TSM.ID = data.ID)

when not matched then

insert(ID,REQUEST_XML,RESPONSE_XML,WEB_SERVICE_NAME,WEB_METHOD_NAME,CREATE_DATE,ERROR_CODE,ERROR_MESSAGE)

values(data.ID,data.REQUEST_XML,data.RESPONSE_XML,data.WEB_SERVICE_NAME,data.WEB_METHOD_NAME,data.CREATE_DATE,data.ERROR_CODE,data.ERROR_MESSAGE);

COMMIT;

END EXT_SOAP_MONITORING;

Tagged:

Answers

  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Apr 24, 2015 5:29AM

    "ORA-03113: end-of-file on communication channel" normally means you connection has been terminated due to a bug/internal error.

    You will normally find a corresponding ORA-00600 or ORA-07445 in the alert log for that DB.

    Look and if there is one, get the error code & message and do a lookup on Oracle support website using the ORA-00600/07445 lookup tool.

  • user12251389
    user12251389 Member Posts: 333 Blue Ribbon
    edited Apr 24, 2015 5:36AM

    Dom i think this issue only happening when i am using Linux OS. In window i used the same procedure,scheduling job and it works absolutely fine without causing any issue for several days. Do u have any idea about this ?

  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Apr 24, 2015 5:51AM

    Alert log is the starting point because of the ORA-03113.

    I would expect there to be something given the ORA-03113 and if so then it's not guessable, might be specific to version/patch/etc

    If there's not anything in there then do a SQL Trace.

  • user12251389
    user12251389 Member Posts: 333 Blue Ribbon
    edited Apr 24, 2015 6:01AM

    I was searching for this error from last 3 days everywhere in internet also in oracle community but did not find any solution. I think its a bug that has to be reported to Oracle

  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Apr 24, 2015 6:04AM

    Have you looked in the alert log?
    The ORA-03113 is not the error you need to lookup.
    For this error, there is often an underlying ORA-00600 or ORA-07445 which did not filter up to your session but would have been recorded in the alert log and forced the disconnection of your session, after which you received the ORA-03113.

  • user12251389
    user12251389 Member Posts: 333 Blue Ribbon
    edited Apr 24, 2015 6:09AM

    Can you please tell me where do i find the alert log ?

  • Pravin Takpire
    Pravin Takpire Member Posts: 1,762 Gold Trophy
    edited Apr 24, 2015 7:18AM

    Please try and read the documentation, do goggle search, it will give you location of alert.log

    show parameter dump

    check for value of background_dump_dest and there you will have alert_SID.log

    Whenever you have this error there would be associated ORA-07445 or ORA-00600.

    Raise Service Request with Oracle Support.

    regards

    Pravin

  • user12251389
    user12251389 Member Posts: 333 Blue Ribbon
    edited Apr 24, 2015 7:58AM

    Ohh ok i found the path now but just a last question how do i Raise Service Request with Oracle Suppor ? .

This discussion has been closed.