Skip Headers
Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

ALTER DISKGROUP

Note:

This SQL statement is valid only if you are using Automatic Storage Management and you have started an Automatic Storage Management instance. You must issue this statement from within the Automatic Storage Management instance, not from a normal database instance. For information on starting an Automatic Storage Management instance, refer to Oracle Database Storage Administrator's Guide.

Purpose

The ALTER DISKGROUP statement lets you perform a number of operations on a disk group or on the disks in a disk group.

See Also:

Prerequisites

You must have the SYSDBA system privilege to issue this statement, and you must have an Automatic Storage Management instance started from which you issue this statement. The disk group to be modified must be mounted.

The SYSOPER role permits the following subset of the ALTER DISKGROUP operations: diskgroup_availability_clause, balance_diskgroup_clause, check_diskgroup_clause.

Syntax

alter_diskgroup::=

Description of alter_diskgroup.gif follows
Description of the illustration alter_diskgroup.gif

(add_disk_clause::=, drop_disk_clauses::=, resize_disk_clauses::=, disk_online_clause::=, disk_offline_clause::=, rebalance_diskgroup_clause ::=, check_diskgroup_clause::=, diskgroup_template_clauses::=, diskgroup_directory_clauses::=, diskgroup_alias_clauses::=, diskgroup_attributes ::=, drop_diskgroup_file_clause::=, undrop_disk_clause::=, diskgroup_availability::=)

add_disk_clause::=

Description of add_disk_clause.gif follows
Description of the illustration add_disk_clause.gif

(qualified_disk_clause::=)

qualified_disk_clause::=

Description of qualified_disk_clause.gif follows
Description of the illustration qualified_disk_clause.gif

(size_clause::=)

drop_disk_clauses::=

Description of drop_disk_clauses.gif follows
Description of the illustration drop_disk_clauses.gif

resize_disk_clauses::=

Description of resize_disk_clauses.gif follows
Description of the illustration resize_disk_clauses.gif

(size_clause::=)

disk_online_clause::=

Description of disk_online_clause.gif follows
Description of the illustration disk_online_clause.gif

disk_offline_clause::=

Description of disk_offline_clause.gif follows
Description of the illustration disk_offline_clause.gif

timeout_clause::=

Description of timeout_clause.gif follows
Description of the illustration timeout_clause.gif

undrop_disk_clause::=

Description of undrop_disk_clause.gif follows
Description of the illustration undrop_disk_clause.gif

rebalance_diskgroup_clause ::=

Description of rebalance_diskgroup_clause.gif follows
Description of the illustration rebalance_diskgroup_clause.gif

check_diskgroup_clause::=

Description of check_diskgroup_clause.gif follows
Description of the illustration check_diskgroup_clause.gif

diskgroup_template_clauses::=

Description of diskgroup_template_clauses.gif follows
Description of the illustration diskgroup_template_clauses.gif

(qualified_template_clause::=)

qualified_template_clause::=

Description of qualified_template_clause.gif follows
Description of the illustration qualified_template_clause.gif

diskgroup_directory_clauses::=

Description of diskgroup_directory_clauses.gif follows
Description of the illustration diskgroup_directory_clauses.gif

diskgroup_alias_clauses::=

Description of diskgroup_alias_clauses.gif follows
Description of the illustration diskgroup_alias_clauses.gif

diskgroup_attributes ::=

Description of diskgroup_attributes.gif follows
Description of the illustration diskgroup_attributes.gif

drop_diskgroup_file_clause::=

Description of drop_diskgroup_file_clause.gif follows
Description of the illustration drop_diskgroup_file_clause.gif

diskgroup_availability::=

Description of diskgroup_availability.gif follows
Description of the illustration diskgroup_availability.gif

Semantics

diskgroup_name

Specify the name of the disk group you want to modify. To determine the names of existing disk groups, query the V$ASM_DISKGROUP dynamic performance view.

add_disk_clause

Use this clause to add one or more disks to the disk group and specify attributes for the newly added disk. Automatic Storage Management automatically rebalances the disk group as part of this operation.

You cannot use this clause to change the failure group of a disk. Instead you must drop the disk from the disk group and then add the disk back into the disk group as part of the new failure group.

