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 220.127.116.11 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 18.104.22.168 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 22.214.171.124.0 00:13:32 JServer JAVA Virtual Machine UPGRADED 126.96.36.199.0 00:02:58 Oracle XDK UPGRADED 188.8.131.52.0 00:00:37 Oracle Database Java Packages UPGRADED 184.108.40.206.0 00:00:11 OLAP Analytic Workspace UPGRADED 220.127.116.11.0 00:00:08 OLAP Catalog OPTION OFF 18.104.22.168.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.
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