Load Ingest Data
Results 1 to 10 of 10

Thread: Warning: No Permissions to create Oracle directory objects

  1. #1
    mjolly is offline Junior Member
    Join Date
    Sep 2015
    Posts
    6
    Rep Power
    0

    Warning: No Permissions to create Oracle directory objects

    Issue: On establishing the connection with the oracle db source, we see the yellow colored warning: No Permissions to create Oracle directory objects.
    (Attached is screenshot)
    Name:  031.JPG
Views: 1803
Size:  42.2 KB

    Source: Oracle 11.2.0.3
    Target: Amazon redshift cluster

    Type of replication: Full Load

    Privs assigned to attunity user using oracle db as a source:
    SELECT ANY TRANSACTION
    SELECT on V_$ARCHIVED_LOG
    SELECT on V_$LOG
    SELECT on V_$LOGFILE
    SELECT on V_$DATABASE
    SELECT on V_$THREAD
    SELECT on V_$PARAMETER
    SELECT on V_$NLS_PARAMETERS
    SELECT on V_$TIMEZONE_NAMES
    SELECT on V_$TRANSACTION
    SELECT on ALL_INDEXES
    SELECT on ALL_OBJECTS
    SELECT on DBA_OBJECTS
    SELECT on ALL_TABLES
    SELECT on ALL_USERS
    SELECT on ALL_CATALOG
    SELECT on ALL_CONSTRAINTS
    SELECT on ALL_CONS_COLUMNS
    SELECT on ALL_TAB_COLS
    SELECT on ALL_IND_COLUMNS
    SELECT on ALL_LOG_GROUPS
    SELECT on SYS.DBA_REGISTRY
    SELECT on SYS.OBJ$
    SELECT on <any-replicated-table>
    SELECT ANY TABLE
    SELECT on DBA_USERS
    SELECT on DBA_ROLES
    SELECT on DBA_OBJECTS
    SELECT on DBA_SYNONYMS
    SELECT on DBA_SEQUENCES
    SELECT on DBA_TYPES
    SELECT on DBA_INDEXES
    SELECT on DBA_TABLES
    SELECT on DBA_CONSTRAINTS
    SELECT on DBA_TRIGGERS
    EXECUTE on DBMS_METADATA

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    Quote Originally Posted by mjolly View Post
    Issue: On establishing the connection with the oracle db source, we see the yellow colored warning: No Permissions to create Oracle directory objects.
    (Attached is screenshot)
    Name:  031.JPG
