Oracle 19c Upgrade: DBUA and ORA-01917 for user DBFS_ADMIN

Let me guess… You’re upgrading to Oracle 19c using the Database Upgrade Assistant and it failed at roughly the 44% mark with this error:

*** WARNING: ERRORS FOUND DURING UPGRADE ***

 1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
 2. Rerun the upgrade when the problem is resolved

REASON:
      ERRORS FOUND: During Upgrade
         FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-05-29_06-51-35PM/orcl/catupgrd0.log AT LINE NUMBER: 1217733
------------------------------------------------------
Identifier CATALOG 20-05-29 06:57:33
SCRIPT    = [/u01/app/oracle/product/19.3.0.0/dbhome_1/rdbms/admin/c18.sql]
ERROR     = [ORA-01917: user or role 'DBFS_ADMIN' does not exist ORA-06512: at line 14
ORA-06512: at line 14
ORA-06512: at line 9

The database alert log likely shows repeated attempts to restart the database and restore from a Global Restore Point. Eventually DBUA gave up, or a startup attempt failed outright because shared memory segments or semaphores were exhausted.

Oracle Database File System (DBFS) isn’t installed and it stands to reason DBFS_ADMIN wouldn’t exist. So why is the upgrade failing? Why didn’t the pre-check catch this?

Good news: while I can’t tell you exactly how it happened, I can tell you why it’s failing and how to fix it!

A Long and Varied History

I encountered this issue upgrading a database with a long and storied history. It began with 10g, was upgraded through multiple 11g versions and eventually landed at 12.2.0.1. There’s a possibility the database in question was restored to new hardware at least once. Somewhere along the way, DBFS was installed and, later, removed.

Whether this is a result of an incomplete or improper installation or removal, something missing in the uninstall scripts themselves, a product of historic restore from/to new hosts, or move from different hardware I can’t say. None of the client’s DBAs dated back far enough to recall the feature ever being used, let alone installed.

Recovering the Failed Upgrade

After the upgrade failed I looked in the alert log and saw that DBUA had made repeated attempts to stop the database and mount it in restricted mode to roll back to the Global Restore Point. It was never able to mount and fell into a loop. DBUA eventually exited when it could no longer attach memory segments.

I discovered a number of processes for ODF running on the system. Every time the database stopped, its ODF processes were left behind, ultimately causing resource starvation. I had to kill these processes and manually restore the GRP.

Blame it on Rio Orphaned Metadata

With such a lengthy history and diverse possibilities I can’t tell exactly how or when this came to pass but orphaned records related to the Oracle File System component were to blame. I located records in DBFS_SFS$_TAB that revealed an Oracle Database File System from the database’s 11.2.0.2 phase (I know this by examining the patch registry):

  select tabid, schema_name, table_name, created, formatted
    from dbfs_sfs$_tab;

TABID SCHEMA_NAM TABLE_NAME CREATED    FORMATTED
----- ---------- ---------- ---------- ----------
    1 DBFS_GG    T_GGS11    2011-05-25 2011-05-25
   11 DBFS_ADMIN T_ADMIN    2011-05-25 2011-05-25

Neither user or schema existed, nor did any DBFS volumes or filesystems. Records still existed in three tables related to DBFS: DBFS_SFS$_FS, DBFS_SFS$_VOL, and DBFS_SFS$_TAB.

Following the GRP restore I tried to remove the file system with DBMS_DBFS_ADM.DROPFILESYSTEM. Not surprisingly, this failed, producing constraint violations on DBFS_SFS$_VOL.

Cleaning the Data

Orphaned records had to be manually removed with:

delete from DBFS_SFS$_FS;
delete from DBFS_SFS$_VOL;
delete from DBFS_SFS$_TAB;
commit;

Once the tables were emptied I was able to run DBUA without further issue.