Well, so did we. In fact, the dba_registry view showed the version of OLAP option as 10.2.0.3. 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 10.2.0.3.0 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
reserved.
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 10.0.0.0.
+---------------------------------------------------------------------------+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 10.0.0.0.
+---------------------------------------------------------------------------+
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.1Strangely, 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.
SQL>
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 418200.1.
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
USER is "SYS"
SQL> exec dbms_aw.convert('ODPCODE');
BEGIN dbms_aw.convert('ODPCODE'); END;
*
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 1Oh, 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.2Essentially, 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
USER is APPS
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.
Conclusion...
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.
, 
and 
were trying out different options. Then someone had the bright idea of checking the timestamp of the completed backup. It was possible that we were trying to restore to a point in time before the backup had completed.
