ASM quick reference

posted Mar 10, 2011, 7:05 AM by Sachchida Ojha   [ updated Mar 10, 2011, 8:27 AM ]
set asm profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/grid
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/bin:/etc:/usr/lbin:/usr/bin/X11:/usr/local/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java14/jre/bin:/usr/java14/bin:/usr/java131/jre/bin:/usr/java131/bin:/usr/vacpp/bin:./

dbaref:+ASM:/home/oracle $sqlplus / as sysasm
SQL*Plus: Release Production on Thu Mar 10 15:01:53 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Automatic Storage Management option

SQL> show parameter ASM_DISKSTRING;

ASM will only scan for disks that match that ASM search string. There are two forms of ASM disk
discovery; shallow and deep. Shallow discovery is simply ASM scanning (essentially an ‘ls-l’ listing) of
all the devices that are eligible to opened; i.e; these disk devices have the appropriate permissions. Deep
discovery is physical open of those eligible disk devices.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring                       string      /dev/rhdisk*

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 DISK_NUMBER                                        NUMBER
 COMPOUND_INDEX                                     NUMBER
 INCARNATION                                        NUMBER
 MOUNT_STATUS                                       VARCHAR2(7)
 HEADER_STATUS                                      VARCHAR2(12)
 MODE_STATUS                                        VARCHAR2(7)
 STATE                                              VARCHAR2(8)
 REDUNDANCY                                         VARCHAR2(7)
 LIBRARY                                            VARCHAR2(64)
 OS_MB                                              NUMBER
 TOTAL_MB                                           NUMBER
 FREE_MB                                            NUMBER
 HOT_USED_MB                                        NUMBER
 COLD_USED_MB                                       NUMBER
 NAME                                               VARCHAR2(30)
 FAILGROUP                                          VARCHAR2(30)
 LABEL                                              VARCHAR2(31)
 PATH                                               VARCHAR2(256)
 UDID                                               VARCHAR2(64)
 PRODUCT                                            VARCHAR2(32)
 CREATE_DATE                                        DATE
 MOUNT_DATE                                         DATE
 REPAIR_TIMER                                       NUMBER
 READS                                              NUMBER
 WRITES                                             NUMBER
 READ_ERRS                                          NUMBER
 WRITE_ERRS                                         NUMBER
 READ_TIME                                          NUMBER
 WRITE_TIME                                         NUMBER
 BYTES_READ                                         NUMBER
 BYTES_WRITTEN                                      NUMBER
 PREFERRED_READ                                     VARCHAR2(1)
 HASH_VALUE                                         NUMBER
 HOT_READS                                          NUMBER
 HOT_WRITES                                         NUMBER
 HOT_BYTES_READ                                     NUMBER
 HOT_BYTES_WRITTEN                                  NUMBER
 COLD_READS                                         NUMBER
 COLD_WRITES                                        NUMBER
 COLD_BYTES_READ                                    NUMBER
 COLD_BYTES_WRITTEN                                 NUMBER
 VOTING_FILE                                        VARCHAR2(1)
 SECTOR_SIZE                                        NUMBER
 FAILGROUP_TYPE                                     VARCHAR2(7)

SQL> col NAME format a32
SQL> col PATH format a32
SQL> set linesize 200


SQL> /

NAME                             MOUNT_S PATH                             LABEL                           FAILGROUP                      REDUNDA
-------------------------------- ------- -------------------------------- ------------------------------- ------------------------------ -------
_DROPPED_0001_QA_FLASH           MISSING                                                                    QA_FLASH_0001                  UNKNOWN
DEV_DATA_0000                    CACHED  /dev/rhdisk10                                                    DEV_DATA_0000                  UNKNOWN
DEV_DATA_0001                    CACHED  /dev/rhdisk11                                                     DEV_DATA_0001                  UNKNOWN
DEV_DATA_0002                    CACHED  /dev/rhdisk12                                                    DEV_DATA_0002                  UNKNOWN
DEV_DATA_0003                    CACHED  /dev/rhdisk13                                                    DEV_DATA_0003                  UNKNOWN
QA_FLASH_0000                    CACHED  /dev/rhdisk14                                                    QA_FLASH_0000                  UNKNOWN
DEV_DATA_0012                    CACHED  /dev/rhdisk15                                                     DEV_DATA_0012                  UNKNOWN
DEV_DATA_0009                    CACHED  /dev/rhdisk16                                                    DEV_DATA_0009                  UNKNOWN
DEV_DATA_0010                    CACHED  /dev/rhdisk17                                                    DEV_DATA_0010                  UNKNOWN
DEV_DATA_0011                    CACHED  /dev/rhdisk18                                                     DEV_DATA_0011                  UNKNOWN
DEV_DATA_19                         CACHED  /dev/rhdisk19                                                        DEV_DATA_19                        UNKNOWN

