Discoverer 10.1.2.3 CP4 is available again

Last updated: May 5, 2009
I am delighted to be able to let you know that CP4 (patch number 7595032) is no longer password protected and the issue with the non-English language versions (see below) has been resolved. You are therefore free and encouraged to apply this patch.

By the way, this patch has all of the updates from 10.1.2.2 CP8 and many more.

Important Note:
Please note that if you are planning on installing CP4 that you will also need to install a patch for JDBC. I have made a comment on this in this posting and will post more information shortly.

Last updated: May 4, 2009
The latest information I have on this is as follows:

It was initially communicated that it only affected a small subset of customers as it was thought to only affect Japanese customers using Discoverer Plus. It was later discovered that some other locales were also affected.

Customer's were later updated with: Note: CP4 has temporarily been withdrawn due to a late breaking regression tracked in Bug 8477401 NLS: Discoverer Plus Always Runs In English Even If Japanese Locale Is Set'.

This can potentially affect all non-English locales. Oracle Support cannot provide a password or give access to the patch until the regression is corrected and the patch is re-uploaded to MetaLink. If you have have already downloaded the patch and use the English locale, then you should not be affected and can continue using the patch. This note will be updated when the updated patch is released.

Original Posting made: April 30, 2009
If you are vigilant and keep up with Oracle's cumulative patches and this blog (see here) you will be aware that CP4 (patch number 7595032) came out a couple of days ago.

Earlier today I noticed that it has now been password protected so I contacted Oracle. Here is what they told me:

CP4 has temporarily been password protected due to a late breaking regression tracked as bug number 8477401: Discoverer Plus Always Runs In English Even If Japanese Locale Is Set. This should only affect a small subset of customers. We are trying to get this password protection removed asap since it only affects a narrow band of customers.

Oracle plans to have this password protection removed quickly so it could even be removed before the information filters out too far. I will let you know when the password requirement has been lifted. If you are using English then you should go ahead and request the password as you won't be affected.

Please note that if you are planning on installing CP4 that you will also need to install a patch for JDBC. I have made a comment on this in this posting and will post more information shortly.

Michael presenting at ODTUG Kaleidoscope

In June, I will be at the ODTUG Kaleidoscope conference between June 21 and June 25 in Monterey, California, where I will be presenting a paper entitled Maximize your investment in Discoverer. If you happen to be going please look me up. I will be presenting the same paper and also sitting as a panelist on the Business Intelligence / Data Warehousing Panel.

I am also scheduled to appear at the Baton Rouge Oracle Users Group meeting in July and have submitted a paper for Oracle Open World which is in October in San Francisco. I will provide more information on ODTUG, BROUG and Open World as the times draw closer.

For the eagle eyed among you, you will notice that originally this posting said that I was also going to be at the IOUG / OAUG Collaborate conference in Orlando next week. Unfortunately, something came up at the last minue which has prevented me from attending. This is the first time in 10 years of going to conferences that I have had to cancel an appearance like this. However, the good news is that Mike Durran from Oracle and Mark Rittman have agreed to step in and give my presentation for me. More on this to follow.

Michael is now a US citizen

Hi everyone
I thought I would let you all know that last Friday afternoon in Nashville I took the oath to become a citizen of the United States. This is such a privilege and honor for me and something I have been looking forward to for quite some time, ever since I first started coming here for business way back in 1994.

I emigrated to the United States in 1999 while working for Logitech as their world-wide manager of business intelligence. Within a few months of my arrival I met Darlene and we were married within the year and have been together ever since.

We moved to Tennesses 5 years ago when we formed Armstrong-Smith Consulting and bought ourselves a small ranch so that we could enjoy the fine weather and get away from the hustle and bustle of consulting when time would allow. If you would like to see a few photographs of our place you can find them here on the About Us page of our website.

Cumulative Patch 4 for 10.1.2.3

I am very pleased to announce that Oracle have released the eagerly awaited cumulative patch 4 for Discoverer 10.1.2.3 (patch number 7595032). When compared to CP2, almost 80 bugs have been fixed including the bug that was preventing usres from logging in correctly after applying CP3 (see here).

