Upgrade fails with error "ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state" (Doc ID 2914906.1)

11:26:53 SQL>
11:26:53 SQL> Rem add new parameters to existing tasks. Note that the definition
11:26:53 SQL> Rem of these parameters will be added later during upgrade
11:26:53 SQL> Rem when dbms_advisor.setup_repository is called.
11:26:53 SQL>
11:26:53 SQL> BEGIN
11:26:53 2
11:26:53 3 -- Proj #47346: In 12.2, we added a new parameter _USE_STATS_ADVISOR to enable
11:26:53 4 -- or disable the statistics advisor leg in SQL Tuning.
11:26:53 5 EXECUTE IMMEDIATE
11:26:53 6 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 7 (SELECT t.id, '_USE_STATS_ADVISOR', 'UNUSED', 2, 9
11:26:53 8 FROM wri$_adv_tasks t
11:26:53 9 WHERE t.advisor_name = 'SQL Tuning Advisor' AND
11:26:53 10 NOT EXISTS (SELECT 0
11:26:53 11 FROM wri$_adv_parameters p
11:26:53 12 WHERE p.task_id = t.id and
11:26:53 13 p.name = '_USE_STATS_ADVISOR'))#';
11:26:53 14
11:26:53 15 -- bug #9853147
11:26:53 16 EXECUTE IMMEDIATE
11:26:53 17 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 18 (SELECT t.id, 'NUM_ROWS_TO_FETCH', 'UNUSED', 2, 8
11:26:53 19 FROM wri$_adv_tasks t
11:26:53 20 WHERE t.advisor_name = 'SQL Performance Analyzer' AND
11:26:53 21 NOT EXISTS (SELECT 0
11:26:53 22 FROM wri$_adv_parameters p
11:26:53 23 WHERE p.task_id = t.id and
11:26:53 24 p.name = 'NUM_ROWS_TO_FETCH'))#';
11:26:53 25
11:26:53 26 EXECUTE IMMEDIATE
11:26:53 27 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 28 (SELECT t.id, 'EXECUTE_TRIGGERS', 'UNUSED', 2, 8
11:26:53 29 FROM wri$_adv_tasks t
11:26:53 30 WHERE t.advisor_name = 'SQL Performance Analyzer' AND
11:26:53 31 NOT EXISTS (SELECT 0
11:26:53 32 FROM wri$_adv_parameters p
11:26:53 33 WHERE p.task_id = t.id and
11:26:53 34 p.name = 'EXECUTE_TRIGGERS'))#';
11:26:53 35
11:26:53 36 EXECUTE IMMEDIATE
11:26:53 37 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 38 (SELECT t.id, 'REPLACE_SYSDATE_WITH', 'UNUSED', 2, 8
11:26:53 39 FROM wri$_adv_tasks t
11:26:53 40 WHERE t.advisor_name = 'SQL Performance Analyzer' AND
11:26:53 41 NOT EXISTS (SELECT 0
11:26:53 42 FROM wri$_adv_parameters p
11:26:53 43 WHERE p.task_id = t.id and
11:26:53 44 p.name = 'REPLACE_SYSDATE_WITH'))#';
11:26:53 45
11:26:53 46 -- bug 23137623: add parameters for SPM evolve advisor tasks
11:26:53 47 EXECUTE IMMEDIATE
11:26:53 48 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 49 (SELECT t.id, 'ALTERNATE_PLAN_LIMIT', 'UNUSED', 1, 8
11:26:53 50 FROM wri$_adv_tasks t
11:26:53 51 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:26:53 52 NOT EXISTS (SELECT 0
11:26:53 53 FROM wri$_adv_parameters p
11:26:53 54 WHERE p.task_id = t.id and
11:26:53 55 p.name = 'ALTERNATE_PLAN_LIMIT'))#';
11:26:53 56
11:26:53 57 EXECUTE IMMEDIATE
11:26:53 58 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 59 (SELECT t.id, 'ALTERNATE_PLAN_SOURCE', 'UNUSED', 2, 8
11:26:53 60 FROM wri$_adv_tasks t
11:26:53 61 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:26:53 62 NOT EXISTS (SELECT 0
11:26:53 63 FROM wri$_adv_parameters p
11:26:53 64 WHERE p.task_id = t.id and
11:26:53 65 p.name = 'ALTERNATE_PLAN_SOURCE'))#';
11:26:53 66
11:26:53 67 EXECUTE IMMEDIATE
11:26:53 68 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:26:53 69 (SELECT t.id, 'ALTERNATE_PLAN_BASELINE', 'UNUSED', 2, 8
11:26:53 70 FROM wri$_adv_tasks t
11:26:53 71 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:26:53 72 NOT EXISTS (SELECT 0
11:26:53 73 FROM wri$_adv_parameters p
11:26:53 74 WHERE p.task_id = t.id and
11:26:53 75 p.name = 'ALTERNATE_PLAN_BASELINE'))#';
11:26:53 76
11:26:53 77 -- handle exception when upgrading from 9i. The advisor tables do not exist
11:26:53 78 EXCEPTION
11:26:53 79 WHEN OTHERS THEN
11:26:53 80 IF SQLCODE = -942
11:26:53 81 THEN NULL;
11:26:53 82 ELSE
11:26:53 83 RAISE;
11:26:53 84 END IF;
11:26:53 85 END;
11:26:53 86 /
BEGIN
*
ERROR at line 1:
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 83
ORA-06512: at line 5

