pat98's always & forever

오라클 데이터베이스 기술지원 뉴스 제57권- 2015년 11월호 (문서 ID 1529796.1)

오라클 데이타베이스를 관리하다보면 아마도 ORA-01555: snapshot too old 에러를 만나게 될 수 있습니다.

이 에러에 대한 원인 분석  및 문제해결 방법에 대해 알고 싶으시다면 아래 내용들을 읽어주시기 바랍니다.

ORA-01555: snapshot too old: rollback segment number XX with name "XX XX XX XX XX" too small

ORA-1555 에러는 쿼리수행시 데이타 복사를 위해 관련 언두(UNDO) 정보에 접속할 수 없을 때 발생합니다. 커밋된(commit) 블럭의 "versions" 과 커밋되지 않은(uncommitted) 블럭의 "versions" 들로 관리가 되어 쿼리가 시작할 때의 데이타베이스에 존재하는 데이타에 접근할 수 있게됩니다. 이는 "읽기 일관성" 블럭으로 알려져 있고, 오라클 언두 관리에 사용되어집니다.

아래 타임라인에 따른 에러발생 예제를 살펴 보시기 바랍니다.

노트: 언두(UNDO) 는 System managed UNDO(또는 Automatic Undo Management AUM) 또는 롤백 세그먼트를 의미합니다.

에러는 어떤 파일에 기록되나요?

에러는 일반적으로 아래 파일들에 기록됩니다.

• Alert Log 파일
  아래와 같은 메시지로 기록됩니다."
  ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small

• 이슈가 발생할 시각에 트레이스 파일이 생성됩니다.
  기본적으로 1555 이벤트를 설정하지 않으면 ORA-1555 발생시 자동으로 트레이스 파일을 생성하지 않습니다.
  alter system set events '1555 trace name errorstack level 3';
  위와 같이 설정하는 경우, 1555 에러가 발생하면 트레이스 파일명은 아래 예제와 같이 alert 로그에 기록됩니다.:
  Wed Jul 22 09:20:11 2015
  Errors in file /u01/sq/diag/rdbms/DB1/ DB1/trace/ DB1_ora_35236.trc:
  ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_417799848$" too small

에러가 왜 발생하나요?

이 이슈의 근본적인 원인은 쿼리가 수행될 시점에 데이타 복사본을 쿼리가 가져갈 수 없게 덮어씌워졌기 때문입니다. 일반적으로 알려진 이슈들은 아래와 같습니다.

• 행의 UNDO 기록이 만료되었습니다.
  이 말은 현재 시점에서 행의 커밋시간을 뺀 시간이 UNDO_RETENTION 보다 크다는 것을 의미합니다. UNDO 기록이 행을 커밋하여 만료(expired) 되면 재사용이 가능합니다.
  아래와 같은 문의가 있을 수 있습니다.
  왜 어떤 쿼리는 ORA-1555 발생전에 UNDO_RETENTION 보다 더 오랫동안 수행되기도 하나요 또 어떤 때는 에러가 빨리 발생하기도 하는데요?
  이 부분은 명확히 답변드리기 어렵습니다. 왜냐면 에러발생은 작업의 양과 UNDO 테이블스페이스가 사용되고 있는지에 달려있습니다.
  
  노트: 사용되고 있는 UNDO 레코드 또는 커밋되지 않은 트랜잭션은 'ACTIVE' 로 표시됩니다. 트랜잭션이 커밋되면, 각각의 UNDO 레코드는 'UNEXPIRED' 로 기록됩니다. 예를 들어 UNDO_RETENTION (또는 AUM 의 경우 시스템에 의해 계산된 TUNED_UNDORETENTION )에 정의된 시간동안 유지됩니다. 유지시간이 정해진 만큼 언두 레코드를 보관한 후, 재사용을 위해 'EXPIRED' 로 기록됩니다.
  


• 행의 UNDO 기록이 만료되지 않았으나 덮어씌워졌습니다.
  이 시나리오는 UNDO 테이블스페이스가 가득차서 'UNEXPIRED' 언두 레코드가 덮어씌워지고 있는 상황입니다.
  이는해당 UNDO 테이블스페이스에 대해 RETENTION GUARANTEE 가 활성화 되지 않아서 발생합니다.
  