So far this has been released for the following platforms:

• Microsoft Windows 32-bit
• Linux x86 (works for both 32 bit and 64 bit)
• Sun Solaris (64-bit)
• HP-UX PA-RISC (64-bit)
• IBM AIX Based Systems (64-bit)

There is also a very important note inside the readme regarding installing an interim patch. Here is the necessary information:

Please apply the JDBC patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5).This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

Then proceed with applying the current patch.

The following postings have been updated:

• Useful Patch Numbers
• Word of warning about 10.1.2.3 and CP3

Oracle buys Sun

If you haven't already heard, Oracle has announced (see here) that it has entered into an agreement to acquire Sun Microsystems (Sun).

My good friend Mark Rittman has a very good article on his blog (see here).

According to the Oracle press release:

The acquisition combines best-in-class enterprise software and mission-critical computing systems. Oracle plans to engineer and deliver an integrated system—applications to disk—where all the pieces fit and work together so customers do not have to do it themselves. Customers benefit as their system integration costs go down while system performance, reliability and security go up.

So what do you make of it? Personally I think it's a great move and will only strengthen Oracle's hand when it comes to vying with Microsoft in the marketplace. I think it was a deal that was always going to happen as Sun's Java is a product that means an awful lot to Oracle as most of their products require it to run effectively.

Of course, this also means that Oracle will now own the Solaris brand of hardware. So if the deal does go through, and I don't see why it should not, you will then be able to get one-stop shopping for hardware and software from the same vendor.

Personally, I say this is a great deal for Oracle and an even greater deal for Oracle's customers.

Charles Phillips, President of Oracle, today said the following:

Oracle's ownership of two key Sun software assets, Java and Solaris, is expected to provide our customers with significant benefit. Java is one of the computer industry's best known brands and most widely deployed technologies. Oracle Fusion Middleware is built on top of Sun's Java language and software. Oracle can now ensure continued innovation and investment in Java technology for the benefit of customers and the Java community.

The Sun Solaris operating system is the leading platform for the Oracle database. With the acquisition of Sun, Oracle can optimize the Oracle database for some of the unique, high-end features of Solaris. Oracle is as committed as ever to Linux and other open platforms, and will continue to support and enhance our strong industry partnerships.

Our customers have been asking us to step up to a broader role to reduce complexity, risk, and cost by delivering a highly-optimized standards-based product stack. Oracle plans to deliver these benefits by offering a broad range of products, including servers and storage, with all the integrated pieces: hardware operating system, database, middleware and applications. We plan to preserve and enhance investments made by our customers, while we continue to work with our partners to provide customers with choice.

April 2009 Patch Levels for Discoverer

Oracle is continually refining and upgrading Discoverer. Because of this refinement process, from time to time, Oracle periodically withdraws support for older releases. These are called obsolescence desupport documents and can be found on MetaLink.

I will summarize the major releases here, along with their patch and desupport numbers.

• Discoverer 3.x - completely desupported. The terminal release was 3.1.36 for Administrator and Desktop and 3.3.62 for Plus and Viewer. You should migrate to Discoverer 10g Release 2 (10.1.2.2 or 10.1.2.3) as soon as you can. Note that there is no direct migration path from Discoverer 3.1 to Discoverer 10g Release 2, as you must migrate via 4.1.48.08. The obsolescence desupport notice on MetaLink is document 162402.1.

• Discoverer 4.x (stand-alone, not as part of E-Business Suite) - completely desupported. The terminal release was 4.1.48.08. You should migrate to Discoverer 10g Release 2 (10.1.2.2 or 10.1.2.3) as soon as you can. The obsolescence desupport notice on MetaLink is document 231072.1.

