54 Invalid Objects when running utlrp.sql script

[valiantvimal]

Hi Tim,
I am migrating database from Windows to Linux using RMAN convert. When running utlrp.sql script as final step I am getting 54 invalid objects. I don't know how to rectify those objects. Can you give me an idea to resolve this?

Thanks and regards,
Vimal.

[Tim...]

Hi.

You need to deal with them on a case-by-case basis. So for example,

Code: Select all

alter package my_package compile body;
show errors

The resulting errors will give you an indication of the problem causing the compilation errors.

Cheers

Tim...

[valiantvimal]

I tried alter package my_package compile body; but it says object does not exists...

when I issue show errors it says no errors but when I see through dba_objects or obj$, I could see 54 invalid objects.

[valiantvimal]

Hi Tim,

I am posting in detail for your reference:

The database version is Oracle 10.2.0.1(32 bit). Migration is from Windows to Linux. Windows version is Vista-64 bit and Linux version is OEL5- 32 bit.
The owner, object_name, object_type are as follows:
SQL> select object_name,object_type,owner from dba_objects where status='INVALID';
6
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$OLAP2_AW_CATALOGS
VIEW OLAPSYS

ALL_OLAP2_AW_CATALOGS
SYNONYM PUBLIC

ALL$OLAP2_AW_CATALOG_MEASURES
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_OLAP2_AW_CATALOG_MEASURES
SYNONYM PUBLIC

ALL$OLAP2_AW_PHYS_OBJ
VIEW OLAPSYS

ALL_OLAP2_AW_PHYS_OBJ
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$OLAP2_AW_PHYS_OBJ_PROP
VIEW OLAPSYS

ALL_OLAP2_AW_PHYS_OBJ_PROP
SYNONYM PUBLIC

ALL$OLAP2_AW_DIMENSIONS
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_OLAP2_AW_DIMENSIONS
SYNONYM PUBLIC

ALL$OLAP2_AW_ATTRIBUTES
VIEW OLAPSYS

ALL_OLAP2_AW_ATTRIBUTES
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$OLAP2_AW_CUBES
VIEW OLAPSYS

ALL_OLAP2_AW_CUBES
SYNONYM PUBLIC

ALL$OLAP2_AW_CUBE_DIM_USES
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_OLAP2_AW_CUBE_DIM_USES
SYNONYM PUBLIC

ALL$AW_DIM_ENABLED_VIEWS
VIEW OLAPSYS

ALL_AW_DIM_ENABLED_VIEWS
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$AW_CUBE_ENABLED_VIEWS
VIEW OLAPSYS

ALL_AW_CUBE_ENABLED_VIEWS
SYNONYM PUBLIC

ALL$AW_CUBE_ENABLED_HIERCOMBO
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_AW_CUBE_ENABLED_HIERCOMBO
SYNONYM PUBLIC

ALL$OLAP2_AW_DIM_LEVELS
VIEW OLAPSYS

ALL_OLAP2_AW_DIM_LEVELS
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$OLAP2_AW_DIM_HIER_LVL_ORD
VIEW OLAPSYS

ALL_OLAP2_AW_DIM_HIER_LVL_ORD
SYNONYM PUBLIC

ALL$OLAP2_AW_CUBE_MEASURES
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_OLAP2_AW_CUBE_MEASURES
SYNONYM PUBLIC

ALL$OLAP2_AW_CUBE_AGG_SPECS
VIEW OLAPSYS

ALL_OLAP2_AW_CUBE_AGG_SPECS
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL$OLAP2_AW_CUBE_AGG_MEAS
VIEW OLAPSYS

ALL_OLAP2_AW_CUBE_AGG_MEAS
SYNONYM PUBLIC

ALL$OLAP2_AW_CUBE_AGG_LVL
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
ALL_OLAP2_AW_CUBE_AGG_LVL
SYNONYM PUBLIC

ALL$OLAP2_AW_CUBE_AGG_OP
VIEW OLAPSYS

ALL_OLAP2_AW_CUBE_AGG_OP
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
MRAC_OLAP2_AW_DIMENSIONS_V
VIEW OLAPSYS

MRAC_OLAP2_AW_ATTRIBUTES_V
VIEW OLAPSYS

MRAC_OLAP2_AW_CUBES_V
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
MRAC_OLAP2_AW_CUBE_MEASURES_V
VIEW OLAPSYS

MRV_OLAP2_AW_DIMENSIONS
SYNONYM PUBLIC

MRV_OLAP2_AW_ATTRIBUTES
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
MRV_OLAP2_AW_CUBES
SYNONYM PUBLIC

MRV_OLAP2_AW_CUBE_MEASURES
SYNONYM PUBLIC

CWM2_OLAP_METADATA_REFRESH
PACKAGE BODY OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
OLAP_SYS_AW_ACCESS_DIM_VIEW
VIEW OLAPSYS