NAME                             MOUNT_S PATH                             LABEL                           FAILGROUP                      REDUNDA
-------------------------------- ------- -------------------------------- ------------------------------- ------------------------------ -------
DEV_DATA_0014                    CACHED  /dev/rhdisk20                                                    DEV_DATA_0014                  UNKNOWN
DEV_DATA_0015                    CACHED  /dev/rhdisk21                                                    DEV_DATA_0015                  UNKNOWN
DEV_DATA_0016                    CACHED  /dev/rhdisk22                                                    DEV_DATA_0016                  UNKNOWN
DEV_DATA_0017                    CACHED  /dev/rhdisk23                                                    DEV_DATA_0017                  UNKNOWN
DEV_DATA_24                          CACHED  /dev/rhdisk24                                                    DEV_DATA_24                       UNKNOWN
DEV_DATA_0004                    CACHED  /dev/rhdisk5                                                     DEV_DATA_0004                  UNKNOWN
DEV_DATA_0005                    CACHED  /dev/rhdisk6                                                     DEV_DATA_0005                  UNKNOWN
DEV_DATA_0006                    CACHED  /dev/rhdisk7                                                     DEV_DATA_0006                  UNKNOWN
DEV_DATA_0007                    CACHED  /dev/rhdisk8                                                     DEV_DATA_0007                  UNKNOWN
DEV_DATA_0008                    CACHED  /dev/rhdisk9                                                     DEV_DATA_0008                  UNKNOWN

21 rows selected.

SQL> select name, path, group_number from v$asm_disk;

NAME                             PATH                             GROUP_NUMBER
-------------------------------- -------------------------------- ------------
_DROPPED_0001_QA_FLASH                                                       2
DEV_DATA_0000                    /dev/rhdisk10                               1
DEV_DATA_0001                    /dev/rhdisk11                               1
DEV_DATA_0002                    /dev/rhdisk12                               1
DEV_DATA_0003                    /dev/rhdisk13                               1
QA_FLASH_0000                    /dev/rhdisk14                               2
DEV_DATA_0012                    /dev/rhdisk15                               1
DEV_DATA_0009                    /dev/rhdisk16                               1
DEV_DATA_0010                    /dev/rhdisk17                               1
DEV_DATA_0011                    /dev/rhdisk18                               1
DEV_DATA_19                      /dev/rhdisk19                               1

NAME                             PATH                             GROUP_NUMBER
-------------------------------- -------------------------------- ------------
DEV_DATA_0014                    /dev/rhdisk20                               1
DEV_DATA_0015                    /dev/rhdisk21                               1
DEV_DATA_0016                    /dev/rhdisk22                               1
DEV_DATA_0017                    /dev/rhdisk23                               1
DEV_DATA_24                      /dev/rhdisk24                               1
DEV_DATA_0004                    /dev/rhdisk5                                1
DEV_DATA_0005                    /dev/rhdisk6                                1
DEV_DATA_0006                    /dev/rhdisk7                                1
DEV_DATA_0007                    /dev/rhdisk8                                1
DEV_DATA_0008                    /dev/rhdisk9                                1

21 rows selected.

Disks have various header statuses that reflect its membership state with a diskgroup. Disks can have the following header statuses:

o Former - This state declares that the disk was formerly part of a diskgroup
o Candidate - When a disk is in this state, it indicates that it is available to be added to a diskgroup.
o Member - This state indicates that a disk is already part of a diskgroup.
o Provisioned - This state is similar to candidate, in that its available to diskgroups.
However, the provisioned state indicates that this disk has been configured or made available using ASMLIB.

ASM and Multipathing

