ORA-15410: Disks in disk group do not have equal size

12.1.0.2 ASM introduced a new feature which might prevent you from adding dissimilar size disks into a normal or high redundancy disk groups. The relevant MOS note is Doc ID 1938950.1.

Unfortunately I've found that some information in this note requires further clarification.

COMPATIBLE.ASM

The note suggests that the check is only enforced when the Disk Group has 'COMPATIBLE.ASM'='12.1.0.2' attribute set. This is incorrect and can be easily demonstrated:

SQL> select name, compatibility from v$asm_diskgroup;

NAME                           COMPATIBILITY
------------------------------ --------------------
DATA                           11.2.0.3.0

SQL> alter diskgroup data add disk '/dev/sdc';
alter diskgroup data add disk '/dev/sdc'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15410: Disks in disk group DATA do not have equal size.

As you can see my Disk Group has 'COMPATIBLE.ASM'='11.2.0.3.0' which did not prevent it from getting ORA-15410.

What happens if you have disks of dissimilar sizes already in the Disk Group?

Let's say you've upgraded from a previous release and your normal/high redundancy disk group had disks of dissimilar sizes in it already. What happens when you try to add a disk after the upgrade to 12.1.0.2 ASM? Some of the questions that immediately jump to mind:

• Will it always rise an error because the existing disks have different sizes forcing you to make everything of the same size first?
• Will it let you add a disk as long as the size is the same to one of the disks you already have?
• Will it let you add a disk with a new size?

Here I have a 12.1.0.2 normal redundancy Disk Group with two disks of dissimilar size:

SQL> select dg.name group_name,
    dg.type,
    dg.compatibility,
    d.name disk_name,
    d.total_mb
  from v$asm_diskgroup dg, v$asm_disk d
  where dg.group_number=d.group_number
  order by d.disk_number;

GROUP_NAME TYPE   COMPATIBILITY        DISK_NAME    TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA       NORMAL 12.1.0.2.0           DATA_0000        4096
DATA       NORMAL 12.1.0.2.0           DATA_0001        5120

Let's add a third disk of some other size:

SQL> alter diskgroup data add disk '/dev/sdd' size 2g;

Diskgroup altered.

SQL> select dg.name group_name,
    dg.type,
    dg.compatibility,
    d.name disk_name,
    d.total_mb
  from v$asm_diskgroup dg, v$asm_disk d
  where dg.group_number=d.group_number
  order by d.disk_number;

GROUP_NAME TYPE   COMPATIBILITY        DISK_NAME    TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA       NORMAL 12.1.0.2.0           DATA_0000        4096
DATA       NORMAL 12.1.0.2.0           DATA_0001        5120
DATA       NORMAL 12.1.0.2.0           DATA_0002        2048

As you can see, as long as you have disks of dissimilar sizes already in the disk group, the check appears to be ignored. I can resize the disks as well:

SQL> alter diskgroup data resize disk DATA_0002 size 4g;

Diskgroup altered.

SQL> select dg.name group_name,
    dg.type,
    dg.compatibility,
    d.name disk_name,
    d.total_mb
  from v$asm_diskgroup dg, v$asm_disk d
  where dg.group_number=d.group_number
  order by d.disk_number;  2    3    4    5    6    7    8

GROUP_NAME TYPE   COMPATIBILITY        DISK_NAME    TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA       NORMAL 12.1.0.2.0           DATA_0000        4096
DATA       NORMAL 12.1.0.2.0           DATA_0001        5120
DATA       NORMAL 12.1.0.2.0           DATA_0002        4096

Now what happens if I drop DATA_0001 thus making all disks to be of equal size?

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data add disk '/dev/sdc';
alter diskgroup data add disk '/dev/sdc'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15410: Disks in disk group DATA do not have equal size.

It appears that the moment the Disk Group got all disks of the same size the check got enforced.

Conclusion

Based on the tests I've demonstrated above the rules for ORA-15410 on 12.1.0.2 ASM appears to be:

• If all the disks in a Disk Group are of the same size then the check is enforced, regardless of what 'COMPATIBLE.ASM' value is.
• If a Disk Group contains disks of different sizes then the check is ignored, regardless of what 'COMPATIBLE.ASM' value is.

(keeping in mind that the check can only happen for normal/high redundancy Disk Groups)

Wrong Results

It is interesting how a combination of technologies in Oracle can play in a way which produce a seemingly bizarre outcomes.

Consider the following query:

SQL> with v as
(
        select 20 n from dual
) select distinct v.n
                from v, t
                where v.n=t.n(+);

no rows selected

Note that I'm doing a left outer join, however, the query somehow managed to loose a single row I have in the subquery factoring (and just in case you're wondering I've used subquery factoring for simplicity and replacing it with a real table makes no difference).

The first reaction is how could something as simple as this go so terribly wrong?

Let's take a look at the plan:

SQL> with v as
(
        select 20 n from dual
) select distinct v.n
                from v, t
                where v.n=t.n(+);  2    3    4    5    6

Execution Plan
----------------------------------------------------------
Plan hash value: 627307704

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |     9 |     6  (17)| 00:00:01 |
|   1 |  HASH UNIQUE                    |      |     1 |     9 |     6  (17)| 00:00:01 |
|*  2 |   FILTER                        |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER              |      |     1 |     9 |     5   (0)| 00:00:01 |
|   4 |     VIEW                        |      |     1 |     3 |     2   (0)| 00:00:01 |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|*  6 |     MAT_VIEW REWRITE ACCESS FULL| MV_T |    10 |    60 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MV_T"."GID"=0)
   3 - access("V"."N"="MV_T"."N"(+))
   6 - filter("MV_T"."N"(+) IS NOT NULL)

As it turns out the query rewrite is in play but the real oddity lies in the Predicate Information section. Line #2 reads filter("MV_T"."GID"=0) which took me by surprise (where did it come from?) as well as explained why the row went missing. A predicate like that essentially turned our query into an inner join!

The answer lies in how this materialized view was created and the cool trick the optimizer tried to do which didn't quite work out. Indeed, I have created the table and the materialized view in the following way:

SQL> create table t as
  2   select mod(level, 10) n, level m
  3    from dual
  4    connect by level <= 1000;
Table created

SQL> create materialized view mv_t enable query rewrite as
  2   select n, sum(m), grouping_id(n) gid
  3    from t
  4    group by rollup(n);
Materialized view created

The materialized view has two levels of aggregation due to the use of a rollup. I'm also using a grouping_id function to identify each grouping level. So what the optimizer tried to do is apply the GID = 0 predicate in order to eliminate the rollup data but, unfortunately, it also turned our query into an inner join equivalent as a result (final query taken from the optimizer trace):

SELECT DISTINCT "V"."N" "N"
 FROM (SELECT 20 "N" FROM "SYS"."DUAL" "DUAL") "V", "ROOT"."MV_T" "MV_T"
 WHERE "MV_T"."GID" = 0
  AND "V"."N" = "MV_T"."N"(+)

Of course the correct predicate in this case should be "MV_T"."GID" (+) = 0.

I have tried the above test case on both 11.2.0.3 and 12.1.0.2.0 with both versions producing the same wrong results.