ASM file number 6

The alias directory - ASM file number 6 - provides a hierarchical  naming system for all the files in a disk group.

A system file name is created for every file and it is based on the file type, database instance and type-specific information such as tablespace name. User alias may also be created if a full path name was given when the file was created.

Alias Directory entries include the following fields:

• Alias or directory name
• Alias incarnation number
• File number
• File incarnation number
• Parent directory
• System flag

The ASM alias information is externalised via V$ASM_ALIAS view.

Using the alias

The following SQL statement is taken from Oracle Press book Automatic Storage Management, Under-the-Hood & Practical Deployment Guide, by Nitin Vengurlekar, Murali Vallath and Rich Long. It demonstrates how to use V$ASM_ALIAS view to generates a list of all database files managed by ASM.

The output is organised in what appears as the list of directories, followed by the list of files with their full path names. I say what appears as the list of directories as ASM does not really keep the files in a hierarchical directory style structure. The output is just formatted so that the list of files is presented in a familiar, operating system like style.

The SQL assumes that the files were created using the ASM file name conventions. In particular, it assumes that the given database name is present in the alias name (the FULL_PATH column). The FULL_PATH variable in the query refers to the alias name. The DIR column indicates if this is a 'directory' and SYS column indicates whether the alias was created by the system.

col full_path format a64
col dir format a3
col sys format a3
set pagesize 1000
set linesize 200

SQL> SELECT full_path, dir, sys
FROM (
SELECT CONCAT ('+'|| gname, sys_connect_by_path (aname,'/')) full_path, dir, sys FROM ( SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex, a.alias_directory dir, a.system_created sys FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex ORDER BY dir desc, full_path asc)
WHERE full_path LIKE upper('%/br%');

FULL_PATH                                                        DIR SYS
---------------------------------------------------------------- --- ---
+DATA/BR                                                         Y   Y
+DATA/BR/CONTROLFILE                                             Y   Y
+DATA/BR/DATAFILE                                                Y   Y
+DATA/BR/ONLINELOG                                               Y   Y
+DATA/BR/PARAMETERFILE                                           Y   Y
+DATA/BR/TEMPFILE                                                Y   Y
+RECO/BR                                                         Y   Y
+RECO/BR/DATAFILE                                                Y   Y
+DATA/BR/CONTROLFILE/Current.260.769030435                       N   Y
+DATA/BR/CONTROLFILE/Current.261.769030431                       N   Y
+DATA/BR/DATAFILE/EXAMPLE.269.769030517                          N   Y
+DATA/BR/DATAFILE/NOT_IMPORTANT.273.771795255                    N   Y
+DATA/BR/DATAFILE/SYSAUX.257.769030245                           N   Y
+DATA/BR/DATAFILE/SYSTEM.256.769030243                           N   Y
+DATA/BR/DATAFILE/TRIPLE_C.272.771794469                         N   Y
+DATA/BR/DATAFILE/TRIPLE_M.271.771793293                         N   Y
+DATA/BR/DATAFILE/UNDOTBS1.258.769030245                         N   Y
+DATA/BR/DATAFILE/USERS.259.769030245                            N   Y
+DATA/BR/ONLINELOG/group_1.262.769030439                         N   Y
+DATA/BR/ONLINELOG/group_1.263.769030445                         N   Y
+DATA/BR/ONLINELOG/group_2.264.769030453                         N   Y
+DATA/BR/ONLINELOG/group_2.265.769030461                         N   Y
+DATA/BR/ONLINELOG/group_3.266.769030471                         N   Y
+DATA/BR/ONLINELOG/group_3.267.769030479                         N   Y
+DATA/BR/PARAMETERFILE/spfile.270.769030977                      N   Y
+DATA/BR/TEMPFILE/TEMP.268.769030503                             N   Y
+DATA/BR/spfileBR.ora                                            N   N
+RECO/BR/DATAFILE/T1.256.771771469                               N   Y

28 rows selected.

Conclusion

Alias directory keeps track of all aliases in an ASM disk group. That information can then be accessed via V$ASM_ALIAS view to present file names in a user friendly format.