• LOB 세그먼트에 대한 LOB 세그먼트 읽기 일관성이 더이상 유효하지 않습니다.
  LOB 컬럼이 in-row 또는 out-row 중 어떻게 구성되어 있느냐에 달렸습니다. In-row LOB 은 UNDO 테이블스페이스의 일반 UNDO 알고리즘에 따릅니다. Out-of-row LOB 은 아래를 참고 바랍니다.
  
  2 가지 방법으로 제어되는 LOB 의 읽기 일관성
  
  • PCTVERSION 사용(과거의 방법)
    LOB 의 버전 관리를 위해 사용되는 전체 LOB 스토리지 공간의 최대 % 를 명시합니다. 기본값은 10 이고 이것은 전체 LOB 스토리지 공간의 10 % 를 사용할 때까지 오래된 LOB 데이타를 덮어쓰지 않습니다.
    이 방법이 사라지게 된 이유로 LOB 에 대한 잦은 업데이트 및 삭제로 인해 100% PCTVERSION 을 종종 초과하게 되기 때문입니다.
    
  • RETENTION 사용(AUM 과 함께 사용되는 최신 방법)
    이 절을 통해 오라클 데이카베이스는 LOB 컬럼의 오래된 version 을 유지해야합니다.
    오라클 데이타베이스는 데이타베이스에서 커밋된 UNDO 데이타의 양을 결정하기 위해 초기 매개변수인 UNDO_RETENTION 값을 사용합니다.
    이 방법은 UNDO 세그먼트와같이 만료 시간을 사용합니다. 만약 LOB 세그먼트에 ORA-1555 가 발생하면 이는 아래를 의미합니다.:
    
    • PCTVERSION 이 초과되었고 LOB 의 읽기 일관성 뷰가 덮어씌워진 경우이거나
      또는
      
    • LOB 이 RETENTION 을 초과하고 행의 LOB 이 덮혀씌여져 ORA-1555 가 발생됩니다.
      

문제해결

아래는 이 이슈를 해결하는 단계목록입니다.:

1. 에러 메세지를 확인
   ORA-1555 에러에 여러 형태가 있어 자세한 내용 확인을 위해 alert 로그(또는 에러를 포함하고 있는 로그파일)를 확인합니다. :
   1. 세크먼트 명을 확인
      ORA-01555: snapshot too old: rollback segment number  with name "" too small
      --> 세그먼트 명이 NULL ""
      또를/그리고
      ORA-22924: snapshot too old
      이 경우 1555 에러는 LOB 세그먼트 내에 저장된 UNDO 데이타에 접근할 때 기록됩니다. LOB 세그먼트 ORA-01555 는 아래의 이유중 하나때문에 발생합니다.:
      
      1. LOB 세그먼트 손상:
         LOB 테이블의 손상을 확인이 필요하며, 아래 문서를 참조 바랍니다.:
         
      • Document 452341.1 ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption
      2. LOB 소산이 확인되지 않으면, Retention/ Pctversion 값을 확인합니다.:
         Retention/Pctversion 값을 올려야할 수도 있습니다. 아래 문서를 참조 바랍니다.
         
         • Document 846079.1 LOBs and ORA-01555 troubleshooting
         ORA-01555: snapshot too old: rollback segment number 107 with name "_SYSSMU107_1253191395$" too small
         -> 세그먼트 명은 "_SYSSMU107_1253191395$" 이고 UNDO 테이블스페이스내의 UNDO data 임을 의미합니다. 이 경우 ORA-1555 에러는 UNDO 테이블스페이스 내의 UNDO 데이타에 접근하려할 때 발생되는 것으로 아래에서 문제해결 방법에 대해 안내해 드리겠습니다.
         
   2. 쿼리 수행시간(duration) 확인
      실패한 쿼리의 수행시간은 ORA-1555 에러 메세지가 alert 로그 또는 어플리케이셔 로그에 기록됩니다.
      ORA-01555 caused by SQL statement below (Query Duration=1974 sec, SCN: 0x0002.bc30bcf7):
      쿼리 수행시간이 0 또는 몇 초라면 아래 문서를 확인해 보시기 바랍니다.
      
      • Document 1131474.1 ORA-01555 When Max Query Length Is Less Than Undo Retention, small or 0 Seconds
      만약 쿼리 수행시간이 UNDO_RETENTION 설정된 값보다 높다면, UNDO_RETENTION 값을 쿼리 수행시간만큼 증가시켜주시기 바랍니다. 그에 따라 UNDO 테이블스페이스 사이즈도 증가되어야 합니다.
      만약 쿼리 수행시간이 UNDO_RETENTION 과 같거나 근접했다면, 다음 분석방법을 진행하십시오.
      


