Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
1. i assume that you target is oracle, please check user guide for permission:
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
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?
sorry, look like you need verbose logging on Target_load, set verbose, and reload, it should provide the query that Replicate run.
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 😞
at this point, we advise to open a support ticket with Diagnostic Package with verbose logging for support team to review.
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.
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;
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 ?
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.