DBA GENESIS: Everything you need to become an Oracle Database Administrator
ALL IN ONE ORACLE ASM
QUICK REFERENCE
GUIDE
Every time I work with Oracle RAC, I had to refer Google to get
exact ASM commands. I started preparing notes and quickly
realized I had gathered most of the ASM commands that I use!
I have divided all the ASM commands in 3 main sections:
● SECTION 1: ASM SQL* PLUS commands
● SECTION 2: Important views that you must know
● SECTION 3: All ASMCMD commands
DBA GENESIS: Everything you need to become an Oracle Database Administrator
This pdf is part of my efforts in training students on Oracle
11gR2 Real Application Clusters (RAC).
What you are about to see are the commands not only used by
me but are also used by my students in real-time.
I always love to see Oracle DBAs turning themselves into Oracle
RAC DBAs with me:
Let’s Start ;)
DBA GENESIS: Everything you need to become an Oracle Database Administrator
SECTION 1: ASM SQL*
PLUS COMMANDS
Start the ASM instance:
$ export ORACLE_SID=+ASM
$ sqlplus "/ as sysdba"
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
Stop the ASM instance:
$ export ORACLE_SID=+ASM
$ sqlplus "/ as sysdba"
shutdown immediate
Adding Diskgroup:
SQL> create diskgroup orag2 external redundancy disk 'ORCL:VOL5';
Diskgroup created.
SQL> select group_number,disk_number,mode_status,name from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MODE_STATUS NAME
------------ ----------- -------------- -------------------------------------
0 5 ONLINE
1 0 ONLINE VOL1
1 1 ONLINE VOL2
1 2 ONLINE VOL3
1 3 ONLINE VOL4
2 0 ONLINE VOL5
6 rows selected.
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
DBA GENESIS: Everything you need to become an Oracle Database Administrator
'/devices/diska2' NAME diska2
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
Drop a diskgroup
Disk groups can be deleted using the DROP DISKGROUP statement.
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
Add disks to existing diskgroup
Disks can be added or removed from disk groups using the ALTER DISKGROUP statement.
Remember that the wildcard "*" can be used to reference disks so long as the resulting
string does not match a disk already used by an existing disk group.
-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;
Resizing disks
Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The
statement can be used to resize individual disks, all disks in a failure group or all disks in the
disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned
by the OS.
-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;
-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;
-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;
DBA GENESIS: Everything you need to become an Oracle Database Administrator
Manually mounting asm diskgroups
Disk groups are mounted at ASM instance startup and unmounted at ASM instance
shutdown. Manual mounting and dismounting can be accomplished using the ALTER
DISKGROUP statement as seen below.
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;
Drop file inside ASM
Files are not deleted automatically if they are created using aliases, as they are not Oracle
Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created.
For these circumstances it is necessary to manually delete the files, as shown below.
-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';
-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';
-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';
Checking Metadata
The internal consistency of disk group metadata can be checked in a number of ways using
the CHECK clause of the ALTER DISKGROUP statement.
-- Check metadata for a specific file.
ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'
-- Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;
-- Check metadata for a specific disk in the disk group.
ALTER DISKGROUP disk_group_1 CHECK DISK diska1;
-- Check metadata for all disks in the disk group.
ALTER DISKGROUP disk_group_1 CHECK ALL;
Rebalancing
DBA GENESIS: Everything you need to become an Oracle Database Administrator
The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter.
Setting it to 0 will disable disk rebalancing.
To force rebalancing of a diskgroup:
ALTER DISKGROUP data REBALANCE POWER 11 WAIT;
Converting A Database to ASM
Ensure the database is using an SPFILE and not a PFILE (it's about time after all!). Set
parameters on the target database. For example, if we set both DB_CREATE_FILE_DEST
and DB_RECOVERY_FILE_DEST we should get mirrored controlfiles and duplexed log files
by default:
SQL> alter system set DB_CREATE_FILE_DEST = '+DATA';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 17G;
SQL> alter system set DB_RECOVERY_FILE_DEST = '+RECOVER';
SQL> alter system set CONTROL_FILES = '+DATA';
Start the database in NOMOUNT mode and restore the controlfile into the new location from
the old location:
RMAN> connect target /
RMAN> STARTUP NOMOUNT
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database and copy the database into the ASM disk group:
RMAN> ALTER DATABASE MOUNT;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Switch all datafiles to the new ASM location and open the database:
RMAN> SWITCH DATABASE TO COPY;
RMAN> ALTER DATABASE OPEN;
Add new tempfiles and drop the old tempfiles:
SQL> alter tablespace temp add tempfile;
SQL> alter database tempfile '...' DROP;
SQL> select * from dba_temp_files;
Optionally, move SPFILE into ASM:
SQL> CREATE SPFILE '+DATA' FROM PFILE;
DBA GENESIS: Everything you need to become an Oracle Database Administrator
Move redo log files into ASM - for each group:
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 100M;
If a logfile is active and cannot be dropped, issue an ALTER DATABASE SWITCH
LOGFILE; command and try again.
Convert a tablespace to ASM
Ensure the database in in archive log mode, and from rman:
connect target;
sql "alter tablespace TSNAME offline";
backup as copy tablespace TSNAME format '+DATA';
switch tablespace TSNAME to copy;
sql "alter tablespace TSNAME online";
exit;
Convert a datafile to ASM
Ensure the database in in archive log mode, and from rman:
connect target;
sql "alter database datafile '...' offline";
backup as copy datafile '...' format '+DATA';
switch datafile '..' to copy;
sql "alter database datafile '...' online";
exit;
Create new tablespace on ASM
CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;
DBA GENESIS: Everything you need to become an Oracle Database Administrator
SECTION 2: Important
views that you must know
View ASM Instance
V$ASM_ALIAS Displays a row for each alias present in every disk group
mounted by the ASM instance.
V$ASM_CLIENT Displays a row for each database instance using a disk group
managed by the ASM instance.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance,
including disks which are not part of any disk group.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM
instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the
ASM instance.
V$ASM_OPERATION Displays a row for each file for each long running operation
executing in the ASM instance.
V$ASM_TEMPLATE Displays a row for each template present in each disk group
mounted by the ASM instance.
ASM Filenames
There are several ways to reference ASM file. Some forms are used during creation
and some for referencing ASM files.
DBA GENESIS: Everything you need to become an Oracle Database Administrator
The forms for file creation are incomplete, relying on ASM to create the fully qualified
name, which can be retrieved from the supporting views.
The forms of the ASM filenames are summarised below.
Filename Type Format
Fully Qualified ASM Filename +dgroup/dbname/file_type/file_type_tag.file.incar
nation
Numeric ASM Filename +dgroup.file.incarnation
Alias ASM Filenames +dgroup/directory/filename
Alias ASM Filename with Template +dgroup(template)/alias
Incomplete ASM Filename +dgroup
Incomplete ASM Filename with Template +dgroup(template)
DBA GENESIS: Everything you need to become an Oracle Database Administrator
SECTION 3: All ASMCMD
commands
Start asmcmd
The ASM Command Line Utility can be started by executing the asmcmd command. Here is
a Linux/Unix example:
$ export ORACLE_SID=+ASM
$ cd $ORACLE_HOME/bin
$ asmcmd
ASMCMD> exit
The asmcmd utility also provides a useful "-p" option that will display the current path in the
prompt. Here is an example:
$ asmcmd -p
ASMCMD [+] > cd FLASH
ASMCMD [+FLASH] >
cd command
Changes to a specified directory.
ASMCMD [+dgroup2/crm] > cd +dgroup1/hrms
ASMCMD [+dgroup1/hrms] > cd DATAFILE
ASMCMD [+dgroup1/hrms/DATAFILE] > cd ..
ASMCMD [+]> cd +dgroup1/sample/C*
pwd command
Displays the absolute path of the current directory.
ASMCMD> pwd
help command
If you do not specify a value for command, then the help command lists all of the ASMCMD
commands and general information about using the ASMCMD utility.
ASMCMD> help
ASMCMD> help lsct
ASMCMD> ?
DBA GENESIS: Everything you need to become an Oracle Database Administrator
ASMCMD> ? mkgrp
du command
Displays the total space used for files in the specified directory and in the entire directory
tree under the directory.
ASMCMD [+dgroup1/prod] > du
Used_MB Mirror_used_MB
1251 2507
find command
Displays the absolute paths of all occurrences of the specified name pattern (can have
wildcards) in a specified directory and its subdirectories.
ASMCMD> find +dgroup1 undo*
+dgroup1/crm/DATAFILE/UNDOTBS1.258.555341963
+dgroup1/crm/DATAFILE/UNDOTBS1.272.557429239
ASMCMD> find -t CONTROLFILE +dg_data/hrms *
+dg_data/hrms/CONTROLFILE/Current.260.555342185
+dg_data/hrms/CONTROLFILE/Current.261.555342183
ASMCMD [+] > find --type CONTROLFILE +data/devdb *
+data/devdb/CONTROLFILE/Current.260.691577263
ls command
Lists the contents of an ASM directory
ASMCMD [+dgroup1/sample/DATAFILE] > ls
EXAMPLE.269.555342243
SYSAUX.257.555341961
SYSTEM.256.555341961
UNDOTBS1.258.555341963
UNDOTBS1.272.557429239
USERS.259.555341963
How do you know how many databases are using an ASM instance?
ASMCMD [+DG1_FRA] > lsct
DB_Name Status Software_Version Compatible_version Instance_Name
PROD CONNECTED 10.2.0.1.0 10.2.0.1.0 PROD
REP CONNECTED 10.2.0.1.0 10.2.0.1.0 REP
DBA GENESIS: Everything you need to become an Oracle Database Administrator
mkdir command
Creates ASM directories under the current directory.
ASMCMD [+dgroup1] > mkdir subdir1 subdir2
rm command
Deletes specified ASM files and directories.
ASMCMD [+dgroup1/sample/DATAFILE] > rm alias293.f
ASMCMD> rm -rf +dg/orcl/DATAFILE
ASMCMD> rm -rf fradg/*
chown command
Changes the owner of a file or list of files.
ASMCMD [+fra/orcl/archivelog/flashback] > chown asmdba1:asm_fra log_9.264.687650269
ASMCMD> chown oracle1:asm_users +data/hrms/Controlfile/Current.175.654892547
mount command
Will mount the specified diskgroups. This operation mounts one or more diskgroups.
ASMCMD [+] > mount -f data
ASMCMD [+] > mount --restrict data
ASMCMD [+] > mount -a
umount command
Will dismount the specified diskgroup.
The first example dismounts all diskgroups mounted on the Oracle ASM instance.
ASMCMD [+] > umount -a
The second example forces the dismount of the data disk group.
ASMCMD [+] > umount -f data
SQL equivalent for umount command is:
SQL> ALTER DISKGROUP diskgroup_name DISMOUNT;
DBA GENESIS: Everything you need to become an Oracle Database Administrator
Amazing, If you are wondering how to become an Oracle RAC expert and
practice above commands in your own two node RAC setup, then
checkout my full course here: Oracle 11gR2 RAC for Beginners
In the upcoming days I will be sending you Oracle RAC Tips that you
must be aware of as an Oracle RAC DBA!
Who knows, you will be part of my complete Oracle RAC course!!
Keep an eye on your inbox!
Hope this helped,
Enjoy !!
DBA GENESIS: Everything you need to become an Oracle Database Administrator
