32-bit to 64-bit database migration tips: OLAP upgrade

A while ago, I had the opportunity to migrate an E-Business Suite database (Apps version 12.0.4, database version 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(>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(>select table_name
2  , partition_name
3  , subpartition_count sub
4  from all_tab_partitions
5  where table_name = 'AW$TESTDEFAULT';

-------------------- -------------- ----------
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(>exec dbms_aw.aw_create('JPTEST.TEST1PART','USERS',1);
PL/SQL procedure successfully completed.

SYSTEM@mactest(>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(>exec dbms_aw.aw_create('JPTEST.TEST0PART','USERS',0);
PL/SQL procedure successfully completed.

SYSTEM@mactest(>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:

  1. 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
  2. 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!

Post a Comment

Your email is never published nor shared. Required fields are marked *