To determine the names of the disks already in this disk group, query the V$ASM_DISK dynamic performance view.

FAILGROUP Clause Use this clause to assign the newly added disk to a failure group. If you omit this clause and you are adding the disk to a normal or high redundancy disk group, then Oracle Database automatically adds the newly added disk 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.

qualified_disk_clause

This clause has the same semantics in CREATE DISKGROUP and ALTER DISKGROUP statements. For complete information on this clause, refer to qualified_disk_clause in the documentation on CREATE DISKGROUP.

drop_disk_clauses

Use this clause to drop one or more disks from the disk group.

DROP DISK The DROP DISK clause lets you drop one or more disks from the disk group and automatically rebalance the disk group. When you drop a disk, Automatic Storage Management relocates all the data from the disk and clears the disk header so that it no longer is part of the disk group.

DROP DISKS IN FAILGROUP The DROP DISKS IN FAILGROUP clause lets you drop all the disks in the specified failure group. The behavior is otherwise the same as that for the DROP DISK clause.

FORCE | NOFORCE These keywords let you specify when the disk is considered to be no longer part of the disk group. The default and recommended setting is NOFORCE.

The rebalance operation invoked when a disk is dropped is time consuming, whether or not you specify FORCE or NOFORCE. You can monitor the progress by querying the V$ASM_OPERATION dynamic performance view. Refer to rebalance_diskgroup_clause for more information on rebalance operations.

resize_disk_clauses

Use these clauses to specify a new size for one or more disks in the disk group. These clauses let you override the size being returned by the operating system or the size you specified previously for the disks.

RESIZE ALL Specify this clause to perform a resize operation on every disk in the disk group.

RESIZE DISK Specify this clause to resize only the specified disk.

RESIZE DISKS IN FAILGROUP Specify this clause to resize every disk in the specified failure group.

SIZE Specify the size of the disk in kilobytes, megabytes, gigabytes, or terabytes. You cannot specify a size greater than the capacity of the disk. If you specify a size smaller than the disk capacity, then you limit the amount of disk space Automatic Storage Management will use. If you omit this clause, then Automatic Storage Management uses the size being returned by the operating system.

disk_offline_clause

Use the disk_offline_clause to take one or more disks offline. This clause fails if the redundancy level of the disk group would be violated by taking the specified disks offline.

By default, Automatic Storage Management drops a disk shortly after it is taken offline. You can delay this operation by specifying the timeout_clause, which gives you the opportunity to repair the disk and bring it back online. You can specify the timeout value in units of minute or hour. If you omit the unit, then the default is hour.

You can change the timeout period by specifying this clause multiple times. Each time you specify it, Automatic Storage Management measures the time from the most recent previous disk_offline_clause while the disk group is mounted. To learn how much time remains before Automatic Storage Management will drop an offline disk, query the repair_timer column of V$ASM_DISK.

This clause overrides any previous setting of the disk_repair_time attribute. Refer to Table 14-1, "Disk Group Attributes" for more information about disk group attributes.

disk_online_clause

Use the disk_online_clause to bring one or more disks online and rebalance the disk group. The WAIT and NOWAIT clause has the same semantics as for a manual rebalancing of the disk group. See the description of POWER and WAIT | NOWAIT for more information.

See Also:

Oracle Database Storage Administrator's Guide for more information about taking ASM disks online and offline

undrop_disk_clause

Use this clause to cancel the drop of disks from the disk group. You can cancel the pending drop of all the disks in one or more disk groups (by specifying diskgroup_name) or of all the disks in all disk groups (by specifying ALL).

This clause is not relevant for disks that have already been completely dropped from the disk group or for disk groups that have been completely dropped. This clause results in a long-running operation. You can see the status of the operation by querying the V$ASM_OPERATION dynamic performance view.

See Also:

V$ASM_OPERATION for more information on the details of long-running Automatic Storage Management operations

diskgroup_clauses

Use these clauses to operate on entire disk groups.

rebalance_diskgroup_clause

