ORA-13516 “CATPROC not valid” during datapatch or autoupgrade to 19.18.0

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.

Photo by Greg Rosenke on Unsplash

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

–Mike