Windows 64bit Informatica 9.0.1 Server Complete Installation

Informatica 9.0.1 Server Installation:

 This post provides complete step by step instruction for installation and configuration of Informatica PowerCenter 9.0.1. This includes the installation of server components, configuration of different Informatica services and client installation and configuration.

Pre-requisites of Informatica Installation:

It is needed to create a schema in an oracle database while installing Informatica server. This schema will be have the metadata repository. And this data will be used by Informatica both server and client. Metadata can include information such as mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets.

Script to create a tablespace in Oracle Database:

CREATE TABLESPACE INFAREP_TS DATAFILE 'D:\app\hp\oradata\orcl\infarep_ts.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;

Note: 

Datafile path should be your machine oracle installed path where the tablespace will be created after executed the above query.

SQL Syntax to create schema to hold metadata:

CREATE USER INFAREP

IDENTIFIED BY INFAREP

DEFAULT TABLESPACE INFAREP_TS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

SQL syntax to extend password life to lifetime:

ALTER profile DEFAULT limit password_life_time UNLIMITED;

Script to create role that will be given to user:

Create role SSE_ROLE;

Grant connect, resource to SSE_ROLE;

Grant select any table to SSE_ROLE;

Grant create session to SSE_ROLE;

Grant insert any table, delete any table, update any table to SSE_ROLE;

SQL Script to create schema to hold metadata:

GRANT SSE_ROLE TO infarep;

GRANT GLOBAL QUERY REWRITE TO infarep;

GRANT CREATE MATERIALIZED VIEW TO infarep;

GRANT CREATE TABLE TO infarep;

GRANT CREATE ANY VIEW, DROP ANY VIEW TO infarep;

GRANT CREATE ANY SEQUENCE, DROP ANY SEQUENCE TO infarep;

GRANT CREATE ANY INDEX, DROP ANY INDEX TO infarep;

GRANT UNLIMITED TABLESPACE TO infarep WITH ADMIN OPTION;

Note: All Scripts shown above should be executed in the same sequence as specified above before starting the installation.

Informatica 9.0.1 Server Installation:

Step1: Download all the necessary files and unpack and keep it in folder. Now start Installation of server from the folder by Double clicking on “install.exe”

Install Anywhere window will open and will guide through the next step

Step2: Installation wizard starts. Click the radio button as shown in screenshot below and click on “Next”.

Step3: Installation Pre-requisites will be shown before the installation starts as below. Click “Next”.

Step4: Enter the license key. You can locate the license key from the desired path where the file “Oracle_All_OS_Prod.key” is there in your system.

Click “Next”.

Note: if the  oracle key store place path have space then it won't go the next step. It will give error like below screen shot, If it is not happened to you just move to next step

Step5: Pre-installation summary will give the items installed during the installation process based on the license key.

Click “Install” 

Step6: Installation Begins. It takes couple of minutes to finish. Soon after completion of this step, Configuring Domain window opens. Continue the steps from Domain Configuration. 

Domain Configuration Steps:

Step7: Creating a domain

o Choose “Create a Domain” radio button.

o Un-Check “Enable HTTPS for Informatica Administrator”

o Leave the Port number as it is and choose “Use a keystore file generated by the installer”

Click “Next”. 

Step8: Provide the Repository database details as below.

o Database Type: Choose your Repository database (Oracle/SQL Server/Sybase)

o Database user ID: Database user ID to connect database.

o User Password: Password to connect to database.

o Schema Name: If Schema name is not provided default schema will be used.

o Database Address and Port: Machine on which database in installed and default port number.

o Database Service Name: Database Name.

Below image shows the configuration using Oracle. Click “Next”.

Step9: You can give the Domain details, Admin user details now.

o Domain Name: Name of your Domain.

o Node Host Name: Machine name on which Informatica Server is running.

o Node Name: Name of the Node.

o Node Port Number: Leave the default port Number.

o Domain user name : This is the Administrator user

o Domain password : Administrator password

Note: Remember the Admin User ID, Password to log on to Admin Console later in the installation. 

Step10: Use the default configuration and Click “Next”. 

Step11: Installation is complete and you get the post-installation summery. You get a link to the installation log file and a link to Admin console. Click “Done”. 

************************************************************************************************************

Important Note:

with the above step we have successfully installed the server. Now in order to configure the Power Center Repository and Integration Services, there are few things we have to make sure. In my case I'm installing theInformatica Client and Server in my local machine were I have my Oracle Server database 64 bit. However in order to configure the Powercenter Repository Services we should also install Oracle Client 32 bit in the same machine. The reason is Informatica can talk to the server only through the 32 bit Client machine.

