topic Re: Loading data into existing predefined tables in Qlik Replicate

Loading data into existing predefined tables

Aegis — Wed, 06 Apr 2022 11:38:48 GMT

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!

Re: Loading data into existing predefined tables

Steve_Nguyen — Wed, 06 Apr 2022 11:55:15 GMT

@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_target.htm#ar_oracleds_385117211_1679463

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

Re: Loading data into existing predefined tables

Aegis — Wed, 06 Apr 2022 13:05:01 GMT

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?

Re: Loading data into existing predefined tables

Steve_Nguyen — Wed, 06 Apr 2022 13:12:14 GMT

@Aegis,

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

Re: Loading data into existing predefined tables

Aegis — Wed, 06 Apr 2022 13:33:53 GMT

@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 😞

Re: Loading data into existing predefined tables

Steve_Nguyen — Wed, 06 Apr 2022 14:15:06 GMT

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

Re: Loading data into existing predefined tables

Heinvandenheuvel — Wed, 06 Apr 2022 21:35:37 GMT

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.

Re: Loading data into existing predefined tables

Aegis — Thu, 07 Apr 2022 10:53:06 GMT

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;

Re: Loading data into existing predefined tables

Steve_Nguyen — Thu, 07 Apr 2022 11:26:44 GMT

to ans:

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 ?

Re: Loading data into existing predefined tables

gdoyle — Thu, 07 Apr 2022 19:50:49 GMT

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.

Re: Loading data into existing predefined tables

Steve_Nguyen — Mon, 11 Apr 2022 12:20:31 GMT

@Aegis did you test with not using direct full path load?