Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
Note:
This SQL statement is valid only if you are using Oracle ASM and you have started an Oracle ASM instance. You must issue this statement from within the Oracle ASM instance, not from a normal database instance. For information on starting an Oracle ASM instance, refer to Oracle Automatic Storage Management Administrator's Guide.Use the CREATE
DISKGROUP
clause to name a group of disks and specify that Oracle Database should manage the group for you. Oracle Database manages a disk group as a logical unit and evenly spreads each file across the disks to balance I/O. Oracle Database also automatically distributes database files across all available disks in disk groups and rebalances storage automatically whenever the storage configuration changes.
This statement creates a disk group, assigns one or more disks to the disk group, and mounts the disk group for the first time. Note that CREATE
DISKGROUP
only mounts a disk group on the local node. If you want Oracle ASM to mount the disk group automatically in subsequent instances, then you must add the disk group name to the value of the ASM_DISKGROUPS
initialization parameter in the initialization parameter file. If you use an SPFILE
, then the disk group is added to the initialization parameter automatically.
See Also:
ALTER DISKGROUP for information on modifying disk groups
Oracle Automatic Storage Management Administrator's Guide for information on Oracle ASM and using disk groups to simplify database administration
ASM_DISKGROUPS
for more information about adding disk group names to the initialization parameter file
V$ASM_OPERATION
for information on monitoring Oracle ASM operations
DROP DISKGROUP for information on dropping a disk group
You must have the SYSASM
system privilege to issue this statement.
Before issuing this statement, you must format the disks using an operating system format utility. Also ensure that the Oracle Database user has read/write permission and the disks can be discovered using the ASM_DISKSTRING
.
When you store your database files in Oracle ASM disk groups, rather than in a file system or on raw devices, before the database instance can access your files in the disk groups, you must configure and start up an Oracle ASM instance to manage the disk groups.
Each database instance communicates with a single Oracle ASM instance on the same node as the database. Multiple database instances on the same node can communicate with a single Oracle ASM instance.
Specify the name of the disk group. Disk groups are subject to the same naming conventions and restrictions as database schema objects. Refer to "Database Object Naming Rules" for information on database object names. However, disk groups are not schema objects. Disk group names are not case sensitive, even if you specify them with quotation marks. They are always stored internally as uppercase.
Note:
Oracle does not recommend using quoted identifiers for disk group names. These quoted identifiers are accepted when issuing theCREATE
DISKGROUP
statement in SQL*Plus, but they may not be valid when using other tools that manage disk groups.The REDUNDANCY
clause lets you specify the redundancy level of the disk group.
NORMAL
REDUNDANCY
requires the existence of at least two failure groups (see the FAILGROUP
clause that follows). Oracle ASM provides redundancy for all files in the disk group according to the attributes specified in the disk group templates. NORMAL
REDUNDANCY
disk groups can tolerate the loss of one group. Refer to ALTER
DISKGROUP
... diskgroup_template_clauses for more information on disk group templates.
NORMAL
REDUNDANCY
is the default if you omit the REDUNDANCY
clause. Therefore, if you omit this clause, you must create at least two failure groups, or the create operation will fail.
HIGH
REDUNDANCY
requires the existence of at least three failure groups. Oracle ASM fixes mirroring at 3-way mirroring, with each extent getting two mirrored copies. HIGH
REDUNDANCY
disk groups can tolerate the loss of two failure groups.
EXTERNAL
REDUNDANCY
indicates that Oracle ASM does not provide any redundancy for the disk group. The disks within the disk group must provide redundancy (for example, using a storage array), or you must be willing to tolerate loss of the disk group if a disk fails (for example, in a test environment). You cannot specify the FAILGROUP
clause if you specify EXTERNAL
REDUNDANCY
.
Use these keywords to qualify either failure group or disk specifications.
REGULAR
disks, or disks in non-quorum failure groups, can contain any files.
QUORUM
disks, or disks in quorum failure groups, cannot contain any database files, the Oracle Cluster Registry (OCR), or dynamic volumes. However, QUORUM
disks can contain the voting file for Cluster Synchronization Services (CSS). Oracle ASM uses quorum disks or disks in quorum failure groups for voting files whenever possible.
Disks in quorum failure groups are not considered when determining redundancy requirements.
If you specify neither keyword, then REGULAR
is the default.
Specify either QUORUM
or REGULAR
before the keyword FAILGROUP
if you are explicitly specifying the failure group. If you are creating a disk group with implicitly created failure groups, then specify these keywords before the keyword DISK
.
See Also:
Oracle Automatic Storage Management Administrator's Guide for more information about quorum and regular disks and failure groupsUse this clause to specify a name for one or more failure groups. If you omit this clause, and you have specified NORMAL
or HIGH
REDUNDANCY
, then Oracle Database automatically adds each disk in the disk group to its own failure group. The implicit name of the failure group is the same as the operating system independent disk name (see "NAME Clause").
You cannot specify this clause if you are creating an EXTERNAL
REDUNDANCY
disk group.
Specify DISK
qualified_disk_clause
to add a disk to a disk group.
search_string For each disk you are adding to the disk group, specify the operating system dependent search string that Oracle ASM will use to find the disk. The search_string
must point to a subset of the disks returned by discovery using the strings in the ASM_DISKSTRING
initialization parameter. If search_string
does not point to any disks the Oracle Database user has read/write access to, then Oracle ASM returns an error. If it points to one or more disks that have already been assigned to a different disk group, then Oracle Database returns an error unless you also specify FORCE
.
For each valid candidate disk, Oracle ASM formats the disk header to indicate that it is a member of the new disk group.
See Also:
The ASM_DISKSTRING
initialization parameter for more information on specifying the search stringNAME Clause The NAME
clause is valid only if the search_string
points to a single disk. This clause lets you specify an operating system independent name for the disk. The name can be up to 30 alphanumeric characters. The first character must be alphabetic. If you omit this clause and you assigned a label to a disk through ASMLIB, then that label is used as the disk name. If you omit this clause and you did not assign a label through ASMLIB, then Oracle ASM creates a default name of the form diskgroup_name
_####, where #### is the disk number. You use this name to refer to the disk in subsequent Oracle ASM operations.
SIZE Clause Use this clause to specify in bytes the size of the disk. If you specify a size greater than the capacity of the disk, then Oracle ASM returns an error. If you specify a size less than the capacity of the disk, then you limit the disk space Oracle ASM will use. If you omit this clause, then Oracle ASM attempts programmatically to determine the size of the disk.
FORCE Specify FORCE
if you want Oracle ASM to add the disk to the disk group even if the disk is already a member of a different disk group.
Caution:
UsingFORCE
in this way may destroy existing disk groups.For this clause to be valid, the disk must already be a member of a disk group and the disk cannot be part of a mounted disk group.
NOFORCE Specify NOFORCE
if you want Oracle ASM to return an error if the disk is already a member of a different disk group. NOFORCE
is the default.
Use this clause to set attribute values for the disk group. You can view the current attribute values by querying the V$ASM_ATTRIBUTE
view. Table 14-1 lists the attributes you can set with this clause. All attribute values are strings.
Table 14-1 Disk Group Attributes
Attribute | Valid Values | Description |
---|---|---|
|
|
Specifies whether Oracle ASM File Access Control is enabled for a disk group. If set to If both the When you set up file access control on an existing disk group, the files previously created remain accessible by everyone, unless you run the Note: This attribute is used in conjunction with |
|
A three-digit number where each digit is 0, 2, or 6. |
Determines which permissions are masked out on the creation of an Oracle ASM file for the user that owns the file (first digit), users in the same user group (second digit), and others not in the user group (third digit). This attribute applies to all files on a disk group. Setting to 0 masks out nothing. Setting to 2 masks out write permission. Setting to 6 masks out both read and write permissions. The default value is If both the When you set up file access control on an existing disk group, the files previously created remain accessible by everyone, unless you run the Note: This attribute is used in conjunction with |
|
Size in bytes. Valid values are powers of 2 from 1M to 64M. Examples '4M', '4194304'. |
Specifies the allocation unit size. This attribute can be set only during disk group creation; it cannot be modified with an |
|
Valid Oracle Database version numberFoot 1 |
Determines whether the disk group can contain Oracle ASM volumes. The value must be set to By default, the value of the |
|
Valid Oracle Database version numberFootref 1 |
Determines the minimum software version for an Oracle ASM instance that can use the disk group. This setting also affects the format of the data structures for the Oracle ASM metadata on the disk. For Oracle ASM in Oracle Database 11g, |
|
Valid Oracle Database version numberFootref 1 |
Determines the minimum Before advancing the For Oracle ASM in Oracle Database 11g, |
|
0 to 136 years |
When disks are taken offline, Oracle ASM drops them after a default period of time. If both the The time can be specified in units of minute (M) or hour (H). The specified time elapses only when the disk group is mounted. If you omit the unit, then the default is H. If you omit this attribute, and both Note: If a disk is taken offline using the current value of See Also: The |
|
Sector size of the disks in the disk group. Valid values are '512' '4096', and '4K'. |
All disks in the disk group must have a sector size equal to the attribute value specified. When processing the When new disks are added to an existing disk group, using the By setting a value for this attribute, you can establish the sector size you intend for the disk group, rather than letting Oracle ASM assume a value for all the disks in the disk group. As a result, users can query the |
Footnote 1 Specify at least the first two digits of a valid Oracle Database release number. Refer to Oracle Database Administrator's Guide for information on specifying valid version numbers. For example, you can specify compatibility
as '10.2' or '11.2'.
See Also:
Oracle Automatic Storage Management Administrator's Guide for more information on managing these attribute settingsThe following example assumes that the ASM_DISKSTRING
parameter is a superset of $ORACLE_HOME/disks/c*
, $ORACLE_HOME/disks/c*
points to at least one device to be used as an Oracle ASM disk, and the Oracle Database user has read/write permission to the disks.
See Also:
Oracle Automatic Storage Management Administrator's Guide for information on Oracle ASM and using disk groups to simplify database administrationCreating a Diskgroup: Example The following statement creates an Oracle ASM disk group dgroup_01
where no redundancy for the disk group is provided by Oracle ASM and includes all disks that match the search_string
:
CREATE DISKGROUP dgroup_01 EXTERNAL REDUNDANCY DISK '$ORACLE_HOME/disks/c*';