Data Integration

Data Integration

Connect with experts and peers to elevate technical expertise, solve problems and share insights.

 View Only

  • 1.  Oracle Trigger agent fails to create instance

    Posted Wed August 03, 2022 12:17 PM

    I am trying to install Oracle Trigger agent (built 5643) on Oracle 19c on AIX and it fails with the following error when creating the new instance:

    Creating a new instance. Please wait...

    Cannot create changes:You must execute the following statement: grant select on SYS.V_$SESSION to USER_NAME

    Would you like to abort instance creation (y/n) [n]:

    I ran the samples/createuser-ora-nodba.sql that, off course, has a grant select sys.v_$session to the idr user

    Notice also that USER_NAME is not the name of the Oracle user I created and configured for IDR.

    Is this a bug on the product or is something I am doing wrong?

    Thank you so much.



    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------


    #DataIntegration
    #DataReplication


  • 2.  RE: Oracle Trigger agent fails to create instance

    Posted Wed August 03, 2022 01:07 PM
    Daniel

    The first step is to add more tracing to the dmconfigurets tool itself. Once way of doing this is as follows:

    1. Take a backup of <CDC_Install_dir>\conf\dmts64.vmargs file.

    2. Edit "dmts64.vmargs" file, to insert in front of existing text the following 

    -Dcom.datamirror.trace.on=true -Dcom.datamirror.trace.dir=<Log Directory Path> 

    Make sure that it is only one line (which will probably wrap of course) and this directory path is NOT the <cdc_install_dir>/instance as in the following example:

    -Dcom.datamirror.trace.on=true -Dcom.datamirror.trace.dir=/tmp/testlog -Dorg.apache.commons.logging.Log=com.datamirror.ts.util.trace.TraceSimpleLog -Dcom.datamirror.ts.instance=%TSINSTANCE% com.datamirror.ts.bootstrap.Launcher com.datamirror.ts.commandlinetools.script.Startup

    3. Recreate the issue: the additional trace files are generated in the <Log Directory Path> directory.

    4. After troubleshooting restore the original dmts64.vmargs file

    If you don't get anywhere with the additional trace information then you or the customer should open a case.

    Regards

    Robert

    ------------------------------
    Robert Philo
    ------------------------------

    Original Message


  • 3.  RE: Oracle Trigger agent fails to create instance

    Posted Thu August 04, 2022 12:15 PM
    Good day Robert:

    I upgraded agent to build 5344 but the problem persists:

    Log extract at the end.

    I executed a SELECT * FROM SYS.V_$SESSION with the created user and it works, so the user has select granted.
    :
    ...
    31 2022-08-04 10:59:19.361 main{1} com.datamirror.ts.commandlinetools.config.TsInstanceConfig deleteInstance() Coping from /cdcstaging/ibm/IDR/RE_Oracle_Trigger/instance/new_instance2/log/trace_dmts_1_000000.log to /cdcbin/ibm/IDR/RE_Oracle_Trigger/log/trace_dmts_1_000000.log
    32 2022-08-04 10:59:19.361 Process output consumer{69} com.datamirror.ts.util.TsThread run() Thread start
    36 2022-08-04 10:59:30.401 Process output consumer{69} com.datamirror.ts.util.TsThread run() Thread end normal
    37 2022-08-04 10:59:30.401 main{1} com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl applyChanges() You must execute the following statement: grant select on SYS.V_$SESSION to USER_NAME†com.datamirror.ts.commandlinetools.config.TsConfigUserException You must execute the following statement: grant select on SYS.V_$SESSION to USER_NAME| at com.datamirror.ts.commandlinetools.config.TsInstanceConfig.commitFailed(TsInstanceConfig.java:2526)| at com.datamirror.ts.commandlinetools.config.TsInstanceConfig.commit(TsInstanceConfig.java:2247)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.applyChanges(TsConfigurationToolCl.java:3812)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.addInstance(TsConfigurationToolCl.java:3306)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.main(TsConfigurationToolCl.java:566)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.executeCommandLine(TsConfigurationTool.java:306)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.execute(TsConfigurationTool.java:193)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.startTool(TsConfigurationTool.java:119)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.main(TsConfigurationTool.java:83)| at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)| at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:90)| at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)| at java.lang.reflect.Method.invoke(Method.java:508)| at com.datamirror.ts.bootstrap.ClassLoaderHierarchy.startMain(ClassLoaderHierarchy.java:283)| at com.datamirror.ts.bootstrap.Launcher.main(Launcher.java:38)|Caused by: com.datamirror.ts.commandlinetools.config.TsConfigUserException You must execute the following statement: grant select on SYS.V_$SESSION to USER_NAME| at com.datamirror.ts.commandlinetools.config.TsInstanceConfig.saveServerChanges(TsInstanceConfig.java:3518)| at com.datamirror.ts.commandlinetools.config.TsInstanceConfig.commit(TsInstanceConfig.java:2113)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.applyChanges(TsConfigurationToolCl.java:3812)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.addInstance(TsConfigurationToolCl.java:3306)| at com.datamirror.ts.commandlinetools.config.TsConfigurationToolCl.main(TsConfigurationToolCl.java:566)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.executeCommandLine(TsConfigurationTool.java:306)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.execute(TsConfigurationTool.java:193)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.startTool(TsConfigurationTool.java:119)| at com.datamirror.ts.commandlinetools.config.TsConfigurationTool.main(TsConfigurationTool.java:83)| at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)| at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:90)| at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)| at java.lang.reflect.Method.invoke(Method.java:508)| at com.datamirror.ts.bootstrap.ClassLoaderHierarchy.startMain(ClassLoaderHierarchy.java:283)| at com.datamirror.ts.bootstrap.Launcher.main(Launcher.java:38)


    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------

    Original Message


  • 4.  RE: Oracle Trigger agent fails to create instance

    Posted Thu August 04, 2022 12:31 PM
    Daniel

    I would go to IBM Fix Central and download the latest build. 5344 is very old.

    Did you open a case?

    Regards

    Robert


    ------------------------------
    Robert Philo
    ------------------------------

    Original Message


  • 5.  RE: Oracle Trigger agent fails to create instance

    Posted Thu August 04, 2022 03:42 PM
    Sorry, typing error... I refers to 5644.

    And regarding opening a case, yes. I opened a case and I double check if I committed the same mistake in the Case, but not, In the case I wrote 5644.

    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------

    Original Message


  • 6.  RE: Oracle Trigger agent fails to create instance

    Posted Mon August 29, 2022 10:05 AM

    For those who follows this conversation:

     

    The issue was caused because in our installation, there are different users for the metadata schema and for IDR to connect to Oracle.  Remember that in Oracle, to create a schema you have to create a user.

     

    The agent requires a couple of permissions for the user associated to the schema of metadata. Once these permissions were assigned, it worked as expected. The permissions we need to grant where select on sys.v_$session, v$mystat and optionally, if fine grained transaction is required, v$transaction

     

    Thanks  

     

    Daniel Lema

    IT Specialist

     

     




    Original Message