From Oracle FAQ
Jump to: navigation, search

Oracle Automatic Storage Management (ASM) FAQ

Why should we use separate ASM home?[edit]

ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How many ASM instances should one have?[edit]

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).

How many diskgroups should one have?[edit]

Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).

Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).

To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:

CREATE DISKGROUP data    EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;

Here is an example how you can enable automatic file management with such a setup:

ALTER SYSTEM SET db_create_file_dest   = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;

You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.

In 11gR2, an ASM Instance can have up to 63 disk groups, 10,000 disks and 1 million files for each disk group. In 12c, an ASM instance can have up to 511 disk groups, the other limits are not changed.

Should I create table and index tablespaces in separate diskgroups?[edit]

No, tables and indexes can be stored within a single disk group. Do not create different disk groups for tables and indexes.

How to check how much disk space is allocated/ in-use by an ASM instance?[edit]

Login to your +ASM instance (SYS AS SYSDBA) and execute the following query:

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;
NAME                              FREE_MB   TOTAL_MB     %
------------------------------ ---------- ---------- -----
DATA                               917104    1482145  61.9
RECOVER                             17387      17437  99.7

From Oracle 10g Release 2, one can also use the asmcmd command line utility:

Used_MB      Mirror_used_MB
   1523                1523
ASMCMD> lsdg
State    Type    Rebal  Unbal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N      N         512   4096  1048576     11264     9885                0            9885              0  DISKGROUP1/
MOUNTED  EXTERN  N      N         512   4096  1048576     10240     9906                0            9906              0  FLASH/

How big should I make my datafiles within ASM?[edit]

Use uniform sized files (say 2 or 4 GB each). Reason being that the a 2TB datafile will unnecessarily extend backup and recovery times.

ASMCMD is very slow. How can I speed it up?[edit]

The asmcmd utility appears to be very slow. This slowness is a result of queries against the v$asm_diskgroup view. To solve this problem edit the $ORACLE_HOME/bin/asmcmdcore script and change all v$asm_diskgroup references to v$asm_diskgroup_stat.

V$asm_diskgroup and v$asm_diskgroup_stat provides exactly the same information, but the %_stat view operates from cache, while v$asm_diskgroup rescans all disk headers. This method is also used by Oracle in their Enterprise Manager product.

ASM disk header/superblock backups?[edit]

ASM disk headers (superblocks) cannot be backed up and restored in Oracle 10g. By implication, if you use EXTERNAL REDUNDANCY and a single disk's header is accidentally overwritten, the entire disk group will have to be restored. To solve this problem, Oracle introduced the md_backup and md_restore asmcmd commands in Oracle 11g. In Oracle 10g, the only viable method to prevent logical corruption of ASM header block is to add failgroup, storage vendor has no responsibility to verify/checksum ASM disk header blocks (EXTERNAL REDUNDANCY is not going to help). There is a kfed utility to backup ASM disk headers and restore them for LIMITED scenario. It is best to be executed under guidance of a few elite support engineers. Oracle did not advertise the utility due to the potential damage it could cause. For those unrecoverable (tedious manual fixes) cases, restoring disk group is the last resort.

How does one create a database directly on ASM?[edit]

The trick is to create an SPFILE and restart the instance before issuing the CREATE DATABASE statement:

CREATE SPFILE FROM pfile='initorcl_0.ora';

Point all OMF files into ASM:

ALTER SYSTEM SET db_create_file_dest = '+DATA';
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER';

Issue the create database command:

   character set "WE8ISO8859P1"
   national character set "AL16UTF16";