APEX 4.0 EA Released

Something to do now during my holidays is to explore APEX 4.0.

I am using APEX extensively and recommend it to my customers when ever required.

I am currently running few production application in APEX , some are form & reporting based and others are basically plain dashboards.

One critical application is an intelligence application where it required few features which were missing in APEX 3 , some are better & complex graphs and better printing.

To achieve better charting , I have recommended AnyChart which has the advanced features and complex charting. One of the common feature that was looked for was saving the chart to some suitable format so that it could be directly used in Microsoft Office application.

To achieve better printing , I had to use BI Publisher as the printing engine.

To get all this in APEX we had to spend some money even APEX is free of cost.

In my case AnyChart and BI Publisher are two areas were licensing was involved.

My first look into APEX 4.0 was these two areas and I found it quite satisfactory

Try this one release by Oracle in Amazon Cloud http://tryapexnow.com

Other topics you might be interested in

APEX and BI Publisher Integration

R12 with Oracle Application Express

Oracle Application Express Software on 11i

Security Alert after SSL

With Oracle Application Server 10.1.2.3.0, SSL is enable with the SSO page.
Portal is running on HTTP, while SSO is secured with HTTPS. After clicking on the Login link on the secured SSO login page, the Internet Explorer raises the following Security Alert:
"You are about to be redirected to a connection that is not secure. The information you are sending to the current site might be retransmitted to a nonsecure site. Do you wish to continue?"

This alert is even raised when the following Advanced Internet Option is unchecked:
Warn if changing between secure and not secure mode setting

To implement the solution, please execute the following steps::
Solution 1:
1. Upgrade your Windows XP installations with the Service Pack 2.
2. Make sure that the registry has the following DWORD entry set to 0:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\InternetSettings\WarnOnHTTPSToHTTPRedirect

See more information in the following Microsoft Support article:
You receive the warning "You are about to be redirected to a connection that is not secure." in Internet Explorer 6 Service Pack 1
at http://support.microsoft.com/kb/883740/

Solution 2:
1. Obtain from Microsoft Support and install the hotfix of the following Microsoft Support article:
You receive the warning "You are about to be redirected to a connection that is not secure." in Internet Explorer 6 Service Pack 1
at http://support.microsoft.com/kb/883740/
2. Make sure that the registry has the following DWORD entry set to 1:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet
Explorer\Main\FeatureControl\FEATURE_NO_WARN_ON_HTTPS_TO_HTTP_REDIRECT_KB883740\iexplore.exe

You might also be interested in other topics

Overview of Oracle Application Server

Oracle Application Server Installation

Oracle Application Server : J2EE and WebCache

Oracle Application Server : Infrastructure

Oracle Application Server : Portal and Wireless

Oracle Application Server : Business Intelligence and Forms

Oracle Application Server : Secure Deployment

Oracle Application Server : Need of a Firewall

Oracle Application Server : Load Balancer

Oracle Application Server : Internet-Firewall-DMZ-Firewall-Intranet

DBUA0 Instance during 11g Upgrade

Recently when upgrading to 11g Release 1 , I found an unknown instance DBUA0 running on the server. I could not connect to the DBUA0 Instance to check further.

oudbtst 24771 1 0 15:16 ? 00:00:00 ora_pmon_DBUA0
oudbtst 24773 1 0 15:16 ? 00:00:00 ora_vktm_DBUA0
oudbtst 24777 1 0 15:16 ? 00:00:00 ora_diag_DBUA0
oudbtst 24779 1 0 15:16 ? 00:00:00 ora_dbrm_DBUA0
oudbtst 24781 1 0 15:16 ? 00:00:00 ora_psp0_DBUA0
oudbtst 24783 1 0 15:16 ? 00:00:00 ora_dia0_DBUA0
oudbtst 24785 1 0 15:16 ? 00:00:00 ora_mman_DBUA0
oudbtst 24787 1 0 15:16 ? 00:00:00 ora_dbw0_DBUA0
oudbtst 24789 1 0 15:16 ? 00:00:00 ora_lgwr_DBUA0
oudbtst 24791 1 0 15:16 ? 00:00:00 ora_ckpt_DBUA0
oudbtst 24793 1 0 15:16 ? 00:00:00 ora_smon_DBUA0
oudbtst 24795 1 0 15:16 ? 00:00:00 ora_reco_DBUA0

