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.