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 = 153764
While 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. 