Raised a ticket with Oracle Support and found that DBUA0 is an Instance created by DBUA when upgrading the database and the presence of the DBUA0 process does not indicate a problem with the upgrade or that any action is required.

DBUA shuts down the instance after the upgrade is completed but for some reasons it did not.

So I had to reboot the server to make sure every thing with the upgrade is fine.

DataPump Incompatible version number 2.1 in Dump File

Recently did a migration task of some data from 11.1.0.6 to 10.2.0.4 using DataPump and hit this specific error "ORA-39142: incompatible version number 2.1 in dump file" during import process.

This means that the exported version was higher and the imported version cannot understand it.

So the import dump cannot be used any more in 10.2.0.4 , workaround to redo the export process with the option "version=10.2" to the expdp command.

What about when you cannot request another dump , may be the other source is a partner and they bill you for additional effort ?

Yes you can still get it done , by creating a local 11.1.0.6 dummy environment and then importing the dump into it. Now you have the dump inside a 11g environment , export it from the dummy environment with the option "version=10.2" and try importing into the 10.2.0.4 environment.

It worked for me and I hope this help.

Database Instance failed to turn on Flashback Database

ORA-38760:This database instance failed to turn on flashback database

Recently starting up a database reported ORA-38760. On this database I had flashback on. The error message indicates that the database flashback is on but the database failed to start generating flashback data. Checking the alert log found that there was no space in the disk group.

ORA-38701: Flashback database log 1442 seq 1519 thread 1: "+FB1"
ORA-17502: ksfdcre:4 Failed to create file +FB1
ORA-15041: diskgroup space exhausted
Can not open flashback thread because there is no more space in flash recovery area

Measures would be to add more disk space or free some space in the Disk Group so that the database can start.

I decided not to use the flashback any more and so determined to stop the flashback.

SQL>aler database flashback off;
SQL>shutdown immediate
SQL>startup

Again it resulted with the same message "ORA-38760:This database instance failed to turn on flashback database" which means the flashback is still not switched off.

Reason was flashback was not opened to close it. It still reported "Can not open flashback thread because there is no more space in flash recovery area" in the alert.log.

So it would be to review v$restore_point to see if there are options and I found that there was a restore point created with option guarantee which means the flashback is maintaining logs that are relevant for the guaranteed restore point. I do not need this restore point and so removed it. Database was then able to release a huge amount of space and my system was back again.

Handy Script to CleanUp WFERROR Workflow Item Type

There was a request to cleanup 100's of WFERROR in Workflow. This simple script can help to do it at the backend;

DECLARE 

     CURSOR wf_error IS 

     SELECT item_key 

     FROM wf_items 

     WHERE item_type = 'WFERROR' 

          AND end_date is null; 

BEGIN 

         FOR i IN wf_error LOOP 

               WF_ENGINE.abortProcess('WFERROR', i.item_key); 

         END LOOP; 

COMMIT; 

END; 

It took 7 Seconds to boot my new OS

I am talking about Google Chrome OS.

You can get the Source Code from http://www.chromium.org/chromium-os and set of instructions to get the ISO Image.

Then move the ISO image into a USB drive and have fun.

It takes 7 seconds to boot.

OS login is single sign-on and so then after no passwords

I hope Oracle will find some space into Google Chrome OS. 

Oracle Database XE and APEX are the two best options that will be interesting for the Oracle Community to start with in Google Chrome OS.

11g R1 Upgrade and My Best Practices

I am not going to be detailed as there are lots of discussion on 11g Upgrade, yet I want to share my experience.

I work on a warehouse environment with discoverer as the reporting tool. ASM is used for storage management.

So there are four databases on one server to be upgraded.

ASM

Warehouse Database

Warehouse Design Repository Database

Application Server Metadata Repository

Interestingly and commonly all the database has to be upgraded in a short time frame to minimizing the downtime.

Note: All the database has been individually tested and so I had an option to bundle them into one upgrade window.

So here are few tips that I took to reduce the downtime

Operating System , I made sure that the OS patches and kernel are properly set on a separate weekend.

Space , I made sure that there is enough space for the new oracle homes.

