So you thought you upgraded your OLAP database to 10g..

ell, so did we. In fact, the dba_registry view showed the version of OLAP option as Here, see it for yourself:

  1  select comp_name , version, status from dba_registry
  2* where comp_name like  '%Analytic%'
SQL> /

COMP_NAME                  VERSION      STATUS
-------------------------- ------------ -------
OLAP Analytic Workspace   VALID

Seems pretty convincing, does it not? One would think so.

An unexpected error..

But, a month later, when the customer tried to do an archive and restore of a demand plan, they received the following error:

Demand Planning: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights

MSDRESTORE module: Restore demand plan


Current system time is 30-MAY-2008 08:24:19


**Starts**30-MAY-2008 08:24:19

**Ends**30-MAY-2008 08:24:21

ORA-35071: EIF file ARCH4027.eif cannot be imported because
analytic workspace ODPCODE has not been upgraded to version


Start of log messages from FND_FILE


Error in restore process: see batch log for details

ORA-35071: EIF file ARCH4027.eif cannot be imported because
analytic workspace ODPCODE has not been upgraded to version


End of log messages from FND_FILE


Executing request completion options...

Finished executing request completion options.


Exceptions posted by this request:

Concurrent Request for "Restore demand plan" has
completed with error.


Metalink, our friend in need..

So we turned to our friend, Metalink and came across Note 390004.1, which says that Imported the Analytic Workspace in an different database release, but the AWs are still of the old version.

Eh! So we tried to confirm the same and found that there were indeed 4 analytical workspaces that were still of 9.1 version, including the ODPCODE AW:

SQL> select aw_name , aw_version
     from all_aws
     where aw_version like '9%';

AW_NAME                        AW_V
------------------------------ ----
ZPBANNOT                       9.1
ZPBDATA                        9.1
MSD4029                        9.1
ODPCODE                        9.1

Strangely, the other AWs were all 10.2:

SQL> select aw_name , aw_version
     from all_aws
     where aw_version like '10%';

AW_NAME                        AW_V
------------------------------ ----
EXPRESS                        10.2
AWMD                           10.2
AWCREATE                       10.2
AWCREATE10G                    10.2
AWXML                          10.2
AWREPORT                       10.2
XWDEVKIT                       10.2
MMSD4027                       10.2
MSD4027                        10.2
MMMSD4027                      10.2
PMMSD4027A0                    10.2
PMMSD4027A21                   10.2
PMMSD4027A22                   10.2
PMMSD4027A26                   10.2
PMMSD4027A32                   10.2
PMMSD4027A33                   10.2
PMMSD4027A55                   10.2
PMMSD4027A57                   10.2
PMMSD4027A58                   10.2
MSD4027A58                     10.2
PMMSD4027A78                   10.2
PMMSD4027A79                   10.2
PMMSD4027A80                   10.2
PMMSD4027A86                   10.2
PMMSD4027A87                   10.2
PMMSD4027A88                   10.2
PMMSD4027A93                   10.2
PMMSD4027A95                   10.2
PMMSD4029A                     10.2
PMMSD4029A5                    10.2
PMMSD4029A3                    10.2
PMMSD4029A1                    10.2
MMMSD4029                      10.2
MMSD4029                       10.2
ZPBCODE                        10.2

35 rows selected.


Where did our OLAP upgrade procedure go wrong..

So the question before us was that how did our production upgrade procedure, the one which we thought was so meticulously planned and executed, go wrong?

For upgrading the ODP workspaces, we had followed Note 418200.1 - ODP: Steps When Upgrading the RDBMS Version Used With ODP.
We had even
got a
clarification from the author of that note about what steps were needed
for ODP
workspaces upgrade.

Unfortunately, The following note does not have any direct reference to upgrading OLAP workspaces:

Note 362203.1
Interopratbility notes Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)

We had found the following Notes on OLAP upgrade/exporting/clone:

