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_eclipseq_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 ') |'/'|. 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_eclipseq_970.1236.1 ';

 

 

 

 

From Oracle

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Chat group: 40132017 chat 2 group: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.