반응형

오라클 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

반응형

+ Recent posts