1. Install Oracle 32 bit client in your system . click here for Oracle 32bit Client Download Link

b. Copy the tnsnames.ora file from installed oracle server 64bit path to to the client path.
*********************************************************************************

Configuring / Creating Repository Services:

Step1: Go to Start menu and Click on “Informatica Administrator Home Page”. This will open up the Admin Console in a web browser. 

Step2: Log on to Admin console using your Admin User ID and Password. You set your Admin User ID and Password in “Step9” above.

Step3: Once you Log on you will see the Screen just like shown below.

Step4: Choose your Domain Name from “Domain Navigator”, Click on “Actions”, Choose “New” and “PowerCenter Repository Service”.

Step5: A new screen will appear, in which you need to fill in details. 

Step6: Fill in following details.

o Repository Name: Appropriate Repository Name.

o Description: An optional description about the repository.

o Location: Choose the Domain you have already created. If you have only one Domain, this value will be pre populated.

o License: Choose the license key from the drop down list.

o Node: Choose the node name from the drop down list.

Click “Next”. 

Step7: A new screen will appear, provide the Repository database details.

o Database Type : Choose your Repository database (Oracle/SQL Server/Sybase)

o Username: Database user ID to connect database.

o Password: Database user Password.

o Connection String: Database Connection String.

o Code Page: Database Code Page

o Table Space: Database Table Space Name

o Choose “No content exists under specified connection string. Create new content”

Click “Finish”.

Step8: It takes couple of minutes create Repository content. After the repository creation below screen will be seen.

Step9: The repository service will be running in “Exclusive” mode as shown below. This needs to be change to “Normal” before we can configure Integration service. Click “Edit” Repository Properties. 

Step10: A pop up window appears, set the properties

o Operation Mode : Normal

o Security Audit Trail : No
Click “OK”.

State is now changed to “Normal” mode.

Configuring Integration Services:

Step1: Choose your Domain Name from “Domain Navigator”, Click on “Actions”, Choose “New” and “PowerCenter Integration Service”.

Step2: Following screen appears. 

Step3: A new window will appear, provide the details as shown below.

o Name: Desired Integration Service Name.

o Description: An optional description about the repository.

o Location: Choose the Domain you have already created. If you have only one Domain, this value will be pre populated.

o License: Choose the license key from the drop down list.

o Node: Choose the node name from the drop down list.

Click “Next”.

Step4: A new window will appear, provide the details as shown below.

o PowerCenter Repository Service: Choose your Repository Service Name from the drop down list.

o Username: Admin user name.

o Password: Admin password.

o Data Movement Mode: ASCII.

Click “Finish”. 

Step5: A pop up window will appear, Choose the Code Page as ANSI. Click “OK”.

Step6: Window will be closed and you can see all the configured services in the “Domain Navigator”.

With that we are all done with the installation and configuration for Informatica PowerCenter Server 9.0.1.

we will see Complete step for Installing Informatica Client Installation in the next Post

Happy Blogging!
Bose

Step by Step Installation of Oracle 11g 64bit in Windows7 64bit

This Post will give you step by step information of Oracle database 11g 64 bit version installation in windows 7 64 machine. If you prefer with Oracle Database 11g installation in Windows 64bit machine, This post will help you to move forward.

Prerequisites Checks:

Requirement	Minimum Value
Physical memory (RAM)	1 GB minimum On Windows 7 and Windows 8, 2 GB minimum
Virtual memory	If physical memory is between 2 GB and 16 GB, then set virtual memory to 1 times the size of the RAM If physical memory is more than 16 GB, then set virtual memory to 16 GB
Disk space	Total: 5.39 GB
Processor Type	AMD64, or Intel Extended memory (EM64T)
Video adapter	256 colors
Screen Resolution	1024 X 768 minimum

Download oracle Database from oracle site and unarchive the zipped download file.

Step1: 

Just double click on setup.exe from the unarchived folder of oracle database source.

Step2:

Oracle Universal Installer will check the disk space and initiates the installation

Step3:

If you have a oracle metalink ID then type your email and oracle support password in the provided space with the below screen. Otherwise uncheck the 'I wish to receive security updates via My Oracle Support' option.

Once we unchecked the option with the above screen we will get a popup like below screen. just Click 'Yes' with this popup window and Click 'Next' with above screen

