My Oracle Support Banner

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 OCTOBER 16, 2019

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> Rem bug 25248712   - Move the copy for bug 19651064   from catuppst.sql (run
15:50:55 SQL> Rem in normal mode) to here (run in upgrade mode).
15:50:55 SQL> Rem Control number of parallel slaves spawned better to reduce
15:50:55 SQL> Rem resource access limits/contention and to reduce chance of
15:50:55 SQL> Rem exceeding PROCESSES.
15:50:55 SQL> Rem Job scheduler (which can spawn parallel slaves) does not
15:50:55 SQL> Rem run in upgrade mode, hence chance of fewer parallel slaves
15:50:55 SQL> Rem running here.
15:50:55 SQL> Rem =======================================================================
15:50:55 SQL> Rem Bug 19651064   - Copy data to new WRH$_SYSMETRIC_HISTORY table
15:50:55 SQL> Rem =======================================================================
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  11   -- Bug 25248712   - cut down on number of parallel slaves spawned
15:50:55  12   BEGIN
15:50:55  13    execute immediate
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  26        -- lets spawn 32 parallel slaves for this copy for non-CDB/ROOT
15:50:55  27        hint1 := ' /*+ APPEND parallel(32) enable_parallel_dml */ ';
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  31      -- lets spawn 2 parallel slaves for this copy per 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  45    sql_str := 'insert ' || hint1 || ' into WRH$_SYSMETRIC_HISTORY ' ||
15:50:55  46         ' select ' || hint2 || ' * from TMP_SYSMETRIC_HISTORY';
15:50:55  47   END;  -- end of Bug 25248712   - cut down on number of parallel slaves spawned
15:50:55  48  
15:50:55  49  
15:50:55  50   -- Bug 19651064   - Copy data to new WRH$_SYSMETRIC_HISTORY table
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  67   end;  -- end of Bug 19651064   - Copy data from TMP_SYSMETRIC_HISTORY table
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

Changes

 

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
Symptoms
Changes
Cause
Solution
References


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