All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Tuesday, September 27, 2011

Error connecting DB from DAC ORA-12516: TNS:listener could not find available handler with matching protocol stack

I ran into this error a lot recently when running DAC execution plan, which takes some time to complete. Here is the error details that you can get from Informatica session log when the sessions failed:

RR_4036 Error connecting to database [Database driver error...Function Name : Logon

ORA-12516: TNS:listener could not find available handler with matching protocol stack

Database driver error...
Function Name : Connect

Database Error: Failed to connect to database using user [etldw] and connection string [BIQATST].].

Upon some researching and some help from a great colleague of mine, we decided to change the number of session connections to the Database. This is likely due to the number of connections at Database level not having enough so some of the connection requests get hung.

It is recommended to have about 500 sessions in the DB.

Upon changing the setting and re-running the execution plan, my error messages have gone away.

Thank you

Wednesday, September 21, 2011

DAC: Fail to create Index during execution plan run

Hello again

Here is something that a beginner may run into every so often when they use DAC to run informatica workflow, which is when the DAC execution plan runs fine except it fails to create table Index after the load. Or it could be shown as the following scenario:

The execution plan is completed with several failures of its tasks. We look at the detail of the task and find out that it fails at the last step of creating table Index:

We found out from the error log that it is complaining about a table space 'USERS' during Index Creation attemp:

As far as when 'USERS' got into this, I have no idea. Usually when we create table in a DB, we use Tablespace. I won't go into detail about what is tablespace and all that. In our case, we use a Tablespace called 'ETL_DW_INDX' for all of the index creation when we create these tables in the target DB. I am assuming that DAC is still looking for tablespace 'USERS' for all of its tasks during run time.

This leads our investigation of this issue to a new direction. Is there a place in DAC that specifies what Index Tablespace it is using for the given Database? Well, it turns out that is yes. It is defined at the physical data source under 'SET UP' tab, so let's go there!

As we can see, it is originally empty for the fleid 'Default Index Space'. This may explains why we see 'USERS' in the error log while we know we are creating Indexes in the Database using 'ETL_DW_INDX'. So, let's tell DAC to use 'ETL_DW_INDX':

After that, let's re-run the execution plan and see how it turns out!

It all succeeded!

So in summary, if we are using specific Index tablespace when creating Indexes in the DB, we need to tell DAC to use that tablespace by defining it in the specific Physical Data Source info. This information will be used by DAC to run create-index tasks during execution plan run.


Until next time!

Related Posts Plugin for WordPress, Blogger...