During the last weeks, I experienced an uncommon bug during upgrade of security sensible databases: The upgrade process was sticking for hours on the script c1101000.sql which tried to fill the column DBID with the database DBID of the tables AUD$ and/or FGA_LOG$ (auditing and fine grained auditing tables).
As expected this bug was already published on My Oracle Support Note 11.2.0.1 Catupgrd.sql Hangs While Running Procedure POPULATE_DBID_AUDIT [ID 1062993.1]. In simple words, when you have many rows in these audit tables, the catupgrd.sql may be stuck until all rows are processed.
The announced workaround:
- exporting the AUD$ and/or FGA_LOG$ tables
- cleaning up these two tables
- finally launching catupgrd.sql
- importing the data
But this solution can be bypassed by a much more “customer friendly” way as it does not require any delete/truncate operation on the audit logs!
My solution was to fill the column AUD$.DBID shortly before the catalog upgrade catupgrd.sql script. You have to use the following query:
DECLARE cur_dbid NUMBER; BEGIN SELECT dbid INTO cur_dbid FROM v$database; UPDATE sys.aud$ SET dbid=cur_dbid WHERE dbid is NULL; COMMIT; END; /
This short PL/SQL code was running about 10min (depending on the number of rows). Afterwards, the catupgrd.sql was running in an acceptable timeframe and the rows appended to the audit tables in the meantime were updated without any pain during the catalogue upgrade.
I hope this information will help some of you in the upgrade of your Oracle databases to Oracle 11gR2!
Jérôme