Tuesday, July 14, 2009

BI Publisher with Proxy Connection (ORA-28043)

Suddenly all production reports in BI Publisher failed with message "ORA-01017: invalid username/password; logon denied"
The dataset are based on a proxy connection and the user management is OID driven. The production database is registered in OID.

At the backend connecting to the OID user resulted as below
SQL> connect biuser1/biuser1
ORA-28043: invalid bind credentials for DB-OID connection

ORA-28043 in this case means that the OID user used to register the database is either deleted or its password has been changed/expired.

In my case the password has expired , I tried to reset the password back to the old state but still it is ORA-28043.
So had to use DBCA and regenerate the password.

After the regeneration BI Publisher worked fine.
It would have been good that if BI Publisher would have reported ORA-28043 instead of ORA-01017

Monday, July 13, 2009

BI Publisher WebService Call does not display Images and Charts

BI Publisher user interface is quite not customizable , so one of my client decided to use webservice call and design a custom application. The custom application was productive and looked excellent. 
BI Publisher web services functionality has been more impressive since the version is released.

But there were some troubles in getting the application working.

The Images and Charts were not visible when the type is HTML , since webservice call just output plan HTML there are no ways to embed the image. Alternate approach is to use MHTML which is known to be storing images/css/html and other files in one archive.
But there are limitations with MHTML when it comes to Browser. It works better with IE8 and FireFox.

When I was testing with the BI Publisher in IE6 , It looked like the below when the output format is HTML

when the output format is MHTML with IE6 , then the screen looked like the below

But when I try to export and reopen the archive file , it works. But no body wants that approach to store it in a temp directory and reopen it again.

I tried with FireFox and IE8 , MHTML was excellent , it was able to display the images and icons.

MHTML output looks like below in firefox and IE8

The same behavior is observed when called from Web Service except the HTML output will not display images/icons since it is a know issue that plain HTML code will not carry images.

One of the stable display format would be PDF viewer else making IE8 or Firefox as mandatory web client for BI Publisher.

I hope this helps someone to decide on the output format when using webservice calls.

Friday, July 10, 2009

DROP TEMP Tablespace

TEMP Tablespace suddenly grew up to 200GB. There might have been some strange sorting which autoextended the temp tablespace. Usually SMON cleans up the temp segment but resize of the temporary datafile failed.

There are two possible ways to handle this issue.
- Reduce the buffer cache size to decrease the time taken to clean up the segment and then resize the temp files. This would take a downtime by restarting the database in a restricted mode with small buffer cache size , wait for SMON to drop the sort segments , then restart the database normally. Resize would work now.

But I always prefer the second option to re-create the temp tablespace.
So planned to create a new tablespace and make it as the active temporary segment. 
Then drop the old temp tablespace.

When I tried to drop the old temp tablespace it was hanging.

Basically it is because of some session which were still using the old temp tablespace.
v$tempseg_usage will help to figure out the information. A combination with v$session can bring up the session information also.

select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks
from v$session a, v$tempseg_usage b
where a.saddr = b.session_addr 
order by b.tablespace, b.blocks;

I found few session using the old temp tablespace.
Had to kill some and had to wait for some sessions to get completed itself.

I hope the above query helps.

Tuesday, July 7, 2009

Oracle Fusion Middlware 11g

Oracle announced the release of 11g Fusion Middleware

It is not a product , it is an upgrade.
It is 5,000 customer driven enhancements.
It is the result of over 7,000 hours of engineering.
It is 3m hours of quality assurance and testing.
It is combination of home grown and acquisitions products.
It is complete and integrated.
It is hot-pluggable.
It is best of the breeds.

To be simple all under one umbrella.

Have fun with OFM 11g

Popular Posts