I thought I could relax this week using my leftover vacation days from 2022. But this issue is triggering me since some of you mailed me already. Maybe this blog post can prevent others from being trapped by this issue: ORA-13516 “CATPROC not valid” during datapatch or autoupgrade to 19.18.0. And as a prolog, you will NOT see the below issue when you started your database in NORMAL mode for the execution of datapatch.
What is the symptom?
Regardless of whether you patch to Oracle Database RU 19.18.0 manually or with AutoUpgrade‘s super cool patching feature, you may hit this problem when your database is NOT in UPGRADE mode when you invoke datapatch. Actually, at the moment this is the norm when you use AutoUpgrade‘s patching feature. We introduced this last fall. At the moment, AutoUpgrade leaves the database in UPGRADE mode when it invokes datapatch. This is not a bigger issue but we optimized it already – and you will see a silent change in behavior in a future edition of AutoUpgrade soon.
But the same problem easily can happen when you patch your database manually, and either intentionally or by accident leave your database in UPGRADE mode. Just remember that UPGRADE mode is not required to execute datapatch. But I know that some of you do this intentionally to prevent connections, especially in cases where downtime is not an issue.
In these cases, you may see this error pattern:
ORA-13516: AWR Operation failed: CATPROC not valid ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 328 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 355 ORA-06512: at line 12
And even if you rerun datapatch or AutoUpgrade, the problem does not disappear.
You may see the same issue when you start the database in RESTRICTED mode. It will not appear when the database is open in NORMAL mode.
What is the root cause?
The problem is coming from the inclusion of unpublished BUG 33527739 – TRACKING BUG TO INCLUDE AWR TABLE WRH$_TEMPSTATXS AND WRH$_FILESTATXS AT FLUSHING LEVEL TYPICAL Regression Alert into 19.18.0. This bug has a SQL script included which gets executed by datapatch. And this SQL script will fail when the database is open either in UPGRADE or in RESTRICTED mode. This is the first time that we saw something – at least in my case.
We have seen this issue only with Oracle Database Release Update 19.18.0 so far. And the below fix has been approved for inclusion in 19.19.0 for April 2023 already. Hence, in case you skip 19.18.0 you may not see this issue at all.
How do you fix it?
There are several options to fix or workaround it quickly. All of the below is described in MOS Note: 2922690.1 – 19.x:datapatch failed with ‘ORA-13516: AWR Operation failed: CATPROC not valid’.
Option 1 – You apply a fix to 19.18.0 before you change to the new home
This patch is downloadable from MyOracle Support via unplublished BUG 35012866 – BUG_33527739_APPLY.SQL GOT ORA-13516: AWR OPERATION FAILED: CATPROC NOT VALID. At the moment, I find the fix being available on top of 19.18.0 for Linux and AIX. If you apply it beforehand to your 19.18.0 you will not see the issue coming up.
Option 2 – You rerun datapatch manually
In case you have hit this issue, start your database in NORMAL mode and invoke datapatch again. Then it should complete successfully, and you’ll be able to generate AWR snapshots again.
Are there any additional pitfalls?
Yes, in very rare cases some customers received the following error when applying the above patch:
Error at line 47019: script rdbms/admin/backport_files/bug_33527739_apply.sql - ORA-06550: line 12, column 24: - PLS-00302: component 'FIXUP_CATALOG' must be declared - ORA-06550: line 12, column 5:
If this happens, please simply invoke datapatch -verbose again, then it will go away.
Further Links and Information
- MOS Note: 2922690.1 – 19.x:datapatch failed with ‘ORA-13516: AWR Operation failed: CATPROC not valid’
- Patch Download for Bug 35012866 from MyOracle Support
- AutoUpgrade Download
… and if I create fresh and new Databases in an Oracle home where the 19.18 patch is applied?
Do I have to expect this error, too? Or will it not be an issue …
It happens only when datapatch gets invoked in UPGRADE/RESTRICTED mode. As far as I am aware, DBCA does not run anything in UPGRADE mode. Hence, you should not see this issue when creating a new DB.
We have a similar issue on one of our OCI DBSystem with a couple of pdbs:
Patch 34765931 apply (pdb XXXXX): WITH ERRORS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CDBTEST_XXXXX_2023Feb24_10_45_52.log (errors)
-> Error at line 338186: script rdbms/admin/backport_files/bug_33527739_apply.sql
– ORA-13520: Database id (106171561) not registered, Status = 1
– ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 328
– ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 355
– ORA-06512: at line 12
At this time, no solution provided by the support works ;-(.
I see that – and there is not much I can do here.
Please escalate the SR, link to my blog post – and ask for a management callback.
I long question 🙂
Up till 19.17, we applied DB ,OJVM and datapump patch with opatch, and the ran the datapatch with the database in upgrade mode ( this is on windows and the database must be in upgrade mode because of OJVM).
OJVM 19.18 for windows has not been released yet, so I don’t know if it still require upgrade mode. But if it dos, should we then apply the database bundle patch and datapump with the database in normal mode. And then after apply the OJVM.
Or can we still apply all 3 , then run datapatch twice – one with the database in normal mode and one in upgrade mode.
upgrade mode is not required for OJVM:
And this applies to Windows as well.
You should be able to apply all, ideally into a new home, then switch your service and your database, and then invoke datapatch.
Thanks for your answer Mike.
We have joined our oracle contact to try to fix this issue.
Thanks for your answer Mike
You have said couple of times on your blog that at some point, Oracle will make patching as easy as updating an app on my phone. With all these issues and unexpected side effects after applying a patch or sometimes issues with the patch itself, would it really be possible to make patching as easy?
we’ll try our best – and please be patient, we will have some cool news later this year.
During my tests on virtual machines I got this error and I was using latest autoupgrade. during DBUPGRADE stage, it got an error and when I follow the logs I found this error. now at that point, db is started in new home and some datapatch job is done.
by looking the solutions you provide, should I just start database in normal mode (it is open now so I will shutdown and startup) and re run datapatch. is this all? should I do something about autoupgrade? since it might have some more steps to do right?
yes, start it and invoke datapatch again while the DB is up in regular mode.