오라클 import 과정중에
아래와 같이 에러를 만났다
ora-00257 archiver error. connect internal only until freed
원인은
아카이브 full로 인해 발생
근데 문제는 ASM 디스크 였다
아래의 쿼리를 통해 조회하면 archive err 상태를 확인 가능하다
SQL> select * from v$archive_dest;
방법은 수동 삭제와 자동 삭제가 있고
개발서버라 고민없이 RMAN으로 접속하여 자동으로 지웠다.
수동삭제
Subject: How To Delete Archive Log Files Out Of +Asm?
Doc ID: Note:300472.1
The information in this document applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.3
Information in this document applies to any platform.
Goal
How to delete archive log files out of +ASM?
Fix
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';
자동삭제
$> rman target /
RMAN> crosscheck archivelog all;
RMAN> delete noprompt archivelog all;
참고 : http://interpiastory.tistory.com/21
http://kooremo.tistory.com/entry/ora00257archiver-error-Connect-internal-only-until-freed
http://database.sarang.net/?inc=read&aid=30826&criteria=oracle&subcrit=&id=&limit=20&keyword=blob&page=3
'Database > ORACLE' 카테고리의 다른 글
| 오라클 수평 출력 print_table 기능 (0) | 2018.02.04 |
|---|---|
| 오라클 SR 처리를 위한 사전 준비 (0) | 2018.01.17 |
| expdp failed with ORA-31641: unable to create dump file ORA-27054: NFS file system where the file is created or resides is not mounted with correct options (0) | 2018.01.08 |
| impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037 (0) | 2018.01.08 |
| PCTFREE,PCTUSED,FREELIST (0) | 2018.01.05 |