• Discoverer 4.x (with E-Business Suite) - desupported as of October 31, 2006. According to Oracle "since Discoverer 10g (10.1.2.0.2 - now 10.1.2.2 or 10.1.2.3) is fully certified, we HIGHLY recommend implementing or migrating to this release". Extended Support (ES) will continue until October 31, 2009. You are advised to migrate to Discoverer 10g Release 2 as soon as possible. The obsolesence desupport notice for Discoverer 4i against E-Business Suite is MetaLink document 337576.1.

• Discoverer 9iAS (9.0.2) - completely desupported as of July 1, 2008. The terminal release was 9.0.2.54.10. You are urged to upgrade to Discoverer 10g Release 2 (10.1.2.2 or 10.1.2.3) as soon as you can. The obsolescence desupport notice for Discoverer 9iAS is MetaLink document 162403.1.

• Discoverer 10g Release 1 (9.0.4) - ECS support was withdrawn December 31, 2006. The recommended release is 9.0.4.46.00 (Application Server 9.0.4.3). Extended Support will continue until December 31, 2009, however you should upgrade to Discoverer 10g Release 2 as soon as you can. The obsolescence desupport notice for Discoverer 10g Release 1 (9.0.4) is MetaLink document 295948.1.

• Discoverer 10g Release 2 initial (10.1.2.0.0) - The terminal release is 10.1.2.45.46c. You can upgrade to 10.1.2.48.18 by applying the 10.1.2.1 patchset which will bring you to the 10.1.2.0.2 code level (see below). No ECS or ES dates have been announced for 10.1.2.0.0. The support notice for Discoverer 10g Release 2 is MetaLink document 329361.1.

• Discoverer 10g Release 2 production (10.1.2.0.2) - This is the current and most up-to-date release and the one you should be on if you can. Out of the box the base version you will get is 10.1.2.48.18. Notwithstanding the fact that this is the current version it is also a brilliant piece of software. Having installed the base release you need to upgrade to either 10.1.2.2 (10.1.2.54.25) or 10.1.2.3 (10.1.2.55.26) and then apply the most recent cumulative patch. No ECS or ES dates have been announced for this release. The support notice for Discoverer 10g Release 2 is MetaLink document 329361.1.

Notes: the definitions of ECS and ES can be found on MetaLink in note 187553.1

Be careful when upgrading to Java 1.6

If you install Discoverer 10g out of the box you will get Java 1.4 on the server.

Last year Oracle announced that the server was now certified with Java 1.6 and issued a MetaLink note complete with instructions on how to upgrade the JVM.

On my travels, I have noticed that some administrators do not read the release and installation notes properly and overlook the fact the configuration.xml (found in $Oracle_Home/discoverer/config) needs to be updated as well. If you only install a new java executable but leave the configuration.xml file untouched you will run into serious user interface issues. One such problem is that parameter values will no longer be displayed from your user's last run or from when the worksheet was created thus causing your end users a great deal of anxiety.

An application server in such a state will also run much slower too, so please make sure you update the configuration.xml correctly.

To help you, here is the JVM section from the application server running on my own laptop:

< name="sun" classid="clsid:CAFEEFAC-0016-0000-0012-ABCDEFFEDCBA" plugin_setup="https://asclaptop0.learndiscoverer.com:7778/jpi/jre6u12.exe" version="1.6" versionie="1,6,0,mn" type="application/x-java-applet" plugin_page="http://java.com" disco_archive="disco5i.jarjar" d4o_archive="d4o_double.jarjar">

Notes:
1. This is all one line with no line breaks or carriage returns. There should also be no space following the leading or before the last brackets.

2. I have spaces to make sure that you can see the text and to stop your browser interpreting this as HTML.

3. The version number 12 only appeard in one place, in the classid switch.

The areas of importance are these switches:

• classid
• plugin_setup
• version
• versionie
• plugin_page

In the following help I will show you the settings for Java 1.6.0_12. All of the switches are enclosed in double quotes.

classid: this complete switch has 36 characters broken down as follows:

• first 9 always CAFEEFAC- all in uppercase
• next 5 are always 0016- meaning Java 1.6
• next 5 should be 0000- meaning Java 1.6.0
• next 5 should be 0012- meaning release 12 (only place this is referenced)
• last 12 should be ABCDEFFEDCBA again all in uppercase
• full switch is: classid="clsid:CAFEEFAC-0016-0000-0011-ABCDEFFEDCBA"

