• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle sql script to report the list of files stored in ASM and CURRENTLY NOT OPENED

By admin

Purpose

One of the features of ASM is databases consolidation. A single diskgroup will be the repository for multiple databases. The result, having a large number of datafiles. If the files were created using OMF (Oracle Managed Files) format, when the datafile is deleted on the database side, it is also deleted on the ASM side. There may be situations when the datafiles are not deleted on the ASM side, particularly when not using OMF format.

With large diskgroups holding large number of datafiles it is necessary to make good usage of the storage and reclaim space allocated for files not used by the databases.

The following script will provide the list of files stored in ASM and CURRENTLY NOT OPENED by any database client of the diskgroups. It is very important to validate the results of the query, particularly for files that could have been marked temporary offline. It does not apply to READ ONLY tablespaces, because once the file is opened by the database, it will be returned by the query.

Note: The parameter file (spfile) will be reported always as not opened. Always double check the file before deleting it.

Pre-requisites

– The query has to be executed on the ASM instance.
– Diskgroups need to be mounted.

The script

The script can be run on the ASM instance as shown below:

$ sqlplus '/ as sysdba'
sql>@files_not_opened

The script is given below:

set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ----------------------------------------------------------------
    Query will return all the files stored on ASM but not currenlty
    opened by any database client of the diskgroups
    ordered by group number, file type
    ---------------------------------------------------------------*/

select * from (
/*+ -----------------------------------------------------------------
    1st branch returns all the files stored on ASM
    -----------------------------------------------------------------*/
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
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) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
/*+ --------------------------------------------------------------
    2nd branch returns all the files stored on ASM
    and currently opened by any database client of the diskgroups
    -----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
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
) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum 
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
;

Sample output

This is the first example of the results of the execution of the script. Note that it includes all type of files like ARCHIVELOGS, FLASHBACK LOGS, REDOLOGS. Once it has been confirmed the files are not required any more, then they can be deleted using ASMCMD or SQL running the command ALTER DISKGROUP … DROP FILE ‘[path]’;

GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ----------------------------------------------------------------------
         1        270 +DATA1/G102/ONLINELOG/group_2.270.645209903
         1       3247 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_54.3247.645369463
         1       3249 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_59.3249.645367259
         1       3250 +DATA1/G102/FLASHBACK/log_104.3250.645367249
         1       3251 +DATA1/G102/FLASHBACK/log_103.3251.645369221
         1       3252 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_58.3252.645367197
         1       3254 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_53.3254.645369095
         1       3255 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_57.3255.645366969
         1       3256 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_52.3256.645368439
         1       3257 +DATA1/G102/ARCHIVELOG/2008_02_04/thread_2_seq_59.3257.645786351
         1       3258 +DATA1/G102/FLASHBACK/log_13.3258.645786863

As mentioned before, it is extremely important to review the results of the script. In this following example, these datafiles are not currently opened but they belong to a database currently closed.

   GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ----------------------------------------------------------------------
         2        256 +DG_NETAPPS/V102/DATAFILE/UNDOTBS2.256.643467013
         2        257 +DG_NETAPPS/NETAPP/DATAFILE/SYSTEM.257.643477635
         2        258 +DG_NETAPPS/NETAPP/DATAFILE/SYSAUX.258.643477637
         2        259 +DG_NETAPPS/NETAPP/DATAFILE/UNDOTBS1.259.643477641
         2        260 +DG_NETAPPS/NETAPP/DATAFILE/USERS.260.643477643
         2        261 +DG_NETAPPS/NETAPP/CONTROLFILE/Current.261.643478451
         2        262 +DG_NETAPPS/NETAPP/ONLINELOG/group_1.262.643478461
         2        263 +DG_NETAPPS/NETAPP/ONLINELOG/group_2.263.643478517
         2        264 +DG_NETAPPS/NETAPP/ONLINELOG/group_3.264.643478569
         2        265 +DG_NETAPPS/NETAPP/TEMPFILE/TEMP.265.643478655
         2        266 +DG_NETAPPS/NETAPP/PARAMETERFILE/spfile.266.643478737
         2        266 +DG_NETAPPS/NETAPP/spfilenetapp.ora
         2        267 +DG_NETAPPS/NETTEST/DATAFILE/UNDOTBS1.267.643723749

Filed Under: ASM, oracle, oracle 12c

Some more articles you might also be interested in …

  1. ORA-30012 Database Does Not Start With UNDO_MANAGEMENT=AUTO – Oracle Database 11gr2
  2. How To Create an Encrypted Tablespace in Oracle 12c Pluggable Database
  3. Oracle RMAN: Monitoring Recovery Manager Jobs
  4. Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake
  5. RMAN: SET NEWNAME Command Using SQL
  6. How to reclaim entire space of an oracle database table with “Truncate Table” statement
  7. Cron Script does not Execute as Expected from crontab – Troubleshoot
  8. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  9. Oracle database – How to create pfile or spfile using the current parameters
  10. How to trace asmcmd command on UNIX/Linux

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary