How To Delete Archive Log Files Out Of +Asm
来源:互联网 发布:网络平台招商方案 编辑:程序博客网 时间:2024/05/26 09:54
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
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请
- How To Delete Archive Log Files Out Of +Asm
- How to delete the log file of log4j dynamically
- OCP-043 creating archive log files in ASM
- How to config (no)archivelog mode and manage archive log files by RMAN (Updated to 10G)
- How to collect the full path name of the files in ASM diskgroups (文档 ID 888943.1)
- How to collect the full path name of the files in ASM diskgroups (文档 ID 888943.1)
- How to clear the archive log
- How to map asm files with online database files
- How To Add/Increase The Size Of Redo Log Files In Rac Environment? (文档 ID 779306.1)
- Delete archive log
- How To Create ASM Diskgroups using NFS/NAS Files
- How to Copy ASM Files Across Nodes [ID 1147859.1]
- How To Create ASM Diskgroups using NFS/NAS Files
- How to use logrotate to manage log files in Linux
- How to break out of the forEach
- How to find Domino installation related log files
- How to Collect Bne Log Files for GL Integrators
- How to Copy asm files between remote ASM instances using ASMCMD command
- Verison Name/Code的获取
- Zju -- 2859 Matrix Searching(线段树)
- 关于二分查找的那些事
- 图片实现等比例缩放
- 国外书签网站
- How To Delete Archive Log Files Out Of +Asm
- 经典的原型链例子。
- 数据库中乐观锁与悲观锁的概念
- 改变dialog的位置
- mysql手册总结2
- c++ completion (vim)
- 数据库可疑处理
- Order a column conditional in SQL
- 组合和聚合