Step4:

If we are installing oracle database in our machine first time then go ahead with first option 'Create and Configure a database' with the below screen. If not, select appropriate option which you want and then click 'Next'

Step5:

Select 'Server Class' to install of full feature of oracle database 11g 64 bit and click 'Next' with below screen

Step6:

As we are not going to install in cluster database, select 'Single Instance database installation' and then click 'Next' with the below screen

Step7:

Select 'Typical Install' and then click 'Next'

Step8:

It will automatically select drive and folder to install database. If you want to change the installation folder you can browse the custom folder you have been created

This screen will ask some more details:

Global Database Name: ORCL (if we want to change it we could name which we want)

Type administrator password and keep remember the password. because this password will help us to login into database with the user sys and system.

and then click 'Next'

Step9:

This steps checks all the prerequisites such as disk space, memory, environmental variable and so on, so once it is done we will get next screen automatically

Step10: 

This summary steps gives us the option 'Save Response File' to store all installation related folder information in a text file.

Just click 'Finish' with this step to initiate the product installation

Step11:

The next few screen will show the progress of oracle database installation such as installation preparation, copying folder to the target folder and setup files

At any of of point if windows security comes like below screen just click 'Allow Access' to continue the installation

After the automatic file copy and configuration it will start creating and configuring some of the default database schema and users automatically

In Database configuration Assistant select 'Password Management'

uncheck 'Lock Account?' column for the users to you want unlock and set the  password. Here I have unlocked the users HR, SH and OE will help us in future to do some sample work with OBIEE and then click 'OK' with the below and above screen.

Step12:

with above steps the installation  and configuration comes to end. So Just click 'Close' with the below screen to complete the installation.

Happy Blogging!!!

Bose

ORACLE BASIC Developer & DBA COMMANDS

After DB installation Ensure the following services are running.

1. Oracle service<Instance Name>

2. Oracle listener service

Goto sqlplus enter userid/pwd or userid/pwd@service_name

If the username and pwd is correct, we would be logged into the oracle and will get the SQL Prompt

SQL>

To Create Table Space:

Create a folder in local drive like BOSE_DBF

SQL> CREATE TABLESPACE KK DATAFILE 'E:\BOSE_DBF\BOSE.DBF' SIZE 50M;

Tablespace created.

To Increase the Tablespace Size:

• Increasing the Existing Datafile

SQL> ALTER DATABASE DATAFILE 'E:\BOSE_DBF\BOSE.DBF' RESIZE 100M;

Database altered.

Appending additional DATA file to the Tablespace

SQL> alter tablespace kk add datafile 'e:\bose_dbf\bose1.dbf' size 20M;

Tablespace altered.

To create DB user:

SQL> create user <username> identified by <password> default tablespace <Tablespace Name>

Temporary tablespace <Temporary tablespace name>;

SQL> create user bose identified by bose default tablespace bose temporary tablespace temp;

User created.

SQL> show user;

USER is "SYS"

Granting Access to the USER:

SQL> grant create session,imp_full_database to <user name>

SQL> grant create session,imp_full_database to bose;

Grant succeeded.

Granting DBA Access to the USER:

SQL> grant dba to user

SQL> grant dba to bose;

Grant Succeeded

Altering USER:

SQL> alter user bose quota unlimited on <username>;

SQL> alter user bose quota unlimited on bose;

User altered.

Now we will exit from the sys user and will connect our newly created to user to import or export the db objects

SQL>conn bose/bose

Syntax for Import:

• C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> full=y

C:\>imp bose/bose file=bose.dmp log=boseimp.log full=y

• C:\>imp <username>/<password> file=<dumpname.dmp> log=<logfilename.log> fromuser=<fromusername> touser=<tousername>

• Before import we need to check the tablespace free space.

SYSTEM -200M

INDX -100M

DEFAULT TABLESPACE (BOSE) 

C:\>imp bose/bose file=bose.dmp log=boseimp.log fromuser=hr touser=bose

Import completed successfully with warnings.

After Import Connect to the Respective DB user

C:\>sqlplus

Enter username: bose/bose

SQL>@c:\compile – it will compile all the Invalid objects run the compile script atlease 4 times.

Exporting DB user:

Syntax:

C:\> exp <username>/<password> file=<filename.dmp> log=<logname.log>

C:\>exp bose/bose file=bose.dmp log=kk.log

.

.

Export terminated successfully without warnings.

