2.2.3 Creating Oracle ASM Disk Groups

You can create Oracle ASM disk groups on Oracle Exadata Storage Server grid disks.

To create an Oracle ASM disk group to use Oracle Exadata Storage Server grid disks, perform the following procedure:

1. Connect to the Oracle ASM instance.
2. Ensure that the ORACLE_SID environment variable is set to the Oracle ASM instance using a command similar to the following:

   $ setenv ORACLE_SID ASM_instance_SID
   

3. Start SQL*Plus on the Oracle ASM instance, and log in as a user with SYSASM administrative privileges.

   $ sqlplus / AS SYSASM
   

4. Determine which Oracle Exadata Storage Server grid disks are available by querying the V$ASM_DISK view on the Oracle ASM instance, using the following syntax:

   SQL> SELECT path, header_status STATUS FROM V$ASM_DISK WHERE path LIKE 'o/%';
   

5. Create an Oracle ASM disk group to include disks on the cells.

   In this example, the ALTER command is needed to change compatible.rdbms for the disk group created during installation to hold the OCR and voting disks. The compatible.rdbms attribute is set to 11.2.0.2 in order to support Oracle Database release 11.2.0.2 and later release databases in a consolidated environment.

   CREATE DISKGROUP data HIGH REDUNDANCY
   DISK 'o/*/DATA*'
   ATTRIBUTE 'AU_SIZE' = '4M',
             'content.type' = 'data',
             'compatible.rdbms'='11.2.0.4',
             'compatible.asm'='19.0.0.0';
   
   SQL> CREATE DISKGROUP reco HIGH REDUNDANCY
   DISK 'o/*/RECO*'
   ATTRIBUTE 'AU_SIZE' = '4M',
             'content.type' = 'recovery',
             'compatible.rdbms'='11.2.0.4',
             'compatible.asm'='19.0.0.0';
    
   REM for Exadata systems prior to X7
   SQL> ALTER DISKGROUP dbfs_dg SET ATTRIBUTE 
        'content.type' = 'system',
        'compatible.rdbms' = '11.2.0.4';
   

   When creating sparse disk groups, use a command similar to the following:

   SQL> CREATE DISKGROUP sparsedg NORMAL REDUNDANCY
   DISK 'o.*/sparse_*'
   ATTRIBUTE 'AU_SIZE' = '4M',
             'content.type' = 'data',
             'cell.smart_scan_capable'='TRUE',
             'compatible.rdbms' = '12.1.0.2',
             'compatible.asm' = '19.0.0.0', 
             'cell.sparse_dg' = 'allsparse';
   

   In the preceding command, the cell.sparse_dg attribute defines the disk group as a sparse disk group. The attribute is not required if the disk group is not a sparse disk group.

   Note:

   • When defining sparse grid disks, the compatible.asm and compatible.rdbms attributes must be at least 12.1.0.2.0.
   • The Oracle ASM disk group compatible attributes take precedence over the COMPATIBLE initialization parameter for the Oracle ASM instance.
   • The Oracle Database and Oracle Grid Infrastructure software must be release 12.1.0.2.0 BP5 or later when using sparse grid disks.
   • The recommended allocation unit size (AU_SIZE) is 4 MB for Oracle ASM disk groups on Exadata.
6. View the Oracle ASM disk groups and associated attributes with a SQL query on V$ASM dynamic views.

   SQL> SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, 
        SUBSTR(a.value,1,24) AS value FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a 
        WHERE dg.group_number = a.group_number;
   
   DISKGROUP                    NAME                       VALUE
   ---------------------------- ------------------------ ------------------------
   DATA                         compatible.rdbms           11.2.0.4
   DATA                         compatible.asm             19.0.0.0
   DATA                         au_size                    4194304
   DATA                         disk_repair_time           3.6h
   DATA                         cell.smart_scan_capable    TRUE
   ...
   

7. Create a tablespace in the disk group to take advantage of Oracle Exadata System Software features, such as offload processing. The tablespace should contain the tables that you want to query with offload processing.

   SQL> CREATE TABLESPACE tablespace_name DATAFILE '+DATA';
   

   In the preceding command, +DATA is the name of the Oracle ASM disk group.

8. Verify that the tablespace is in an Oracle Exadata Storage Server disk group. The PREDICATE_EVALUATION column of the DBA_TABLESPACES view indicates whether predicates are evaluated by host (HOST) or by storage (STORAGE).

   SQL> SELECT tablespace_name, predicate_evaluation FROM dba_tablespaces
        WHERE tablespace_name = 'DATA_TB';
   
   TABLESPACE_NAME                PREDICA
   ------------------------------ -------
   DATA_TB                        STORAGE