OLAP_SYS_AW_ACCESS_CUBE_VIEW
VIEW OLAPSYS

OLAP_SYS_AW_ENABLE_ACCESS_VIEW
VIEW OLAPSYS
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
OLAP_SYS_AW_ACCESS_CUBE_VIEW
SYNONYM PUBLIC

OLAP_SYS_AW_ACCESS_DIM_VIEW
SYNONYM PUBLIC

OLAP_SYS_AW_ENABLE_ACCESS_VIEW
SYNONYM PUBLIC
OBJECT_NAME
OBJECT_TYPE OWNER
------------------------------
CWM2_OLAP_OLAPAPI_ENABLE
PACKAGE BODY OLAPSYS

DBMS_AWM
PACKAGE BODY OLAPSYS

CWM2_OLAP_CUBE
PACKAGE BODY OLAPSYS
54 rows selected.

When I try to compile it manually, I get:

SQL> alter package CWM2_OLAP_OLAPAPI_ENABLE compile body;
alter package CWM2_OLAP_OLAPAPI_ENABLE compile body
*
ERROR at line 1:
ORA-04043: object CWM2_OLAP_OLAPAPI_ENABLE does not exist
I get same error for other invalid objects also.

Thanks.

[Tim...]

Hi.

Did you compile them as the owner? If not, you have to prefix with the owner of the object.

Code: Select all

alter package OLAPSYS.CWM2_OLAP_OLAPAPI_ENABLE compile body;

Cheers

Tim...

[valiantvimal]

I did as you said. But I get - Warning: View altered with compilation errors.

After this command I checked with total invalid objects again, I get same as before: 54 invalid objects.

Thanks.

[Tim...]

Hi.

I'm not suggesting this will fix it. I'm saying *you* need to attempt a compilation of each individual item to allow *you* to identify the specific compilation errors that prevented that item from compiling. Once you know that, *you* can make a decision about what is needed to allow that item to be compiled.

For example, if the package body scott.my_package fails to compile, I can do this,

Code: Select all

alter package scott.my_package compile body;
show errors

In attempting to compile the object (and failing) I can use show errors to list the specific reasons why it fails. I can then take the appropriate action to allow the object to compile.

In many cases, the failures are due to missing system or object privileges, in which case you must grant them to allow the object to compile.

Just to confirm, this is not the solution to the failed compilations, it is the process *you* must follow to investigate and subsequently correct the issues.

Cheers

Tim...

[valiantvimal]

Tim I understand what you mean. As you said it is not the one-step solution.

Coming to the issue,
After compilation, when I issue Show Error, I get the following result...

SQL> show errors
Errors for PACKAGE BODY OLAPSYS.CWM2_OLAP_CUBE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
588/28 PL/SQL: SQL Statement ignored
588/55 PL/SQL: ORA-04063: view "OLAPSYS.OLAP_SYS_AW_ENABLE_ACCESS_VIEW"
has errors

Do you have any clue for the type of error it is?

Thanks.

[Tim...]

Hi.

So the package won't compile because the view has errors. Now what you need to do is compile the view and see what errors it has. Once you've fixed the view, the package should compile.

Cheers

Tim...

[valiantvimal]

Hi Tim,
My database version is (32-bit)10.2.0.1 running on windows 64-bit. I am migrating this to 32-bit Linux. Is this generally possible first of all? What do you think?

Moreover, the error I get now is (AFTER COMPILATION):

SQL> show error
Errors for PACKAGE BODY SYS.CWM2_OLAP_INSTALLER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PL/SQL: Item ignored
7/5 PL/SQL: Statement ignored
7/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.CREATE_MEASURE' must be
declared

10/5 PL/SQL: Statement ignored
10/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.DROP_MEASURE' must be
declared

12/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
be declared

20/3 PL/SQL: Item ignored
22/5 PL/SQL: Statement ignored
22/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.CREATE_CUBE' must be
declared

25/5 PL/SQL: Statement ignored
25/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.DROP_CUBE' must be declared
27/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
be declared

35/3 PL/SQL: Item ignored
37/5 PL/SQL: Statement ignored
37/5 PLS-00201: identifier
'CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE' must be
declared

40/5 PL/SQL: Statement ignored
40/5 PLS-00201: identifier
'CWM2_OLAP_LEVEL_ATTRIBUTE.DROP_LEVEL_ATTRIBUTE' must be declared

42/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
be declared

52/5 PL/SQL: Statement ignored
52/5 PLS-00201: identifier 'CWM2_OLAP_LEVEL.CREATE_LEVEL' must be
declared

SQL>


Kindly, guide me please...

Vimal.

[Tim...]

Hi.

I don't work on Windows very often and I don't think I've ever done this, so I'm not really the person to comment on it.

A quick Google shows this:

http://www.orafaq.com/node/2342

Oracle recommend using expdp/impdp for platform changes as a guaranteed solution. That's not to say it is the only method, but it's the slow and steady approach.

Cheers

Tim...