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.
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.
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:
- Installed Oracle Database 19.3.0 into a new home
- Installed the January 2020 Release Update into this home and patched it to 19.6.0
- 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.
- Installed Oracle Database 19.3.0 into a new home
- Installed the January 2020 Release Update into this home and patched it to 19.6.0
- 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%.
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
- Patching all my environments with the January 2020 patch bundles
- Always create databases as CUSTOM databases
- Use your own templates in DBCA to create databases
- JAVAVM (OJVM) and XML Cleanup
- Spatial Data Option (SDO) Cleanup
–Mike
Mike,
I am sorry to ask an unrelated question but the situation is desperate. After upgrading several of our DW databases from 11g to 19c single tenant, we are seeing ORA-1555 very frequently. The ORA-1555 started right after the day of upgrade. So, one day on 11g, no issues. Next day on 19c and ORA-1555. I am using local undo for PDB. The same UNDO_RETENTION for 11g and 19c PDB. Same UNDO tablespace as was in 11g was copied to 19c PDB. Before I open SR, I thought of asking you since you have a broader range of customer experience. Have you had any other customer report such an issue?
Thanks,
Arun
Hi Arun,
so far I haven’t heard that people generally complain about getting more ORA-1555 after changing to LOCAL UNDO from non-CDB to PDBs.
I fear you will need to open an SR.
Cheers,
Mike
Thanks Mike. After posting here, as I was searching on Google, found something about ORA-1555 posted by Jonathan Lewis. It was very similar to what I am seeing in the database. So, I asked him and he pointed to this MOS note: “Wrongly calculated MAXQUERYLEN Causing ORA-01555 or ORA-30036 Errors (Doc ID 2005931.1)”. I had seen this, but this is very generic and did not have any Oracle version number, so I did not pay much attention. Apparently, we are hitting this bug. Here is link to Jonathan Lewis post in case someone else reports the issue:
https://jonathanlewis.wordpress.com/2020/02/04/maxquerylen/#comments
Thanks,
Arun
Hi Arun, thanks for the update – but I’m not fully convinced yet. The bugs the note and Jonathan refer to are all fixed as of 18.1 as far as I see. The note hasn’t IMHO really updated for the newest releases. I personally find it a bit disturbing when the release it applies to doesn’t get mentioned. As you feel exactly the same, I would recommend you opening an SR. Of course, it could be that 19c generates more undo traction than 11.2 does due to all the additional things in the dictionary.
So I’d do the following:
1) Tune the undo_retention
If that doesn’t help or ends up with too large undo:
2) Open an SR
Cheers and thanks for your update!
Mike
Hi Mike
I falled down into the same issue whith my CDB 19.5. I created a DB with OLTP template. I’ve not noticed that during this creation datapatch have generated errors.
When I launch manually datapach, this errors appears :
Patch 30125133 apply (pdb CDB$ROOT): WITH ERRORS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30125133/23151502/30125133_apply_DB195_CDBROOT_2019Dec18_10_28_59.log (errors)
-> Error at line 121869: script md/admin/catmdloc.sql
– ORA-29516: echec d’assertion Aurora : Bogus uncaught exception
-> Error at line 121870: script md/admin/catmdloc.sql
– ORA-00604: une erreur s’est produite au niveau SQL recursif 1
-> Error at line 121871: script md/admin/catmdloc.sql
– ORA-29532: appel Java arrete par une exception Java non interceptee :
-> Error at line 121925: script md/admin/catmdloc.sql
– ORA-29516: echec d’assertion Aurora : Bogus uncaught exception
-> Error at line 121926: script md/admin/catmdloc.sql
– ORA-00604: une erreur s’est produite au niveau SQL recursif 1
-> Error at line 121927: script md/admin/catmdloc.sql
– ORA-29532: appel Java arrete par une exception Java non interceptee :
As you, 256 objects belonging to MDSYS are INVALID. I try utlrp and as you ‘catcon.pl –n 1 –e –b utlrp –d ”’.”’ utlrp.sql’, but all invalid objects were allways there.
So, I supposed that the problem was due to a too small Java Pool Size during the creation phase. So, I have defined my java pool size to 1Gb, launch commands as told into the note 1917134.1 (sdounloadj.sql + sdoloadj.sql + utlrp), and now all is valid.
And, now datapach run without error :
Current state of release update SQL patches:
Binary registry:
19.5.0.0.0 Release_Update 190909180549: Installed
PDB CDB$ROOT:
Applied 19.5.0.0.0 Release_Update 190909180549 with errors on 20/02/20 10:10:14,715596
PDB PDB$SEED:
Applied 19.5.0.0.0 Release_Update 190909180549 with errors on 20/02/20 10:10:21,047560
PDB PDBDIG:
Applied 19.5.0.0.0 Release_Update 190909180549 with errors on 19/02/20 17:02:07,706133
Adding patches to installation queue and performing prereq checks…done
Installation queue:
For the following PDBs: CDB$ROOT
No interim patches need to be rolled back
Patch 30125133 (Database Release Update : 19.5.0.0.191015 (30125133)):
Apply from 19.1.0.0.0 Feature Release to 19.5.0.0.0 Release_Update 190909180549
No interim patches need to be applied
For the following PDBs: PDB$SEED PDBDIG
No interim patches need to be rolled back
Patch 30125133 (Database Release Update : 19.5.0.0.191015 (30125133)):
Apply from 19.1.0.0.0 Feature Release to 19.5.0.0.0 Release_Update 190909180549
No interim patches need to be applied
Installing patches…
Patch installation complete. Total patches installed: 3
Validating logfiles…done
Patch 30125133 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30125133/23151502/30125133_apply_DB195_CDBROOT_2020Feb20_15_59_37.log (no errors)
Patch 30125133 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30125133/23151502/30125133_apply_DB195_PDBSEED_2020Feb20_16_08_19.log (no errors)
Patch 30125133 apply (pdb PDBDIG): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30125133/23151502/30125133_apply_DB195_PDBDIG_2020Feb20_16_08_19.log (no errors)
SQL Patching tool complete on Thu Feb 20 16:12:28 2020
I am very excited to meet you when you come to Luxembourg on March 24 for Digora.
Pierre
Hi Mike
Re-install of the Spatial objects according 19c : INVALID SPATIAL in the registry due to INVALID JAVA Objects Owned By MDSYS (Doc ID 2658253.1) has helped to compile the invalid objects, but datapatch had still an error. As you mentioned, OJVM patch apply is required. Thanks for the hint.
Gruess Martin
Thanks Martin!
Cheers,
Mike
Hi Mike,
during/after upgrade 12.1.0.2 to 19.6.0 we get MDSYS objects invalid and Spatial as well invalid into registry.
Is there any know workaround on this?
I can only recommend you to try 19.8.0 instead – or otherwise please open an SR and check back with Oracle Support.
Thanks,
Mike
Hi Mike,
Just wanted to say thank you, thank you for the above utlrp script. I ran it a couple of times, and was able to get the invalid objects in pdb$seed from 1407 to 15. And then down to 7 after the Oracle January 2022 quarterly patch.
Thanks Elizabeth – glad it helped at least a bit 🙂
Cheers,
Mike