plugin_setup: this switch should be fully qualified domain name pointer to the Java executable on the server. Even though this switch always has /jpi/ in the pointer the actual executable must be stored in /jpi/bin/ under the $Oracle_Home for the middle tier. The name of the executable can be anything you look but must be the same as the executable stored in $Oracle_Home/jpi/bin.

version: this switch should be 1.6 with a period between the two numbers

• full switch looks like this: version="1.6"

versionie: this switch should be "1,6,0,mn" this time with commas between the 4 elements. Yes, mn should be used and not 12 as you might think.

• full switch looks like this: versionie="1,6,0,mn"

plugin_page: this switch should just be http: //java.com and not a pointer to the complete version as it was in 1.4

• full switch looks like this: plugin_page="http://java.com"

After you have upgraded the server to Java 1.6 I recommend you go to each of your user machines and clear the local Java cache. Discoverer will then ensure that they have the correct applet and will send down the right Java version the first time the user connects.

If your users have an earlier version of Java installed or no Java installed then the first time that they connect to Discoverer on an application server that is running Java 1.6 they will be prompted to install it. All they have to do is follow the on-screen prompts and they will be ok. If your users don't have administrative rights over their local PC or if you are making them connect via a Citrix terminal server then your administrator will need to install JVM 1.6 for them.

No more cumulative patches for 10.1.2.2

If you have been keeping up with the latest information for Discoverer on Oracle MetaLink you will have noted that Oracle recently announced that cumulative patch 8 for 10.1.2.2 is the last one for that platform and that the platform currently being developed is 10.1.2.3.

If you haven't already upgraded to 10.1.2.2 you should skip it and proceed direct to 10.1.2.3. The steps to installing 10.1.2.3 on a native 10.1.2.0.2 are identical to the steps that you would have to apply to install 10.1.2.2. This means that if you are using an infrastructure then you will have to upgrade the metadata repository from 10.1.0.4 to at least 10.1.0.5.

If you have already installed 10.1.2.2 and any of the cumulative patches the good news is that 10.1.2.3 (patch 5983622) will install directly on top as there is no need to de-install anything, the upgrade will take of it for you. After you have upgraded to 10.1.2.3 you should then proceed directly to install cumulative patch 2 (patch 7198716).

You might be wondering why I say to should upgrade only to cumulative patch 2 when cumulative patch 3 was released earlier this year. This is because there are some bugs in cumulative patch 3 and you are advised to wait until cumulative patch 4 which, by the way, I have heard is due for release within the next 2 to 3 weeks. Just as soon as it released I will update my master patch blog note.

Clamping Down Discoverer

Performance is often a big issue with Discoverer especially when Discoverer users share resources with other system users. It is often said that Discoverer must always use a separate reporting database because the Discoverer users would slow down the production system. This is rubbish; there are some good reasons for putting Discoverer on a separate system, but performance is not one of them.

Much better is to put all your system resources into one database and then clamp down Discoverer so that it cannot interfere with other processes. You can reduce the priority or limit the CPU; you decide what database resource Discoverer should use and when it should use it.

This post describes how to clamp down the Discoverer so that it does not slow down the rest of the system.

Resource Consumer Groups

The Oracle Database Resource Manager provides essential functionality to help you do this. It allows you to manage the hardware resources that are allocated to a database session. The Resource Manager provides a flexible way to control the Discoverer sessions because it allows you:

1. limit the CPU or I/O used by a session
2. lower the priority of session
3. terminate sessions that use too much resource
4. terminate sessions where the optimizer estimates that the SQL run time will be over a threshold

Even better, the resource scheme used can be changed dynamically, so that you can for example, have one resource allocation for during the day and another for night time and weekends.