Taking full DB Export:

 C:\>exp system/<system pwd> file=<filename.dmp log=logfile.log full=y

C :\> exp system/system file=fulldump.dmp log=fulldump.log full=y

To drop DB user:

SQL> drop user kk cascade;

              User dropped.

To drop DB user object:

Connect to specified Database

SQL> CONN bose/bose

SQL> SHOW USER

BOSE

SQL>@C:\drop.sql

To Drop Tablespace:

SQL> drop tablespace bose including contents;

Tablespace dropped.

SQL> select username from dba_users:

It will display all the username in the DB

SQL> select name from v$database;

NAME

ORCL

It will display the DB name

SQL> select count(*) from user_objects;

           It will display the number of objects in that schema.

Cheers!

Bose

Step by Step installation of Oracle Client in Windows 7

I am giving here the complete step by step installation of  Oracle Client 32bit installation in Windows7 64 bit machine environment. If you are planning to install oracle client in windows machine this post will help you install completely.

Step 1: 

Download oracle client software from oracle site and unarchive the zip file and click on setup.exe from the unarchived client folder

Step 2: 
Oracle Universal Installer starts to initiate the installation with the below screen

Step 3:
Actually from here actual only actual installation steps begins. Select the type as per your needs. Here I am having the master database installed in another machine in the network so going with 'Administrator' installation to have a complete installation and features of Oracle Client

Step 4:
Select the languages support you need to be have with this installation. Here I have selected all the languages.

Step 5: 
This step is an important step in Oracle Client Installation. If you are having database installed already in the machine and if you are installing oracle Client also on the same machine then It will select the same path where database is installed automatically.

I suggest always to create a separate folder and Install it in separate place to differentiate database and client easily.

Step 6:
once you clicked with 'Next' button with above screen, we will get the below screen. With this screen we have the button 'Save Response File' to save the installation details in a file and then Click 'Finish'

Step 7:
Once we clicked 'Finish' with above screen installation of client starts and It takes a while to install all the necessary files and details in folder.

Step 8:
After successful completion of installation we will be getting below screen. Just click 'Close' to complete installation.

Happy Blogging!!!

Bose

Right Click Problem "Creating New Data Server in ODI 11g"

I have installed Oracle Data Integrator 11g (ODI 11.1.1.7.0) successfully my laptop. But I couldn't create New Data Server for my new project. So here is the problem and solution.

Problem:
when we are trying to create New Data Server on right clicking in Physical Architecture in Topology Manager, We may be surprised because It doesn't respond and problem looks very odd.

Solution:
The solution is simple but weird, Refer Step1 and step2 for the solution below:

Step1:

Open ODI Studio -> Select 'Topology Manager' -> Expand Physical Architecture and select the database type which you want to create new data server

Instead of right clicking on "Oracle", Just click on the "+" symbol which is used to to expand the database type. ( For your reference, I have indicated in the red box in the above screenshot)

                                 

Step2:

After clicking on the "+" symbol, we will get the right click option to create 'New Data Server".

           

 Simple!!! Isn't it??!!

Cheers!
Bose

Connecting Essbase with OBIEE 11g - A Complete Step by Step Guide

This post is containing a PDF document which best describes how to create an Essbase Cube that we could use for OBIEE Report.

This simple step by step explanation guide will help us to create the Essbase Cube, Importing the cube in OBIEE Administration Tool and creating the report based on the Base.

To download the document click here

Hope it helps!

Cheers!
Bose

Types of Hierarchies in OBIEE 11g

OBIEE supports various kinds of hierarchies. OBIEE 10g support Level-based, value-based hierarchies but OBIEE 11g supports two more hierarchies. Here is the definition for that

Level-based	Consists of one or more levels. For example, a Time hierarchy might have levels for Year, Quarter, and Month.
Value-based	Consists of values that define the hierarchy, rather than levels. For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees. Employees can have titles, such as Vice President. Vice Presidents might report to other Vice Presidents and different Vice Presidents can be at different depths in the hierarchy.
Ragged	A hierarchy in which all the lower-level members do not have the same depth. For example, a Time hierarchy might have data for the current month at the day level, the previous month’s data at the month level, and the previous 5 years’ data at the quarter level. This is also known as an unbalanced hierarchy.
Skip-level	A hierarchy in which certain members do not have values for certain higher levels. For example, in the United States, the city of Washington in the District of Columbia does not belong to a state. The expectation is that users can still navigate from the country level (United States) to Washington and below without the need for a state.