Oracle Data Integrator for beginners Part 1

 Oracle Data Integrator for beginners Part 1

 I will share my knowledge of ODI with this post by listing some key points about this application.

 From this series of post, you can expect some good information and I will try not to put unnecessary content which most of the people don't like to read also.

 I will start the first post with basic information.

 Basics of Oracle Data Integrator

-  "Oracle Data Integrator" is an application from Oracle corporation often called as "ODI". (People who works in Oracle Data warehouse technology, they always call this application with simple short name "ODI").

- This product is initially developed by  "Sunopsis" company and later it was acquired by Oracle corporation.

- ODI is a data integration tool and comprises E L T architecture.The characters or individual letters have the below full forms.

    E           Extraction
    L           Loading
    T          Transformation 

- If you consider Informatica(Another power leading data integration tool), it works with ETL architecture.

Informatica Logical ETL architecture:

- There are some disadvantages with this type of architecture.

•  Nowadays almost every database is designed with inbuilt algorithm to perform these type of calculation efficiently. So we are missing the usage of this feature with this architecture.
•  Proprietary engine needs additional hardware device(mostly) to participate in data loading.( Highlighted in blue color area in the above image)
• Data moved from source DB to "ETL engine" and then to Target DB. Unnecessary data movement is happening in this case.
 

- In order to overcome this disadvantages, Oracle came with new architecture that is E L T.

- In a simple statement, Informatica is an ETL tool while ODI is E-LT tool.

- So almost whatever you can do in Informatica, you can accomplish the same(more or less) with ODI.

ODI Logical E-LT architecture:

- If you observe the above image, there is no holding of data before loading it to the Target database. Everything is initially moved to Target database directly.

- Then based on business requirements, we will apply business rules to transform the data. 

- After loading the data into some temporary tables in Target database, ODI takes the advantage of database feature to transforming the data.

- Try to understand a small example which relates ODI loading feature.

  You have a table in Database A. You want to load it to Database B. Practically, it takes some time to do it. 
  But if you want to load the table data to another table within the database, obviously it takes little time when compare to above case.  

Let me post some more information in the upcoming topics.

SQL Error: ORA-01940: cannot drop a user that is currently connected

SQL Error: ORA-01940: cannot drop a user that is currently connected
01940. 00000 -  "cannot drop a user that is currently connected"
*Cause:    Attempt was made to drop a user that is currently logged in.
*Action:   Make sure user is logged off, then repeat command.

Issue: When I am trying to drop a user from Oracle database, I am getting the above error though my application is in stopped stated which uses this schema. 
 Basically I am not a DBA to find out SID and SERIAL number and issue command to kill the session.

Solution: Use "Oracle SQL Developer" if you already having it. It is very simple and quick process. I will demonstrate how to achieve this. Just follow below steps.

 We can kill user session with SQL Developer option.

Step 1: Login to SQL Developer with system user account or any other user who are having DBA privileges and try dropping a user.

 

Step 2: Go to Tools -> Monitor Sessions.

Step 3: Select your database connection from the drop down list.

Step 4:It displays all user session which are active.

 Step 5: Select the user session you want to kill and click "Apply" button to kill the session.

 

 Step 6: You will get the below confirmation message.

 

 This demonstration is intended only for testing or demo environments who are learning the technology and not for advanced users and DBA.

DOM_10215 Node is already running in domain

com.informatica.isp.corecommon.exceptions.ISPHardException: [DOM_10215] Node [node01_infoserver] is already running in domain [DOMAIN_INFOSERVER] at [biserver:6005].

Issue: My current project involves maintaining multiple Informatica servers like DEV, SIT, UAT and PROD. Mostly every month cloning activity will happen. So as part of this activity, DBA(Database Administrators) people clone some schema  from PROD database to rest of the databases.

 Ex:-  a) INFODOM (Informatica domain schema)
             b) INFOREP (Informatica repository schema)

 Basically they are taking the above schemas backup in ONLINE mode from PROD environment and placing them and restoring them in DEV(for example).

No need to mention, they obviously revert previous DEV database passwords.

Somehow, after restoring or cloning the Informatica schema, we are not able start Informatica services in DEV environment and when we verify the log(exception.log) from the below path.

D:\Informatica\9.0.1\logs

And getting the below error.

"FATAL [Domain Monitor] [DOM_10215] Node [node01_infoserver] is already running in domain [DOMAIN_INFOSERVER] at [biserver:6005]."

 We have spent lot of time to resolve this issue. But no luck, we haven't find exact solution to fix this issue. But alternatively we found some workaround.

Solution: Stop PROD Informatica services and try to start DEV Informatica services. Once the services are up and running try to update passwords and Database details. Finally start PROD Informatica services.

You are done.

Viewers inputs are highly appreciated if they can provide some inputs or resolution.

Currently I am doing some research on this issue, I can come with solution soon.

Backup Informatica repository with pmrep command

 Today I ran into a tricky situation where Informatica administrator(Admin console) page is not coming up. But repository service and integration service are running fine. I have verified this information by connecting to Informatica client tools.

 Now I have to take Informatica repository backup. How to get rid of this situation...!

 So here is an alternative way to take repository backup.

use pmrep utility.

Step 1: Open command prompt and type pmrep.

 

 Step 2: Enter the below command.  

 

connect -r Info_Rep -n Administrator -x Administrator -h etlserver -o 6005 

-r Repository name

-n Informatica username

-x Password

-h Host name

-o Repository port number

Change the values according to your environment.

 

 Step 3: Make sure you connect to the repository by checking the successful connect message.

 Step 4: Now perform repository backup using below command.

             backup -o Info_Rep_BKP.rep

 Step 5: It takes little to time complete the activity depending on size of your repository.

 

 Step 6: Go to Informatica server repository backup folder and verify your backup file.