Arun Kumar
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
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';