An I/O path generally consists of an initiator port, fabric port, target port, and LUN. Each permutation of this I/O path is considered an independent path. Dynamic Multi-pathing/failover tools aggregate these independent paths into a single logical path. This path abstraction provides I/O load balancing across the host bus adapters (HBA), as well as non-disruptive failovers on I/O path failures. Multi-pathing (MP) software requires all the required disks to be visible on each available and eligible HBA. A MP driver will detect multi-paths by performing a SCSI inquiry command7. Multi-pathing software also provides multi-path software drivers. Most multi-pathing drivers support multipath services for fibre channel attached SCSI-3 devices. These drivers receive naming and transport services from one or more physical HBA devices. To support multi-pathing, a physical HBA driver must comply with the multi-pathing services provided by this driver. Multipathing tools provides the following benefits:

o Provide a single block device interface for a multi-pathed LUN
o Detect any component failures in the I/O path; e.g., fabric port, channel adapter, or HBA.
o When a loss of path occurs, ensure that I/Os are re-routed to the available paths, with no process disruption.
o Reconfigure the multipaths automatically when events occur.
o Ensure that failed paths get revalidated as soon as possible and provide auto-failback capabilities.
o Configure the multi-paths to maximize performance using various load balancing methods; e.g., round robin, least I/Os queued, or least service time.

