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

user12251389

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;

Dom Brooks

"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

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

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

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

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

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

Pravin Takpire

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

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