11:28:16 SQL> Rem *************************************************************************
11:28:16 SQL> Rem BEGIN Changes for Bug 29018307
11:28:16 SQL> Rem *************************************************************************
11:28:16 SQL>
11:28:16 SQL> BEGIN
11:28:16 2 -- bug 29018307: add parameters for SPM evolve advisor tasks
11:28:16 3 EXECUTE IMMEDIATE
11:28:16 4 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:28:16 5 (SELECT t.id, '_ALTERNATE_PLAN_STS_RATIO', '110', 1, 9
11:28:16 6 FROM wri$_adv_tasks t
11:28:16 7 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:28:16 8 NOT EXISTS (SELECT 0
11:28:16 9 FROM wri$_adv_parameters p
11:28:16 10 WHERE p.task_id = t.id and
11:28:16 11 p.name = '_ALTERNATE_PLAN_STS_RATIO'))#';
11:28:16 12
11:28:16 13 EXECUTE IMMEDIATE
11:28:16 14 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:28:16 15 (SELECT t.id, '_ALTERNATE_PLAN_THRESHOLD', '100', 1, 9
11:28:16 16 FROM wri$_adv_tasks t
11:28:16 17 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:28:16 18 NOT EXISTS (SELECT 0
11:28:16 19 FROM wri$_adv_parameters p
11:28:16 20 WHERE p.task_id = t.id and
11:28:16 21 p.name = '_ALTERNATE_PLAN_THRESHOLD'))#';
11:28:16 22
11:28:16 23 EXECUTE IMMEDIATE
11:28:16 24 q'#INSERT INTO wri$_adv_parameters (task_id, name, value, datatype, flags)
11:28:16 25 (SELECT t.id, '_ALTERNATE_PLAN_AI_MODE', 'FALSE', 2, 9
11:28:16 26 FROM wri$_adv_tasks t
11:28:16 27 WHERE t.advisor_name = 'SPM Evolve Advisor' AND
11:28:16 28 NOT EXISTS (SELECT 0
11:28:16 29 FROM wri$_adv_parameters p
11:28:16 30 WHERE p.task_id = t.id and
11:28:16 31 p.name = '_ALTERNATE_PLAN_AI_MODE'))#';
11:28:16 32
11:28:16 33 EXCEPTION
11:28:16 34 WHEN OTHERS THEN
11:28:16 35 IF SQLCODE = -942
11:28:16 36 THEN NULL;
11:28:16 37 ELSE
11:28:16 38 RAISE;
11:28:16 39 END IF;
11:28:16 40 END;
11:28:16 41 /
BEGIN
*
ERROR at line 1:
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 38
ORA-06512: at line 3

