Exadata Database Upgrade From 12.1.0.2 To 18.3.0 Fails with Error - ORA-01405: fetched column value is NULL (Doc ID 2593095.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 18.3.0.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 18.3.0.0.0 version, Upgrading

When attempting to upgrade at phase 100 when catmmig.sql is being run,
the following error occurs.

ERROR
-----------------------

catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = TRUE
catrequtlmg: b_InUtlMig = TRUE
catrequtlmg: Deleting table stats
catrequtlmg: Gathering Table Stats OBJ$MIG
declare
*
ERROR at line 1:
ORA-00604: Error at recursive SQL level 1
ORA-06512: in "SYS.DBMS_STATS", line 39132
ORA-06512: in "SYS.DBMS_STATS", line 38420
ORA-06512: in "SYS.DBMS_STATS", line 37314
ORA-06512: in "SYS.DBMS_STATS", line 35517
ORA-01405: Retrieved column value is NULL
ORA-06512: in "SYS.DBMS_STATS_INTERNAL", line 422
ORA-06512: in "SYS.DBMS_STATS_INTERNAL", line 9979
ORA-06512: in "SYS.DBMS_STATS", line 24624
ORA-06512: in "SYS.DBMS_STATS", line 34820
ORA-06512: in "SYS.DBMS_STATS", line 37150
ORA-06512: in "SYS.DBMS_STATS", line 38139
ORA-06512: in "SYS.DBMS_STATS", line 38568
ORA-06512: in "SYS.DBMS_STATS", line 39114
ORA-06512: at line 145

Which crashes the upgrade script with the following:

Serial Phase #:100 [tdwprd] Files:1
Unexpected error encountered in catctlMain; Error Stack Below; exiting
Died at <18c DB Home>/rdbms/admin/catctl.pl line 8976.
at <18c DB Home>/rdbms/admin/catctl.pl line 8976.
main::catctlDie("\x{a}Unexpected error encountered in catconExec; exiting\x{a} 2") called at <18c DB Home>/rdbms/admin/catctl.pl line 5481
main::catctlExecutePhaseFiles(100, 1, undef, undef, undef) called at <18c DB Home>/rdbms/admin/catctl.pl line 1979
main::catctlRunPhase(100, 1, undef, undef, undef) called at <18c DB Home>/rdbms/admin/catctl.pl line 2123
main::catctlRunPhases(100, 109, 109, undef, undef, undef) called at <18c DB Home>/rdbms/admin/catctl.pl line 2739
main::catctlRunMainPhases() called at <18c DB Home>/rdbms/admin/catctl.pl line 1405
main::catctlMain() called at <18c DB Home>/rdbms/admin/catctl.pl line 1314
eval {...} called at <18c DB Home>/rdbms/admin/catctl.pl line 1312

The very first occurrence of ORA-01405 is for the Table - WRH$_SYSMETRIC_HISTORY table

15:50:55 SQL>

..
15:50:55 SQL>
15:50:55 SQL> DECLARE
15:50:55 2 num_cpu NUMBER := 0;
15:50:55 3 sql_str VARCHAR2(240) := '';
15:50:55 4 con_id VARCHAR2(100) := '0';
15:50:55 5 is_pdb BOOLEAN := FALSE;
15:50:55 6 hint1 VARCHAR2(80) := '';
15:50:55 7 hint2 VARCHAR2(80) := '';
15:50:55 8
15:50:55 9 BEGIN
15:50:55 10
..
15:50:55 14 'select value from v$parameter where name=''cpu_count''' into num_cpu;
15:50:55 15
15:50:55 16 con_id := sys_context('USERENV', 'CON_ID');
15:50:55 17 IF (con_id <> '0') AND (con_id <> '1') THEN
15:50:55 18 is_pdb := TRUE;
15:50:55 19 END IF;
15:50:55 20
15:50:55 21 IF (is_pdb = FALSE) THEN -- db is a non-cdb or ROOT
15:50:55 22 IF num_cpu < 8 THEN -- if less than 8 cpus
15:50:55 23 hint1 := ' /*+ APPEND parallel enable_parallel_dml */ ';
15:50:55 24 hint2 := ' /*+ PARALLEL */ ';
15:50:55 25 ELSE
..
15:50:55 28 hint2 := ' /*+ PARALLEL(32) */ ';
15:50:55 29 END IF;
15:50:55 30 ELSE -- db is a PDB
..
15:50:55 32
15:50:55 33 -- note:
15:50:55 34 -- have tested with 4 million (4,058,580) rows in TMP_SYSMETRIC_HISTORY
15:50:55 35 -- on linux host of 2 cpus.
15:50:55 36 -- no parallel hint at all: Elapsed: 00:05:03.99
15:50:55 37 -- parallel(2) in both insert and select clauses: Elapsed: 00:00:56.19
15:50:55 38 -- parallel(4) in both insert and select clauses: Elapsed: 00:00:54.63
15:50:55 39 -- parallel(5) in both insert and select clauses: Elapsed: 00:00:58.20
15:50:55 40 --
15:50:55 41 hint1 := ' /*+ APPEND parallel(2) enable_parallel_dml */ ';
15:50:55 42 hint2 := ' /*+ PARALLEL(2) */ ';
15:50:55 43 END IF;
15:50:55 44
..
15:50:55 49

15:50:55 51 begin
15:50:55 52 execute immediate 'drop index WRH$_SYSMETRIC_HISTORY_INDEX';
15:50:55 53 execute immediate sql_str;
15:50:55 54 execute immediate 'drop index TMP_SYSMETRIC_HISTORY_INDEX';
15:50:55 55 execute immediate 'drop table TMP_SYSMETRIC_HISTORY';
15:50:55 56 execute immediate 'create index WRH$_SYSMETRIC_HISTORY_INDEX '
15:50:55 57 || 'on WRH$_SYSMETRIC_HISTORY '
15:50:55 58 || '(dbid, snap_id, instance_number, group_id, '
15:50:55 59 || 'metric_id, begin_time, con_dbid) '
15:50:55 60 || 'local tablespace SYSAUX '
15:50:55 61 || 'parallel 2 ';
15:50:55 62 commit;
15:50:55 63 exception when others then
15:50:55 64 if sqlcode in (-942, -1418) then null;
15:50:55 65 else raise;
15:50:55 66 end if;
..
15:50:55 68
15:50:55 69 END;
15:50:55 70 /
DECLARE
*
ERROR in line 1:
ORA-01405: fetched column value is NULL

2. Later on,Attempts to collect Stats on few SYS Tables like AW$,PS$,AW_OBJ$,AW_PROP$ so on fail with below errors-

15:55:17 SQL> EXECUTE dbms_stats.gather_table_stats('SYS', 'AW$')
BEGIN dbms_stats.gather_table_stats('SYS', 'AW$'); END;

15:55:17 SQL> EXECUTE dbms_stats.gather_table_stats('SYS', 'PS$')
BEGIN dbms_stats.gather_table_stats('SYS', 'PS$'); END;

*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-06512: in "SYS.DBMS_STATS", Zeile 39132
ORA-06512: in "SYS.DBMS_STATS", Zeile 38420
ORA-06512: in "SYS.DBMS_STATS", Zeile 37314
ORA-06512: in "SYS.DBMS_STATS", Zeile 35517
ORA-01405: Abgerufener Spaltenwert ist NULL
ORA-06512: in "SYS.DBMS_STATS_INTERNAL", Zeile 422
ORA-06512: in "SYS.DBMS_STATS_INTERNAL", Zeile 9979
ORA-06512: in "SYS.DBMS_STATS", Zeile 24624
ORA-06512: in "SYS.DBMS_STATS", Zeile 34820
ORA-06512: in "SYS.DBMS_STATS", Zeile 37150
ORA-06512: in "SYS.DBMS_STATS", Zeile 38139
ORA-06512: in "SYS.DBMS_STATS", Zeile 38568
ORA-06512: in "SYS.DBMS_STATS", Zeile 39114
ORA-06512: in Zeile 1

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Exadata Database Upgrade From 12.1.0.2 To 18.3.0 Fails with Error - ORA-01405: fetched column value is NULL (Doc ID 2593095.1)

Applies to:

Symptoms

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!