utlrp.sql fails to execute

Question

I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside.

The process worked fine and no errors happens during the load of a huge sql file (10K+) with sqlplus. But on the last step the process breaks. I could pinpoint the issue to the following line for recompilation:

@?/rdbms/admin/utlrp.sql

The error received is the following:

ERRORS DURING RECOMPILATION
---------------------------
 0
Function created.
 p_schemaname dbms_id;
 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 5, column 18:
PL/SQL: Item ignored
ORA-06550: line 6, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 6, column 18:
PL/SQL: Item ignored
ORA-06550: line 62, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 62, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 65, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 64, column 12:
PL/SQL: Statement ignored
Function dropped.

I tried to find an answer on google but the best I could find is that there are some invalid objects. Which I couldn't find any.

SQL> SELECT owner,
 2 object_type,
 3 object_name,
 4 status
 5 FROM dba_objects
 6 WHERE status = 'INVALID'
 7 ORDER BY owner, object_type, object_name;
no rows selected

I am executing the process as sys with sysdba. Maybe the re-compilation is not necessary but I cannot remove it from the script.

A week ago I tried to do the same thing on a "real" Oracle 11g installation without docker. There the script worked. So I assume that there is something wrong with the container but I am not sure what exactly.

Did anybody encounter this issue before, do you have any ideas?

Thanks in advance

Version

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

• Michael Kutz: 11.2.0.1 is seriously bugged. Use 11.2.0.4. Better yet, go for 18c/19c version.
• Oldfighter: @MichaelKutz, would love to :) I have already have a 12c image and it works. Unfortunately the customer has 11 ...

Answer 1

DBMS_ID is a subtype in DBMS_STANDARD starting with version 12c.

DBMS_ID does not exist in 11g.

You have a messed up environment with 12c scripts and a 11g database.

The code that fails is in $ORACLE_HOME/rdbms/admin/reenable_indexes.sql, which is called by $ORACLE_HOME/rdbms/admin/utlprp.sql, which is called by $ORACLE_HOME/rdbms/admin/utlrp.sql.

This is an environmental issue but we do not know what you have done so far. In a properly created and configured environment this would not happen.

If you want to use 11g, look for a 11g image. There must have been someone desperate enough to create one. Like this one: docker-oracle-ee-11g. No, I haven't tested it.

• Oldfighter: Thanks for the answers. The explanation really helps. I tried the same with the image you linked in your answer. Unfortunately I experience the same error message with the other image. It seems like all db objects were correctly created. Do you maybe have an idea what else could be wrong?
• user153556: @Oldfighter I tested docker container from the link provided by Balazs it works fine.Invalidated an object and ran recompilation without any errors.
• Balazs Papp: @Oldfighter Let me guess. You connect to the 11g database remotely from somewhere else, where a 12c database is installed, and you executed @?/rdbms/admin/utlrp.sql in that session, and not locally inside the container. This syntax calls scripts local to the client, not the server, so the scripts are executed from the local 12c ORACLE_HOME, and not the 11g ORACLE_HOME installed in the container. If this the case, I suggest using DBMS_UTILITY.COMPILE_SCHEMA on the schema you work with, instead of running utlrp. docs.oracle.com/cd/E18283_01/appdev.112/e16760/…
• Oldfighter: @BalazsPapp, that was it. I tested it and it works. Thank you very much, this saved me so much time and effort!