How To Delete Archive Log Files Out Of +Asm

 

1. Run the following SQL to find the full path for the archivelog files.

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;

The results will look similar to the following.

+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1

2. When the file is created by Oracle the format in +ASM is:
   DISKGROUP_NAME/db_name/file_type/creation_date/<file_name>.

This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.

Note: Change the <diskgroup> and <dbname> to the actual values from what is returned from previous SQL output.

 

select 'alter diskgroup DSKGRP1 drop file
''<diskgroup>/<dbname>/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';'
from v$asm_alias a, v$asm_file b
where a.group_number = b.group_number
and a.file_number = b.file_number
and b.type='ARCHIVELOG'
order by a.name;

This will generate SQL similar to the following.

alter DISKGROUP DSKGRP1 drop file  '+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1';

 

 

 

 

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表空间和数据文件的关系，否则拒绝申请