Issues with seed databases, patch bundles and OJVM in 19c

I really rely on people telling me about issues they saw. And in this case, it happened twice within a few days. At OOW London, a customer came after my talk and told me about invalid objects and an incredible long recompilation time. In fact, he had to apply an OJVM patch in order to solve this. When I returned home, Jure commented on the blog about a very similar issue. I spent some time on the weekend to check it out. And I realized: There are issues with prebuilt seed databases and OJVM in 19c.

Issues with seed databases, patch bundles and OJVM in 19c

Photo by Max Leveridge on Unsplash

Seed databases?

Well, as you may know, I’m not a fan of using the prebuilt seed databases you can choose in DBCA when you create a new database. I blogged about this in the past and had some discussions with colleagues about it, too.

One of the main reasons is that these databases have all components installed. Then your upgrade will take longer as more scripts will have to be run. And component upgrades are not happening in parallel with a few exceptions.

Issues with prebuilt seed databases and OJVM in 19c

The problem does not happen when you create a CUSTOM database

The below issue seems to not happening with the CUSTOM creation in DBCA but only when you choose OLTP or DWH.

What’s the issue?

This is what Jure commented on the blog:

Hi,
Just to inform all of you that this latest 19.6 RU (patch 30557433) has a bug which is not mentioned among known issues…

Bug 30521071 – 19.5 OJVMRU patch is mandatory to apply prior to 19.5DBRU/19.6DBRU bundle patch was created.

I’ve patched 19.3 oracle_home with this JAN2020 RU and after creating new instance out of this patched home there are:
256 invalid objects (mdsys objects)
– and here also EM Express does not work.. (on 19.3 it does).
– compiling invalids via @utlrp.sql takes ages…literally 10 min..(on 19.3 it finishes immediately)

The current workaround is to go from 19.3 to 19.4–>19.5–>19.6 applying each RU separately..

..that much about patches being “cumulative”..

I investigated and checked the non-public bug at first. But from the bug I couldn’t really figure out the issue Jure and the customer in London told me about. So I started reproducing the case.

Attempt 1 – Create a CUSTOM database

For the first attempt, I did the following:

  1. Installed Oracle Database 19.3.0 into a new home
  2. Installed the January 2020 Release Update into this home and patched it to 19.6.0
  3. Then I created – as I usually do – a CUSTOM multitenant database with almost all components in it

And the result?

No issues at all. Everything worked fine. As usual, the CUSTOM creation took very long due to the hundreds of resize operations especially for the PDB$SEED. But I knew that already.

Then I asked myself: What could be different in the case Jure described above? And this lead me to attempt 2.

Attempt 2 – Create a SEED database

Same start as in attempt 1, but now I take a different approach with DBCA.

  1. Installed Oracle Database 19.3.0 into a new home
  2. Installed the January 2020 Release Update into this home and patched it to 19.6.0
  3. Then I created a SEED OLTP multitenant database with almost all components in it

I let it run on Sunday afternoon. And when I returned to my desk, I was wondering why the creation hadn’t been finished hours later. DBCA seemed to got stuck at 54%.

Issues with seed databases, patch bundles and OJVM in 19c

I checked the alert.log:

$ tail -f alert_OLTP19C.log
30247305,30252098,30252156,30253608,30255143,30264405,30266791,30269428,
30274188,30282591,30299817,30312094,30318638,30324180,30342878,30365745,
30389229,30402386,30408515,30412188,30453442,30458593,30474167,30474774,
30485255,30534827,30545281,30641755
===========================================================
2020-02-16T18:54:14.754321+01:00
PDB(3):SERVER COMPONENT id=UTLRP_BGN: timestamp=2020-02-16 18:54:14 Container=PDB Id=3
2020-02-16T18:54:17.865683+01:00
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 3 seq# 15 mem# 0: /u02/oradata/OLTP19C/redo03.log

My first suspicion was “no space left on device”. But there was plenty of space.

l logged in to the fresh CDB as it obviously got stuck in recompilation:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  YES
	 3 PDB				  READ WRITE YES

And I checked for INVALID objects:

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
       256

Ok, this is the case Jure was commenting on. And while I’m writing this blog post, finally DBCA came to a successful end. The compilation had taken a bit longer than usual

2020-02-16T18:54:14.754321+01:00
PDB(3):SERVER COMPONENT id=UTLRP_BGN: timestamp=2020-02-16 18:54:14 Container=PDB Id=3
2020-02-16T18:54:17.865683+01:00
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 3 seq# 15 mem# 0: /u02/oradata/OLTP19C/redo03.log
2020-02-16T20:26:46.230384+01:00
PDB(3):SERVER COMPONENT id=UTLRP_END: timestamp=2020-02-16 20:26:46 Container=PDB Id=3
2020-02-16T20:26:48.169003+01:00
PDB(3):
PDB(3):XDB installed.
PDB(3):
PDB(3):XDB initialized.

