Oracle 11g R2 32/64bit Client Installation Steps

Here is how to proceed installing an Administrative database client for an Oracle 11g Database. Go the client setup folder and run the setup.exe file as Administrator.

Select the type of Installation of Client, Choose the Administrator option as seen in screenshot below.

Give necessary details to the Software updates and Product language step and proceed to Installation location step. Proceed with default as seen below or give a custom location for the same.

The installation will give a summary of the installation process before it starts. Save a response file for later reference of the installation. 

Wait for Installation to finish. 

Now to check the installation, go to Run and type sqlplus 

Check the header of the command prompt window to see the location of the sqlplus if this is from the client_1 folder then installation is successful. 

See also: How to Install a 11g R2 Database Server 

Oracle 11g R2 Server Database Installation Steps

Follow the below steps to Install and configure an Oracle 11g R2 Enterprise Edition on Windows Server 2008. Run the setup.exe file inside the setup folder as administrator. 

At the third step select 'Create and Configure a database' as shown in the below screenshot,

The next step is to select the system class for installation, choose server class since we are creating a server database. Refer screenshot below,

Since we are not going for a clustered installation select 'Single instance database installation'. 

Select the type of Installation as Typical Install. Choose Advanced install if you want to particularly configure database to detail. 

At the next step provide details such as directory location, database name, administrative password for SYS user etc and proceed. Refer screenshot below. 

The installation will check if the environment meets minimum requirements for installation of R2 Server. 

Before Installation step is started, a summary of the Installation will be displayed. It is recommended to save this Response file for future reference. 

Installation will proceed and finish.

Once the Installation is complete, go to Windows > Run and type 'sqlplus / as sysdba' and click ok.

A new sqlplus command prompt will be opened and give a sample query to ensure that the database has been connected.

If at any step the Installation fails check the Oracle 11g Database logs to debug.

OBIEE: What is a Driving Table and when is it preferred ?

A driving table is a feature available in OBIEE using which one can improve performance of cross database joins. However this feature is very limited and can be counter productive. 

Here is how Oracle defines a Driving Table,

Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large. Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined. 

When you specify a driving table, the Oracle BI Server will use it if the query plan determines that its use will optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows

This feature is available in the BMM(Business Model and Mapping Layer) of repository where you define a logical join between two tables, Here you will be able to see an option called Driving Table where you can select one of the two tables in the join. Make sure you are selecting the table with the less number of rows, usually preferred when data in one of the tables is very less ( <1000) and the other table is very large. 

Driving Table feature in Logical Join of OBIEE RPD.

Reference: Driving Table - Oracle 

What are Alias Tables and It's significance in OBIEE Physical Layer

An Alias Table or Alias in OBIEE is a physical table which references to a different physical OLAP/OLTP table as it's source.

Alias tables are often used for Repository development over Physical tables as they have the following properties,

1. Enables reuse of a physical table.
2. Avoids circular joins that can form when importing OLTP source tables.
3. Create self joins between tables, for eg: Manager is also an Employee.
4. Enables to easily identify source tables and adhere to data warehousing naming standards.
5. Makes physical query generated easier to understand.
6. Can override source table's caching properties like Cacheable, Cache never expires and cache persistence time. 

Alias Tables in OBIEE 11g - Example.

Find rows with Arabic/Non English Text in a Database Column

Scenario: Find and delete contact records with Arabic names from a database table.

Solution: In order to find the records we can use the query below, which will find you all records that contain non english alphabets and numbers. 

SELECT EMPLOYEE_ID, FIRST_NAME  FROM HR.EMPLOYEES WHERE NOT REGEXP_LIKE (FIRST_NAME,'[a-z,A-Z,0-9]');

To test the same, We updated one record from the EMPLOYEES table in HR Schema to an arabic text as seen below,

UPDATE HR.EMPLOYEES SET FIRST_NAME='اغراوال' WHERE EMPLOYEE_ID=183;

Arabic text in Employees table First Name column

Now run the first query to get this particular record alone with arabic text. 

Query output

How are OBIEE Metadata files stored ?

The metadata for an OBIEE Instance is stored both as Database tables and as Physical files.

Metadata in Database tables are stored in the Metadata Schema's

MDS (Metadata Services Schema)

BIPLATFORM 

The Physical metadata files for OBIEE are the

• Repository (RPD)
• Catalog Files

The repository file can be found at the following location and will have the extension .rpd. 

MW_HOME/instance/instance1/config/OracleBIServerComponent/coreapplication_obips

The currently active RPD name can be checked from EM or from the instanceconfig.xml file. The repository file contains the details of Physical Tables, Physical and logical mappings, Presentation tables etc. 

The catalog files are located the physical directory below,

MW_HOME/instance/instance1/config/OracleBIPresentationServiceComponent/coreapplication_obips1

The catalog contains Metadata for reports,prompts, dashboards etc and their permissions. The physical directory for the latest catalog displayed can be found from the EM.

What is OPMN and it's functions ?

OPMN stands for Oracle Process Management and Notification System that acts as a management service for different oracle components. In an OBIEE instance, the OPMN is responsible for controlling the different Oracle BI 11g components.

Functions of OPMN are the following

• Start and stop the OBIEE components separately or at once. 

Oracle BI 11g system components

• Restart an OBIEE component upon failure.

The different Oracle BI components that OPMN works on are the following

1. BI Presentation Services
2. BI Server
3. BI Schedulers
4. BI Cluster Control
5. BI JavaHosts

The OPMN batch file can be accessed from the below location from the Middleware Home folder.

FMW_HOME/instances/instance1/opmnctl.bat

You perform the following operations using the opmnctl batch file,

1. Start All
2. Stop All
3. Start Procedure
4. Restart Procedure 
5. Stop Procedure
6. Status

eg CMD scripts:

• Start all - opmnctl startall
• Stop all - opmnctl stopall
• Startproc - opmnctl startproc ias-component=OracleBIServerComponent
• Restart process - opmnctl restartproc ias-component=OracleBIServerComponent
• Stop process - opmnctl stopproc ias-component=OracleBIServerComponent
• Status - opmnctl status You can also invoke the OPMN services from Enterprise Manager.

Oracle 11g: Get a list of all Indexes defined for table/schema

This is how you can get a list of all indexes defined on a particular table or the number of indexes on tables inside a particular schema.

For a particular table,

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='YOUR_TABLE_NAME'

For a particular schema,

SELECT * FROM ALL_INDEXES WHERE TABLE_OWNER='YOUR_SCHEMA_NAME'

Find folder paths of $PM Informatica variables

This is where you can find the paths of $PM variables used in Informatica.

Log in to Informatica Administration console and go to the Integration service you are using. In the Integration service go to Process and you will be able to all the paths.

Informatica Administration Console > Integration Service > Process

[Solved]Informatica Integration service initialization failed - SF_34004 and LM_44229

We were facing the below error while trying to create and configuring a new Integration service for Informatica 910.

We checked the log and found that the error was because of the credentials provided. We had used the Informatica Repository Schema username and password instead of the Informatica Administrator password.

Go to Integration service > Associated Repository and correct the username and password. 

Once you have changed the password, Re-enable the integration service. 

[Solved] sqlplus / as sysdba not working

Earlier in one of our previous posts we had discussed how to login to Oracle 11g Database as the SYS user without credentials. However this doesn't apply everywhere, chances are that you might have come across this error as shown below,

Login as SYSDBA

Error

Cause: This is because the Windows user you have used to login is not a part of the ORA_DBA group. 

Resolution: The issue can be resolved by adding the user to the ORA_DBA group as described in the steps below,

1. Go to Administrative Tools > Server Manager 
2. Inside the Server Manager > Configuration > Local Users and Groups > Groups locate the ORA_DBA group 
   
3. Open the ORA_DBA group and add the windows user to the group as seen below,
   
4. Check again with the sqlplus / as sysdba command again after you have added the user to the group and the issue will be fixed as below,

   

   SQLPlus logged successfully as SYSDBA user

[Solved] Forgot SYSDBA credentials for Oracle 11g

This is a really helpful tip incase you forgot the password to the SYSDBA user in your Oracle 11g Database. You can use the following syntax to login to the server as SYSDBA from RUN,

sqlplus / as sysdba

Login to SQLPlus without SYS credentials

This would open SQLPlus as shown below and you can test the same by running the command SHOW USER

Logged into SQLPlus and test the name of the current user.

Note: This will only work if the Windows User is a part of the ORA_DBA user group. 

DW Basics: What is a Data Mart ?

A Data Mart is a part of the Data Warehouse or a set of dimensions and fact tables that is concentrated on a particular area of a business.

Consider the example of an IT organization, the organization will be having many sub domains like HR, Finance, Sales, Recruitment etc.In a Data warehouse the data from all the subdomains will be available. A data mart is simply all the dimension or fact tables that are based on a single subject area, for example all the dimension and fact tables related to the Finance department would come under Finances data mart. 

Definition for a Data Mart is as follows,

Oracle - A data mart is a simple form of a data warehouse that is focused on a single subject (or functional area), such as Sales, Finance, or Marketing. Data marts are often built and controlled by a single department within an organization. 

Wikipedia - The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts are small slices of the data warehouse.

DW Basics: What is a Data Warehouse ?

So what exactly is a Data Warehouse?. For any BI developer it is very essential to understand the concept of a Data Warehouse. 

Here are the definitions of a Data Warehouse, scrapped from across the internet.

Google - A large store of data accumulated from a wide range of sources within a company and used to guide management decisions.

Oracle - A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing.

Wikipedia - A system used for reporting and data analysis. They store current and historical data and are used for creating analytical reports for knowledge workers throughout the enterprise.

Bill Inmon - A  Data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.

Ralph Kimball - A copy of transaction data specifically structured for query and analysis.

In simple words Data Warehouse is nothing but a database with the following key points, 

• Data analysis and historical data storage is the goal of a Data warehouse.
• Data storage is not a concern and hence all levels of Normalization are not applied here.
• Data from multiple sources are fetched and stored in an organized manner. 
• Data is stored in a structured manner (eg: Star Schema) in order to optimize query and data retrieval process. 
• Basically a high performance server with little constraints on storage and memory usage. 
• Provides fast data throughput.

[Solved] Informatica Keystore Creation Failed - Create New Keystore

Informatica Keystore Creation Failed.

During Informatica installation if the files are not properly copied then there are chances of keystore creation getting failed, 

In this case we need to create a new keystore to proceed with the installation, continue with the following steps for Installation

Inside the source/java/jre/bin folder, check whether you have a file named keystore.exe 

Keystore file in JRE Bin folder.

Type the following command to create a new keystore.

keytool -genkey -alias infa32key -keyalg RSA -keysize 2048 -keystore infa32key.keystore

Keytool and command to generate keystore file

Enter the rest of the details and a keystore will be created with the same name in the folder.

Use this keystore and proceed with Installation.

Source: Informatica Toolbox