A while ago, I had the opportunity to migrate an E-Business Suite database (Apps version 12.0.4, database version 10.2.0.4) from 32-bit Linux to 64-bit Linux. It's a straightforward process, outlined in My Oracle Support Note 471566.1: Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit. Performing one of the critical migration steps, upgrading OLAP analytical workspaces (AWs), requires some careful reading, starting with the primary migration document for the database tier: Note 456197.1: Using Oracle E-Business Suite Release 12 with a Database Tier Only Platform on Oracle 10g Release 2. This document directs you to Note 352306.1: Upgrading OLAP from 32 to 64 bits, which covers the migration process for OLAP workspaces: export and delete from the the 32-bit system, then recreate on the 64-bit system and import the contents. The remainder of this blog post includes some embellishment of those four steps, from my migration notes. Examples were recreated on my test database; please don't look for these workspace names in an actual EBS database.
Please note that I'm by no means an OLAP expert; if you have your own observations or experiences to share, including corrections to any errors I might have made, please leave a comment. The last thing I want to do is spread misinformation! And, as always, remember: test systems exist for a reason, and instructions from Oracle Support should trump anything you read in this blog entry 
"No objects to export" error when exporting AWs
The export process is explained thoroughly in Note 352306.1. You may encounter the following error, however, when attempting to export an empty workspace:
BEGIN dbms_aw.execute('export all to eif file ''EXPORT_DIR/AWTEST.eif'''); END;
*
ERROR at line 1:
ORA-33390: There are no objects to export.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at line 1
An export file will not be created, since there's no data in the workspace. Nonetheless, you will still need to recreate the AW in the 64-bit database, which leads us to the next section...
Before deleting AWs
In addition to gathering the OLAP workspace's name, schema, and tablespace, make sure that you make a note of how the AW is partitioned. This will allow you to more faithfully reconstruct the AW in the 64-bit database. Again, the basics can be found in Note 352306.1, except for a discussion of workspace partitioning. According to the documentation for DBMS_AW.AW_CREATE, by default, analytic workspaces are created with 8 partitions. Querying dba_segments seemed to tell a different story:
SYSTEM@mactest(10.2.0.4)>select segment_name 2 , segment_type 3 , count(*) 4 from dba_segments 5 where segment_name= 'AW$TESTDEFAULT' 6 group by segment_name 7 , segment_type; SEGMENT_NAME SEGMENT_TYPE COUNT(*) ------------------------------ -------------------- ---------- AW$TESTDEFAULT TABLE SUBPARTITION 16
This initially confused me, until I found that the table created for the default workspace is actually comprised of two partitions, each comprised of 8 subpartitions. Apparently, "partition" means different things to different people:
SYSTEM@mactest(10.2.0.4)>select table_name 2 , partition_name 3 , subpartition_count sub 4 from all_tab_partitions 5 where table_name = 'AW$TESTDEFAULT'; TABLE_NAME PARTITION_NAME SUB -------------------- -------------- ---------- AW$TESTDEFAULT PTN1 8 AW$TESTDEFAULT PTNN 8
So, before you delete the AWs in the 32-bit database, be sure to consult the data dictionary. In most cases, you'll probably see segment count of 16 in dba_segments (implying a default partitioning scheme). But there are exceptions...
There's always one goofball
One of those exceptions came when my query to get a count of AW segments returned a 1. Naturally, I was expecting an even number, so this came as a surprise. At first, I thought this might be a special case when specifying partnum=>1 when creating the workspace:
SYSTEM@mactest(10.2.0.4)>exec dbms_aw.aw_create('JPTEST.TEST1PART','USERS',1);
PL/SQL procedure successfully completed.
SYSTEM@mactest(10.2.0.4)>select segment_name
2 , segment_type
3 ,count(*)
4 from dba_segments
5 where segment_name = 'AW$TEST1PART'
6 group by segment_name
7 , segment_type
8 /
SEGMENT_NAME SEGMENT_TYPE COUNT(*)
------------------------------ -------------------- ----------
AW$TEST1PART TABLE SUBPARTITION 2
Then it occurred to me that zero is also a number... 
SYSTEM@mactest(10.2.0.4)>exec dbms_aw.aw_create('JPTEST.TEST0PART','USERS',0);
PL/SQL procedure successfully completed.
SYSTEM@mactest(10.2.0.4)>select segment_name
2 , segment_type
3 ,count(*)
4 from dba_segments
5 where segment_name = 'AW$TEST0PART'
6 group by segment_type
7 , segment_name
8 /
SEGMENT_NAME SEGMENT_TYPE COUNT(*)
------------------------------ -------------------- ----------
AW$TEST0PART TABLE 1
Please recall: Note 352306.1 recommends using the Analytic Workspace Manager (AWM) tool to recreate the AWs in the 64-bit database. If you want to create the AWs manually, I suggest engaging with Oracle Support to get their approval. The preceding examples are provided only for illustration of what's going on when the AW is created.
Importing AWs
I don't really have much to add here, other than:
- If you had any empty AWs in the 32-bit system, you won't have anything to import for those workspaces, though hopefully you recreated them in the 64-bit system
- Isn't this process the sort of thing that cries out to be scripted? Would I really resort to such cheap, obvious devices to foreshadow my next post? I would indeed!
