Wednesday, March 22, 2017

Collect the full path name of the files in ASM diskgroups

One of node name in my RAC setup was appearing in UPPERCASE ( wrong one), let's try to correct this.


We have a requirement to list down all the full path name of files in ASM diskgroup.

Let's use below script :-

Set your ORACLE_SID to the ASM instance name.

Connect to the ASM instance:
in 10g: sqlplus / as sysdba
in 11g: sqlplus / as sysasm

Then perform the following query:

SELECT gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/')) "FileName"
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex, a.group_number gnum,a.file_number filnum 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;

Example :-

[oracle@rac2 ~]$ grid_env

[oracle@rac2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 22 07:03:39 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> SELECT gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/'))"FileName"
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex, a.group_number gnum,a.file_number filnum
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 3;   

      GNUM     FILNUM FileName
---------- ---------- ----------------------------------------------------------------------
         1 4294967295 +CRS/rac-cluster
         1 4294967295 +CRS/rac-cluster/OCRFILE
         1                 255 +CRS/rac-cluster/OCRFILE/REGISTRY.255.938470333
         2 4294967295 +DATA/ORCL
         2 4294967295 +DATA/ORCL/ARCHIVELOG
         2 4294967295 +DATA/ORCL/ARCHIVELOG/2017_02_22
         2                 269 +DATA/ORCL/ARCHIVELOG/2017_02_22/thread_1_seq_8.269.936656143
         2                 272 +DATA/ORCL/ARCHIVELOG/2017_02_22/thread_1_seq_9.272.936659955
         2                 271 +DATA/ORCL/ARCHIVELOG/2017_02_22/thread_2_seq_3.271.936656717
         2 4294967295 +DATA/ORCL/ARCHIVELOG/2017_02_23
         2                 276 +DATA/ORCL/ARCHIVELOG/2017_02_23/thread_1_seq_10.276.936683643
         2 4294967295 +DATA/ORCL/CONTROLFILE
         2                 260 +DATA/ORCL/CONTROLFILE/Current.260.936514653
         2 4294967295 +DATA/ORCL/DATAFILE
         2                 264 +DATA/ORCL/DATAFILE/EXAMPLE.264.936514701
         2                 257 +DATA/ORCL/DATAFILE/SYSAUX.257.936514513
         2                 256 +DATA/ORCL/DATAFILE/SYSTEM.256.936514511
         2                 270 +DATA/ORCL/DATAFILE/TEST.270.936656501
         2                 258 +DATA/ORCL/DATAFILE/UNDOTBS1.258.936514515
         2                 265 +DATA/ORCL/DATAFILE/UNDOTBS2.265.936515105
         2                 259 +DATA/ORCL/DATAFILE/USERS.259.936514515
         2 4294967295 +DATA/ORCL/ONLINELOG
         2                 261 +DATA/ORCL/ONLINELOG/group_1.261.936514659
         2                 262 +DATA/ORCL/ONLINELOG/group_2.262.936514661
         2                 266 +DATA/ORCL/ONLINELOG/group_3.266.936515201
         2                 267 +DATA/ORCL/ONLINELOG/group_4.267.936515205
         2 4294967295 +DATA/ORCL/PARAMETERFILE
         2                 268 +DATA/ORCL/PARAMETERFILE/spfile.268.936515211
         2 4294967295 +DATA/ORCL/TEMPFILE
         2                 263 +DATA/ORCL/TEMPFILE/TEMP.263.936514679
         2                 268 +DATA/ORCL/spfileorcl.ora
         2 4294967295 +DATA/rac-cluster
         2 4294967295 +DATA/rac-cluster/ASMPARAMETERFILE
         2                 253 +DATA/rac-cluster/ASMPARAMETERFILE/REGISTRY.253.936378415
         2 4294967295 +DATA/rac-cluster/OCRFILE
         2                 255 +DATA/rac-cluster/OCRFILE/REGISTRY.255.936378419






No comments:

Post a Comment