472215.1 How to Export AWs or a Plan in Demand Planning
418200.1 ODP Steps When Upgrading the RDBMS Version Used With ODP
412308.1 How to Check the Version for ODP 11.5.10?
339110.1 How To Clone the OLAP Portion of ODP 11.5.10

Note 418200.1 is the most relevant for the upgrade activity, but unfortunately, even that does not have any reference to dba_aws view to check the aw_version column of each workspace and use the dbms_aw.convert API for the workspaces that could not be upgraded due to some reason.

So that is definitely one thing that should be part of that note. We would be sending a note to the author to include these steps into note

Another gotcha while implementing the solution (exec dbms_aw.convert API) is that it does not accept the OWNER name of the workspace in the first argument. Note 390004.1 actually sets a false expectation on this front.

Also, you need to be logged in as the owner of the workspace while issuing the dbms_aw API or you would get this error:

SQL> show user
SQL> exec dbms_aw.convert('ODPCODE');
BEGIN dbms_aw.convert('

ERROR at line 1:
ORA-33262: Analytic workspace
ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 58
ORA-06512: at "SYS.DBMS_AW", line 134
ORA-06512: at "SYS.DBMS_AW", line 1245
ORA-06512: at line 1

Oh, Oh.. A Gotcha while implementing the solution..

To make matters worse, by a copy-paste, I specified the owner name along with the workspace like this:

SQL> exec dbms_aw.convert('apps.odpcode');

It came back with an error like you cant specify the owner name of the table.   

After that, the name of the aw got changed to ODPCODE_ (with the _ in the end), but it upgraded to 10.2 as per dba_aws or all_aws.

SQL> select aw_name, aw_version
     from all_aws
     where aw_name like 'ODP%';

AW_NAME                        AW_V
------------------------------ ----
ODPCODE_                       10.2

Essentially, the dbms_aw.convert had left the ODPCODE workspace in a transient state and not done a good job of cleaning up after the exception.

A workaround for cleaning up...

Well, there is an API called dbms_aw.aw_rename that would do the trick of putting the name back to ODPCODE like this:

SQL> show user

SQL> exec dbms_aw.aw_rename('ODPCODE_','ODPCODE');

PL/SQL procedure successfully completed.

SQL> select aw_name, aw_version from all_aws where aw_name='ODPCODE';

AW_NAME                         AW_V
------------------------------ ----
ODPCODE                        10.2

Now just to make sure that it was really 10.2, we ran the dbms_aw.convert API again and got this message, which implied that it was already in 10.2 format:

SQL> exec dbms_aw.convert('ODPCODE');
BEGIN dbms_aw.convert('ODPCODE'); END;

ERROR at line 1:
ORA-33298: Analytic Workspace ODPCODE is already in the newest format allowed
by the current compatibility setting
ORA-06512: at "SYS.DBMS_AW", line 1267
ORA-06512: at line 1

After this, the MSDRESTORE module: Restore demand plan concurrent job completed successfully.


There were multiple learnings from this Sev 1 experience in production instance:
  • Ask your customer to test exhaustively. In this case, the customer is a very small shop with an IT department of 5-6 people and yet to graduate to the enterprise level thinking for managing their IT systems.
Any testing was good testing for them and this error was discovered when they were going to go live with another project phase implementation.

Unfortunately, in this case, we had very little say in their testing plan.
  • In this case, we were caught a little off guard because we did not have exhaustive expertise in OLAP upgrades and had to rely on published metalink notes, which did not cross link all the known upgrade issues. 
In this case, it would have paid to check the output of all_aws view too. Similarly, other Oracle Server options may have their own views.
  • While executing the dbms_aw APIs, it is best to be logged in as the owner of the analytical workspace being worked on, or you might land up in a worse soup than you signed up for.
As per our observation, even the alter session set current_schema=<OWNER> sql does not work on dbms_aw APIs.


Post a Comment:
  • HTML Syntax: NOT allowed



« July 2016