Use this clause to manually rebalance the disk group. Automatic Storage Management redistributes datafiles evenly across all drives. This clause is rarely necessary, because Automatic Storage Management allocates files evenly and automatically rebalances disk groups when the storage configuration changes. However, it is useful if you want to use the POWER clause to control the speed of what would otherwise be an automatic rebalance operation.

POWER In the POWER clause, specify a value from 0 to 11, where 0 stops the rebalance operation and 11 permits Automatic Storage Management to execute the rebalance as fast as possible. The value you specify in the POWER clause defaults to the value of the ASM_POWER_LIMIT initialization parameter.

If you omit the POWER clause, then Automatic Storage Management executes both automatic and specified rebalance operations at the power determined by the value of the ASM_POWER_LIMIT initialization parameter.

WAIT | NOWAIT Use this clause to specify when in the course of the rebalance operation control should be returned to the user.

You can monitor the progress of the rebalance operation by querying the V$ASM_OPERATION dynamic performance view.

See Also:

ASM_POWER_LIMIT and Oracle Database Storage Administrator's Guide for more information on rebalancing disk groups and "Rebalancing a Disk Group: Example"

check_diskgroup_clause

The check_diskgroup_clause lets you verify the internal consistency of Automatic Storage Management disk group metadata. The disk group must be mounted. Automatic Storage Management displays summary errors and writes the details of the detected errors in the alert log.

The CHECK keyword performs the following operations:

REPAIR | NOREPAIR This clause lets you instruct Automatic Storage Management whether or not to attempt to repair any errors found during the consistency check. The default is REPAIR. The NOREPAIR setting is useful if you want to be alerted to any inconsistencies but do not want Automatic Storage Management to take any automatic action to resolve them.

Deprecated Clauses In earlier releases, you could specify CHECK for ALL, DISK, DISKS IN FAILGROUP, or FILE. Those clauses have been deprecated as they are no longer needed. If you specify them, then their behavior is the same as in earlier releases and a message is added to the alert log. However, Oracle recommends that you do not introduce these clauses into your new code, as they are scheduled for desupport. The deprecated clauses are these:

diskgroup_template_clauses

A template is a named collection of attributes. When you create a disk group, Automatic Storage Management associates a set of initial system default templates with that disk group. The attributes defined by the template are applied to all files in the disk group. The table that follows lists the system default templates and the attributes they apply to the various file types. The diskgroup_template_clauses described following the table let you change the template attributes and create new templates.

You cannot use this clause to change the attributes of a disk group file after it has been created. Instead, you must use Recovery Manager (RMAN) to copy the file into a new file with the new attributes.

Table 10-1 Automatic Storage Management System Default File Group Templates

Template Name File Type External Redundancy Normal Redundancy High Redundancy Striped

CONTROL

Control files

Unprotected

2-way mirror

3-way mirror

Fine

DATAFILE

Datafiles and copies

Unprotected

2-way mirror

3-way mirror

Coarse

ONLINELOG

Online logs

Unprotected

2-way mirror

3-way mirror

Fine

ARCHIVELOG

Archive logs

Unprotected

2-way mirror

3-way mirror

Coarse

TEMPFILE

Tempfiles

Unprotected

2-way mirror

3-way mirror

Coarse

BACKUPSET

Datafile backup pieces, datafile incremental backup pieces, and archive log backup pieces

Unprotected

2-way mirror

3-way mirror

Coarse

PARAMETERFILE

SPFILEs

Unprotected

2-way mirror

3-way mirror

Coarse

DATAGUARDCONFIG

Disaster recovery configurations (used in standby databases)

Unprotected

2-way mirror

3-way mirror

Coarse

FLASHBACK

Flashback logs

Unprotected

2-way mirror

3-way mirror

Fine

CHANGETRACKING

Block change tracking data (used during incremental backups)

Unprotected

2-way mirror

3-way mirror

Coarse

DUMPSET

Data Pump dumpset

Unprotected

2-way mirror

3-way mirror

Coarse

XTRANSPORT

Cross-platform converted datafile

Unprotected

2-way mirror

3-way mirror

Coarse

AUTOBACKUP

Automatic backup files

Unprotected

2-way mirror

3-way mirror

Coarse


ADD TEMPLATE Use this clause to add one or more named templates to a disk group. To determine the names of existing templates, query the V$ASM_TEMPLATE dynamic performance view.

