OBIEE Upgrade - The SCHEMA_VERSION_REGISTRY$ table

Monday, October 13, 2014

Just recently I was performing an OBIEE upgrade for a client on a windows machine. We were going from a 11.1.1.6.0 to a 11.1.1.7.1 OBIEE installation which is a pretty straightforward if you follow the right steps.

 

In the following screen, I received a ‘UPGAST-00224:The specified database does not contain any schemas for Oracle MDS’ error.

 

 

I entered the right user information and I could login to the DEV_MDS schema, but the installer didn’t budge. The error read:

 

UPGAST-00224:The specified database does not contain any schemas for Oracle MDS

 

After some research, I found out that the client moved the repository schemas DEV_MDS and DEV_BIPLATFORM to a different server, but they adjusted the data source locations. This got me thinking in a distinct direction. After some google’ing, I found out that the system.SCHEMA_VERSION_REGISTRY$ holds vital information for the upgrade process! Naturally the client removed the old location so the system.SCHEMA_VERSION_REGISTRY$ was lost for good...

 

Luckily I always keep an OBIEE installation around, so I managed to find the table in a different installation! Below you can see the data in the table;

 

 

It indicates the version for the repository tables and without this data, the installer won’t continue and will keep throwing the UPGAST-00224 error at you.

 

So I just recreated the table with a user that had sysdba privileges (this is required) using the following derived script;

 

create table"SYSTEM"."SCHEMA_VERSION_REGISTRY$"

(

  "COMP_ID"      varchar2 (30byte) not null enable,

  "COMP_NAME"    varchar2 (255byte),

  "MRC_NAME"     varchar2 (30byte) not null enable,

  "MR_NAME"      varchar2 (30byte),

  "MR_TYPE"      varchar2 (30byte),

  "OWNER"        varchar2 (30byte),

  "VERSION"      varchar2 (30byte),

  "STATUS"       varchar2 (11byte),

  "CUSTOM1"      number,

  "CUSTOM2"      varchar2 (30byte),

  "UPGRADED"     char (1byte)default 'N',

  "START_TIME"   timestamp (6),

  "MODIFIED"     timestamp (6)

)

TABLESPACE "SYSTEM";

 

And the corresponding view;

 

create or replace force view "SYSTEM"."SCHEMA_VERSION_REGISTRY"

(

  "COMP_ID",

  "COMP_NAME",

  "MRC_NAME",

  "MR_NAME",

  "MR_TYPE",

  "OWNER",

  "VERSION",

  "STATUS",

  "UPGRADED",

  "START_TIME",

  "MODIFIED"

)

as

    selectcomp_id,

           comp_name,

           mrc_name,

           mr_name,

           mr_type,

           owner,

           version,

           status,

           upgraded,

           start_time,

           modified

      from SYSTEM.SCHEMA_VERSION_REGISTRY$

  order by comp_id;

 

Once the table is ready, just insert the above mentioned data in line with your environment (make sure the OWNER information is correct).


Good luck!

 

PVE/JUVO