ORACLE 에러로그(ORA-) 발생원인/해결방안 정리

minies 2024. 3. 6. 21:19

ORA-00361

## 발생 시나리오

redo log 파일 다중화 중 기존 로그파일을 삭제하려 할 때 발생

alter database drop logfile member '/app/oracle/oradata/ORA19C/redo01.log'; -- 삭제

ORA-00361: cannot remove last log member /app/oracle/oradata/ORA19C/redo01.log
for group 1

## 발생 원인

그룹에 멤버가 1개만 있을 경우 그룹을 삭제해야하는데 멤버를 삭제하려 해서 발생한다.

## 해결 방법

그룹에 멤버가 1개만 있는 경우는 그룹을 삭제 해야 한다.

▶ 그룹 번호 확인

select a.group# -- 그룹번호
 , a.member -- 멤버 파일의 경로 및 파일명 
 , b.bytes/1024/1024 MB -- 파일 사이즈
 , b.status -- 그룹의 상태
 , b.sequence# -- 일련번호
from v$logfile a, v$log b
where a.group# = b.group#
order by a.group#, a.member;

▶ 그룹 삭제

alter database drop logfile group 1;

ORA-39070 / ORA-39087

## 발생 원인

impdp 중 디렉토리 경로를 제대로 설정 안 해주었을 경우 발생

[/home/oracle/datapump]$ impdp system/oracle dumpfile=scml.dmp content=metadata_only directory=prod_dir2 remap_schema=damin:damin2

Import: Release 19.0.0.0.0 - Production on Wed Apr 19 20:48:16 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name PROD_DIR2 is invalid

## 해결 방안

아래와 같이 디렉토리가 제대로 생성되어 있는지 확인한 뒤, 생성이 제대로 안되어 있다면 생성한다.

select directory_name, directory_path
  2  from dba_directories
  3  where directory_name='PROD_DIR';

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
PROD_DIR
/home/oracle/datapump

※ directory 생성

create directory prod_dir as '/home/oracle/datapump';

Deadlock (ORA-00060)

## 발생원인

A세션에서 `UPDATE DAMIN.STUDENT SET NAME='DAMIN' WHERE ID=1;` -> LOCK 걸린 상태

B세션에서 `UPDATE DAMIN.STUDENT SET NAME='DAMIN2' WHERE ID=2;` -> LOCK 걸린 상태

를 한 뒤 COMMIT하지 않은 상태에서

A세션에서 `UPDATE DAMIN.STUDENT SET NAME='DAMIN2' WHERE ID=2;` -> LOCK이 걸렸기 때문에 무한대기

B세션에서 `UPDATE DAMIN.STUDENT SET NAME='DAMIN' WHERE ID=1;` -> LOCK이 걸렸기 때문에 무한대기

하려고 할때 발생한다.

일반적으로 운영할때 이런DEADLOCK은 잘 발생하지 않는다.
신규로 개발된 프로시저나 테이블에서 드물게 발생한다.

## 관련 내용 조회

ORA-00060에러가 발생했다면 관련 상세내용은 ALERT LOG에서 확인 가능

오라클 LOCK의 종류
- DML LOCK
- 테이블레벨 또는 로우레벨에서 발생 
- 참조뷰 : DBA_DML_LOCKS
- DDL LOCK
- DDL 작업에 의해 발생
- 참조뷰 : DA_DDL_LOCKS
- LATCH, MUTEX LOCK
- 메모리 구조를 보호하기 위한 LOCK
- V$latch