Software , Base 11.1.0.6 , Upgrade Patchset 11.1.0.7 and PSU October 2009 is properly staged.

A day before the upgrade downtime

I create the directory structures for the 4 oracle home

Installed 11.1.0.6 into the ASM Home.

Upgraded the ASM Home to 11.1.0.7

Installed PSU Patch Setup Update which also includes the latest CPU to the ASM Oracle Home.

Ran the Pre-Upgrade Scripts and took necessary action.

Prepared a checklist for re-checking the same during upgrade window.

For the other Oracle Home I cloned the ASM Home by following the cloning procedure , there by saved a lot of time. The ASM Home had the latest patchset and PSU update , so it was handly and contacted once to run all the root.sh by the unix administrator.

The Upgrade Day

So , 50% of my work was done , i.e, I have all my Oracle Home with latest patchset updates ready for upgrade.

Reverified Pre-Upgrade Scripts

Backed Up the Databases

First I started with the ASM migration with DBUA

Then I started DBUA from three different VNC Server for the 3 Oracle Home and upgraded three of them in parallel.

Note: During Upgrade I opted to switch of archival and flashback.

Performed Post Upgrade necessary task.

My server had enough CPU and Memory to support the parallelism and so I did , please do not upgrade in parallel if the hardware is less resourced.

Summary of key areas of reducing the downtime window:

- Prepare the Operating System in advance.

- Prepare the 11g Oracle Home in advance.

- Use Oracle Home cloning procedure to reduce the effort.

- Upgrade all the Database in Parallel

- Switch off archival and flashback in the DBUA wizard.

Overall I had a peaceful upgrade to 11g R1. I hope this helps.

X.Cygwin xserver Setup

I had a tough time running xserver on windows , sometime back I used Exceed but I could not use it longer since it is a trial version.

Running VNC was not an option in my environment.

I switched from Exceed to XMing , I ran into many problems. Performance was one key area.

DBCA has some problems with the XServers , after the Finish Button is clicked at the end of the setup , the Database Configuration Assistance pops up. This was not possible for me in XMing.

I contacted a linux expert to understand the problem with XMing and he suggested that there may be some missing fonts. Even that did not help.

Later I switched to Cygwin

It seems to be stable as of now , DBCA works fine.

Here is what you have to do if you need Cygwin.

Download it and complete the setup.

Start a cygwin prompt

Run /usr/X11R6/bin/startxwin.bat

XServer pops out.

Open Putty and use X11 forwarding and connect to the server

Invoke xclock and it should be working.

I hope this helps.

11gR2 SELinux prevents running root.sh on OEL5 Update4

On Oracle Enterprise Linux 5 Update 4 , SELinux Security Policy does not allow to run root.sh after the Grid Oracle Home Installation.

Fixing the SELinux policy by running the recommendation and by rerunning root.sh did not succeed.

There are other policy restrictions to sqlplus by SELinux also , so it better to Disable the SELinux or set it to 'Permissive' mode which gives a warning message in the SELinux control panel.

Metalink Document 457458.1 How to Disable SELinux can help to understand how to handle SELinux.

In my case

I cleaned up the 11g R2 setup with Oracle DeInstall utility

Set SELinux to 'Permissive' ( Server reboot is not required )

Restarted the Oracle runInstaller.

Hope this helps

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

ERROR:

ORA-28043: invalid bind credentials for DB-OID connection

SQL>

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

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 10.1.3.4.1 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.

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.

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

LDAP and ObjectClass

After AS Upgrade to 10.1.2.3.0 adding an a new object class does not get automatically included when the user is updated.

I introduced a new objectclass for self password validation and updating it would result with an error as below

This affected old users in the system but new users were not.

By making a ldapsearch on the user , it is found that the objectclass "passwordvalidation" is not found.

Note: passwordvalidation is an objectclass that is custom created and not available by default.

$ ldapsearch -v -h server01 -p 389 -D "cn=orcladmin" -w "welcome1" -s sub -b "dc=oracle,dc=com"  "uid=testuser" "objectclass=*"

objectclass=top

objectclass=person

objectclass=organizationalPerson

objectclass=inetOrgPerson

objectclass=orcluser

objectclass=orcluserv2

objectclass=customuserclass

So I had to add it manually for all the users.

I did a quick fix what came in my mind , not sure if there are other ways to workaround it.

