Upgrade from 11.2.0.4 to 19c with Label Security Policies

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 11.2.0.4 to 19c with Label Security Policies. All other readers may simply ignore this blog post.

Photo by Erik Mclean on Unsplash

What happens?

You are using Oracle Label Security, and you upgrade from Oracle Database 11.2.0.4 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     19.14.0.0.0  00:13:32
JServer JAVA Virtual Machine           UPGRADED     19.14.0.0.0  00:02:58
Oracle XDK                             UPGRADED     19.14.0.0.0  00:00:37
Oracle Database Java Packages          UPGRADED     19.14.0.0.0  00:00:11
OLAP Analytic Workspace                UPGRADED     19.14.0.0.0  00:00:08
OLAP Catalog                         OPTION OFF      11.2.0.4.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:

MOS Note: 2878457.1 – With OLS Policy 11g to 19c Upgrade Fails as ORA-00942/ ORA-06512: at “LBACSYS.LBAC_SERVICES”

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:

1. Rollback to the GRP or restore and recover your backup you’d taken before upgrade
2. Then you need to preserve the table policies you’ve had created (this part is missing in the MOS note right now)
3. Afterwards you will drop the policies, e.g.

   BEGIN
   SA_POLICY_ADMIN.REMOVE_TABLE_POLICY('REGION_POLICY','OLS_TEST','CUSTOMERS');
   END;

4. Then you do the upgrade – when you use AutoUpgrade, it will run olspreupgrade.sql for you
5. 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

–Mike