Oracle ASM Administration

Managing data under ASM disks and diskgroups is very important task for a DBA. In this article, we will be looking at general ASM administration tasks you must know.

• Create Directory Under ASM Diskgroup

• Find ASM physical disk mapping

• Create Diskgroup ASM

• Create Tablespace in Oracle ASM

Create Directory Under ASM Diskgroup

In this activity, you will understand how to create directories inside disk groups to place data. Unlike OS level commands, Adding directories to ASM disk groups is little different.

ASMCMD Utility

Type asmcmd at command prompt.

asmcmd

Under asmcmd, you can use many OS level commands like

• ls – to list contents,

• pwd – check present working directory,

• mkdir – create new directory,.

• rm – remove directory or file,

• cp – copy file or directory

ASMCMD> ls
CRSVOL1/
DATAVOL1/
FRAVOL1/
RMANVOL/

Let us create directory under RMANVOL diskgroup

ASMCMD> cd RMANVOL/
ASMCMD> mkdir RMAN_BACKUP
ASMCMD> mkdir RMAN_SCRIPT
ASMCMD> mkdir RMAN_LOG

You can create multiple directories in one single command too

ASMCMD> mkdir RMAN_BACKUP RMAN_SCRIPT RMAN_LOG

Using SQL PLUS / as sysasm

Connect to Oracle (or grid) user and export Oracle home and SID variables to point Grid home and ASM instance

sqlplus / as sysasm

Issue below SQL queries to create directories:

SQL> alter diskgroup RMANVOL add directory ‘+RMANVOL/RMAN_BACKUP’;
SQL> alter diskgroup RMANVOL add directory ‘+RMANVOL/RMAN_SCRIPT’;
SQL> alter diskgroup RMANVOL add directory ‘+RMANVOL/RMAN_LOG’;

Find ASM physical disk mapping

When you work in a RAC environment with a list of ASM disks, sometimes you might want to figure out which ASM disk is mapped to which physical device. Below is a simple script that will help you find out ASM disks that are mapped to Linux partitions

#!/bin/bash
ORACLEASM=/etc/init.d/oracleasm
echo "ASM Disk Mappings"
echo "----------------------------------------------------"for f in `$ORACLEASM listdisks`do
dp=`$ORACLEASM querydisk -p  $f | head -2 | grep /dev | awk -F: '{print $1}'`
echo "$f: $dp"
done

And below is the sample output

ASM Disk Mappings
----------------------------------------------------
CRSDISK1: /dev/sdb3
DATADISK1: /dev/sdb1
FRADISK1: /dev/sdb2

Create Diskgroup ASM

In this article we will be creating new ASM diskgroup using SQLPLUS command while connected to ASM instance. You must have an unused partition / disk on the server that can be used to create ASM diskgroup.

In this activity, we are going to use /dev/sdb4 and /dev/sdb5 to create a new normal redundancy disk group which needs at least two failure groups. Mark the disk headers using the oracleasm utility as root user. You need to create ASM disks only from one node

oracleasm createdisk RMANVOL1 /dev/sdb4
oracleasm createdisk RMANVOL2 /dev/sdb5

Scan and list the ASM disks from node 1. If successful, you must be able to see the ASM disks on other nodes too

oracleasm scandisks
oracleasm listdisks

Now that we have marked our ASM disks, we need to create a normal redundancy diskgroup using these two disks. As grid user on node 1

sqlplus / as sysasm

SQL> CREATE DISKGROUP RMANVOL NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK '/dev/sdb4',
FAILGROUP failgrp2 DISK '/dev/sdb5';

Check the disk & its status

SELECT PATH, HEADER_STATUS, NAME FROM V$ASM_DISK;

Check the disk group & its status

SELECT NAME, STATE FROM V$ASM_DISKGROUP;

Mount the disk groups from other nodes

SELECT INST_ID, NAME, STATE FROM GV$ASM_DISKGROUP;
ALTER DISKGROUP RMANVOL MOUNT;
SELECT INST_ID, NAME, STATE FROM GV$ASM_DISKGROUP;

Create Tablespace in Oracle ASM

Creating a new tablespace inside ASM is not different than creating tablespace on filesystem. ASM uses OMF feature of Oracle database to auto create datafiles in respective ASM diskgroups.

First we will check if OMF is enabled. DB_CREATE_FILE_DEST defines the default location where Oracle creates datafiles

SQL> Show parameter db_create_file_dest

DB_RECOVERY_FILE_DEST defines the default location for FRA

SQL> show parameter db_reocovery_file_dest
SQL> show parameter db_recovery_file_dest_size

Note: make sure to add space if db_recovery_file_dest_size is small

Below command creates test_tbs tablespace with 100 MB initial size and autoextend unlimited. You can always change these default parameters

SQL> CREATE TABLESPACE test_tbs;

You can manually give datafile name too

SQL> CREATE TABLESPACE test_tbs datafile '+ASM/DATAFILES/ORCL/test_tbs.dbf';