Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.3 to 11.1.0.7 - Release: 10.1 to 11.1Information in this document applies to any platform.
Goal
How to delete archive log files out of +ASM?Solution
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/.
This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.
Note: Change the and to the actual values from what is returned from previous SQL output.
select 'alter diskgroup DSKGRP1 drop file
''//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';
![](https://support.oracle.com/epmos/adf/images/t.gif)
![](https://support.oracle.com/epmos/adf/images/t.gif)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-741109/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/38267/viewspace-741109/