$ ldapsearch -v -h server01 -p 389 -D "cn=orcladmin" -w "welcome1" -s sub -b "cn=users,dc=oracle,dc=com" "uid=*" dn > 1.txt

$ sed 's/ //g' 1.txt > 11.txt ( to remove space in the DN )

$ for i in `cat 11.txt`

  do

  echo $i

  echo "objectclass=passwordvalidation"

  echo 

  done > 111.txt

$ ldapmodify -c -h server01 -p 389 -D "cn=orcladmin" -w "welcome1" -f 111.txt > 1111.txt

ldapsearch for the same user

$ ldapsearch -v -h server01 -p 389 -D "cn=orcladmin" -w "welcome1" -s sub -b "dc=oracle,dc=com"  "uid=testuser" "objectclass=*"

objectclass=top

objectclass=person

objectclass=organizationalPerson

objectclass=inetOrgPerson

objectclass=orcluser

objectclass=orcluserv2

objectclass=customuserclass

objectclass=passwordvalidation

The error is no more and I was able to modify the user profile without any issues.

Import Dump Utility and Logging

Recently I implemented an automated refresh logic for a warehouse database from production to test.

I used Export/Import Dump utility to achieve it as I need to extract only a subset of data from the production.

The Export was fine but the Import had huge delays.

The reason being archivelogs during the import process which cannot skip the redo. So , for all the objects that were created the system was generating redo which turned out to generate archivelogs.

IMPDP does not have an option to switch off archivelogs.

Study in metalink found that there is an Enhancement Request 4568451 for the same which will be available from release 1 version of the database as a patch.

At present I am switching off archival at the database level to achieve the speed in importing.

Discoverer and LDAP

Even after Oracle is tightly integrating its components , something is left behind which makes the application completely unusable sometimes.

I had a Discoverer instance which has to be integrated with Single SignOn. This is quite possible and it works perfect.

But after login in to the application with single signon user, there is a possibility for re-using the SSO user in the database by registering the target database with the Oracle Internet Directory.

I am here just making a link to a demo where the integration is quite explained briefly.

Following it a database can be registered , I registered the database where EUL resides with OID.

Now from the database I could login as the oid user.

Now I am accessing discoverer and connecting as the OID user (DISOC1).

I created a workbook and saved it.

Now I am accessing discoverer and connecting as another OID user (DISOC2) and I am able to see the workbook created by the previous user(DISOC1).

At the backend the EUL5_DOCUMENTS has the DOC_CREATED_BY as the GLOBAL SCHEMA USER instead of DISOC1.

What could be the reason ?

I asked the same to Oracle Discoverer Support and got a feedback

"ER 5637910 DISCOVERER (OID SUPPORT) is currently at Status: 23,Scheduled for Future Release, however no timeframe for inclusion in the Disco product has been included in the ER. So not firm date for this functionality has been released. That means it will not be in Disco 11g, possibly 12g."

This issue is not seen in APPS mode EUL which is setup in Oracle E-Business Suite.

Good , now as usual we have to look for work arounds which can make this work which is under Oracle Standards.

Here are some tips to achive it.

I managed to use Oracle Internet Directory plugins to create and maintain the user in the database.

The plugins created the user by mapping to the DN in OID using the option in create user "globally identified as" when a user is created from the OIDDAS page.

Ex: create user test globally identified as 'cn=test,cn=users,dc=testdomain,dc=com';

Here the user in the database does not hold a seperate password but rather share the one from the OID.

Also there are special pluginscreated for user modification and deletion action.

Rest I managed using Roles in Enterprise Security Manager and still have the user management from OIDDAS.

The OID Plugins are quite interesting and once used to it , one can make use of it in different areas.

The purpose of this plugin and user management is to make sure the EUL5_DOCUMENTS records the login username in DOC_CREATED_BY.

Once Oracle Supports delivers a model which can perform this then I have to just switch off my plugins and delete all the users OID users in the database.

I have taken measure to store the scheduled workbooks in a seperate repository so that the user schema does not own any objects.

I hope this can help somebody who is developing a similar logic.

APEX and BI Publisher Integration

I had a requirement to setup BI Publisher as a printing server for APEX. 

Enabling it would make APEX deliver high rich printing with the help of BI Publisher Engine.

