Well it's been almost three years since my last post and while I haven't blogged for that long I am still working with Oracle technologies. I joined Accenture Enkitec Group in February 2018 and I've been working on a big migration project to Oracle Cloud Infrastructure for a UK customer since then. I should have posted some of the cloud stuff I did or issues I encountered but I didn't for one reason or another. I hope I can resume blogging and as before share issues and interesting things that I've been doing. Now on to the main topic of this post.
I patched Exadata recently, it was X5-2 running ESS 18.1.21 and 188.8.131.52.191015 BP. I applied latest available at that time - ESS 18.1.24 and 184.108.40.206.200114. And while everything was fine with storage cells, compute nodes and GI patching, the DB post-patching failed during the datapatch, shortly after running datapatch I got the following error:
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_221343_2020_03_06_18_49_03/sqlpatch_invocation.log: Patch 30372179 rollback (pdb CDB$ROOT): WITH ERRORS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30372179/23142433/30372179_rollback_PDB_CDBROOT_2020Mar06_18_51_42.log (errors) Error at line 5480: ORA-46385: Message 46385 not found; product=RDBMS; facility=ORA
So the patch 30372179 failed to rollback. This patch was a merge request actually:
Patch 30372179: MERGE REQUEST ON TOP OF DATABASE BP 220.127.116.11.191015 FOR BUGS 29990674
The patch will fix two bugs 23604553 and 25985768. The first one is related to pre-12.1 database upgrades and can be ignored but the second is related to unified auditing:
Patch 25985768: TRANSFER_UNIFIED_AUDIT_RECORDS PROCEDURE FAILS WITH ORA-46385 ON RAC
Now, when you rollback this patch it will try to drop the AUD$ table and that's where it fails during rollback:
Naturally checking the logfile shows what the issue is:
vi /u01/app/oracle/cfgtoollogs/sqlpatch/30372179/23142433/30372179_rollback_ELHUBT2_CDBROOT_2020Mar06_18_51_42.log .... SQL> -- Drop the unified audit internal table SQL> drop table audsys.aud$unified; drop table audsys.aud$unified * ERROR at line 1: ORA-46385: Message 46385 not found; product=RDBMS; facility=ORA ; arguments: [AUDSYS] [AUD$UNIFIED] ....
The solution is very simple and that is to comment the drop command on line 42 in the rollback SQL script located under the ORACLE_HOME:
You are probably reading this because datapatch failed already ? Don't worry just re-run datapatch and this time it will rollback the patch successfully. If you have happen to be reading this prior datapatch then make sure you comment the drop command in advance.
The reason for the rollback of this patch was because of the conflict with 18.104.22.168.200114. If you still want to apply the patch afterwards please note that the bug 25985768 has been replaced by new bug 28186466 and you will need the following patch which is already available for 22.214.171.124.200114:
Patch 28186466: DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS NOT FUNCTIONING
For more information refer to the MOS note:
Rollback Datapatch 25985768 Fails on Drop Table audsys.aud$unified (Doc ID 2573394.1)
Also, the following MOS note is very useful to track this bug as it's been superseded four times now:
Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)