Invalid Table Data before Upgrade to 12.1.0.2?

You plan to upgrade your database(s) to Oracle Database 12.1.0.2,?

You did run the preupgrd.sql including the preupgrade package in your current database already?

But in the preupgrade.log you’ll see the following ERROR:

ERROR: --> Invalid Oracle supplied table data found in your database.

     Invalid data can be seen prior to the database upgrade
     or during PDB plug in.  This table data must be made
     valid BEFORE upgrade or plug in.

   - To fix the data, load the Preupgrade package and execute
     the fixup routine.
     For plug in, execute the fix up routine in the PDB.

    @?/rdbms/admin/utluppkg.sql
SET SERVEROUTPUT ON;
exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')
SET SERVEROUTPUT OFF;

Where does it come from?

First of all it’s important to know that you can’t upgrade your database without resolving this error condition before. If you’d ignore it you’ll see the magic universal ORA-1722: invalid number error indicating that one of the mandatory checks in the upgrade scripts had failed.

For more information on type evolution check the Oracle Database 12c Object-Relational Developer’s Guide.

What does this warning mean? 

The error results from a failed check for table data in columns of evolved types. Those must be upgraded before the database can be upgraded, otherwise they will be considered as “invalid” data. The same thing can happen when you try to plugin a stand-alone (non-CDB) database making it a pluggable database. The sanity script noncdb_to_pdb.sql will also check for this condition.

And how do you solve the ERROR? 

• For oracle-supplied data, the conversion would be done
  by:

  SET SERVEROUTPUT ON;
  exec DBMS_PREUP.run_fixup_and_report('INVALID_SYS_TABLEDATA');
  SET SERVEROUTPUT OFF;
  

• For user data, the conversion would be done by:

  SET SERVEROUTPUT ON;
  exec DBMS_PREUP.run_fixup_and_report('INVALID_USR_TABLEDATA');
  SET SERVEROUTPUT OFF;

In both cases the preupgrade package @?/rdbms/admin/utluppkg.sql from either your 12.1.0.2 $ORACLE_HOME or from the download via MOS Note: 884522.1 must be loaded into your database – it has been loaded already if you’d execute the preupgrd.sql.

You shouldn’t run the fixups on a live production system during normal operation but only right before upgrade as the fixups run ALTER TABLE DDL commands. Column metadata and data related to the object columns/tables that
contains data of older version types will be updated.

The below query will check how many of the tables/columns (oracle-supplied and user data) are affected:

SQL> SELECT COUNT(*) FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL# AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256;

Further Information?

MOS Note: 2009405.1
ORA-01722: invalid number SELECT TO_NUMBER(‘INVALID_TABLEDATA’) while upgrading to 12c

–Mike

PS: Credits to my colleagues Cindy and Agrim for providing tech background and the MOS Note