Get Data Moving 1
Results 1 to 3 of 3

Thread: Oracle CDC Privileges Needed

  1. #1
    Costi Zaboura is offline World Wide Support Manager
    Join Date
    Aug 2006
    Posts
    300
    Rep Power
    14

    Oracle CDC Privileges Needed

    DESCRIPTION

    can you pls specify the previliges needed in order to be able to work with Oracle CDC


    SOLUTION

    In addition to executing the logminer (execute DBMS_LOGMNR, and query v$logmnr_contents, v$log, v$logfile, v$archived_log, v$logmnr_logs) and the queries mentioned bellow:

    select version from dba_registry where comp_id = 'CATALOG';
    SELECT name, value, description FROM v$parameter WHERE name = 'compatible';
    SELECT supplemental_log_data_min FROM v$database;


    The account should also have the privileges to run the following:

    ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
    select distinct table_name from ALL_LOG_GROUP_COLUMNS;
    select distinct table_name from ALL_LOG_GROUPS;

    If you face any problem please send us the Oracle CDC log file so we can know where the problem is.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  2. #2
    Tony Mou is offline APAC Support Manager
    Join Date
    Sep 2006
    Location
    China
    Posts
    35
    Rep Power
    0

    Details and some speical priviledge needed in Oracle 10

    Normally you cannot grant priviledge on v$xxx...directly you need grant to V_$xxx,e.g.

    grant select any table to SCOTT;
    grant execute on dbms_logmnr to SCOTT;
    grant select on v_$logmnr_contents to SCOTT;
    grant select on v_$log to SCOTT;
    grant select on v_$logfile to SCOTT;
    grant select on v_$archived_log to SCOTT;
    grant select on v_$logmnr_logs to SCOTT;
    grant select on dba_registry to SCOTT;
    grant select on v_$parameter to SCOTT;
    grant select on v_$database to SCOTT;

    After that it should be ok for Oracle 9. But for Oracle 10 ,Attunity agent will still report :

    ORA-01031: insufficient privileges error .

    e.g:

    select ... from v$logmnr_contents where ...

    ./oracdclog.c:741 Trace / API Call --->
    --------
    ORACDC.onFetch
    ./oracdcutl.c:598 Trace / Internal Call --->
    --------
    ORACDC.oracdc_set_caller_context
    TUE OCT 31 12:38:03 2006: %ORACDC: ORA-01031: insufficient privileges
    - / Failure in oracdc_fetchNextRow(...)

    I think it is an issue of Oracle 10g itself. e.g you execute it in sqlplus scott/tiger:

    SQL> select * from v$logmnr_contents;
    select * from v$logmnr_contents
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL> select * from v_$logmnr_contents;
    select * from v_$logmnr_contents
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    Some article in the internet says after you grant SELECT ANY TRANSACTION it will be ok.e.g:


    SQL>grant SELECT ANY TRANSACTION to SCOTT;


    I test Oracle agent works fine after that.But not sure if it is the only way to grant v$logmnr_contents to non-dba users.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Tony Mou is offline APAC Support Manager
    Join Date
    Sep 2006
    Location
    China
    Posts
    35
    Rep Power
    0
    Certainly this oracle account show have priviledge to execute LOGMNR procedure.

    e.g

    grant execute on sys.DBMS_LOGMNR to scott;
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

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