MODIFY TEMPLATE Use this clause to modify the attributes of a system default or user-defined disk group template. Only the specified attributes are altered. Unspecified properties retain their current values.

Note:

In earlier releases, the keywords ALTER TEMPLATE were used instead of MODIFY TEMPLATE. The ALTER keyword is still supported for backward compatibility, but it replaced with MODIFY for consistency with other Oracle SQL.

template_name Specify the name of the template to be added or modified. Template names are subject to the same naming conventions and restrictions as database schema objects. Refer to "Schema Object Naming Rules" for information on database object names.

Redundancy Level Specify the redundancy level of the newly added or modified template:

If you omit this clause, then the value defaults to MIRROR for a normal redundancy disk group, HIGH for a high redundancy disk group, and UNPROTECTED for an external redundancy disk group.

Disk Striping Specify how the files to which this template are applied will be striped:

DROP TEMPLATE Use this clause to drop one or more templates from the disk group. You can use this clause to drop only user-defined templates, not system default templates.

diskgroup_directory_clauses

Before you can create alias names for Automatic Storage Management filenames (see diskgroup_alias_clauses), you must specify the full directory structure in which the alias name will reside. The diskgroup_directory_clauses let you create and manipulate such a directory structure.

ADD DIRECTORY Use this clause to create a new directory path for hierarchically named aliases. Use a slash (/) to separate components of the directory. Each directory component can be up to 48 bytes in length and must not contain the slash character. You cannot use a space for the first or last character of any component. The total length of the directory path cannot exceed 256 bytes minus the length of any alias name you intend to create in this directory (see diskgroup_alias_clauses).

DROP DIRECTORY Use this clause to drop a directory for hierarchically named aliases. Automatic Storage Management will not drop the directory if it contains any alias definitions unless you also specify FORCE. This clause is not valid for dropping directories created as part of a system alias. Such directories are labeled with the value Y in the SYSTEM_CREATED column of the V$ASM_ALIAS dynamic performance view.

RENAME DIRECTORY Use this clause to change the name of a directory for hierarchically named aliases. This clause is not valid for renaming directories created as part of a system alias. Such directories are labeled with the value Y in the SYSTEM_CREATED column of the V$ASM_ALIAS dynamic performance view.

diskgroup_alias_clauses

When an Automatic Storage Management file is created, either implicitly or by user specification, Automatic Storage Management assigns to the file a fully qualified name ending in a dotted pair of numbers (see file_specification). The diskgroup_alias_clauses let you create more user-friendly alias names for the Automatic Storage Management filenames. You cannot specify an alias name that ends in a dotted pair of numbers, as this format is indistinguishable from an Automatic Storage Management filename.

Before specifying this clause, you must first create the directory structure appropriate for your naming conventions (see diskgroup_directory_clauses). The total length of the alias name, including the directory prefix, is limited to 256 bytes. Alias names are case insensitive but case retentive.

ADD ALIAS Use this clause to create an alias name for an Automatic Storage Management filename. The alias_name consists of the full directory path and the alias itself. To determine the names of existing Automatic Storage Management aliases, query the V$ASM_ALIAS dynamic performance view. Refer to ASM_filename for information on Automatic Storage Management filenames.

DROP ALIAS Use this clause to remove an alias name from the disk group directory. Each alias name consists of the full directory path and the alias itself. The underlying file to which the alias refers remains unchanged.

RENAME ALIAS Use this clause to change the name of an existing alias. The alias_name consists of the full directory path and the alias itself.

Restriction on Dropping and Renaming Aliases You cannot drop or rename a system-generated alias. To determine whether an alias was system generated, query the SYSTEM_CREATED column of the V$ASM_ALIAS dynamic performance view.

diskgroup_attributes

Use this clause to specify attributes for the disk group. Table 14-1, "Disk Group Attributes" lists the attributes you can set with this clause. Refer to the CREATE DISKGROUP "ATTRIBUTE Clause" for information on the behavior of this clause.

drop_diskgroup_file_clause

