http://blog.naver.com/k65fac?Redirect=Log&logNo=40105702092
----------------------------------------------------
ORA-1555 “SNAPSHOT TOO OLD” 에러의 원인과 해결방법
-----------------------------------------------------
데이타 일관성(Read Consistency)과 Block CleanOut
ORA-1555 에러의 원인을 이해하기 전에 먼저 데이타 일관성(Read Consistency)에 대한 이해가
필요한다. 여러 단계의 데이타 일관성을 지원하는 오라클의 이 기능은, 한 시점에서 데이타를
일관된 내용으로 볼 수 있도록 제공하는 것으로 데이타베이스 사용자에게는 아주 중요한 개념이다.
[ 예제 ]
테이블에 있는 백만건의 레코드를 수정하는 트랜잭션이 있다고 가정해 보자. 이 작업을 수행하기
위해서는 아주 많은 데이타베이스 블럭에 접근해야 한다. 사용자가 이 트랜잭션을 Commit했을 때
오라클은 이것을 반영하기 위해 데이타 블럭에 다시 접근하지 않는다. 그것은 수정된 데이타가
저장되어 있는 데이타 블럭에 접근하여 데이타를 읽는, 다음 트랜잭션을 위해 남겨진다.
( 이런 과정을 ‘Delayed Block Cleanout’ 이라고 한다.)
오라클이 Table, Index, Cluster등을 수정 할 때마다 그 트랜잭션에 의해 바뀌기 전의 원래
데이타를 롤백 세그먼트에 저장하고, 그 롤백 세그먼트의 식별자들은 수정된 데이타 블럭의
Header에 저장한다. (이것은, 만약 사용자가 수정한 내용을 Commit하지 않고 있다가 나중에
수정하기 전의 원래 데이타로 Undo하기를 원할 때 필요하다.)
Commit을 하면, 오라클은 그 트랜잭션과 관련된 롤백 세그먼트의 블럭 Header에 그 데이타가
이미 Commit되었다는 것을 표시한다. 그리고, 수정된 블럭에 다른 트랜잭션이 다시 조회를 하면
오라클은 어떤 시점에서 수정되었는지를 가리키는 데이타 블럭 Header를 조사한다. 오라클은
수정사항이 이미 Commit된 것인지, 아직 Commit되지 않은 것인지 확인한다. 이를 위해 오라클은
이전 트랜잭션에 의해 사용된 롤백 세그먼트를 찾아서 롤백 세그먼트 Header에서 이 정보를
확인한다. 그 블럭이 Commit되었다는 것을 확인하면, 그 블럭을 읽는 다음 트랜잭션이 같은 과정을
다시 겪지 않도록 그 데이타 블럭의 Header를 수정한다. (Block CleanOut)
ORA-1555 에러가 발생하는 기본적인 두 가지 원인은 다음과 같다.
트랜잭션이 Commit된 후에 롤백 세그먼트 안에 저장되어 있는 롤백을 위한 데이타들이 다른
데이타로 인해 지워지는 경우이다. 트랜잭션이 Commit된 후에 롤백 세그먼트 Header에 있는
트랜잭션 테이블의 트랜잭션 슬롯의 정보가 지워지는 경우이다.
해결책
롤백 세그먼트의 크기를 키워준다. 필요한 롤백 정보가 다른 트랜잭션의 발생으로 지워질
가능성을 줄인다. Commit 하는 수를 줄인다.전체 테이블보다 부분으로 나누어서 작업을 수행한다.
롤백 세그먼트를 추가로 생성한다. 이것은 수정, 삭제등의 작업을 수행할 때 한 롤백 세그먼트의
사용을 줄이는 효과를 가져온다.
PL/SQL문에서 Loop안에 Fetch 후 Commit을 넣으면 빈번한 Commit으로 에러가 발생할 수 있다.
http://blog.naver.com/k65fac?Redirect=Log&logNo=40105702092
ROLLBACK SEGMENT 크기를 증가하여 재생성하는 방법
ROLLBACK SEGMENT 는 LONG TRANSACTION 을 수행하면 크게되는 경우가 있다.
확장된 ROLLBACK SEGMENT 는 줄어들지 않게되므로(OPTIMAL을 지정하지 않는 경우)
주기적으로 재생성하여 TABLESPACE의 FREESPACE 영역을 확보한다.
1. SQLDBA 기동한다.
$ sqldba lmode=y
sqldba > connect internal
2. ROLLBACK SEGMENT 를 OFFLINE 시킨다. ROLLBACK SEGMENT 를 OFFLINE
시키기위해서는 그 ROLLBACK SEGMENT 가 INACTIVE상태여야 하므로 모든
TRANSACTION 을 종료 하고 ROLLBACK SEGMENT r01-r04를 OFFLINE 시켜야한다.
주의) 이때 SELECT * FROM V$ROLLSTAT; 를 하여 OFFLINE하고자 하는
ROLLBACK SEGMENT의 XACTS 컬럼을 확인하여 이 값이 0일때만 OFFLINE
하도록 한다.
XACTS는 그 ROLLBACK SEGMENT를 현재 사용하고 있는 ACTIVE
TRANSACTION의 수이다.
SQLDBA> select * from dba_rollback_segs;
-> rollback segment의 이름과 크기 등을 확인한다.
예를 들어 RBS가 r01,r02,r03,r04가 있다면
SQLDBA> alter rollback segment r01 offline;
SQLDBA> alter rollback segment r02 offline;
** r03 ~ r04 도 같은 방법으로 OFFLINE 함.
3. ROLLBACK SEGMENT를 DROP한다.
SQLDBA > drop rollback segment r01;
SQLDBA > drop rollback segment r02;
** r03 ~ r04 도 같은 방법으로 DROP 함.
4. ROLLBACK SEGMENT 를 생성한다. SYSTEM ROLLBACK 이외에 모든 ROLLBACK
SEGMENT 를 DROP 한 경우는 SYSTEM TABLESPACE 에 ROLLBACK SEGMENT 를 한 개
이상 생성후에 NON-SYSTEM TABLESPACE에 ROLLBACK SEGMENT를 생성해야한다.
SQLDBA > create rollback segment r0;
SQLDBA > alter rollback segment r0 online;
SQLDBA > create rollback segment r01 tablespace rbs
storage(initial 512k next 512k minextents 2);
SQLDBA > create rollback segment r02 tablespace rbs
storage(initial 512k next 512k minextents 2);
** r03 ~ r04 도 같은 방법으로 생성 함.
5. 데이타베이스를 재기동 한다.
SQLDBA > shutdown
SQLDBA > startup
RBS TABLESPACE 를 재생성 후 ROLLBACK SEGMENT 를 생성하는 방법
----------------------------------------------------------------
6. RBS TABLESPACE를 DROP 하기 위해서는 1-2 과 같이 ROLLBACK SEGMENT를
전부 OFFLINE를 한다.
ROLLBACK SEGMENT 를 OFFLINE할수 있는 다른 방법은
ORACLE_HOME/dbs/init.ora 화일의 rollback_segments=r01..
부분을 #으로 COMMENT 처리하고 DB를 다시 startup 시키면 된다.
SQLDBA > drop rollback segment r01;
SQLDBA > drop rollback segment r02;
** r03 ~ r04 도 같은 방법으로 DROP 함.
7. RBS TABLESPACE를 DROP 한다.
SQLDBA> drop tablespace RBS including contents;
SQLDBA> ! rm /oracle/dbs/rbs.dbf
8. RBS TABLESPACE 를 다시 생성한다.
SQLDBA> create tablespace RBS datafile '/../oracle/dbs/rbsORA7.dbf'
size 50M default storage(initial 512k next 512k);
9. ROLLBACK SEGMENT r0, r01-r04 를 생성한다.
SQLDBA> create rollback segment r0;
SQLDBA> alter rollback segment r0 online;
SQLDBA> create rollback segment r01 tablespace RBS;
** r02 - r04 도 같은 방법으로 생성한다.
10.1에서 COMMENT 처리했던 부분을 다음과 같이 풀고 DB 를 다시 startup한다.
rollback_segments=r01,r02,r03,r04
SQLDBA> shutdown
SQLDBA> startup
< ORA-1562 : failed to extend rollback segment (id = %s) 해결 방법>
----------------------------------------------------------------
ROLLBACK SEGMENT는 TRANSACTION을 수행하면 필요한 만큼의 EXTENT를 발생하여
그 크기가 증가된 이후에는 그 TRANSACTION이 COMMIT 혹은 ROLLBACK되더라도
SIZE가 줄어들지 않는다.(OPTIMAL을 지정하지 않는 경우).
이것은 DB를 SHUTDOWN 후 다시 STARTUP하여도 마찬가지이며, ROLLBACK SEGMENT가
자신이 속한 TABLESPACE로부터 EXTENT를 할당받다가 더 이상 할당받을 EXTENT가
없을 때, 혹은 이미 MAXEXTENTS(DEFAULT 121)에 도달한 경우에 ORA-1562가 발생한다.
1. 현재 ROLLBACK SEGMENT가 포함되어 있는 RBS TABLESPACE의 크기 확인
sqlplus system/manager
SQL> select file_name, bytes
from dba_data_files
where tablespace_name = 'RBS';
FILE_NAME BYTES
--------------------------- -------------
/oracle/pms/dbs/rbsPMS01.dbf 20971520
/oracle/pms/dbs/rbsPMS02.dbf 10485760
여기에서 나타난 결과 값이 절대적으로 작은 경우 RBS TABLESPACE에 DATAFILE을
ADD시켜 줄 필요가 있다.
2. 각 ROLLBACK SEGMENT의 SIZE 및 발생한 EXTENT의 수 확인
sqlplus system/manager
SQL> select segment_name, initial_extent, next_extent, max_extents,
rssize, extents, xacts
from dba_rollback_segs a, v$rollstat b
where a.segment_id = b.usn;
결과는 다음과 같은 형태로 나온다.
(1) INITIAL_EXTENT는 ROLLBACK SEGMENT가 필요한 경우 처음으로 자신의
SPACE를 잡는 크기이며, 이 크기 만큼의 연속된 공간을 RBS TABLESPACE
부터 잡는다.
(2) NEXT_EXTENT는 INITIAL_EXTENT를 잡은 후에 추가적으로 SPACE가 필요한
경우 이 크기 만큼씩 SPACE를 잡게 된다.
(3) MAXEXTENTS는 각 ROLLBACK SEGMENT에 지정된 최대 발생가능한 EXTENT의
갯수이다. 여기에 설정된 값 이상의 EXTENT를 발생시킬 수 없다.
(4) RSSIZE는 현재 각 ROLLBACK SEGMENT가 잡고 있는 RBS TABLESPACE 내의
ROLLBACK SEGMENT의 크기이다.
(5) EXTENTS는 현재 각 ROLLBACK SEGMENT 별로 발생한 EXTENT의 갯수이다.
(6) XACTS는 현재 각 ROLLBACK SEGMENT를 잡고 있는 ACTIVE ROLLBACK
TRANSACTION의 갯수이다.
ROLLBACK SEGMENT를 OFFLINE하거나 DROP할 때는 이 XACTS가 0인지
확인하고 작업하여야 한다.
(컬럼 이름은 편의상 축소하였다.)
SEGMENT INITIAL NEXT MAX RSSIZE EXNTENTS XACTS
------------------------------ ----------- ---------- ------ ----
SYSTEM 262144 262144 121 407552 8 0
R01 262144 262144 121 530432 2 0
R02 262144 262144 121 3192832 12 0
R03 262144 262144 121 5056512 19 0
R04 262144 262144 121 11180032 42 1
이 때 ORA-1562 발생시 표시된 message에 나타난 ROLLBACK SEGMENT 이름의
EXTETNS가 MAX_EXTETNS의 수와 같으면, 이것은 그 ROLLBACK SEGMENT가
MAXEXTENT에 도달하여 이 오류가 발생한 것이고, 그렇지 않은 경우는
TRANSACTION에 필요한 SPACE가 더 이상 RBS TABLESPACE에 FREE SPACE로
남아 있는 것이 없어서 발생한 것이다.
3. 가능한 조치 방법
(1) 2번의 QUERY 결과 MAXEXTENTS에 도달하지도 않았고, 오류가 발생한
TRANSACTION을 처리할 만큼 큰 크기의 RSSIZE가 있는 ROLLBACK
SEGMENT도 없는 경우에는 1번의 조회 결과 나타난 RBS의 DATAFILE 이외에
추가적으로 RBS TABLESPACE에 새로운 DATAFILE을 추가하여야 한다.
sqlplus system/manager
SQL> alter tablesapce RBS add datafile
'/oracle/pms/dbs/rbsPMS03.dbf' size 50M;
(2) 2번의 조회를 확인한 결과, 오류가 발생한 ROLLBACK SEGMENT의
RSSIZE보다 큰 RSSIZE를 가지는 ROLLBACK SEGMENT가 존재하여 이
TRANSACTION을 수행하기에 충분하다고 판단되는 경우 그 ROLLBACK
SEGMENT를 지정하여 사용할 수 있다.
RSSIZE가 매우 큰 ROLLBACK SEGMENT가 R04라고 할 때, SQL*PLUS 상에서는
다음과 같이 하면 된다.
SQL> set transaction use rollback segment R04;
(FORMS나 STORED PROCEDURE에서 지정하는 방법은 BULLETIN 10063번을
참조한다.) 일반적으로 BATCH JOB과 같이 ROLLBACK SEGMENT가 많이
필요한 TRANSACTION에 대해서는 INITIAL과 NEXT가 크게 지정되어 있는
큰 크기의 ROLLBACK SEGMENT를 지정하여 사용하는 것이 효율적이다.
(3) 2번의 조회에서 획인된 INITIAL_EXTENT와 NEXT_EXTENT가 작아 실제로는
RBS의 FREE SPACE가 남아 있는데도 MAXEXTENT에 도달하여 ORA-1652가
발생하는 경우 ROLLBACK SEGMENT의 INITAL과 NEXT를 크게하여 ROLLBACK
SEGMENT를 재생성할 수 있다.
(이 방법은 BULLETIN #10072에 자세히 나와 있다.)
참고) 각 TABLESPACE 별 FREE SPACE는 다음과 같은 방법으로 확인 가능하다.
sqlplus system/manager
SQL> select tablesapce_name, sum(bytes), max(bytes)
from dba_free_space
group by tablespace_name;
(4) 이러한 ROLLBACK SEGMENT에 관한 오류는 ROLLBACK SEGMENT가 일단
필요한 SPACE를 확보한 이후에는 그 크기가 줄어들지 않는 것이 그
원인이 될 수도 있는데 이때 ROLLBACK SEGMENT에 OPTIMAL SIZE를
지정하면 어느 정도의 해결이 가능하다.
OPTIMAL을 지정하면 ROLLBACK SEGMENT가 그 크기 이상으로 증가되는
경우, 이후에 OPTIMAL로 지정된 크기만 유지하도록 ROLLBACK SEGMENT가
줄어들게 된다.
이 때, 이 값을 너무 작게 잡으면 빈번하게 ROLLBACK SEGMENT가 늘어나고
줄어드는 작업으로 인해 PERFORMANCE에 지장을 초래할 수 있으므로
20 ~ 30개의 EXTENT 정도를 보유할 수 있도록 한다.
OPTIMAL의 지정 방법은 ROLLBACK SEGMENT 생성 시나 그 이후에 다음과
같이 하면 된다.
sqlplus system/manager
SQL> create rollback segment r01
tablespace rbs
storage (initial 1M next 1M optimal 20M) ;
이미 생성된 rollback segment에 대해서는,
SQL> alter rollback segment r01 storage(optimal 20M);
Oracle은 다음과 같은 규칙에 의해 각 Transaction이 사용하는 Rollback
segment를 결정한다.
1. Active Transaction의 수가 가장 작은 Rollback Segment에 할당한다.
2. 만약 1의 조건에 만족하는 Rollback segment가 하나 이상이면, 가장
마지막에 할당된 rbs 다음의 rbs를 할당한다. 이것은 undo 기능을 더
오래 지속시키도록 해 준다.
그러나 Application이 큰 rollback segment가 필요하다면 위와 같이
자동적인 rollback segment의 할당이 아닌 Manual하게 특정 rollback
segment를 할당 가능하다.
다음의 방법을 사용한다.
a. 큰 rollback segment를 만든다. 큰 rollback segment 를 만들기
위해서는 rbs tablespace에 영역이 충분히 커야 하므로 필요할 경우
tablespace를 확장하는 작업을 한다.
$ sqlplus system/manager
sql> alter tablespace rbs add datafile '?/rbs1SID.dbf' size 100m;
sql> create rollback segment big_rbs storage(initial 10m next 10m)
tablespace rbs;
sql> alter rollback segment big_rbs online;
b. Transaction 를 assign 한다.
SQL> set transaction use rollback segment big_rbs;
....
exec sql commit work;
exec sql set transaction use rollback segment big_rbs;
dbms_transaction.use_rollback_segment('big_rbs');
이와 같이 지정하면 이후에 commit 또는 rollback이 일어날 때까지
한 transaction에만 해당된다.
[한국 Oracle 자료]
'wif LiNoUz > Oracle,Sql' 카테고리의 다른 글
| wait event 관련 (0) | 2014.06.12 |
|---|---|
| Code Colorful (0) | 2014.06.12 |
| 오라클 인덱스 관련 (0) | 2014.06.09 |
| 롤백세그먼트 관리 (0) | 2014.06.02 |
| 롤백세그먼트 관련 문서 (0) | 2014.06.02 |