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.

Converting non-CDB database to a PDB when TDE is in use

Converting a non-CDB database to a PDB is a rather straightforward process. However once TDE (Transparent Data Encryption) gets involved certain things become not so obvious so I've decided to write a small guide on how to accomplish that. In order for a non-CDB database to be converted to a PDB it's version needs to be at least 12.1.0.2.0.

Encryption Wallet Location

My encryption wallet location is set to the following (sqlnet.ora):

ENCRYPTION_WALLET_LOCATION=
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/wallet/tde)
    )
  )

Create a non-CDB database

Let's start by creating a non-CDB database. I will call this database db1 and it will later be converted to a pluggable database called pdb1:

dbca -silent \
-createDatabase \
-templateName New_Database.dbt \
-gdbName db1 \
-createAsContainerDatabase false \
-sysPassword oracle \
-systemPassword oracle \
-emConfiguration none \
-datafileDestination /u02/oradata \
-redoLogFileSize 128 \
-recoveryAreaDestination /u02/fra \
-storageType FS \
-characterSet al32utf8 \
-nationalCharacterSet al16utf16 \
-automaticMemoryManagement false \
-initParams filesystemio_options=setall \
-initParams session_cached_cursors=100 \
-totalMemory 1024
Creating and starting Oracle instance
1% complete
...
100% complete

Since I have PSU2 applied I need to run datapatch once the database has been created:

[oracle@ora12cr1 ~]$ . oraenv
ORACLE_SID = [oracle] ? db1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12cr1 ~]$ $ORACLE_HOME/OPatch/datapatch -verbose

Create the wallet and encrypted table

We're now ready to setup the wallet and create a user with an encrypted table:

