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 

3 thoughts on “Invalid Table Data before Upgrade to 12.1.0.2?

  1. Mike,
    Your blog is an excellent source of information about 12c upgrade. It has targeted real life hands-on information which is very useful. I have a question about a statement you made:

    "You shouldn’t run the fixups on a live production system…"

    I guess you mean that I should not run the fixups on a live production system till I am ready to upgrade. Correct?

    Thanks,
    Arun

  2. Arun,

    thanks for your feedback – and you are right as my comment was very unprecise. I corrected it 🙂

    Please do it right before the upgrade due to the DDLs happening on tables.

    Thanks 🙂
    Mike

  3. Hi Mike,
    Thank you for valuable info regarding this.
    I also noticed that even if you don’t run it prior to upgrade, it will ask you to do it on the last step before upgrade (in DBUA). You just click on "Fix and Check Again" and it performs needed operations. Handy.

    Regards,
    Igor

Leave a Reply

Your email address will not be published. Required fields are marked *