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.