```
-- ROW LOCK 조회
select event
       , instance_number
       , sample_time
       , sql_id 
       , session_id
       , session_serial#
       , blocking_inst_id
       , blocking_session
       , blocking_session_serial# 
from DBA_HIST_ACTIVE_SESS_HISTORY

WHERE to_char(sample_time, 'YYYY/MM/DD HH24:MI') LIKE '2023/06/05%'   
and event like '%row lock contention%'
order by sample_time;

-- TABLE LOCK 조회
select 
    a.sid
  , a.serial#
  , 'alter system kill session '''||a.sid||','||a.serial#||''''||';' as aaa
  , a.status
  , a.process
  , a.username
  , a.osuser
  , c.object_name
  , round(a.SECONDS_IN_WAIT/60,2)  as wait_MIN
  , round(a.SECONDS_IN_WAIT/60/60,2)  as wait_HOUR

from v$session a, v$lock b, dba_objects c

where a.sid = b.sid

  and b.id1 = c.object_id

  and b.type = 'TM'

  and c.object_name = '테이블명'

-- DML LOCK 조회
select
    a.session_id
  , b.serial#
  , 'alter system kill session '''||a.session_id||','||b.serial#||''''||';' as aaa
  , a.os_user_name
  , a.oracle_username
  , b.status
  , round(b.SECONDS_IN_WAIT/60,2)  as wait_MIN
  , round(b.SECONDS_IN_WAIT/60/60,2)  as wait_HOUR
from v$locked_object a, v$session b
where a.session_id = b.sid
;


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 이렇게 죽였을때 SESSION이 죽지 않고 KILLED 상태로 남아있는경우는 OS에서 KILL 시켜줘야함

```

## 해결방안

일반적으로는 DB에서 자동 감지하고 해결한다.
COST를 고려해서 하나의 세션에서 COMMIT할지 ROLLBACK 할지 선택하라고 한다.
- COMMIT : DEADLOCK걸린 작업을 제외한 모든 작업을 저장
- ROLLBACK : 모든 작업을 롤백

1. 예방 기법
- 필요한 모든 데이터를 Lock -> 병행성이 떨어짐
- SET LOCK_TIMEOUT문을 통해 일정 시간이 지나면 쿼리를 취소
` set lock_timeout 3000 `

2. 회피 기법
- Wait-Die 방식
- 트랜잭션 A가 트랜잭션 B에 의해 잠금된 데이터를 요청할 때 트랜잭션 A이 먼저 들어온 트랜잭션이라면 대기(Wait)한다.
- 트랜잭션 A가 나중에 들어온 트랜잭션이라면, 포기(Die)하고 나중에 다시 요청한다.
- Wound-Wait 방식
- 트랜잭션 A가 트랜잭션 B보다 먼저 들어온 트랜잭션이라면, 데이터를 선점(Wound)한다.
- 반면, 트랜잭션A가 트랜잭션 B보다 나중에 들어온 트랜잭션이라면 대기(Wait)한다.

참고자료 : https://dataonair.or.kr/db-tech-reference/d-lounge/expert-column/?mod=document&uid=53046

ORA-00905: missing keyword

## 발생원인

권한 부여 중 ORA-00905 에러가 뜰경우
본인은 테이블에 권한을 부여할 경우 하나씩 부여해야하는데 , 로 묶을경우 발생

## 해결방안

하나씩 분리해서 테이블을 생성

SQL> GRANT SELECT ON DAMIN.STUDENT TO SCOTT;

Grant succeeded.

SQL> GRANT SELECT ON DAMIN.PROFESSOR TO SCOTT;

Grant succeeded.

SQL> GRANT SELECT ON DAMIN.PROFESSOR,DAMIN.STUDENT TO SCOTT;
GRANT SELECT ON DAMIN.PROFESSOR,DAMIN.STUDENT TO SCOTT
                               *
ERROR at line 1:
ORA-00905: missing keyword

ORA-00932 inconsistent datatypes: expected NUMBER got CHAR

## 발생 시나리오

SELECT NULLIF(100, 'A') FROM DUAL;

## 발생원인

NULLIF 테스트 중 NUMBER타입과 CHAR타입을 같이 넣었더니 에러발생
이외에도 다른타입을 넣으면 ORA-00932에러가 발생됐다

## 해결방안

NULLIF안에는 같은 타입으로 맞춰줘야 함

SELECT NULLIF(100, 100) FROM DUAL;

ORA-03137: malformed TTC packet from client rejected: [3146] [94] [] [] [] [] [] []

## 발생원인

case1 : db server 버전과 client 접속 버전의 버전차이로 발생할 수 있음
case 2 : 기존 끊어서 진행되는 bulk insert를 한번에 수행하다가 세션이 끊어져서 발생

## 해결방안

: 대량 bulk insert를 다시 끊어서 처리하도록 변경
: 또는 client version 문제일 경우 버전을 맞춰주고 $ORACLE_HOME/network/admin/sqlnet.ora 파일에서 아래 구문 추가

   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
   SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
   SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

