Last year, when I was migrating/upgrading a huge Oracle database from 11.1 to 12.1, I encountered one really tough issue and I found the reason after several months.
The error messages were like this during the upgrade with DBUA:
RDBMS component upgrade error:ORA-29382: validation of pending area failedORA-29375: sum of values 105 for level 1, plan DEFAULT_MAINTENANCE_PLAN exceeds RDBMS component upgrade error:ORA-06512: at "SYS.DBMS_RMIN_SYS", line 8705ORA-06512: at "SYS.DBMS_RMIN_SYS", line 8755ORA-06512: at line 1 XOQ component upgrade error:ORA-06512: at "SYS.DBMS_RMIN_SYS", line 8705ORA-06512: at "SYS.DBMS_RMIN_SYS", line 8755ORA-06512: at line 1
In fact they were caused by the same Oracle resource plan issue, so if the first issue was fixed, then the later two issues would disappear also.
At the first time I just ignored such error and finished the upgrade, then I checked the database system objects while did not find any issue. The upgraded database worked really well as a non-cdb database.
I had to face this issue when I did the same migration one more time and imported it to a 12c CDB as a PDB, and it became a nightmare to me.
I found sometimes the instance would be terminated abnormally, and got errors from the database alert logs:
ORA-00700: soft internal error, arguments: [kgskireadplantree:cgnull], [INTERNAL_PLAN], [ORA$AUTOTASK], [], [], [], [], [], [], [], [], []
...........
Sat Jul 01 18:31:24 2017
ORA-7452: resource plan 'INTERNAL_PLAN' does not exist
Sat Jul 01 18:31:24 2017
Resource Manager failed to initialize on pdb 3
Pluggable database XXXXXPD opened read write
Completed: ALTER PLUGGABLE DATABASE xxxxxpd OPEN /* svc agent *//* {0:325:5} */
Sat Jul 01 18:31:24 2017
Dumping diagnostic data in directory=[cdmp_20170701183124], requested by (instance=2, osid=153764 (DBRM)), summary=[incident=4360179].
Sat Jul 01 18:31:26 2017
Setting Resource Manager plan SCHEDULER[0x4480]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
Errors in file /app/oracle/rdbms/diag/rdbms/xxxxxcdb/xxxxxcdb2/trace/xxxxxcdb2_dbrm_153764.trc (incident=4360180) (PDBNAME=XXXXXPD):
ORA-00600: internal error code, arguments: [kgskigetelt3], [ORA$AUTOTASK], [], [], [], [], [], [], [], [], [], []
..........
Sat Jul 01 18:31:27 2017
Errors in file /app/oracle/rdbms/diag/rdbms/xxxxxcdb/xxxxxcdb2/trace/xxxxxcdb2_dbrm_153764.trc:
ORA-00600: internal error code, arguments: [kgskigetelt3], [ORA$AUTOTASK], [], [], [], [], [], [], [], [], [], []
Sat Jul 01 18:31:27 2017
Errors in file /app/oracle/rdbms/diag/rdbms/xxxxxcdb/xxxxxcdb2/trace/xxxxxcdb2_dbrm_153764.trc:
ORA-00600: internal error code, arguments: [kgskigetelt3], [ORA$AUTOTASK], [], [], [], [], [], [], [], [], [], []
Sat Jul 01 18:31:27 2017
USER (ospid: 153764): terminating the instance due to error 56710
Sat Jul 01 18:31:27 2017
System state dump requested by (instance=2, osid=153764 (DBRM)), summary=[abnormal instance termination].
System State dumped to trace file /app/oracle/rdbms/diag/rdbms/xxxxxcdb/xxxxxcdb2/trace/xxxxxcdb2_diag_153762_20170701183127.trc
Sat Jul 01 18:31:28 2017
Instance terminated by USER, pid = 153764While I did find the plan named INTERNAL_PLAN, and I thought this issue was caused by the resource plan issue during the upgrade.
I searched lots of notes on the Oracle support portal and the whole internet, while none of them worked and I got really confused and tired.
Several months passed and I checked this issue time to time.
One day I compared the resource plans on the production database and a new created 11.1 database and one big assumption appeared:
The customer modified some database default resource plans while some values would cause error in 12.1 database so I got this issue and in fact if I ignored it the updated database would have wrong resource plans or miss some of them.
In the production database, I got below settings:
SQL> select PLAN,GROUP_OR_SUBPLAN,TYPE,STATUS,CPU_P1,CPU_P2,CPU_P3,MGMT_P1,MGMT_P2, MGMT_P3 from dba_rsrc_plan_directives order by 1,2; PLAN GROUP_OR_SUBPLAN TYPE STATUSCPU_P1 CPU_P2 CPU_P3MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- DEFAULT_MAINTENANCE_PLAN LOW_GROUP CONSUMER_GROUP 0 20 0 0 20 0 DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 0 25 0 0 25 0 DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 05 0 0 5 0 DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS CONSUMER_GROUP 0 50 0 0 50 0 DEFAULT_MAINTENANCE_PLAN SYS_GROUP CONSUMER_GROUP 1000 0 100 0 0 DEFAULT_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 05 0 0 5 0 DEFAULT_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 05 0 0 5 0 DEFAULT_PLAN OTHER_GROUPS CONSUMER_GROUP 0 90 0 0 90 0 DEFAULT_PLAN SYS_GROUP CONSUMER_GROUP 1000 0 100 0 0 INTERNAL_PLAN OTHER_GROUPS CONSUMER_GROUP 00 0 0 0 0 INTERNAL_QUIESCE OTHER_GROUPS CONSUMER_GROUP 00 0 0 0 0 PLAN GROUP_OR_SUBPLAN TYPE STATUSCPU_P1 CPU_P2 CPU_P3MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- INTERNAL_QUIESCE SYS_GROUP CONSUMER_GROUP 00 0 0 0 0 MIXED_WORKLOAD_PLAN BATCH_GROUP CONSUMER_GROUP 00 100 0 0 100 MIXED_WORKLOAD_PLAN INTERACTIVE_GROUP CONSUMER_GROUP 0 85 0 0 85 0 MIXED_WORKLOAD_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 05 0 0 5 0 MIXED_WORKLOAD_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 05 0 0 5 0 MIXED_WORKLOAD_PLAN OTHER_GROUPS CONSUMER_GROUP 05 0 0 5 0 MIXED_WORKLOAD_PLAN SYS_GROUP CONSUMER_GROUP 1000 0 100 0 0 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_HEALTH_GROUP CONSUMER_GROUP 250 0 25 0 0 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_SPACE_GROUP CONSUMER_GROUP 250 0 25 0 0 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_SQL_GROUP CONSUMER_GROUP 250 0 25 0 0 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_STATS_GROUP CONSUMER_GROUP 250 0 25 0 0 PLAN GROUP_OR_SUBPLAN TYPE STATUSCPU_P1 CPU_P2 CPU_P3MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_HIGH_SUB_PLAN PLAN 0 100 0 0 100 0 ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_MEDIUM_GROUP CONSUMER_GROUP 00 100 0 0 100 ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_URGENT_GROUP CONSUMER_GROUP 1000 0 100 0 0 25 rows selected.
These subplans worked well on the production servers so I did not try to change them for a long time.
And I changed them as below before the upgrade:
execute dbms_resource_manager.clear_pending_area(); EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA; execute dbms_resource_manager.update_plan_directive( plan =>'DEFAULT_MAINTENANCE_PLAN',group_or_subplan => 'SYS_GROUP',new_cpu_p1 =>75,new_MGMT_P1 =>75); execute dbms_resource_manager.update_plan_directive( plan =>'DEFAULT_PLAN',group_or_subplan => 'SYS_GROUP',new_cpu_p1 =>75,new_MGMT_P1 =>75); execute dbms_resource_manager.update_plan_directive( plan =>'MIXED_WORKLOAD_PLAN',group_or_subplan => 'SYS_GROUP',new_cpu_p1 =>75,new_MGMT_P1 =>75); execute dbms_resource_manager.validate_pending_area(); execute dbms_resource_manager.submit_pending_area();
Guess what happened? OMG! All these errors gone during the upgrade including the ORA-7452 error!
SQL> select PLAN,GROUP_OR_SUBPLAN,TYPE,STATUS,CPU_P1,CPU_P2,CPU_P3,MGMT_P1,MGMT_P2, MGMT_P3 from dba_rsrc_plan_directives order by 1,2 PLAN GROUP_OR_SUBPLAN TYPE STATUS CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- DEFAULT_MAINTENANCE_PLAN LOW_GROUP CONSUMER_GROUP 0 20 0 0 200 DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 0 25 0 0 250 DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 0 5 0 0 50 DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS CONSUMER_GROUP 0 50 0 0 500 DEFAULT_MAINTENANCE_PLAN SYS_GROUP CONSUMER_GROUP75 0 0 75 00 DEFAULT_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 0 5 0 0 50 DEFAULT_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 0 5 0 0 50 DEFAULT_PLAN OTHER_GROUPS CONSUMER_GROUP 0 90 0 0 900 DEFAULT_PLAN SYS_GROUP CONSUMER_GROUP75 0 0 75 00 INTERNAL_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 00 INTERNAL_QUIESCE OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 00 PLAN GROUP_OR_SUBPLAN TYPE STATUS CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- INTERNAL_QUIESCE SYS_GROUP CONSUMER_GROUP 0 0 0 0 00 MIXED_WORKLOAD_PLAN BATCH_GROUP CONSUMER_GROUP 0 0 100 0 0 100 MIXED_WORKLOAD_PLAN INTERACTIVE_GROUP CONSUMER_GROUP 0 85 0 0 850 MIXED_WORKLOAD_PLAN ORA$AUTOTASK_SUB_PLAN PLAN 0 5 0 0 50 MIXED_WORKLOAD_PLAN ORA$DIAGNOSTICS CONSUMER_GROUP 0 5 0 0 50 MIXED_WORKLOAD_PLAN OTHER_GROUPS CONSUMER_GROUP 0 5 0 0 50 MIXED_WORKLOAD_PLAN SYS_GROUP CONSUMER_GROUP75 0 0 75 00 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_HEALTH_GROUP CONSUMER_GROUP25 0 0 25 00 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_SPACE_GROUP CONSUMER_GROUP25 0 0 25 00 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_SQL_GROUP CONSUMER_GROUP25 0 0 25 00 ORA$AUTOTASK_HIGH_SUB_PLAN ORA$AUTOTASK_STATS_GROUP CONSUMER_GROUP25 0 0 25 00 PLAN GROUP_OR_SUBPLAN TYPE STATUS CPU_P1 CPU_P2 CPU_P3 MGMT_P1 MGMT_P2 MGMT_P3 ------------------------------ ------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_HIGH_SUB_PLAN PLAN 0 100 0 0 1000 ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_MEDIUM_GROUP CONSUMER_GROUP 0 0 100 0 0 100 ORA$AUTOTASK_SUB_PLAN ORA$AUTOTASK_URGENT_GROUP CONSUMER_GROUP 100 0 0100 00 25 rows selected.
This issue was hard because I did not think the workable subplans would become invalid during the upgrade, and I also could not do the upgrade tests as needed.
It was lucky for me to find the root cause before the final cutover. 