Setting up a resource plan can be quite complex but fortunately there is a default SYSTEM_PLAN provided which has a low priority group into which you can add the Discoverer sessions. All other sessions will use the default consumer group so that any Discoverer session in the low priority group will only use CPU not consumed by other database sessions.

A listing of the plans and consumer groups available in the database can be obtain from the DBA_RSRC_PLANS and DBA_RSRC_CONSUMER_GROUPS views.

By default the Resource Manager is not enabled. So first you need to enable the Resource Manager by setting the database initialization parameter to a resource plan:
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

Alternatively, you can activate the Resource Manager on the fly by entering:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'SYSTEM PLAN'

Next you need to ensure that the Discoverer sessions are added to the low priority consumer group. There are basically two ways that you can do this; using the consumer group mapping functionality to automatically add the session to the consumer group when the session is created in the database; or run an initialization procedure at the start of the session to switch the consumer group from the default group to the low priority group.

Consumer Group Mappings – The database lets you set up a mapping between session and consumer groups. Many new mapping options were added in Oracle 10g and therefore on databases prior to 10g the mapping option you need may not be available. A mapping that automatically adds sessions to a consumer group can be created using DBMS_RESOURCE_MANAGE.

There are lots of mapping options available. You can add all the sessions create by a user into a consumer group. For example, to add all the sessions created by the DISCO_USER database user into the low priority consumer group the following call to the DBMS_RESOURCE_MANAGER package can be used:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'DISCO_USER', 'LOW_GROUP');

You can add all the sessions created by a particular program. For example, the sessions created by the Discoverer Desktop program can be added using:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'dis51usr.exe', 'LOW_GROUP');

You can also add sessions by service name, so here you would define a service name in the TNSNAMES.ora files used by Discoverer and map the service name to a resource group.

Initialization Procedure - This is the method we use. A procedure is run at the start of every Discoverer session which switches the consumer group. Because we are in an Oracle Applications 11i environment we use the ‘Initialization SQL Statement – Custom’ system profile to call the initialization procedure rather than a trigger. The procedure includes the following statement:

DBMS_SESSION.switch_current_consumer_group('LOW_GROUP',v_varchar2_240, FALSE);

to switch the session to the lower priority consumer group.

The initialization procedure gives you the best control over the consumer group setting. For example, you can set the priority of scheduled Discoverer sessions to be lower than the priority of regular sessions.

RAC Nodes

Real Application Clusters (RAC) provide an effective way of segregating Discoverer from other processes. Typically you would have a database node dedicated to processing Discoverer reports. Discoverer can then be directed to the dedicated node by setting the RAC node in the TNSNAMES.ora file on the Discoverer Application server (and on the desktop if Discoverer Desktop is used). You can use service names to specify preferred and failover nodes.

Using a dedicated RAC node is the cleanest way of separating Discoverer from other processing in a RAC environment however, if you use parallel processing in your Discoverer reports then some further configuration is required.

By default on a RAC node any SQL statement that uses parallel processing will parallelize the workload by running slave processes on all RAC nodes in the database. So if the parallel_max_servers database parameter is set to 8 then 8 parallel processes will be started on each RAC node in the database. That's great if you want your Discoverer report to use CPU resource across all the RAC nodes, but if you want Discoverer just to use the dedicated RAC node then in Oracle 10g you need to set up a RAC instance group. In 11g it’s a bit easier as the nodes are service aware and only run the named service.

1. For example in 10g, if PROD is the database instance and PROD4 is the dedicated RAC node then an INSTANCE_GROUP called DISCO for the Discoverer dedicated node can be defined by including in the database initialization file:
PROD4.INSTANCE_GROUP=DISCO