2. Undo 데이타파일 확인
   select tablespace_name, file_id, sum(bytes)/1024/1024 a, sum(maxbytes)/1024/1024 b, autoextensible
   from dba_data_files
   where tablespace_name in
   (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' )
   group by file_id, tablespace_name, autoextensible
   order by tablespace_name;
   non-autoextensible UNDO 데이타파일을 사용중이라면, TUNED_UNDORETENTION 에 의해 높게 산정되어 에러가 발생될 수 있고 그로 인해 UNDO 데이타파일을 많이 사용하게됩니다.
   이 현상을 피하기 위해서는 여유 공간이 있다면, UNDO 데이타파일을 autoextensible( MAXSIZE 를 명시하여) 로 두어야 합니다.
   노트: UNDO 테이블스페이스 구성시 non-autoextensible 과 autoextensible 을 모두 사용하는 UNDO 데이타파일로 구성하지 마십시오. TUNED_UNDORETENTION 가 잘못 산정될 수 있습니다.
     
   
3. TUNED_UNDORETENTION 확인
   SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;
   SQL> select max(maxquerylen),max(tuned_undoretention) from dba_hist_undostat;
   
   1. TUNED_UNDORETENTION 가 MAXQUERYLEN 값 보다 작음:
      이 경우 UNDO 테이블스페이스에 공간부족이 있다는 것을 의미합니다. 따라서, UNDO 레코드는 충분한 시간동안 유지하지 않게 됩니다. 이 경우에는 UNDO tablespace 에 공간을 추가하여야 합니다.
      
   2. TUNED_UNDORETENTION 가 MAXQUERYLEN 에 비해 너무 높음:
      이런 경우 일반적으로 UNDO 테이블스페이스가 non-autoextensible 데이타파일인 경우입니다. 내부 알고리즘에 따라 가능한한 오랫동안 UNDO 레코드를 유지하려고 하는데, 이 때문에 TUNED_RETENTION 이 높게 산정됩니다. 회피책으로 모든 UNDO 데이타파일을 autoextensible 모드로 변경하는 것입니다.(데이타파일에 MAXSIZE 를 설정할 수 있습니다.)
      오랫동안 수행되는 쿼리들로 TUNED_UNDORETENTION 가 높아질 수 있습니다.
      이런 쿼리들의 UNDO 데이타들이 너무 오랫동안 유지되는 것을 막을 필요가 있습니다. 이런 오랫동안 수행되는 쿼리를 확인하기 위해서는 아래를 사용하십시오.:
      select maxquerysqlid, maxquerylen from dba_hist_undostat order by maxquerylen desc;
      select maxqueryid, maxquerylen from v$undostat order by maxquerylen desc;
      


4. ACTIVE/UNEXPIRED 익스텐트의 높은 사용율
   select distinct status,tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name;
   ACTIVE/UNEXPIRED 익스텐트의 과도한 할당은 다음 이유들 중 하나때문일 수 있습니다.
   
   1. 앞서 안내해드렸듯이 UNDO_RETENTION or TUNED_UNDORETENTION 의 값이 높거나
      
   2. 특정 시점의 높은 UNDO 데이타 생성때문인 것으로 아래 쿼리를 사용하여 확인할 수 있습니다.:
      alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
      select begin_time , undoblks from dba_hist_undostat order by undoblks desc ;
      select begin_time , undoblks from v$undostat order by undoblks desc;
   3. 대량의 dead 트랜잭션 롤백
      
   4. 플래시백 데이타 아카이브 사용
   상세정보는 아래 문서를 참조 바랍니다.
   
   • Document 1337335.1 How To Check the Usage of Active Undo Segments in AUM
     


5. UNDO_RETENTION :
   UNDO_RETENTION 은 최소 MAXQUERYLEN 의 평균값으로 설정할 것을 권고드리며 만약 ORA-1555 에러가 발생하면 증가시켜주시기 바랍니다.
   select avg(maxquerylen) from dba_hist_undostat;
   select avg(maxquerylen) from v$undostat;