When a given disk has several paths defined, each one will be presented as a unique path name at the OS level; e.g.; /dev/rdsk/c3t19d1s4 and /dev/rdsk/c7t22d1s4 could be pointing to same disk device. ASM,however, can only tolerate the discovery of one unique device path per disk. For example, if the asm_diskstring is ‘/dev/rdsk/*’, then several paths to the same device will be discovered, and ASM will produce an error message stating this. When using a multipath driver, which sits above this SCSI-block layer, the driver will generally produce a pseudo device that virtualizes the sub-paths.

For example, in the case of EMC’s PowerPath, you can use the following asm_diskstring setting of ‘/dev/rdsk /emcpower*’. When I/O is issued to this disk device, the multipath driver will intercept it and provide the necessary load balancing to the underlying subpaths. Examples of multi-pathing software include EMC PowerPath, Veritas DMP, Sun Traffic Manager, Hitachi HDLM, and IBM SDDPCM. Linux 2.6 has a kernel based multipathing driver called Device Mapper. Additionally, some HBA vendors also provide multipathing solutions. Oracle Corporation does not certify or qualify these multipathing tools. Although ASM does not provide multi-pathing capabilities, ASM does leverage multi-pathing tools, as long the path or device produced by the Multi-pathing tool returns a successful return code from an fstat system call. Metalink Note 294869.1 provides more details on ASM and Multipathing.


SQL> desc v$asm_diskgroup;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 GROUP_NUMBER                                                                                                               NUMBER
 NAME                                                                                                                       VARCHAR2(30)
 SECTOR_SIZE                                                                                                                NUMBER
 BLOCK_SIZE                                                                                                                 NUMBER
 ALLOCATION_UNIT_SIZE                                                                                                       NUMBER
 STATE                                                                                                                      VARCHAR2(11)
 TYPE                                                                                                                       VARCHAR2(6)
 TOTAL_MB                                                                                                                   NUMBER
 FREE_MB                                                                                                                    NUMBER
 HOT_USED_MB                                                                                                                NUMBER
 COLD_USED_MB                                                                                                               NUMBER
 REQUIRED_MIRROR_FREE_MB                                                                                                    NUMBER
 USABLE_FILE_MB                                                                                                             NUMBER
 OFFLINE_DISKS                                                                                                              NUMBER
 COMPATIBILITY                                                                                                              VARCHAR2(60)
 DATABASE_COMPATIBILITY                                                                                                     VARCHAR2(60)
 VOTING_FILES                                                                                                               VARCHAR2(1)


SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME                             STATE       TYPE     TOTAL_MB    FREE_MB
-------------------------------- ----------- ------ ---------- ----------
DEV_DATA                         MOUNTED     EXTERN    2128019      88785
QA_FLASH                         MOUNTED     NORMAL      51200      35813

SQL> select name, path, mode_status, state, disk_number from v$asm_disk;

NAME                             PATH                             MODE_ST STATE    DISK_NUMBER
-------------------------------- -------------------------------- ------- -------- -----------
_DROPPED_0001_QA_FLASH                                            OFFLINE FORCING            1
DEV_DATA_0000                    /dev/rhdisk10                    ONLINE  NORMAL             0
DEV_DATA_0001                    /dev/rhdisk11                    ONLINE  NORMAL             1
DEV_DATA_0002                    /dev/rhdisk12                    ONLINE  NORMAL             2
DEV_DATA_0003                    /dev/rhdisk13                    ONLINE  NORMAL             3
QA_FLASH_0000                    /dev/rhdisk14                    ONLINE  NORMAL             0
DEV_DATA_0012                    /dev/rhdisk15                    ONLINE  NORMAL            12
DEV_DATA_0009                    /dev/rhdisk16                    ONLINE  NORMAL             9
DEV_DATA_0010                    /dev/rhdisk17                    ONLINE  NORMAL            10
DEV_DATA_0011                    /dev/rhdisk18                    ONLINE  NORMAL            11
DEV_DATA_19                      /dev/rhdisk19                    ONLINE  NORMAL            13

NAME                             PATH                             MODE_ST STATE    DISK_NUMBER
-------------------------------- -------------------------------- ------- -------- -----------
DEV_DATA_0014                    /dev/rhdisk20                    ONLINE  NORMAL            14
DEV_DATA_0015                    /dev/rhdisk21                    ONLINE  NORMAL            15
DEV_DATA_0016                    /dev/rhdisk22                    ONLINE  NORMAL            16
DEV_DATA_0017                    /dev/rhdisk23                    ONLINE  NORMAL            17
DEV_DATA_24                      /dev/rhdisk24                    ONLINE  NORMAL            18
DEV_DATA_0004                    /dev/rhdisk5                     ONLINE  NORMAL             4
DEV_DATA_0005                    /dev/rhdisk6                     ONLINE  NORMAL             5
DEV_DATA_0006                    /dev/rhdisk7                     ONLINE  NORMAL             6
DEV_DATA_0007                    /dev/rhdisk8                     ONLINE  NORMAL             7
DEV_DATA_0008                    /dev/rhdisk9                     ONLINE  NORMAL             8

21 rows selected.

SQL> create diskgroup DATA external redundancy disk

SQL> select name, path, mode_status, state, disk_number from v$asm_disk
------------ ---------------------- ------- -------- -----------
DATA_0000 /dev/rdsk/c3t19d5s4 ONLINE NORMAL 0
DATA_0001 /dev/rdsk/c3t19d16s4 ONLINE NORMAL 1
DATA_0002 /dev/rdsk/c3t19d17s4 ONLINE NORMAL 2
DATA_0003 /dev/rdsk/c3t19d18s4 ONLINE NORMAL 3

In the example above, a DATA diskgroup is created using four disks, which reside in a storage array, with the redundancy being handled externally via the storage array.

Once the disks are discovered, a diskgroup can be created that will encapsulate one or more of these disks. A diskgroup, which is the highest-level data structure in ASM, is comparable to a LVM’s volume group. However, there are several differentiators between typical LVM volume groups and ASM diskgroups:

o An ASM filesystem layer is implicitly created within a diskgroup. This filesystem is transparent to users and only accessible through ASM, interfacing databases, and 10.2 ASM command line tool.
o There are inherent automatic file-level striping and mirroring capabilities. A database file created

within an ASM diskgroup will have its file extents (not to be confused with its database extents) distributed equally across all online disks in the diskgroup, which provides an even IO load. The creation of a diskgroup involves the validation of the disks to be added. These disks must have the following attributes:

o Cannot already be in use by another diskgroup
o Must not have a pre-existing ASM header
o Cannot have an Oracle file header (from a Oracle raw device datafile).
This check and validation prevents ASM from destroying any in use data device. Disks with a valid header status, which include candidate, former, or provisioned, are the only ones allowed to be diskgroup members

Diskgroups and databases
A diskgroup can contain files from many different 10g Oracle databases, and these databases can be either from the same server or can reside on different servers. However, a disk and a database file can only be part of one diskgroup. Alternatively, one Oracle database may also store its files in multiple disk groups managed by the same ASM instance. Allowing multiple databases to share a diskgroup provides greater potential for improved disk utilization and greater overall throughput.

To reduce the complexity of managing ASM and its diskgroups, Oracle recommends that generally no more than two diskgroups be maintained and managed per RAC cluster or single ASM instance10.

o Database area: This is where active database files such as datafiles, control files, online redo logs, and change tracking files used in incremental backups are stored

o Flash recovery area: Where recovery-related files are created, such as multiplexed copies of the current control file and online redo logs, archived redo logs, backup sets, and flashback log files To provide higher availability for the database, when a Flash Recovery Area is chosen at database creation time, an active copy of the controlfile and one member set of the redo log group is stored in the Flash Recovery Area. Note, additional copies of the controlfile or extra logfiles can be created and placed in either diskgroup as desired.

SQL> desc  V$asm_client;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 GROUP_NUMBER                                                                                                               NUMBER
 INSTANCE_NAME                                                                                                              VARCHAR2(64)
 DB_NAME                                                                                                                    VARCHAR2(8)
 STATUS                                                                                                                     VARCHAR2(12)
 SOFTWARE_VERSION                                                                                                           VARCHAR2(60)
 COMPATIBLE_VERSION                                                                                                         VARCHAR2(60)


------------ -------------------------------- -------- ------------ -------------------------------- --------------------------------
           1     TXQADB2                                                              TXQADB2  CONNECTED             
           1     TXQADB3                                                              TXQADB3  CONNECTED             
           2     TXQADB3                                                              TXQADB3  CONNECTED             
           1     TXQADB4                                                              TXQADB4  CONNECTED             
           2     TXQADB4                                                              TXQADB4  CONNECTED             
           1     TXQADB5                                                              TXQADB5  CONNECTED             
           2     TXQADB5                                                              TXQADB5  CONNECTED             
           1     STGRPTDB                                                             STGRPTDB CONNECTED             

8 rows selected.

Database Init.ora parameters to support ASM

The SGA parameters for database instance needs slight modification to support ASM extent maps and other ASM information. Note if the 10g Automatic Memory Management feature is being used on the database instance, then the following sizing data can be treated as informational only or as supplemental data in gauging appropriate values for the SGA. Oracle highly recommends using the Automatic Memory Management feature. The following are guidelines for SGA sizing on the database instance:

Processes = Add 16
Large_pool = Add additional 600k
Shared_pool – Additional memory is required to store extent maps. Aggregate the values from the following queries to obtain current database storage size that is either already on ASM or will be stored in ASM. Then determine the redundancy type that is used (or will be used), and calculate the shared_pool, using the aggregated value as input.

select sum(bytes)/(1024*1024*1024) from v$datafile;

select sum(bytes)/(1024*1024*1024) from v$logfile a, v$log b where;

select sum(bytes)/(1024*1024*1024) from v$tempfile where status='ONLINE';

For diskgroups using external redundancy = (Every 100Gb of space needs 1Mb of extra shared pool) + 2M
For diskgroups using Normal redundancy: (Every 50Gb of space needs 1Mb of extra shared pool) + 4M.
For diskgroups using High redundancy: (Every 33Gb of space needs 1Mb of extra shared pool) + 6M.

Rebalance and Redistribution
With traditional volume managers, expansion/growth or shrinkage of striped filesystems has typically been difficult. With ASM, these disk/volume changes are now seamless redistribution (rebalancing) of the striped data and can be performed online.

Any change in the storage configuration will trigger a rebalance. The main objective of the rebalance operation is to always provide an even distribution of file extents and space usage across all disks in the diskgroup. Rebalancing is performed on all database files on a per file basis20; however, some files may not require a rebalance. The Oracle background process, RBAL, from the ASM instance manages this rebalance.

The Rebalance process examines each file extent map, and the new extents are re-plotted on to the new storage configuration. For example, consider an 8-disk diskgroup, with a datafile with 40 extents (each disk will house 5 extents). When 2 new drives of same size are added, that datafile is rebalanced and spread across 10 drives, with each drive containing 4 extents. Only 8 extents need to move to complete the rebalance; i.e., a complete redistribution of extents is not necessary, only the minimum number of extents are moved to reach equal distribution.

The following is a typical process flow for ASM rebalancing:

1. On the ASM instance, a DBA adds (or drops) a disk to (from) a diskgroup.

2. This invokes the RBAL process to create the rebalance plan and then begin coordination of the redistribution

3. RBAL will calculate estimation time and work required to perform the task and then message the ARBx processes to actually handle the request. The number of ARBx processes invoked is directly determined by the asm_power_limit.

4. The Continuing Operations Directory (metadata) will be updated to reflect a rebalance activity.

5. Each extent to be relocated is assigned to an ARBx process.

6. ARBx performs rebalance on these extents. Each extent is locked, relocated, and unlocked. This is shown as Operation REBAL in V$ASM_OPERATION.

Rebalancing involves physical movement of file extents. This impact is generally low because the rebalance is done one AU at a time; therefore, there will only be one outstanding I/O at any given time, per ARBx processes. This should not adversely affect online database activity. However, it is generally advisable to schedule the rebalance operation during off-peak hours. The init.ora parameter asm_power_limit is used to influence the throughput and speed of the rebalance
operation21. The range of values for asm_power_limit are 0 to 11; where a value of 11 is full throttle and a value of 1 is low speed. A value of 0, which turns off automatic rebalance, should be used with caution. The power value can also be set for a specific rebalance activity using the alter diskgroup command. This value is only effective for the specific rebalance task. See Figure 8. A power value of 0 indicates that no rebalance should occur for this rebalance. This setting is particularly important when adding or removing storage (that has external redundancy), and then deferring the rebalance to a later scheduled time.

“Session1 SQL”> alter diskgroup DATA add disk '/dev/rdsk/c3t19d39s4' rebalance power 11

20 A weighting factor, influenced by disk size and file size, affects rebalancing. A larger drive will consume more extents. This is done for even distribution based on overall size.
21 The asm_power_limit is specific to each ASM instance.

From another session
“Session2 SQL”> select * from v$asm_operation
----- ---- ---------- ---------- ---------- ---------- ---------- -----------
1 REBAL WAIT 11 0 0 0 0 0
1 DSCV WAIT 11 0 0 0 0 0
(time passes…………..)
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
1 REBAL REAP 11 2 25 219 485 0
If removing or adding several disks, for best practices, it is best to add or remove drives all at once, this will reduce the number rebalance operations that are needed for storage changes. An ASM diskgroup rebalance is an asynchronous operation, in that the control is returned immediately to DBA after the operation is sent in the background, with the status of the ongoing operation query-able from V$ASM_OPERATION. However, there are situations when the diskgroup operation needs to synchronous; i.e., wait until rebalance is completed. In Oracle Database 10g Release 2, ASM alter diskgroup commands which result in a rebalance now have the option of specifying the option to wait. This allows for accurate [sequential] scripting that may rely on the space change from a rebalance completing before any subsequent action is taken. For instance, if you add 100GB of storage to a completely full disk group, you won't be
able to use all 100GB of storage until the rebalance completes.

If a new rebalance command is entered while one is already in progress in wait mode, the prior command will not return until the diskgroup is in a balanced state or the rebalance operation encounters an error.

An example SQL script below illustrates how the WAIT option can be used in SQL scripting. The script below adds a new disk, /dev/raw/raw6, and waits until the add and rebalance operations complete, returning the control back to the script. The subsequent step adds a large tablespace.

#An example script to test WAIT option

alter diskgroup data add disk '/dev/raw/raw6' rebalance power 2 wait;

#login into database and create a tablespace for the next month’s Order Entry data

sqlplus oe_dba/oe1@proddb << EOF
create BIGFILE tablespace May_OE datafile size 800 Gb
<< EOF

With external redundancy, the dropping and adding of disks in the diskgroup is very seamless, and becomes more of an exercise of scheduling the rebalancing. However, with failure groups, some planning and forethought maybe required with respect to how disks are removed and added.

SQL> desc v$asm_file;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 GROUP_NUMBER                                                                                                               NUMBER
 FILE_NUMBER                                                                                                                NUMBER
 COMPOUND_INDEX                                                                                                             NUMBER
 INCARNATION                                                                                                                NUMBER
 BLOCK_SIZE                                                                                                                 NUMBER
 BLOCKS                                                                                                                     NUMBER
 BYTES                                                                                                                      NUMBER
 SPACE                                                                                                                      NUMBER
 TYPE                                                                                                                       VARCHAR2(64)
 REDUNDANCY                                                                                                                 VARCHAR2(6)
 STRIPED                                                                                                                    VARCHAR2(6)
 CREATION_DATE                                                                                                              DATE
 MODIFICATION_DATE                                                                                                          DATE
 REDUNDANCY_LOWERED                                                                                                         VARCHAR2(1)
 PERMISSIONS                                                                                                                VARCHAR2(16)
 USER_NUMBER                                                                                                                NUMBER
 USER_INCARNATION                                                                                                           NUMBER
 USERGROUP_NUMBER                                                                                                           NUMBER
 USERGROUP_INCARNATION                                                                                                      NUMBER
 PRIMARY_REGION                                                                                                             VARCHAR2(4)
 MIRROR_REGION                                                                                                              VARCHAR2(4)
 HOT_READS                                                                                                                  NUMBER
 HOT_WRITES                                                                                                                 NUMBER
 HOT_BYTES_READ                                                                                                             NUMBER
 HOT_BYTES_WRITTEN                                                                                                          NUMBER
 COLD_READS                                                                                                                 NUMBER
 COLD_WRITES                                                                                                                NUMBER
 COLD_BYTES_READ                                                                                                            NUMBER
 COLD_BYTES_WRITTEN                                                                                                         NUMBER

+ASM (ASM instance)

SQL> select file_number , sum(bytes)/(1024*1024) from v$asm_file group by file_number

----------- ----------------------
256 360.007813
257 35.0078125
258 450.007813
261 .002441406
262 150.007813
263 23.0078125
264 10.0004883
265 5.0078125
266 10.0004883
267 10.0004883
268 2.2109375

ORCL (database instance)

SQL> select name from v$datafile

SQL> select member from v$logfile;


The file number in the ASM instance can be used to correlate and determine filenames in database instance. The illustration below shows the relationship between a database instance’s file identity and ASM’s. Note,
the file number from V$ASM_FILE is embedded in the file name.

The filename notation described thus far is called the Fully Qualified FileName (FQFN) notation. FQFN are generally long and awkward, therefore, to make file-naming convention easier to remember the ASM Alias name format was introduced. ASM Aliases are essentially in hierarchical directory format, similar to the filesystem hierarchy:

Alias names specify a disk group name, but instead of a file and incarnation number, a user-friendly string name is used. Alias ASM filenames are distinguished from fully qualified or numeric names because they do not end in a dotted pair of numbers. The following is an example of an ASM Alias:23:

#Create ASM directory
SQL> alter diskgroup DATA add directory '+DATA/oradata/orcl;

#Now create the alias

SQL> alter diskgroup DATA add alias '+DATA/oradata/orcl/nitin01.dbf' for +DATA/orcl/datafile/nitin.263.3;

For best practices, every database should implement the Oracle Managed File (OMF) feature. OMF can be enabled by setting the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters. OMF will allow simpler file naming and also provides better file handling. For example, if a tablespace is dropped, ASM will delete the file automatically only if it is an OMF datafile. If OMF is not used, then the DBA must drop the file manually by connecting to the ASM instance. OMF files are located in the DB_CREATE_FILE_DEST directory24. Please review OMF documentation on usage and implementation.

Note, OMF is not used anytime file names are provided in create/alter tablespace add datafile commands. Aliases are particularly useful when dealing with controlfiles and spfiles; e.g., an Alias ASM filename is normally used in the CONTROL_FILES and SPFILE initialization parameter. In Figure 10 below, SPFILE and Control_files parameter are set to the alias, and the db_create_file_dest and db_recovery_file_dest are set to the appropriate OMF destinations.

spfile = +DATA/orcl/spfileorcl.ora
control_files = +DATA/orcl/controlfile/control_01.ctl
db_create_file_dest = +DATA
db_recovery_file_dest = +FLASH

The following table illustrates the various used and their relationship with the instance
ASM related V$ Views ASM Instance Database Instance

V$ASM_DISKGROUP row/diskgroup discovered Row/diskgroup available to the local database instance.
V$ASM_CLIENT row/client connected row for local ASM instance if DB contains open ASM files
V$ASM_DISK Row/disk discovered, across all diskgroups as well as disks that are not in disk groups Row/disk, across all diskgroups used by the local database instance.
V$ASM_FILE Row/file allocated; across all client instances-diskgroups N/A
V$ASM_TEMPLATE Row/template Row/template w/associated-attached diskgroup
V$ASM_ALIAS Row/file alias N/A
V$ASM_OPERATION Row/per active ASM operation (could be more than 1 operation per invocation)

Sachchida Ojha,
Mar 10, 2011, 8:31 AM