Right away, I started a manual compilation again to see whether I could get rid of the invalid 256 objects in CDB$ROOT.

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 16 20:40:31 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

  COUNT(*)
----------
       256

SQL>  SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

  COUNT(*)
----------
       256

No luck.

The really bad thing here: The PDBs don’t open unrestricted at this stage:

SQL> 
Warning: PDB altered with errors.

SQL> 
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  YES
	 3 PDB				  READ WRITE YES

Now I was wondering: Will the OJVM patch solve this problem? I’ve had patch 30484981 on disk already as I applied the OJVM 19.6.0 patch for another test a week ago. I shutdown my database which seem to hang again in the compilation phase.

Applying interim patch '30484981' to OH '/u01/app/oracle/product/19JVM'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...
Patch 30484981 successfully applied.
Log file location: /u01/app/oracle/product/19JVM/cfgtoollogs/opatch/opatch2020-02-16_20-47-25PM_1.log

OPatch succeeded.

And before I created another database similar to attempt 2, I wanted to check whether “datapatch” would resolve this compilation disaster.

Does the OJVM patch solve the problem?

Once I applied the OJVM patch successfully, I had to run datapatch:

$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.6.0.0.0 Production on Sun Feb 16 20:49:42 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6035_2020_02_16_20_49_42/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981)):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB: Not installed
  PDB PDB$SEED: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.6.0.0.0 Release_Update 191217155004: Installed
  PDB CDB$ROOT:
    Applied 19.6.0.0.0 Release_Update 191217155004 with errors on 16-FEB-20 05.19.44.192301 PM
  PDB PDB:
    Applied 19.6.0.0.0 Release_Update 191217155004 with errors on 16-FEB-20 05.19.50.075434 PM
  PDB PDB$SEED:
    Applied 19.6.0.0.0 Release_Update 191217155004 with errors on 16-FEB-20 05.19.50.075434 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDB
    No interim patches need to be rolled back
    Patch 30557433 (Database Release Update : 19.6.0.0.200114 (30557433)):
      Apply from 19.1.0.0.0 Feature Release to 19.6.0.0.0 Release_Update 191217155004
    The following interim patches will be applied:
      30484981 (OJVM RELEASE UPDATE: 19.6.0.0.200114 (30484981))

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
Patch 30557433 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_OLTP19C_CDBROOT_2020Feb16_20_51_03.log (no errors)
Patch 30484981 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30484981/23248235/30484981_apply_OLTP19C_CDBROOT_2020Feb16_20_50_20.log (no errors)
Patch 30557433 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_OLTP19C_PDBSEED_2020Feb16_20_54_11.log (no errors)
Patch 30484981 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30484981/23248235/30484981_apply_OLTP19C_PDBSEED_2020Feb16_20_53_56.log (no errors)
Patch 30557433 apply (pdb PDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30557433/23305305/30557433_apply_OLTP19C_PDB_2020Feb16_20_54_09.log (no errors)
Patch 30484981 apply (pdb PDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30484981/23248235/30484981_apply_OLTP19C_PDB_2020Feb16_20_53_56.log (no errors)
SQL Patching tool complete on Sun Feb 16 20:56:57 2020

After another restart of the database, I could at least open the PDBs now unrestricted:

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB				  READ WRITE NO

But how about the INVALID objects?

Well …

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
       995

This looks worse than before. Just for the records, the entire CDB with all PDBs had now 1934 INVALID objects.
Holy Moly!

What’s next?

Ok, last chance – recompilation of CDB$ROOT, PDB$SEED and the PDB.

$ cd $ORACLE_HOME/rdbms/admin
[HUGOJVM] oracle@hol:/u01/app/oracle/product/19JVM/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19JVM/rdbms/admin/utlrp_catcon_14704.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19JVM/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19JVM/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

Let’s check the result:

SQL> select count(*) from cdb_objects where status='INVALID';

  COUNT(*)
----------
	 0

Ok, problem finally solved.

Summary

That is a weird issue. I had no chance to compile the 256 invalid objects belonging to MDSYS without applying an OJVM patch. You don’t have to go step by step as Jure assumed – but that is not so important. The important fact is that I won’t get a clean database with all objects VALID in a freshly created seed database without applying the OJVM patch beforehand. And I have to do this compilation stunt, and wait quite a bit. Not cool!

Of course, this doesn’t happen when you create a CUSTOM database as I usually do. But there’s no doubt that this shouldn’t happen when you use a standard option the DBCA provides.

Why does it not happen with a CUSTOM database creation?

When you create a CUSTOM database, all scripts to build the dictionary will be run. So you will get a fresh clean dictionary whereas a prebuild seed database brings it’s SYSTEM tablespace already. During the creation, some adjustments will happen but you don’t have to wait for the dictionary being built and populated.

Further Information and Links

–Mike

Share this: