How to map asm files with online database files

 

       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.

Software Requirements/Prerequisites

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

Configuring the Script

None. Download the script and execute on the ASM instance

Running the Script

$ sqlplus '/ as sysdba'
sql>@files_not_opened

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script


The following code can be downloaded here

set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ----------------------------------------------------------------
    Query will return all the files stored on ASM but not currently
    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;

 

The key of this sql is the reference to v$lock view.  For any file stored on ASM and opened by a database instance, there will be an entry on v$lock:

 Column

Description

type

The enqueue name is FA (File Access)

ID1

For this enqueue, represents the diskgroup number

ID2

For this enqueue, represents the ASM file number

mode

If the file is opened, mode will be 4 (Shared) for 10g, and 2 (row S) for 11g

   


The first branch of the main query will return ALL the ASM files stored on the diskgroup
The second branch of the main query will return all the ASM files currently opened by any database.
The query finally will return the files stored on ASM but not present on the second query (MINUS), which basically will be the files not currently opened.

Script Output

This is the first example of the results of the execution of the script.  Note 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

 

 

 

 

 

 

From Oracle

-------------------------------------------------------------------------------------------------------

QQ: 492913789
Email: ahdba@qq.com
Blog: http://www.cndba.cn/dave

DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823   

聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值