ORA-29382 or ORA-06512 during database upgrade from 11.1 to 12.1

  • 1,066 views

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. ORA-29382 or ORA-06512 during database upgrade from 11.1 to 12.1

  • by Published on 18/04/201801:37:51
  • Repost please keep this link: https://blogs.dbcloudsvc.com/oracle/ora-29382-or-ora-06512-during-database-upgrade-from-11-1-to-12-1/
匿名

Comment

Anonymous Write

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: