- 1 Why should we use separate ASM home?
- 2 How many ASM instances should one have?
- 3 How many diskgroups should one have?
- 4 Should I create table and index tablespaces in separate diskgroups?
- 5 How to check how much disk space is allocated/ in-use by an ASM instance?
- 6 How big should I make my datafiles within ASM?
- 7 ASMCMD is very slow. How can I speed it up?
- 8 ASM disk header/superblock backups?
- 9 How does one create a database directly on ASM?
Why should we use separate ASM home?
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?
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.
How many diskgroups should one have?
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?
How to check how much disk space is allocated/ in-use by an ASM instance?
Login to your +ASM instance (SYS AS SYSDBA) and execute the following query:
SQL> COL % FORMAT 99.0 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:
ASMCMD> du 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?
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?
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?
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?
The trick is to create an SPFILE and restart the instance before issuing the CREATE DATABASE statement:
STARTUP NOMOUNT PFILE=initorcl_0.ora CREATE SPFILE FROM pfile='initorcl_0.ora'; SHUTDOWN IMMEDIATE STARTUP NOMOUNT
Point all OMF files into ASM:
ALTER SYSTEM SET db_create_file_dest = '+DATA'; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 134G; ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER';
Issue the create database command:
CREATE DATABASE orcl UNDO TABLESPACE undots DEFAULT TEMPORARY TABLESPACE temp character set "WE8ISO8859P1" national character set "AL16UTF16";