Views: 1803
Size:  42.2 KB

    Source: Oracle 11.2.0.3
    Target: Amazon redshift cluster

    Type of replication: Full Load

    Privs assigned to attunity user using oracle db as a source:
    SELECT ANY TRANSACTION
    SELECT on V_$ARCHIVED_LOG
    SELECT on V_$LOG
    SELECT on V_$LOGFILE
    SELECT on V_$DATABASE
    SELECT on V_$THREAD
    SELECT on V_$PARAMETER
    SELECT on V_$NLS_PARAMETERS
    SELECT on V_$TIMEZONE_NAMES
    SELECT on V_$TRANSACTION
    SELECT on ALL_INDEXES
    SELECT on ALL_OBJECTS
    SELECT on DBA_OBJECTS
    SELECT on ALL_TABLES
    SELECT on ALL_USERS
    SELECT on ALL_CATALOG
    SELECT on ALL_CONSTRAINTS
    SELECT on ALL_CONS_COLUMNS
    SELECT on ALL_TAB_COLS
    SELECT on ALL_IND_COLUMNS
    SELECT on ALL_LOG_GROUPS
    SELECT on SYS.DBA_REGISTRY
    SELECT on SYS.OBJ$
    SELECT on <any-replicated-table>
    SELECT ANY TABLE
    SELECT on DBA_USERS
    SELECT on DBA_ROLES
    SELECT on DBA_OBJECTS
    SELECT on DBA_SYNONYMS
    SELECT on DBA_SEQUENCES
    SELECT on DBA_TYPES
    SELECT on DBA_INDEXES
    SELECT on DBA_TABLES
    SELECT on DBA_CONSTRAINTS
    SELECT on DBA_TRIGGERS
    EXECUTE on DBMS_METADATA
    This is from the Replicate User Guide:

    Binary Redo Logs Access Privileges
    n Grant the SELECT on v_$transportable_platformprivilege if the Redo logs are
    stored in ASM.
    -- Grant the CREATE ANY DIRECTORYprivilege in the following configuration:
    Attunity Replicate does not have file-level access to the Redo logs
    The Redo logs are not stored in ASM
    The Oracle database is configured to copy archived Redo logs to a temporary
    directory
    -- Grant the READ on DIRECTORYprivilege if the Oracle user needs to access
    tables created by another user.
    -- If the Oracle Source is configured to copy archived Redo log files to a temporary
    folder, grant the EXECUTE ON DBMS_FILE_TRANSFERprivilege. Also grant
    READprivilege on the Oracle directory object specified as the source directory
    and WRITEprivilege on the directory object specified as the destination directory
    in the copy process

    See also Using Advanced Properties for an Oracle Source.

  3. #3
    mjolly is offline Junior Member
    Join Date
    Sep 2015
    Posts
    6
    Rep Power
    0
    Steve,

    We are conducting a full load only as compared to a CDC.

    1. Does attunity replicate software (endpoint) access redo logs when conducting a full load only ?

    I referred again to the section you mentioned from user guide that applies to the binary redo logs.
    The user guide does not mention if it accesses the redo logs for a full load or if it does a file level scan of data files or tablespace.

    2. More importantly, the user guide does not state why it needs the priv CREATE ANY DIRECTORY.
    The CREATE ANY DIRECTORY is a serious priv and it might affect the storage planning of the database instance if the attunity end point creates large directories in unplanned manner on the prod machine.(PRODUCTION db).

    3. Is the attunity end point first creating the csv files on the source db in a directory and then replicating them to the redshift cluster ?

    4 . FYI: We did not assign the SELECT on v_$transportable_platform, CREATE ANY DIRECTORY, READ on DIRECTORY, EXECUTE ON DBMS_FILE_TRANSFER.
    (We are not sure if Full Load needs the above privs and essentially why it needs those privs on the production database)

    --thnx

  4. #4
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    1. Does attunity replicate software (endpoint) access redo logs when conducting a full load only ?

    ANS: Full load only does not access the redo logs, and type of CDC does.


    I referred again to the section you mentioned from user guide that applies to the binary redo logs.
    The user guide does not mention if it accesses the redo logs for a full load or if it does a file level scan of data files or tablespace.

    2. More importantly, the user guide does not state why it needs the priv CREATE ANY DIRECTORY.
    The CREATE ANY DIRECTORY is a serious priv and it might affect the storage planning of the database instance if the attunity end point creates large directories in unplanned manner on the prod machine.(PRODUCTION db).

    ANS: For Bfile and ASM+copy we need to have you create any directory. You could leave it off but then the DBA must do all of the manual work creating the directories for Replicate, daily and/or more frequent. If the DBA cannot provide such then it should set to use the default of log minor access.

    3. Is the attunity end point first creating the csv files on the source db in a directory and then replicating them to the redshift cluster ?

    ANS: Replicate read your Source DB, make a CSV file on the Replicate server then copy them to S3 folder using the Attunity Cloudbeam server for performance, then issue a copy command to Redshift to copy the file from S3 into Redshift. We do not save the CSV file on S3.

    4 . FYI: We did not assign the SELECT on v_$transportable_platform, CREATE ANY DIRECTORY, READ on DIRECTORY, EXECUTE ON DBMS_FILE_TRANSFER.
    (We are not sure if Full Load needs the above privs and essentially why it needs those privs on the production database)

    ANS: Full load does not need them, (see answer 2 for Bfile and ASM+copy for Directory and DBMS file transfer is for ASM) and we think for CDC you will always need SELECT on v_$transportable_platform

    Thanks,
    Steve Nguyen

  5. #5
    mjolly is offline Junior Member
    Join Date
    Sep 2015
    Posts
    6
    Rep Power
    0
    2. More importantly, the user guide does not state why it needs the priv CREATE ANY DIRECTORY.
    The CREATE ANY DIRECTORY is a serious priv and it might affect the storage planning of the database instance if the attunity end point creates large directories in unplanned manner on the prod machine.(PRODUCTION db).

    ANS: For Bfile and ASM+copy we need to have you create any directory. You could leave it off but then the DBA must do all of the manual work creating the directories for Replicate, daily and/or more frequent. If the DBA cannot provide such then it should set to use the default of log minor access.

    >>>>>>>>>>>>>>>>

    Steve,

    Question: In regards to the above point and your solution, the user guide does NOT provide the list of directories(names) to be created, their required location in the file system, directory permissions, directory purpose ( what type of files shall be placed in them), job or user that writes files to these directories.

    DBA creating the directories manually might be an acceptable solution until we completely under the growth rate of these directories.

    Can you provide the directory related info or point us to the reference documentation for manual creation of directories ?

    --
    Thnx
    Last edited by mjolly; 09-11-2015 at 09:30 PM.

  6. #6
    mjolly is offline Junior Member
    Join Date
    Sep 2015
    Posts
    6
    Rep Power
    0
    Steve,

    We assigned the priv to user attunity on our source db oracle 11g:
    SYS > grant create any directory to attunity;

    On running the replication task we noticed the following directory got created:
    DIRECTORY: ATTUREP_DIR_TEST
    PATH: atturep_dir_test

    The path atturep_dir_test is not a valid directory path.

    Is there a specific reason Attunity creates this path for the attunity directory ?




  7. #7
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    Hello Mjolly,

    The ATTUREP_DIR_TEST is use for internal test communication and can be ignore.

    Also, does the ATTUREP_DIR_TEST get create and remain when you run a task?

    Thanks,
    Steve

  8. #8
    gchaika is offline Junior Member
    Join Date
    Oct 2018
    Posts
    2
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    Hello Mjolly,

    The ATTUREP_DIR_TEST is use for internal test communication and can be ignore.

    Also, does the ATTUREP_DIR_TEST get create and remain when you run a task?

    Thanks,
    Steve
    I know this is older but I'm getting the same error on a brand new install of 6.4.

    I know the Oracle user/PW is correct because I'm able to connect via SQLPLUS at the Command Prompt.

    This is a replacement server for an older Windows 2008 install so the Oracle user I'm trying to create the ENDPOINT connection as already works on another Attunity 4.0 install and has all the Oracle privs needed.


    Name:  Untitled.png
