Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aegis
Contributor II
Contributor II

Loading data into existing predefined tables

Hello, I have a question - Is it possible to load data in pre-existing tables in target database(oracle) with - do nothing(if table exists) function with the same names as source database, as a note the target database has some pre-existing data already there.

Reason asking is when I try to do this I get:

Stream component 'st_1_TARGET' terminated
Stream component failed at subtask 1, component st_1_TARGET
Error executing data handler
Endpoint is disconnected
Direct Path error
ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges 

As for the missing permissions, I cannot seem to trace what its missing as Im running session trace and it does not pop any lacking permissions during stream.

Thanks!

 

Labels (3)
1 Solution

Accepted Solutions
gdoyle
Contributor II
Contributor II

In your Target connection settings click on the advanced tab and turn off the checkbox "Use direct full path load". This should resolve the issue for Oracle connections. 

View solution in original post

10 Replies
Steve_Nguyen
Support
Support

@Aegis,

 

1. i assume that you target is oracle, please check user guide for permission:

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Oracle/security_req_oracle...

 

2. you can also, enable target_load ,, trace and review log to see what cause the failure, like query that Replicate try to run and fail

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Aegis
Contributor II
Contributor II
Author

02164417: 2022-04-06T15:00:26 [SOURCE_UNLOAD   ]T:  Supplemental logging is not returned by the last query  (oracle_endpoint_utils.c:90)
02164417: 2022-04-06T15:00:26 [SOURCE_UNLOAD   ]T:  Initializing table 'SCHEMA'.'COMPANY' for unload succeeded  (oracle_endpoint_unload.c:387)
02164417: 2022-04-06T15:00:26 [SOURCE_UNLOAD   ]T:  Start unload loop for the table 'SCHEMA'.'COMPANY'  (oracle_endpoint_unload.c:397)
02164418: 2022-04-06T15:00:26 [SOURCE_CAPTURE  ]T:  Free component Utility Source  (oracle_endpoint.c:51)
02164418: 2022-04-06T15:00:26 [TARGET_LOAD     ]T:  Free component st_1_TARGET1  (oracle_endpoint.c:51)
02164418: 2022-04-06T15:00:26 [INFRASTRUCTURE  ]T:  Thread ID 7fe8a8f29700 has completed with status 1020414  (ar_apr.c:334)
02164418: 2022-04-06T15:00:25 [TARGET_LOAD     ]E:  ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges  [1022307]  (oracle_endpoint_load.c:1598)
02164418: 2022-04-06T15:00:25 [TARGET_LOAD     ]E:  Direct Path error [1022307]  (oracle_endpoint_load.c:1598)
02164418: 2022-04-06T15:00:25 [TARGET_LOAD     ]E:  Endpoint is disconnected [1020414]  (endpointshell.c:3971)
02164418: 2022-04-06T15:00:25 [TARGET_LOAD     ]E:  Error executing data handler [1020414]  (streamcomponent.c:1998)
02164418: 2022-04-06T15:00:25 [TASK_MANAGER    ]E:  Stream component failed at subtask 1, component st_1_TARGET1  [1020414]  (subtask.c:1396)
02164418: 2022-04-06T15:00:25 [TARGET_LOAD     ]E:  Stream component 'st_1_TARGET1' terminated [1020414]  (subtask.c:1565)
02164418: 2022-04-06T15:00:26 [UTILITIES       ]T:  No notification is defined for trigger of type 'GENERAL_ERROR'  (notification_manager.c:1044)

 The permissions listed are granted, but the error remains as it is, any ideas?

Steve_Nguyen
Support
Support

@Aegis,

 

sorry, look like you need verbose logging on Target_load, set verbose, and reload, it should provide the query that Replicate run.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Aegis
Contributor II
Contributor II
Author

@Steve_Nguyen 

 

