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:
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:
- 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
Why autoupgrade and not dbua?
Hahaha … is this a serious question?
Then please stroll around on the blog and watch our virtual classroom seminars.
DBUA has so many limitations, will be desupported soon – and is just a tool from the past which isn’t able to deal well with modern environments anymore.
AU does everything much better – MUCH BETTER.
Gee Willikers! I feel so prehistoric!
Dbua did get us from 22.214.171.124 to 19C, perhaps luckily.
At least I have two years to learn autoupgrade, before we have to go to the next long term release!
Thanks for the info, I was unaware dbua was deprecated. Perhaps dbua should indicate that when run.
Besides, I like dbua. I like watching each step get executed and being able to immediately react if there are issues. Does autoupgrade do the same?
I’m Hyunse Lee, working as a DBA at LG CNS.
I participated in an Oracle 19C upgrade seminar in Seoul on July 11th.
It was a great experience to learn a lot from the seminar.
Thank you for preparing a great seminar.
The reason I sent you this email is because of the recommended parameters provided by the seminar.
I have a question about optimizer_adaptive_plans.
Previously, when using the 12.2 version, Oracle recommended that the parameter be specified as False.
But, in this seminar, we were informed to designate the default value as True.
In 12.2 version, when optimizer_adaptive_plans is set to True, performance degradation often occurs
I’d like to know if you guided me to True because the issue has been resolved in 19C version.
In the situation of upgrading the 12 versions of DBs to 19C, all the parameters were set to False
I would like to ask if there will be a lot of problems even if DBs that are scheduled to be upgraded in the future are true.
Of course, there will be no correct answers for each project, but I would like to know the general recommendations.
Thank you for reading the long inquiry.
I hope you had a good experience in Korea.
please excuse my delayed response due to a longer absence.
And thanks for visiting our seminar. It was a true pleasure to speak to you and your teams.
The official recommendation is to have optimizer_adaptive_plans=true which suits most environments. So for instance, I have experienced live at a customer site in Frankfurt, Germany, that we set it to FALSE in the production environment since the customer had doubts. And it took about 5-10 minutes until the spikes on the system were visible. Just two queries benefited a lot from the adaptive plan feature – and those apparently went bad. Problem was: Both of these queries got executed several million times per hour. The impact on the system was brutal. So the feature did a fantastic job.
But there is always a flip side. With a customer in Poland we found a wrong result issue in 19.12.0 with adaptive plans. This has been fixed already.
As we explained, you need to test carefully. And please mail me directly under email@example.com for assistance. Roy and I are happy to help.
The problematic features were buried in the adaptive statistics – and those are off by default.
Thanks, and Korea was an incredible week – it is such a fantastic country. I hope to be back soon again!