[oracle@ora12cr1 ~]$ mkdir -p /u01/app/oracle/admin/db1/wallet/tde
[oracle@ora12cr1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 7 15:51:21 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system set encryption key identified by "db1";

System altered.

SQL> alter system set db_create_file_dest='/u02/oradata';

System altered.

SQL> create tablespace encrypted datafile size 64m encryption using 'AES256' default storage (encrypt);

Tablespace created.

SQL> create user encrypt_user identified by "encrypt_user" default tablespace encrypted;

User created.

SQL> alter user encrypt_user quota unlimited on encrypted;

User altered.

SQL> create table encrypt_user.z_encrypted as
        select dbms_random.string('x', 100) s
                from dual
                connect by level <= 100;  2    3    4

Table created.

Note that I did not use the new administer key management syntax on purpose and instead created the wallet the old style way as I expect most of the existing environments to be the same. We will explore the significance of this in a moment.

Create a CDB

Before our non-CDB database can be converted to a PDB and plugged into a CDB we need to create the container database first:

[oracle@ora12cr1 ~]$ dbca -silent \
> -createDatabase \
> -templateName New_Database.dbt \
> -gdbName cdb12cr1 \
> -createAsContainerDatabase true \
> -sysPassword oracle \
> -systemPassword oracle \
> -emConfiguration none \
> -datafileDestination /u02/oradata \
> -redoLogFileSize 128 \
> -recoveryAreaDestination /u02/fra \
> -storageType FS \
> -characterSet al32utf8 \
> -nationalCharacterSet al16utf16 \
> -automaticMemoryManagement false \
> -initParams filesystemio_options=setall \
> -initParams session_cached_cursors=100 \
> -totalMemory 1024
Creating and starting Oracle instance
1% complete
...
100% complete

As before I need to run the datapatch utility:

[oracle@ora12cr1 ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb12cr1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12cr1 ~]$ $ORACLE_HOME/OPatch/datapatch -verbose

Setup CDB with a wallet

Our newly created CDB needs to be setup with a wallet before we proceed with plugging a PDB which utilizes TDE. Let's do it now:

[oracle@ora12cr1 ~]$ mkdir -p /u01/app/oracle/admin/cdb12cr1/wallet/tde
[oracle@ora12cr1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 7 17:23:58 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> administer key management
        create keystore '/u01/app/oracle/admin/cdb12cr1/wallet/tde'
        identified by "cdb12cr1";  2    3

keystore altered.

SQL> administer key management
        set keystore open
        identified by "cdb12cr1";  2    3

keystore altered.

SQL> administer key management
        set key identified by "cdb12cr1"
        with backup;  2    3

keystore altered.

Here I have setup the wallet and the master encryption key using the new syntax.

Wallets

Before we move forward let's explore the differences between db1 (created using the old syntax) and cdb12cr1 (created using the new syntax) wallets:

[oracle@ora12cr1 ~]$ orapki wallet display -wallet /u01/app/oracle/admin/db1/wallet/tde
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AY/sdMkWZk/4v3YgBKzZtcIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
Trusted Certificates:
[oracle@ora12cr1 ~]$  orapki wallet display -wallet /u01/app/oracle/admin/cdb12cr1/wallet/tde
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.Ad+A607CLU+Ivx4f4E7KUYIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.Ad+A607CLU+Ivx4f4E7KUYIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

Notice how db1 has the encryption key listed under ORACLE.SECURITY.DB.ENCRYPTION while cdb12cr1 in addition has the encryption key listed under the new ORACLE.SECURITY.KM.ENCRYPTION as well.

In practice what I found is unless your encryption key is listed under ORACLE.SECURITY.KM.ENCRYPTION it will not be exported when doing administer key management export keys. As a result when you import the export file the master key required to decrypt the data will not be there potentially leaving you in a peculiar situation especially if you have used an in-place conversion. Even if you specifically export the key using the with identifier in syntax you will not be able to import the key because specific key exports are not permitted to be imported into the PDBs.

Add ORACLE.SECURITY.KM.ENCRYPTION to db1's wallet

This step is only required if you created the wallet without using the new administer key management syntax. Re-keying the wallet will generate a new master key preserving the old master key necessary to decrypt the data while adding missing ORACLE.SECURITY.KM.ENCRYPTION entries at the same time. Execute while connected to db1:

SQL> administer key management
        set key identified by "db1"
        with backup;  2    3

keystore altered.

If we look at the wallet we can see that it now contains the necessary entires:

[oracle@ora12cr1 ~]$ orapki wallet display -wallet /u01/app/oracle/admin/db1/wallet/tde
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
Subject:        CN=oracle
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DB.ENCRYPTION.AXZlzWtP/U/xv+vihPzeaGkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AY/sdMkWZk/4v3YgBKzZtcIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.AXZlzWtP/U/xv+vihPzeaGkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AY/sdMkWZk/4v3YgBKzZtcIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.MASTERKEY
Trusted Certificates:

Prepare db1 to be converted into a PDB

Before db1 can be plugged into a container database it needs to be converted to a PDB and it's encryption keys exported. Shutdown db1 and open it in read only:

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      db1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  2923872 bytes
Variable Size             452985504 bytes
Database Buffers          771751936 bytes
Redo Buffers               13852672 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

We can now export the encryption keys:

SQL> administer key management
        set keystore open
        identified by "db1";  2    3

keystore altered.

SQL> administer key management
        export keys with secret "db1"
        to '/u01/app/oracle/admin/cdb12cr1/wallet/tde/db1.exp'
        identified by "db1";  2    3    4

keystore altered.

Note that in order for the export operation to work the wallet needs to be explicitly opened with a password otherwise you will receive an error. In case of an auto login (local) wallet you will have to close the wallet and reopen it with a password.

The next step is to generate metadata necessary for PDB conversion and shutdown db1 database:

SQL> exec dbms_pdb.describe(pdb_descr_file => '/u01/app/oracle/db1.xml');

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Convert and plug db1 into a CDB

We can now login into cdb12cr1 and perform in-place conversion of db1 which does not requires any datafiles to be copied. Of course if something were to go wrong with the conversion process you might end up in a situation where you need to restore your original database from a backup so use this approach with care.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb12cr1
SQL> create pluggable database pdb1
        using '/u01/app/oracle/db1.xml'
        nocopy tempfile reuse;  2    3

Pluggable database created.

Before pdb1 can be opened we need to run the script which will convert pdb1's data dictionary:

SQL> alter session set container=pdb1;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

Once the script completes we can open pdb1:

SQL> alter pluggable database pdb1 open;

Warning: PDB altered with errors.

The error while opening the PDB tells us that the encryption key is missing (can be seen in pdb_plug_in_violations view). Let's go and import the key now:

[oracle@ora12cr1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 7 18:22:23 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=pdb1;

Session altered.

SQL> administer key management
        set keystore open
        identified by "cdb12cr1";  2    3

keystore altered.

SQL> administer key management
        import keys with secret "db1"
        from '/u01/app/oracle/admin/cdb12cr1/wallet/tde/db1.exp'
        identified by "cdb12cr1"
        with backup;  2    3    4    5

keystore altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

The encryption keys are now imported and we no longer get an error. Note that as with the export in order to import the keys the wallet must be explicitly opened with a password. We can verify that everything is in order by querying the encrypted table:

SQL> administer key management
        set keystore open
        identified by "cdb12cr1";  2    3

keystore altered.

SQL> select count(*) from encrypt_user.z_encrypted;

  COUNT(*)
----------
       100