02166038: 2022-04-06T15:23:13:976115 [TARGET_LOAD ]T: Going to prepare the statement 'select i.index_name, i.tablespace_name, NVL(c.column_name,' '), NVL(c.DESCEND, 'ASC') from all_indexes i, all_ind_columns c where i.index_name = c.index_name and (i.INDEX_TYPE = 'NORMAL' or i.INDEX_TYPE = 'FUNCTION-BASED NORMAL') and i.table_name = :1 and i.owner = c.index_owner and i.table_owner = :2 and c.table_name = :1 and c.table_owner = :2 and i.uniqueness = 'UNIQUE' order by c.index_name, c.column_position for 'SCHEMA.COMPANY' (oracle_endpoint_table.c:1537)
02166038: 2022-04-06T15:23:13:977223 [TARGET_LOAD ]T: Table schema 'SCHEMA.COMPANY' is completed (oracle_endpoint_table.c:2214)
02166038: 2022-04-06T15:23:13:990416 [TARGET_LOAD ]V: Oracle error code is '604' (oracle_endpoint_load.c:1598)
02166038: 2022-04-06T15:23:13:990456 [TARGET_LOAD ]T: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges [1022307] (oracle_endpoint_load.c:1598)
02166038: 2022-04-06T15:23:13:990467 [TARGET_LOAD ]T: Direct Path error [1022307] (oracle_endpoint_load.c:1598)
02166038: 2022-04-06T15:23:13:990478 [TARGET_LOAD ]T: Endpoint is disconnected [1020414] (endpointshell.c:3971)
02166038: 2022-04-06T15:23:13:990485 [TARGET_LOAD ]T: Error executing data handler [1020414] (streamcomponent.c:1998)
02166038: 2022-04-06T15:23:13:990501 [TARGET_LOAD ]T: Stream component 'st_2_TARGET1' terminated [1020414] (subtask.c:1565)
02166038: 2022-04-06T15:23:14:56900 [TARGET_LOAD ]T: Free component st_2_TARGET1 (oracle_endpoint.c:51)
02166038: 2022-04-06T15:23:13:990456 [TARGET_LOAD ]E: ORA-00604: error occurred at recursive SQL level 1 ORA-01031: insufficient privileges [1022307] (oracle_endpoint_load.c:1598)
02166038: 2022-04-06T15:23:13:990467 [TARGET_LOAD ]E: Direct Path error [1022307] (oracle_endpoint_load.c:1598)
02166038: 2022-04-06T15:23:13:990478 [TARGET_LOAD ]E: Endpoint is disconnected [1020414] (endpointshell.c:3971)
02166038: 2022-04-06T15:23:13:990485 [TARGET_LOAD ]E: Error executing data handler [1020414] (streamcomponent.c:1998)
02166038: 2022-04-06T15:23:13:990494 [TASK_MANAGER ]E: Stream component failed at subtask 2, component st_2_TARGET1 [1020414] (subtask.c:1396)
02166038: 2022-04-06T15:23:13:990501 [TARGET_LOAD ]E: Stream component 'st_2_TARGET1' terminated [1020414] (subtask.c:1565)

 

Does not really give me an idea which is the failing query 😞 

Steve_Nguyen
Support
Support

at this point, we advise to open a support ticket with Diagnostic Package with verbose logging for support team to review.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist II
Specialist II

Hmmm, maybe it is the statement:

select i.index_name, i.tablespace_name, NVL(c.column_name,' '), NVL(c.DESCEND, 'ASC') from all_indexes i, all_ind_columns c where i.index_name = c.index_name and (i.INDEX_TYPE = 'NORMAL' or i.INDEX_TYPE = 'FUNCTION-BASED NORMAL') and i.table_name = :1 and i.owner = c.index_owner and i.table_owner = :2 and c.table_name = :1 and c.table_owner = :2 and i.uniqueness = 'UNIQUE' order by c.index_name, c.column_position

 

It would be good to verify that to work. Can you run that in a SQLplus (or SQLdeveloper) session with a connection using the same credentials as Replicate is given and substitute the '<tablename>' as ":1" and '<schema>' as ":2"

Which Oracle version on target? Surely more recent than version 11? There was an issue with (tablename) synonyms in the version: https://knowledge.informatica.com/s/article/299673?language=en_US

Hein.

Aegis
Contributor II
Contributor II
Author

Tested - works on both sides. Running out of ideas 😅

 

To jump back also a bit - question 

Is it possible to load data in pre-existing tables in target database(oracle) with - do nothing(if table exists) function with the same names as source database, as a note the target database has some pre-existing data already there?

EDIT: I granted DBA to attunity to test, and well, it works, problem is - when I enable trace for 1031(alter system set events '1031 trace name errorstack level 10';) - even when it says no permission - it does not trigger the entry, so im baffled. any ideas?

 

Current Grant list asides from DBA

