Job is falling due to error in Data_Transfer Transformation

[former_member241220]

Hi Experts,

My job is falling in production environment due to below error. Please guide me how to resolve this issue.

(14.2) 11-14-16 09:50:5 (E) (5772:6792) DBS-070300: |Sub data flow DF_Finace_Load_FULL_1|Loader Data_Transfer_SQL_DT__12240_258_1_1 SQL submitted to Oracle Server <(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.26.9)(PORT = 1521)))(CONNECT_DATA =(SID=FINACE)))> resulted in error <ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 521 Serial number: 36348 >. The SQL submitted is <select index_name, status from user_indexes where table_name = 'DT__12240_258_1_1' and table_owner = 'FINACE_DWH' and status = 'UNUSABLE'>. (14.2) 11-14-16 09:50:5 (E) (5772:6792) RUN-051005: |Sub data flow DF_TRAPEZE_ACTUAL_BUS_TRSE_FULL_1|Loader Data_Transfer_SQL_DT__12240_258_1_1 Execution of <Check Table Index After Load> for target <DT__12240_258_1_1> failed. Possible causes: (1) Error in the SQL syntax; (2) Database connection is broken; (3) Database related errors such as transaction log is full, etc.; (4) The user defined in the datastore has insufficient privileges to execute the SQL. If the error is for preload or postload operation, or if it is for regular load operation and load triggers are defined, check the SQL. Otherwise, for (3) and (4), contact your local DBA. (14.2) 11-14-16 09:51:04 (E) (0660:6752) FIL-080134: |Dataflow DF_Finace_Load_FULL|Pipe Listener for DF_Finace_Load_FULL_1 Named pipe error occurred: <The pipe has been ended. > (14.2) 11-14-16 09:51:04 (E) (0660:8088) DFC-250038: |Dataflow DF_Finace_Load_FULL Sub data flow <DF_Finace_Load_FULL_1> terminated due to error <70300>. (14.2) 11-14-16 09:51:09 (E) (0660:8088) DBS-070301: |Dataflow DF_Finace_Load_FULL Oracle <(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.26.9)(PORT = 1521)))(CONNECT_DATA =(SID=FINACE)))> error message for operation <OCITransRollback>: <ORA-03114: not connected to ORACLE >. (14.2) 11-14-16 09:51:11 (E) (10864:5900) DBS-070301: |Dataflow DF_Finace_Load_FULL Oracle <(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.26.9)(PORT = 1521)))(CONNECT_DATA =(SID=FINACE)))> error message for operation <OCITransRollback>: <ORA-03114: not connected to ORACLE>

[Former Member]

Hi Murali,

Can I know what steps are taken to resolve this issue my source is also Oracle and we are using audit table , because even I am facing the same issue.

Please help me to resolve.

Please find the attached screen shot.

capture.png

Regards,

Prathyusha

[titto_antony]

Hi,

This is mostly Oracle database error caused due to the network congestion at db side. Are you using tns connection for connecting to the database? Please check the oracle driver version installed in the server machine and oracle database version. It may happen if the client version installed in server is lower\different from the database version. Also discuss with database DBA and increase value set for parameter SQLNET.SEND_TIMEOUT in the sqlnet.ora file for increasing the connection timeout

[denise_meyer]

http://dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

Ora-03113

[denise_meyer]

This appears to be an Oracle communications error - like network issue or connection with Oracle was broken. Please google this error or check with Oracle for root cause of this error.

[former_member241220]

Hi Aasavari,

Thanks for your reply,

Here my job flow is like below

SQL Transform-->Data_Transfer-->Query1-->Query2-->SAPBW Target.

My source is oracle, I can extract data from source to data_transfer, but from data_transfer to Query1 data is not loading and getting above error. May I know the reason why data not extracting from data_transfer to Query1

(14.2) 11-14-16 09:50:5 (E) (5772:6792) DBS-070300: |Sub data flow DF_Finace_Load_FULL_1|Loader Data_Transfer_SQL_DT__12240_258_1_1 SQL submitted to Oracle Server <(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.26.9)(PORT = 1521)))(CONNECT_DATA =(SID=FINACE)))> resulted in error <ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 521 Serial number: 36348 >.

The SQL submitted is <select index_name, status from user_indexes where table_name = 'DT__12240_258_1_1' and table_owner = 'FINACE_DWH' and status = 'UNUSABLE'>. (14.2) 11-14-16 09:50:5 (E) (5772:6792) RUN-051005: |Sub data flow DF_TRAPEZE_ACTUAL_BUS_TRSE_FULL_1|Loader Data_Transfer_SQL_DT__12240_258_1_1 Execution of <Check Table Index After Load> for target <DT__12240_258_1_1> failed.

Note: This job running production, through this job everyday we are extraction billion of records, due to increase performance I have added Data_Transfer transform.

Thanks

Murali

[aasavaribhave]

can you run this query directly on oracle? This is a fairly generic Oracle error message, make sure your Oracle server and client versions match. Check \ change job design and see if different sql is generated.