Views: 174
Size:  24.0 KB

    Why else would I get this error? I do not have the license yet for this new install (request to Attunity support in) would that produce this error?

    EDIT: Just got the license and it didn't change a thing. I'm missing something obvious.
    Last edited by gchaika; 06-04-2019 at 09:24 AM.

  9. #9
    gchaika is offline Junior Member
    Join Date
    Oct 2018
    Posts
    2
    Rep Power
    0
    Okay I figured it out.

    Attunity 6.4 now has an "Advanced" feature in Oracle Endpoint management that allows you to use "ATTUNITY LOG READER" instead of "ORACLE LOGMINER". Once I clicked the Advanced tab and switched it back to the Oracle LogMiner the connection tested out just fine.

    Name:  Fix.png
Views: 175
Size:  33.0 KB

  10. #10
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    152
    Rep Power
    12
    Thanks for the feedback!

    Attunity 6.4 now has an "Advanced" feature in Oracle Endpoint management that allows you to use "ATTUNITY LOG READER" instead of "ORACLE LOGMINER". Once I clicked the Advanced tab and switched it back to the Oracle LogMiner the connection tested out just fine.

    The "Log Reader" option was always there.
    I think what happened here is that the default change to "Log Reader".
    "Log Reader" does indeed require a few more privs, but is the Attunity Recommended road forward.
    Oracle is deprecating the Logminer interface.
    For example, Plug-able databases are not supported.
    Logminer puts less stress on the network - only selecting relevant log entries - but puts more stress on the source server.
    Attunity Log Reader reads the entire log over the network - even if only a few tables are selected - but processes more effectively and on the Replicate server, not the source server.

    Cheers,
    Hein.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •