Qlik Replicate - dba_objects and attrep_dir_test

[lqthinguyen]

Dear panel,

It would be helpful if a Qlik Replicate expert to enlighten me  the purposes behind the following query and directory within the application; especially when they are being exercised even though I understand the commands themselves

select count(*) from dba_objects where 1 = 0 

Note We're using Oracle version 19c for the SOURCE.  We didn't execute the GRANT permission on DBA_OBJECTS; therefore an error was logged to  the Oracle *.trc file

create or replace directory attrep_dir_test

Note:  We created manually two directories without the attrep* prefix for the ARCHIVED and ONLINE logs instead relying on Qlik Replicate ; yet we received ORA-1031 error.   

Thank you in advance

[SushilKumar]

Hello Team,

The Quires you have raised related to Privileges and Permission related. Request you to Follow help document.

Required permissions | Qlik Replicate Help

if replicate user has not those permission, then in such case it would not be functional as it Supposed to be.

DBA_OBJECTS used by Qlik replicate to get the info of table's metadata and later to capture and process the changes for the participating Tables. Reason for that when Oracle process works internally, they didn't capture changes on the basis of the OBJECT_NAME/TABLE_NAME rather than they use OBJECT_ID. 

About manually two directories which created manually.  Qlik Replicate Muut have File level Access to that Directory, and it must have Read and write privileges assigned at DB and OS level. 

kindly find the snippet from HELP DOC.

" CREATE ANY DIRECTORY
* Qlik Replicate uses following Oracle file access features:

Oracle file features work together with Oracle directories. Each Oracle directory object includes the name of the folder containing the files which need to be processed.

If you want Replicate to create and manage the Oracle directories, you need to grant the CREATE ANY DIRECTORY privilege specified above. Note that the directory names will be prefixed with attrep_. If you do not grant this privilege, you need to create the corresponding directories manually. The names of directories that are created manually must not be prefixed with ATTUREP_, ATTUTMP_, or ATTUGRP_. If you create the directories manually and the Oracle user specified in the Oracle Source endpoint is not the user that created the Oracle Directories, grant the READ on DIRECTORY privilege as well.

If the Oracle source endpoint is configured to copy the Redo log files to a temporary folder, and the Oracle user specified in the Oracle source endpoint is not the user that created the Oracle directories, the following additional privileges are required:

READ on the Oracle directory object specified as the source directory
WRITE on the directory object specified as the destination directory in the copy process."

if it answers your Question then request you to mark it as Accept as Solution "

Regards,

Sushil Kumar

[john_wang]

Hello @lqthinguyen ,

Besides @SushilKumar comment, I'd like to add additional explanations for the 2 SQL usage:

select count(*) from dba_objects where 1 = 0

Replicate utilizes this system data dictionary view to verify the permission granted, and this also why "1= 0" used.

create or replace directory attrep_dir_test

Replicate use this SQL to verify if the create directory permission granted and if it works fine by creating the directory first then drop it as it's unnecessary during task runtime. If DBA creates the needed directories manually prior to the task startup, then the warning about this privilege can be safely ignored.

Hope this helps.

Regards,

John.

[lqthinguyen]

Good evening Sushil Kumar and Mr. John Wang

Thank you for your replies to my post!

https://help.qlik.com/en-US/replicate/November2022/Content/Global_Common/Content/SharedReplicateHDD/...

1. SELECT on DBA_OBJECTS - Required if the Oracle version is earlier than 11.2.0.3.  - I  have interpreted as if the SOURCE ( Oracle ) version is older than 11.2.0.3 then execute the 'GRANT SELECT on DBA_OBJECTS to <username>'.  We are using Oracle 19c therefore the GRANT is NOT required for <username> ?

• Please confirm this for me otherwise I have to execute the GRANT command because Mr. John Wang has explained that "Replicate utilizes this system data dictionary view to verify the permission granted, and this also why "1= 0" used," 

1. CREATE OR REPLACE DIRECTORY  attrep_dir_test

• At a high level, it seems like Replication from SOURCE ( Oracle Database ) to TARGET ( Oracle RDS ) works accordingly.  Mr. Wang has enlightened me to ignore this error/warning because "If DBA creates the needed directories manually prior to the task startup, then the warning about this privilege can be safely ignored. Hope this helps".  

I am doing my diligent with all the ORA- and message that have logged to the *.trc file

Once again THANK YOU!

[john_wang]

Hello @lqthinguyen ,

Good catch! Thank you so the clarification.

We'd like to suggest you open a support ticket and we will trace the issue with R&D, looks to me this line is out of date, we need to keep the documentation updated.

thank you,

John.

[lqthinguyen]

Mr. Wang,

Our SOURCE ( Oracle WE8MSWIN1252 ) and TARGET ( Oracle RDS  AL32UTF8) databases have different values for the NLS_NCHAR_CHARACTERSET parameter.

We want to do a very quick test whether vi 'alter session'  to see if that is the root of our  'ORA-12899:value too large for column DESC_TX (actual:4010, maximum:4000)'  error before executing the 'ALTER DATABASE character set INTERNAL_USE' command.

Anyway we did followed the instructions from the below link

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Oracle-Character-Set-leading-...

and we received the following error message

Direct Path error: Failed converting column 'DESC_TX' data

ORA-12899:value too large for column DESC_TX (actual:3998, maximum:4000)

Our preference is to go through Qlik Replicate instead making the change in the database instance.

Thank you 

[john_wang]

Hello @lqthinguyen ,

UTF8 is super char set of 1252, in general it should be fine. We need additional information to understand the issue. please open a support ticket and attach:

1. both source and target sides table creation DDL

2. verbose task log file

The support team would like to help with this case.

Regards,

John.

[lqthinguyen]

Good morning Mr. Wang,

Thank you for the recommendation; besides the verbose task log file I couldn't view the attrep_apply_exceptions table in order to gather information for the support team

Please enlighten me how to make that possible

Thank you

[john_wang · Accepted solution]

Hello @lqthinguyen ,

This internal table , attrep_apply_exceptions , was created in target side database, as same as other replicated table's location (unless it's positioned to another specific location. please take note the name is in lower case, if the target database is case sensitive). You can query it and save the return set to an Excel format file for support team analysis.

Thanks,

John.

[lqthinguyen]

Good evening,

Thank you