※ ALLOWED_LOGON_VERSION_CLIENT 값 별 기능
12a : 12c이상 가능 (가장 강력한 보호 기능)
12 : 중요 패치 업데이트 11G 인증 프로토콜
11 : ORACLE DB 11G 인증 프로토콜용(기본값)
10 : 10G 인증 프로토콜용
8 : 8I 인증 프로토콜용

※ 19c에서는 8로 설정시 10, 11, 12 다 가능
※ 19c에서 10으로 설정해도 10g부터 접속 가능

ORA-01013: user requested cancel of current operation

## 발생원인

: DB에서 작업중이던 사용자가 수행하던 SQL문을 ctrl+C 등으로 강제 종료시켰을 경우 발생할 수 있음
: Alertlog에 남는 정보성 로그

## 조치방안

: 유저가 작업을 취소했을 경우 발생하므로 서비스 특이사항 없음

ORA-01743: only pure functions can be indexed

## 발생 시나리오

## 발생원인

: 12c 이상의 oracle db에서 REGEXP_REPLACE 가 들어간 function based index를 생성하려고 할 경우 발생
: 비결정적 함수(REGEXP_REPLACE)는 가상 열이나 함수 기반 인덱스에 넣을 수 없음

## 조치방안

: DETERMINISTIC함수를 생성하여 함수 기반 인덱스 생성

CREATE OR REPLACE FUNCTION SCHEMA.TEST_REGREP_REPLACE(p1 VARCHAR2, p2 VARCHAR2) 
RETURN varchar2 
DETERMINISTIC IS
BEGIN
RETURN REGEXP_REPLACE(p1, p2);
END;
/
show error

CREATE INDEX T1 ON SCHEMA.TABLE1(TEST_REGREP_REPLACE(a,'[^a-zA-Z0-9]'))

참고 : https://blog.go-faster.co.uk/2019/02/regular-expression-functions-are.html

ORA-01743: only pure functions can be indexed

## 발생원인

## 조치방안

: DETERMINISTIC함수를 생성하여 함수 기반 인덱스 생성

CREATE OR REPLACE FUNCTION SCHEMA.TEST_REGREP_REPLACE(p1 VARCHAR2, p2 VARCHAR2) 
RETURN varchar2 
DETERMINISTIC IS
BEGIN
RETURN REGEXP_REPLACE(p1, p2);
END;
/
show error

CREATE INDEX T1 ON SCHEMA.TABLE1(TEST_REGREP_REPLACE(a,'[^a-zA-Z0-9]'))

참고 : https://blog.go-faster.co.uk/2019/02/regular-expression-functions-are.html

Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2

You cannot put non-deterministic functions into virtual columns or function-based index. Regular expressions have always been sensitive to ...

blog.go-faster.co.uk

ORA-1555 (Snapshot too old)

## 발생 시나리오

현재 undo_retention = 900 (15분)으로 설정

1번세션

A -> B로 업데이트쿼리 수행 (commit X)

2번세션

대량 데이터를 읽는 SQL 수행 (30분 소요라고 가정)

1번 세션

commit 하고 15분이 지남

undo 정보가 덮어씌어짐

2번세션

A가 없어진 상태

ORA-1555 발생

## 발생 원인

덮어쓰기로 인해 언두 데이터가 존재하지 않는 경우
수행 시간이 긴 쿼리일수록 ora-1555 에러가 발생 가능성 높아짐

## 해결 방법

언두 테이블스페이스의 크기를 충분하게 유지
UNDO_RETENTION의 언두 데이터 유지 기간을 충분히 설정
튜닝을 통해 쿼리 시간 단축

ORA-1013 (unified audit record write to audit trail table failed due to ora-1013)

## 발생 시나리오

compile이 되지 않는 오브젝트를 compile 시도하려다가 작업을 취소했다.

## 발생 원인

이미 접속이 되지 않는 서버로 dblink를 통해 내용을 select 하는 프로시저가 있었는데.. 해당 프로시저를 compile 하려그러다 취소하여 발생.

## 해결 방법

해결방안은 필요없으며, 미사용 오브젝트의 경우 제거 조치를 한다.