ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Topology – Data Server – Which user should be used to connect?

Hi All,

Today we will discuss about which database user can be used at Data Server (Topology) and why. As usual, Oracle database will be used in the example.

When you start with ODI, one of the firsts things to learn is that is necessary configure database connections to reverse the source and target table.

And what every single beginner does? Configures both Data Servers (source and target) using the owner schema of the source and target tables and, after that in the ODI physical schema, choose the same schema as “Data Schema” and “Work Schema”.

This is a natural behavior once he is a beginner but let us make a simple analysis about what was done from Database point of view.

  1. A new set of tables (C$, I$, E$) were added into database with no planning from DA (Data Administrator)
  2. A new application is accessing the database by a user that wasn’t created for it
  3. Thinking about tablespaces and ETL/ELT process a not provided load is happening with no warning to those responsible to manager the database

Some possible effects of this 3 points:

  1. Developers from other softwares than ODI starts to see ODI tables and could interferer in its working dropping a I$ table that “shouldn’t” be there once it doesn’t belongs  to the original schema.
  2. The amount of allowed connections can be not sufficient for the ODI connections
  3. Massive data transfers can interferer in the normal working of source and target systems like:
    1. The target database raise an error about no more Tablespace space, in the Final User GUI at 2:15 PM and a SR to Infra-Structure department is opened
    2. When the DBA, at 2:30 PM, investigates what is happening, he founds nothing once there is the projected space on database
    3. Real situation: A massive process create a huge C$ and a huge I$ that used all tablespace (in that moment the Final User got the error) but as the C$ and I$ are dropped at end of execution, everything comes back to the normal. See bellow:
  2:00 PM 2:15 PM 2:30 PM
Free Tablespace 55 0 55
Normal Use 45 45 45
ODI instantaneous Use 0 55 0

 

at 2:15 PM there is no tablespace to the database works.

at 2:15 PM there is no tablespace to the database works.

Then what do to avoid all of this problems (and a few more not discussed…)???

The solution is very simple and the same as always…  CREATE A USER JUST FOR ODI!  And make it as your Staging Area (Work Schema at Physical Schema).

In this way it will be possible:

  • Define a specific tablespace to it
  • Total security control once it will be necessary to grant access to any table that needs to be read or updated or selected, etc.
  • No interference with systems already established
  • Less ODI objects once only one Data Server (Topology) by database will be necessary.
  • Centralized control of database objects created by ODI once they will be under the same owner/schema

These are some advantages between others!

Well folks, I hope to have helped in the understanding of a “Best Practice” in ODI Administration.

One small tip:

–  Print this post and take it to the DBA from your environment, I can assure you that a lot of questions and problems will be avoid when ODI starts… 😉

As always, comments are very welcome.

Cezar Santos

12 Comments

  1. Hi. My target table does not (cannot) belong to the ODI user (the user has all the rights though). In the integration KM when it tries to insert data, it uses the target table name as .
    Due to this it gives error “table or view does not exist”. Any work-around for this ?

  2. Hi,
    Could you please help me in migration of physical schemas from one environment to other.
    Currently, after export (from old env) and import (into new env), we have to go and manually change the topology connection details to refer to new target instance.
    Is there any way, this manually activity can be automated?

    Thanks

    • Physical are linked to Data Server . If in case the Data Server is the same you can export and import and now interms of making it automated looks into ODI SDK .

  3. I have implemented this best practice but we have had many problems with privileges between different schemas.
    When we defined data server, we define as username the odi work schema and the physical schema was defined with data target schema as schema user and the odi work schema as work schema as the best practice rules.
    All works fine except when the intefaces are created with a truncate table for target table, due to insufficiente privileges, because Oracle does not allow truncate a table if it is not executed from the same schema. The interface execute a truncate of a table of data target schema connected to odi work schema.
    If I change the credentials of DataServer to data target schema, the truncate is possible, but the creation and DDLs on the odi work schema failed due to privileges, which cannot be granted, because these are temporal objects and they exists only while execution.
    Is there a simple way to resolve this problem? I hope to have explain my problem.
    Thanks.
    Hoping to hear from you soon.

  4. Looking for Oracle Data Integrator professionals for Hyderabad/Qatar locations having good experience in Administration. Interested can send CV’s to sandeep.k@talent21.in.

  5. Hi Adriana,

    Please, try the post http://odiexperts.com/?p=594, it could help you.

    Thanks for visit us!

  6. I have a source of Oracle and SQL Server 2005 with the goal, and I have a problem with the model reverse SQL because I see no tables. I was browsing the forums “http://forums.oracle.com/forums/thread.jspa?threadID=1051678&start=0&tstart=0” , but the problem continues without showing any error.

    Please help.

    Thanks

  7. I have a source of Oracle and SQL Server 2005 with the goal, and I have a problem with the model reverse SQL because I see no tables. I was browsing the forums “http://forums.oracle.com/forums/thread.jspa?threadID=1051678&start=0&tstart=0” , but the problem continues without showing any error.

    Please help.

    Thanks

  8. Good post!

    I have query regarding how do you setup your Data schema to access your Work Schema?

    I can’t think of any way without giving privileges like CREATE ANY TABLE, SELECT ANY TABLE, DROP ANY TABLE to the Data schema. This would compromise the security and DBAs won’t be happy to grant these privileges to Data schema on Production Database.

    In ODI Best Practice guide it suggests to have a separate Work Schema, also on forums and blogs I have seen many people have implemented this. But I have not came across anyone discussing how they have implemented this?

    Cheers

  9. Perfect…

    I know that can be improved, my idea was show the simplest way because this language is easily understood by DBA and can be adapted to any security police.

    Thank you very much for the comment!!!!

    Cezar Santos

  10. Excellent.

    Our DBA recommended one better – all permissions for the ODI user are managed by role – the user itself does not have any permissions.

    That way, the role can be applied to other non-ODI ETL processes as well, and if someone tries to hack something using the ODI user, they won’t have the role or any permissions.

Leave a Reply

Required fields are marked *.