Use this clause to drop a file from the disk group. Automatic Storage Management also drops all aliases associated with the file being dropped. You must use one of the reference forms of the filename. Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed. Refer to ASM_filename for information on the reference forms of Automatic Storage Management filenames.

diskgroup_availability

Use this clause to make one or more disk groups available or unavailable to the database instances running on the same node as the Automatic Storage Management instance. This clause does not affect the status of the disk group on other nodes in a cluster.

MOUNT Specify MOUNT to mount the disk groups in the local Automatic Storage Management instance. Specify ALL MOUNT to mount all disk groups specified in the ASM_DISKGROUPS initialization parameter. File operations can only be performed when a disk group is mounted.

RESTRICTED | NORMAL Use these clauses to determine the manner in which the disk groups are mounted.

FORCE | NOFORCE Use these clauses to determine the circumstances under which the disk groups are mounted.

See Also:

ASM_DISKGROUPS for more information about adding disk group names to the initialization parameter file

DISMOUNT Specify DISMOUNT to dismount the specified disk groups. Automatic Storage Management returns an error if any file in the disk group is open unless you also specify FORCE. Specify ALL DISMOUNT to dismount all currently mounted disk groups. File operations can only be performed when a disk group is mounted.

FORCE Specify FORCE if you want Automatic Storage Management to dismount the disk groups even if some files in the disk group are open.

Examples

The following examples require a disk group called dgroup_01. They assume that ASM_DISKSTRING is set to $ORACLE_HOME/disks/*. In addition, they assume the Oracle user has read/write permission to $ORACLE_HOME/disks/d100. Refer to "Creating a Diskgroup: Example" to create dgroup_01.

Adding a Disk to a Disk Group: Example To add a disk, d100, to a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  ADD DISK '$ORACLE_HOME/disks/d100';

Dropping a Disk from a Disk Group: Example To drop a disk, dgroup_01_0000, from a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  DROP DISK dgroup_01_0000;

Undropping a Disk from a Disk Group: Example To cancel the drop of disks from a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  UNDROP DISKS;

Resizing a Disk Group: Example To resize every disk in a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  RESIZE ALL
  SIZE 36G;

Rebalancing a Disk Group: Example To manually rebalance a disk group, dgroup_01, and permit Automatic Storage Management to execute the rebalance as fast as possible, issue the following statement:

ALTER DISKGROUP dgroup_01
  REBALANCE POWER 11 WAIT;

The WAIT keyword causes the database to wait for the disk group to be rebalanced before returning control to the user.

Verifying the Internal Consistency of Disk Group Metadata: Example To verify the internal consistency of Automatic Storage Management disk group metadata and instruct Automatic Storage Management to repair any errors found, issue the following statement:

ALTER DISKGROUP dgroup_01
  CHECK ALL
  REPAIR;

Adding a Named Template to a Disk Group: Example To add a named template, template_01 to a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  ADD TEMPLATE template_01
    ATTRIBUTES (UNPROTECTED COARSE);

Changing the Attributes of a Disk Group Template: Example To modify the attributes of a system default or user-defined disk group template, template_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  ALTER TEMPLATE template_01
    ATTRIBUTES (FINE);

Dropping a User-Defined Template from a Disk Group: Example To drop a user-defined template, template_01, from a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  DROP TEMPLATE template_01;

Creating a Directory Path for Hierarchically Named Aliases: Example To specify the directory structure in which alias names will reside, issue the following statement:

ALTER DISKGROUP dgroup_01
  ADD DIRECTORY '+dgroup_01/alias_dir';

Creating an Alias Name for an Automatic Storage Management Filename: Example To create a user alias by specifying the numeric Automatic Storage Management filename, issue the following statement:

ALTER DISKGROUP dgroup_01
  ADD ALIAS '+dgroup_01/alias_dir/datafile.dbf'
    FOR '+dgroup_01.261.1';

Dismounting a Disk Group: Example To dismount a disk group, dgroup_01, issue the following statement. This statement dismounts the disk group even if one or more files are active:

ALTER DISKGROUP dgroup_01
  DISMOUNT FORCE;

Mounting a Disk Group: Example To mount a disk group, dgroup_01, issue the following statement:

ALTER DISKGROUP dgroup_01
  MOUNT;