GRANT SELECT ANY TRANSACTION to attunity_aps;
GRANT SELECT on V_$ARCHIVED_LOG to attunity_aps;
GRANT SELECT on V_$LOG to attunity_aps;
GRANT SELECT on V_$LOGFILE to attunity_aps;
GRANT SELECT on V_$DATABASE to attunity_aps;
GRANT SELECT on V_$THREAD to attunity_aps;
GRANT SELECT on V_$PARAMETER to attunity_aps;
GRANT SELECT on V_$NLS_PARAMETERS to attunity_aps;
GRANT SELECT on V_$TIMEZONE_NAMES to attunity_aps;
GRANT SELECT on GV_$TRANSACTION to attunity_aps;
GRANT SELECT on V_$CONTAINERS to attunity_aps;
GRANT SELECT on ALL_INDEXES to attunity_aps;
GRANT SELECT on ALL_OBJECTS to attunity_aps;
GRANT SELECT on ALL_TABLES to attunity_aps;
GRANT SELECT on ALL_USERS to attunity_aps;
GRANT SELECT on ALL_CATALOG to attunity_aps;
GRANT SELECT on ALL_CONSTRAINTS to attunity_aps;
GRANT SELECT on ALL_CONS_COLUMNS to attunity_aps;
GRANT SELECT on ALL_TAB_COLS to attunity_aps;
GRANT SELECT on ALL_IND_COLUMNS to attunity_aps;
GRANT SELECT on ALL_LOG_GROUPS to attunity_aps;
GRANT SELECT on SYS.DBA_REGISTRY to attunity_aps;
GRANT SELECT on SYS.OBJ$ to attunity_aps;
GRANT SELECT on SYS.ENC$ to attunity_aps;
GRANT SELECT on DBA_TABLESPACES to attunity_aps;
GRANT SELECT on ALL_TAB_PARTITIONS to attunity_aps;
GRANT SELECT on ALL_ENCRYPTED_COLUMNS to attunity_aps;
GRANT SELECT on ALL_VIEWS to attunity_aps;
GRANT SELECT ANY TABLE to attunity_aps;
GRANT ALTER ANY TABLE to attunity_aps;
GRANT SELECT on all_nested_tables to attunity_aps;
GRANT SELECT on all_nested_table_cols to attunity_aps;
GRANT CREATE SESSION to attunity_aps;
GRANT EXECUTE on DBMS_LOGMNR to attunity_aps;
GRANT SELECT on V_$LOGMNR_LOGS to attunity_aps;
GRANT SELECT on V_$LOGMNR_CONTENTS to attunity_aps;
GRANT LOGMINING to attunity_aps;
GRANT SELECT on V_$TRANSPORTABLE_PLATFORM to attunity_aps;
GRANT SELECT ON V_$DATABASE_INCARNATION to attunity_aps;
GRANT CREATE ANY DIRECTORY to attunity_aps;
GRANT DBMS_FILE_TRANSFER to attunity_aps;
GRANT EXECUTE ON DBMS_FILE_TRANSFER  to attunity_aps;
GRANT DBMS_FILE_GROUP to attunity_aps;
GRANT EXECUTE ON DBMS_FILE_GROUP to attunity_aps;
GRANT DROP ANY DIRECTORY to attuntiy_aps;
GRANT CREATE ANY TABLE to attuntiy_aps;
GRANT CREATE ANY INDEXES to attuntiy_aps;
GRANT ALTER ANY TABLE to attuntiy_aps;
GRANT DROP ANY TABLE to attuntiy_aps;
GRANT INSERT ANY TABLE to attuntiy_aps;
GRANT UPDATE ANY TABLE to attuntiy_aps;
GRANT DELETE ANY TABLE to attuntiy_aps;
GRANT SELECT ANY TABLE to attuntiy_aps;
GRANT SELECT all_indexes to attuntiy_aps;
GRANT SELECT all_ind_columns to attuntiy_aps;
GRANT SELECT all_constraints to attuntiy_aps;
GRANT SELECT all_cons_columns to attuntiy_aps;

 

Steve_Nguyen
Support
Support

to ans:

Is it possible to load data in pre-existing tables in target database(oracle) with - do nothing(if table exists) function with the same names as source database, as a note the target database has some pre-existing data already there?

 

ANS: YES

 

====

ask in term of permission, if you grant DBA and it works, then it mean that the error is correct. we just need to isolate to what error .

 

have you try to un check direct path on the target endpoint advanced tab ?

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
gdoyle
Contributor II
Contributor II

In your Target connection settings click on the advanced tab and turn off the checkbox "Use direct full path load". This should resolve the issue for Oracle connections.