hi there,
I am attempting to convert a non-cdb to a pdb in oracle 12.1.0.2.0.
I successfully remotely cloned the non-cdb to my cdb as a pdb and am just trying to successfully run noncdb_to_pdb.sql.
the script constantly reports ORA-01722: invalid number at this stage in the script:
-- Check that we have no invalid table data
DOC
#######################################################################
#######################################################################
The following statement will cause an "ORA-01722: invalid number"
error, if the database contains invalid data as a result of type
evolution which was performed without the data being converted.
To resolve this specific "ORA-01722: invalid number" error:
Perform the data conversion (details below) in the pluggable database.
Please refer to Oracle Database Object-Relational Developer's Guide
for more information about type evolution.
Data in columns of evolved types must be converted before the
database can be converted.
The following commands, run inside the PDB, will perform the data
conversion for Oracle supplied data:
@?/rdbms/admin/utluppkg.sql
SET SERVEROUTPUT ON;
exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
SET SERVEROUTPUT OFF;
You should then confirm that any non-Oracle supplied data is also
converted. You should review the data and determine if it needs
to be converted or removed.
To view the data that is affected by type evolution, execute the
following inside the PDB:
SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,
rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,
SYS.USER$ u
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
AND o.OWNER# = u.USER# AND o.OWNER# NOT IN
(SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r
WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER');
Once the data is confirmed, the following commands, run inside the PDB,
will convert the data returned by the above query.
@?/rdbms/admin/utluppkg.sql
SET SERVEROUTPUT ON;
exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
SET SERVEROUTPUT OFF;
Depending on the amount of data involved, converting the evolved type
data can take a significant amount of time.
After this is complete, please rerun noncdb_to_pdb.sql.
#######################################################################
#######################################################################
#
declare
do_abort boolean := false;
begin
if dbms_preup.condition_exists ('INVALID_SYS_TABLEDATA') then
-- dump out the info
dbms_preup.run_check('INVALID_SYS_TABLEDATA');
do_abort := TRUE;
end if;
if dbms_preup.condition_exists ('INVALID_USR_TABLEDATA') THEN
-- dump out the info
dbms_preup.run_check('INVALID_USR_TABLEDATA');
do_abort := TRUE;
END IF;
If do_abort THEN
dbms_output.put_line ('Invalid table data.');
dbms_output.put_line ('Non-CDB conversion aborting.');
dbms_output.put_line ('For instructions, look for ORA-01722 in this script.');
dbms_output.put_line ('Please resolve these and rerun noncdb_to_pdb.sql.');
RAISE INVALID_NUMBER;
end if;
end;
/
the thing is that I ran the following as instructed:
SQL> SET SERVEROUTPUT ON;
SQL> exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
**********************************************************************
Check Tag: INVALID_SYS_TABLEDATA
Check Summary: Check for invalid (not converted) table data
Fix Summary: UPGRADE Oracle supplied table data prior to the database upgrade.
**********************************************************************
Fixup Succeeded
**********************************************************************
PL/SQL procedure successfully completed.
SQL> exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
**********************************************************************
Check Tag: INVALID_USR_TABLEDATA
Check Summary: Check for invalid (not converted) user table data
Fix Summary: UPGRADE user table data prior to the database upgrade.
**********************************************************************
Fixup Succeeded
**********************************************************************
PL/SQL procedure successfully completed.
SQL> SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,
2 rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,
3 SYS.USER$ u
4 WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#
5 AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256
6 AND o.OWNER# = u.USER# AND o.OWNER# NOT IN
7 (SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r
8 WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER');
no rows selected
but the procedure still fails with an "ORA-01722: invalid number"
can anyone recommend how i can debug what the issue is?