I remember, I have a blog. Forgive me that I don’t write a lot at the moment even though I still have a long queue of “future posts to write”. It is just too busy, and I was away for almost two full weeks in June as well. But let me briefly drop information on an issue I learned about recently. It just affects your when Upgrade from 18.104.22.168 to 19c with Label Security Policies. All other readers may simply ignore this blog post.
You are using Oracle Label Security, and you upgrade from Oracle Database 22.214.171.124 to Oracle Database 19c. You have your own Label Security Policies – and then, during upgrade, you will see this error in the upgrade summary:
Oracle Database Release 19 Post-Upgrade Status Tool 06-26-2022 06:38:5 Database Name: HUGO Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 126.96.36.199.0 00:13:32 JServer JAVA Virtual Machine UPGRADED 188.8.131.52.0 00:02:58 Oracle XDK UPGRADED 184.108.40.206.0 00:00:37 Oracle Database Java Packages UPGRADED 220.127.116.11.0 00:00:11 OLAP Analytic Workspace UPGRADED 18.104.22.168.0 00:00:08 OLAP Catalog OPTION OFF 22.214.171.124.0 00:00:00 Oracle Label Security ORA-12432: LBAC error: Create or drop triggers failed during upgrade ORA-06512: at line 5 ORA-12433: create trigger failed, policy not applied ORA-06512: at "LBACSYS.LBAC_SERVICES", line 117 ORA-06512: at line 2
Now don’t start dropping anything by yourself. If you do so, you may create a mess.
How do you solve this?
Unfortunately, there is only one way to solve this right now: You need to rollback and restart the upgrade again.
This MOS Note has been written to describe the correct solution:
The current version of olspreupgrade.sql which you execute in such a situation (or AutoUpgrade does for you) does not handle this situation. olspreupgrade.sql is in your 19c home and will for instance move the auditing table when you upgrade from 11g to 19c. The audit table AUD$ is in the SYSTEM user schema when you have OLS (Oracle Label Security) installed – and it needs to be move into the SYS user schema as part of the upgrade.
In a later stage, olspreupgrade.sql may be able to handle this. But right now, it doesn’t.
So you will need to:
- Rollback to the GRP or restore and recover your backup you’d taken before upgrade
- Then you need to preserve the table policies you’ve had created (this part is missing in the MOS note right now)
- Afterwards you will drop the policies, e.g.
BEGIN SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('REGION_POLICY','OLS_TEST','CUSTOMERS'); END;
- Then you do the upgrade – when you use AutoUpgrade, it will run olspreupgrade.sql for you
- After the upgrade completed successfully, you then recreate your table policies again
As always, please don’t shoot the messenger.
I guess this will apply only to a very small number of customers. But if you got trapped by this, this blog post may help you to solve the issue.
Update Jan 9, 2023
It has been a while since I wrote this blog post. And the above (or below) MOS note is “not public” at the moment. Development has now produced a fix for the issue making manual interactions unnecessary. This fix has been done in ?/rdbms/admin/olse112.sql.
At this moment I can’t tell you in which RU this fix will be included but the earliest possibility will be 19.19.0 in April 2023 since code freeze for 19.18.0 has been passed for weeks.
For you seeing this issue this means you should request a one-off patch for bug 34149109 which basically will exchange ?/rdbms/admin/olse112.sql in your database home. Of course, you need to apply the fix before you upgrade your database.
Further Links and Information
- MOS Note: 2878457.1 – With OLS Policy 11g to 19c Upgrade Fails as ORA-00942/ ORA-06512: at “LBACSYS.LBAC_SERVICES”
- Unpublished BUG:34149109 – 19C UPGRADE FROM 11G FAIL WITH ORA-12432: LBAC ERROR: CREATE OR DROP TRIGGERS FAILED DURING UPGRADE