11:43:03 SQL> Rem *************************************************************************
11:43:03 SQL> Rem BEGIN Changes for Bug 29055635
11:43:03 SQL> Rem *************************************************************************
11:43:03 SQL> BEGIN
11:43:03 2 prvt_advisor.set_task_parameter(task_name =>'SYS_AUTO_SPM_EVOLVE_TASK',
11:43:03 3 parameter =>'ALTERNATE_PLAN_LIMIT',
11:43:03 4 data =>'UNLIMITED',
11:43:03 5 task_owner_id => prvt_advisor.SYS_USER_ID);
11:43:03 6
11:43:03 7 prvt_advisor.set_task_parameter(task_name =>'SYS_AUTO_SPM_EVOLVE_TASK',
11:43:03 8 parameter =>'ALTERNATE_PLAN_SOURCE',
11:43:03 9 data =>'AUTO',
11:43:03 10 task_owner_id => prvt_advisor.SYS_USER_ID);
11:43:03 11
11:43:03 12 EXCEPTION
11:43:03 13 WHEN OTHERS THEN
11:43:03 14 IF SQLCODE = -942
11:43:03 15 THEN NULL;
11:43:03 16 ELSE
11:43:03 17 RAISE;
11:43:03 18 END IF;
11:43:03 19 END;
11:43:03 20 /
BEGIN
*
ERROR at line 1:
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 17
ORA-06512: at "SYS.PRVT_ADVISOR", line 5014
ORA-06512: at "SYS.PRVT_ADVISOR", line 4963
ORA-06512: at line 2

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 83
ORA-06512: at line 5

ORA-06512: at line 5
ORA-06512: at line 83 ORA-06512: at line 5

unusable state ORA-06512: at line 83
ORA-06512: at line 5

unusable state ORA-06512: at line 38
ORA-06512: at line 3

ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 38
ORA-06512: at line 3

ORA-06512: at line 38 ORA-06512: at line 3

ORA-06512: at line 3
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at line 9
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2761
ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

unusable state ORA-06512: at line 9
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2761
ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at line 9 ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2761
ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845

ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2761 ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 6090 ORA-06512: at "SYS.PRVT_ADVISOR",line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 1845 ORA-06512: at "SYS.PRVT_ADVISOR",line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 1792 ORA-06512: at "SYS.PRVT_ADVISOR",line 6052
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 6052 ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2667 ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806
ORA-06512: at line 2

ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 2806 ORA-06512: at line 2

ORA-06512: at line 2
ORA-01502: index 'SYS.WRI$_ADV_PARAMETERS_PK' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7564
ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845

ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SQLPA", line 299
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7537
ORA-06512: at line 2

unusable state ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7564
ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR",
line 6052
ORA-06512: at "SYS.DBMS_SQLPA", line 299
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7537
ORA-06512: at line 2

ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7564 ORA-06512: at "SYS.PRVT_ADVISOR", line 6090
ORA-06512: at "SYS.PRVT_ADVISOR", line 1845
ORA-06512: at
"SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SQLPA", line 299
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7537
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 6090 ORA-06512: at "SYS.PRVT_ADVISOR",line 1845
ORA-06512: at "SYS.PRVT_ADVISOR", line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SQLPA", line 299
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7537
ORA-06512: at line 2

ORA-06512: at "SYS.PRVT_ADVISOR", line 1845 ORA-06512: at "SYS.PRVT_ADVISOR",line 1792
ORA-06512: at "SYS.PRVT_ADVISOR", line 6052
ORA-06512: at "SYS.DBMS_SQLPA", line 299
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7537
ORA-06512: at line 2

Oracle Server INVALID 19.17.0.0.0 00:26:49                                                      <<<<<<<<<<<<<<<<<
JServer JAVA Virtual Machine UPGRADED 19.17.0.0.0 00:05:20
Oracle XDK UPGRADED 19.17.0.0.0 00:02:37
Oracle Database Java Packages UPGRADED 19.17.0.0.0 00:00:17
OLAP Analytic Workspace UPGRADED 19.17.0.0.0 00:00:17
Oracle Text UPGRADED 19.17.0.0.0 00:01:02
Oracle Real Application Clusters UPGRADED 19.17.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.17.0.0.0 00:02:22
Oracle Multimedia UPGRADED 19.17.0.0.0 00:00:48
Spatial UPGRADED 19.17.0.0.0 00:18:43
Oracle OLAP API UPGRADED 19.17.0.0.0 00:00:14
Datapatch 00:01:07
Final Actions 00:32:41
Post Upgrade 00:02:10

Total Upgrade Time: 01:33:36

Database time zone version is 18. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time: [0d:1h:37m:26s]