2. Then in the Discoverer initialization procedure include the following statement:
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_INSTANCE_GROUP = ''DISCO''';

The Discoverer processes will then join the DISCO parallel instance group when the process is started and any subsequent parallel SQL run by this process will only use the RAC nodes in the DISCO instance group.

Temporary Tablespace

A Discoverer report that is very large or has been badly written can use a lot of temp space. The database uses temporary tablespace when a database sort is too large to fit in memory and so if Discoverer has to sort lots of data for example in a hash join then it will use temp space. The size of the temp space needs to be controlled to stop the disks filling up and hence a maximum size limit for the temporary tablespace should be set.

This is a problem if Discoverer is sharing the temp space with other processes. When a Discoverer report runs out of temp space then it will fail with ORA-01652: Unable to extend temp segment. The problem is that the temporary tablespace is a database user resource and so is shared with other processes that use the same temporary table space.

Hence when Discoverer processes are sharing a database with other processes it is important that the Discoverer processes use a separate tablespace. Otherwise one or more rogue Discoverer processes can gobble up all the temp space causing another process to fail when there is no temp space available.

Now if your Discoverer processes always connect to the database as separate database users then the solution is easy; you create one or more separate temporary tablespaces for these database users. Then the Discoverer processes may still fail with the ORA-01652: Unable to extend temp segment error but at least other processes will be unaffected.

However, where Discoverer is used with the e-Business Suite (and also in many other systems) Discoverer connects to the database using a common database user. In the e-Business Suite all processes connect as the APPS database user and therefore by default all processes share the temporary tablespace associated with the APPS database user. The OLTP processes probably do not use any temp space but interface and batch processing often use temp space and could error as a result of Discoverer processing.

The solution is not easy and different solutions may be required for different systems, but where Discoverer is implemented with the e-Business Suite a separate temporary tablespace can be implemented as follows.

1. Create a new user with a limited tablespace.

CREATE TEMPORARY TABLESPACE TEMP_DISCO TEMPFILE SIZE AUTOEXTEND OFF;

CREATE USER APPSDISCO IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE TEMP_DISCO ACCOUNT LOCK;

2. Create an initialization procedure for the Discoverer which changes the current schema to the APPSDISCO user for the Discoverer process. The current schema defines which database schema is used as the default schema where no schema is specified in front of a database object. Consequentally the temporary tablespace used is the temporary tablespace associated with the APPSDISCO user.

PROCEDURE initialize
IS
BEGIN
IF SYS_CONTEXT('USERENV','MODULE') LIKE 'Disco%' THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=APPSDISCO';
END IF;
END initialize;

3. Call the initalization procedure from the ‘Initialization SQL Statement – Custom’ system profile for all the responsibilities that use Discoverer. So if your initialization procedure is held within the DISCO_UTILITIES_PKG PL/SQL package then the profile would be set to:

begin disco_utilities_pkg.initialize; end;

However, there are some complications that may need to be addressed:

1. The SYS_CONTEXT('USERENV','MODULE') only works in Oracle 10g. In Oracle 9i you need to check in the V$SESSION table.


2. Although Discoverer puts the APPS schema in front of all the database objects I found that there were some database bugs in the version of the database I was using (Oracle 9i) and I had to create some synonyms for some of the APPS objects, for example, to get all the Discoverer reports to run I had to create synonyms in the APPSDISCO schema for all the packages used by functions mapped into the EUL.


3. Again, although the Discoverer processes still connect as the APPS user and therefore have all the APPS database privileges I found that database/Discoverer bugs caused privilege errors. To workaround this I had to grant privilege on some APPS database objects to the APPSDISCO user.

Conclusion

Where Discoverer is reporting on transactional data then Discoverer should use the same database as the transactional system. A single database allows Discoverer to report on real-time data and saves all the complications of replicating and managing separate databases.

A single database allows more efficient use of the hardware resources by allowing Discoverer to use spare CPU that would otherwise be wasted. Long running Discoverer processes can run at lower priority than other processes in the system increasing the overall throughput of the system. Provided Discoverer has been properly clamped down then the Discoverer processes will not affect other processing in the system.

You can go further and separate Discoverer scheduled processes from interactive Discoverer processes. Then you can run the scheduled larger Discoverer reports at a lower priority and separate temp space than the interactive reports.

When Discoverer Users and management know that they will not be able to affect the performance of the system as a whole by running Discoverer reports, they will then have confidence to build and run more complex reports making greater use of the Discoverer reporting system.