How to validate invalid objects in pdb$seed pluggable database

 

Env:- 2 node RAC Database

DB Version: 12.2.0.1

Platform:- Exadata x6

OS- OEL 7

 

Recently , while working on a crucial database upgrade project (12.2.0.1 to 19c), I came across with one observation .

For upgrade , I ran preupgrade.jar script which generally throws a list of recommendations along with fixes . After checking the recommendations I found out one of the recommendation, was related to validation of invalid objects that was  residing inside SYS or SYSTEM schema in PDB$SEED .

 

Now generally we run utlrp.sql to validate the invalid objects , so I did the same to fix the invalid objects

SQL> @?/rdbms/admin/utlrp SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 834 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># DECLARE * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.UTL_RECOMP", line 875 ORA-06512: at line 4 SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 834 DOC> The following query reports the number of objects that have compiled DOC> with errors. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database or pluggable database open for read-only access PL/SQL procedure successfully completed. DROP function local_enquote_name * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access Warning: XDB now invalid, could not find xdbconfig ORDIM INVALID OBJECTS: ORDIMDPCALLOUTS - INVALID - PACKAGE BODY PL/SQL procedure successfully completed. SQL>

 

In short, it failed as the pdb$seed pdb is in read only mode.

SQL> show pdbs       CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO

 

 

So to validate the invalid objects in PDB$SEED I followed the following steps :-

sqlplus / as sysdba   SQL> ALTER SESSION SET CONTAINER = PDB$SEED;   SQL> show con_name;   CON_NAME ------------------------------ PDB$SEED   -- I Set a hidden parameter at session level SQL> alter session set "_oracle_script" = true instances=all;   -- Now lets try to open the pdb$seed instance   SQL> alter pluggable database pdb$seed close immediate instances=all;   SQL> alter pluggable database pdb$seed open read write instances=all; SQL> show pdbs       CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ WRITE NO   SQL> @?/rdbms/admin/utlrp.sql   SQL> select count(*) from dba_objects where status='INVALID' AND OWNER IN ('SYS','SYSTEM');     COUNT(*) ----------          0   SQL> alter session set "_oracle_script" = true instances=all;   SQL> alter pluggable database pdb$seed close instances=all; <Make sure there is no session active to pdb$seed database from any other terminal>   SQL> alter pluggable database pdb$seed OPEN READ ONLY instances=all;

 

So this way I validated all the invalid objects in pdb$seed  PDB.