RTF templates that are built from BI Publisher Word Template Builder works good in APEX .

You can follow instructions in the link to setup the APEX and BI Publisher integration.

Refer Section 3.3 Configuring Application Express to use BI Publisher

When it comes to hand written or template builder generated XSL-FO layout there are few limitations and bugs.

An XSL-FO which generates data in the format of tables comes up good when APEX process it with the help of BI Publisher Engine.

But if the XSL-FO layout has a Graph then it never gets processed. There will be a zero-byte PDF file generated when done.

For some technical reasons the BI Publisher Engine does not process the Graph that is in the XSL-FO Code that is loaded into APEX.

But the same XSL-FO layout works find with a graph if invoked from a BI Publisher standalone edition.

Oracle Apex Support group is addressing this issue and it has been promised to get a fix in version APEX version 4.0

BUG 8237966 : PDF PRINTING OF CHARTS FROMAPEX TO BI PUBLISHER WITH XSL-FO LAYOUT NOT WORKING

BI Publisher Template Builder - Alternative Text

I am developing a complex graph solution with bipublisher.

I have a combined graph created with the bipublisher wizard and copied the code from the alternative text of the graph item.

Later I copied the graph code and modified it to suit my need , basically have added some more series.

Then I tried to copy the code back into the alternative text of the graph component and it stops my work as the content of the alternative text is restricted. I had a requirement of around 25 graphs which is quite not feasable with XSL-FO

This way of editing the graph is working and is supported as per documentation.

The problem is when the graph becomes bigger then there is a probem with the alternative text which has the limitation of 255 characters.

I needed a solution to move this graph. XSLFO translation works but it is mandatory for me to have it in the RTF template format as XSL-FO will introduce too much of efforts.

Later a detailed investigation with Oracle BI Publisher support was made and the final outcome was "Not Possible" because Word is used as the template builder and Oracle BI Publishers uses the Alternate Text area to store its information. Only if Microsoft can increase the size it can help BI Publisher in that case else Oracle has to look for some other reliable editor , may be open office can help them but it needs some improvement.

BI Publisher roles does not reflect immediately

BI Publisher security is setup as LDAP
The roles are created in OIDDAS ( XMLP_ADMIN,XMLP_SCHEDULE,...)

Issue is when a user is added to any of the role above does not immediately reflect in BI Publisher.
A bounce make it to work.

The version affected is 10.1.3.4

This is fixed in version 10.1.3.4.1 where the cache can be refreshed by scheduling.

The setup is simple after applying 10.1.3.4.1

See this link for details

AS 10.1.2.3 SSLConfigTool does not update the config files

This issue happened on HP UX machine where a 10.1.2.2 Application Server was upgraded to 10.1.2.3.

After the upgrade the SSLConfigTool does not update the necessary configuration files.

The configuration files update part has to be done manually following Note 351365.1 - How To Set up and Configure SSO and OIDDAS to use SSL.

CTAS and Default Values

Recently I created a table as select from the table and then I found the default values are no more in the new table. Found the below from metalink , it should be a usefull information for those who use CTAS mostly.

579636.1 Create Table As Select Does Not Copy Table's Default Values.

Note: Please refer to the latest information on the metalink document.

Symptoms

If you use CTAS ( Create Table As Select ) to copy a table, the new duplicate table does not contain the default values of the original table. As a result, if you use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, it does not copy column's default values.

Cause

There is an unpublished ER ( Enhancement Request ) # 4341693 suggesting that CTAS should inherit the table default values and that should enable DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS to copy the column's default values which is handled in another ER # 7596038. 

Solution

To implement the solution, please execute the following steps:

1. Do not use CTAS to copy the table, use another method.

2. If you want the column's default values to be copied when using DBMS_REDEFINITION, the table has to be pre-created with the default column values before getting re-organized with DBMS_REDEFINITION.  

Replace 'no rows selected'

I wanted to replace the message 'no rows selected' with an alternate message.

SQL> select * from dual where 1=0;

no rows selected

SQL>

I found a good one by using NOT EXISTS clause

SQL> select * from dual where 1=0

           UNION

           select 'no data found' from dual where not exists (select * from dual where 1=0); 

DUMMY

no data found

1 row selected.

SQL>