Invalid Schemas

[droodle]

I have various invalid scemas after applying a security patch .. i've been googling different cmds to re compile them, but not getting anywhere ..

I have tried running utlrp.sql but doesn't fix them and tried recompiling them individually .. but keep getting syntax errors

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

Please assist to rectify - thanks.

 

[taupirho]

What errros are you getting when recompiling these manually


In order to understand recursion, you must first understand recursion.

 

[droodle]

Well i'm not sure i'm using the correct syntax ..

SQL> ALTER "PACKAGE BODY" SCHEMENAME.DBMS_REPCAT_AUTH COMPILE;
ALTER "PACKAGE BODY" SCHEMENAME.DBMS_REPCAT_AUTH COMPILE
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> ALTER PACKAGE BODY SCHEMENAME.DBMS_REPCAT_AUTH COMPILE;
ALTER PACKAGE BODY SCHEMENAME.DBMS_REPCAT_AUTH COMPILE
*
ERROR at line 1:
ORA-00922: missing or invalid option

 

[taupirho]

$ CHAS:JAVA> sqlplus system/my_system_password

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Oct 17 15:08:30 2012

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning and Data Mining options

SQL> alter package DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL>



In order to understand recursion, you must first understand recursion.

 

[droodle]

Any idea whats happening now ?

SQL> alter package DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
---------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

SQL> alter package MGMT_BSLN COMPILE;
alter package MGMT_BSLN COMPILE
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN does not exist


SQL> alter package MGMT_BSLN_INTERNAL COMPILE;
alter package MGMT_BSLN_INTERNAL COMPILE
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN_INTERNAL does not exist


SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
---------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

SQL>

 

[taupirho]

Who "owns" the MGMT* objects . Try connecting as their owner and re-doing the compile command


In order to understand recursion, you must first understand recursion.

 

[droodle]

Thanks btw .. how do i find that out ? everything should be installed by the same user.

There does seem to be a discrepancy .. the original install was owned by a a user in a domain group and look the upgrade was done by a local account ..

-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 2560 Sep 25 15:40 PWDXXXX1.ORA
-rwxrwxrwa 1 Administrators SYSTEM 14467072 Oct 12 10:45 SNCFFT.ORA
-rwxrwxrwx 1 Administrators SYSTEM 5632 Sep 25 16:47 SPFILEFT.ORA
drwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 0 Sep 25 15:39 archive
-rwxrwxrwa 1 Administrators SYSTEM 106 Sep 25 17:05 core_ft_pid_3140_tid_x93C_2012_9_25_15_56_46.log
-rwxrwxrwa 1 Administrators SYSTEM 106 Oct 12 11:25 core_ft_pid_696_tid_x3F8_2012_9_25_17_6_43.log
-rwxrwxrwa 1 Administrators SYSTEM 106 Oct 12 11:56 core_ft_pid_872_tid_x9F8_2012_10_12_11_42_3.log
-rwxrwxrwa 1 Administrators SYSTEM 2048 Sep 25 17:05 hc_ft.dat
-rwxrwxrwx 1 Administrators SYSTEM 2048 Sep 25 15:40 hc_xxxx1.dat
-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 4524 Sep 25 15:56 initFT.ora
-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 4524 Sep 25 15:56 initFT.ora.safe

 

[taupirho]

SELECT OBJECT_NAME, owner FROM DBA_OBJECTS WHERE STATUS='INVALID'


In order to understand recursion, you must first understand recursion.

 

[droodle]

SQL> SELECT OBJECT_NAME, owner FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

OWNER
------------------------------
DBMS_REPCAT_AUTH
PUBLIC

DBMS_REPCAT_AUTH
SYSTEM

MGMT_BSLN
DBSNMP


OBJECT_NAME
--------------------------------------------------------------------------------

OWNER
------------------------------
MGMT_BSLN_INTERNAL
DBSNMP


SQL>

 

[droodle]

hi have you anything else to try ?

Still have the same issue

thanks.

 

[taupirho]

So for each invalid object you tried connecting to the database as the objects owner, then did an alter ... compile on it ?


In order to understand recursion, you must first understand recursion.

 

[carp]

Try the following:

SELECT 'ALTER '||DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||
        owner||'.'||object_name||' COMPILE'||DECODE(object_type,'PACKAGE BODY',' BODY;',';')
  FROM dba_objects 
 WHERE status = 'INVALID';

This should spit out your compilation statements.

 

[droodle]

So i tried this yesterday and although it said they have been altered - still showing as invalid !

D:\oracle\Bundle\Patch17>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 23 16:48:24 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> conn sys/ehealth as sysdba;
Connected.
SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE PACKAGE;

Package altered.

SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;

Package body altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN
MGMT_BSLN_INTERNAL

SQL>

 

[carp]

What object types are invalid? If they are package bodies and they have interdependencies, you are invalidating them by recompiling the packages. The package bodies are compiled against the other package specs. If you recompile an entire package (as you are doing), any package bodies that reference the package spec will be invalidated. Assuming this is the case, try this:

ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE;  -- TO RECOMPILE YOUR PACKAGE SPEC
ALTER PACKAGE MGMT_BSLN COMPILE BODY;    -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE
ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY;  -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE
ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;  -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE

Without full information, I cannot guarantee that this will fix your problem, but I am fairly certain it will.
This is one of the advantages of using packages instead of standalone procedures/functions - you can modify the logic without invalidating dependent objects (as long as you just recompile the body and not the spec).

 

[droodle]

Nope afraid not - thanks for your help so far ..

SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL> ALTER PACKAGE MGMT_BSLN COMPILE BODY;
ALTER PACKAGE MGMT_BSLN COMPILE BODY
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN does not exist


SQL> ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY;
ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN_INTERNAL does not exist


SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;

Package body altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN
MGMT_BSLN_INTERNAL

SQL>

 

[carp]

OK, so the two package bodies don't belong to the same schema you are using them. Use the fully-qualified form of schema.object_name and try it again. The objects will obviously stay invalid if you cannot get the compilation command to run successfully. Also, could you PLEASE include the object types with your query? You are giving us partial clues to your problem and it makes it rather difficult to diagnose your problem. Have you tried the original script I posted? That would give us the owner, type, and name - all of which are crucial elements.