231214 Linux_UNDO

■ UNDO 

1. DML 작업 시에 이전 값을 저장하는 공간(필수)

2. 트랜잭션이 종료(commit, rollback)될 때 까지는 이전 값을 보존해야 한다.

3. 목적

     1) rollback 

     2) read consistent (읽기 일관성)

     3) flashback query

     4) 실패한 트랜잭션 recovery 

4. undo 값은 AUTO 로 진행해야 한다.

 

# undo monitoring 

1. undo space 가 부족한 경우(트랜잭션 작업할 경우) ORA-01650 : unable to extent rollback segment

2. 읽기 일관성이 어긋났을 경우, long query 문에서 ORA-01555 : snapshot too old

3. 트랜잭션 작업이 늦어졌을 경우 enq : US - contention  wait event

 

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
undo_management               string      AUTO
undo_retention                      integer    900       (초단위)       

undo_tablespace                   string      UNDOTBS1(UNDO TABLESPACE 명)

 

1) undo_retention  integer    900  :  read consistent (읽기 일관성), flashback query 를 900초동안 보존.

- 트랜잭션이 종료가 되었더라도 이전 값을 undo_retention  파라미터에 설정되어 있는 초시간 까지 보존하자

SELECT *
FROM v$parameter
WHERE name = 'undo_retention';

 

# 변경작업

- read consistent (읽기 일관성), flashback query  를 같이 보장하겠다는 의미

ALTER SYSTEM SET undo_retention = 1800;

 

# 트랜잰셕이 종료 되었더라도 이전 값을 undo_retention 꼭 보장하자

# undo_retention의 이전 값을 보장하기 위해서 새로운 트랜잭션이 실패할 수 도 있다.

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

 

# undo segment 정보 확인

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

 

 

# hr session 

SELECT * FROM hr.emp;

UPDATE hr.emp
SET salary = salary * 1.1
WHERE employee_id = 100;

 

# putty session01 로 DML 작업

 

# putty session02 로 DML 작업

 

# 현재 트랜잭션 중인 작업을 보는 방법

xacts = 트랜잭션 중인 확인방법

- 트랜잭션 하려면 offline 해야 한다.

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

username : 사용된 session 계정

xidusn : undo segment 번호 - segment_id (dba_rollback_segs 의 컬럼)

ubafil : datafile 번호 - file_id (dba_rollback_segs 의 컬럼)

ubablk : data id 번호 

used_ublk : block 번호 - block_id(dba_rollback_segs 의 컬럼) ( undo 공간 부족해 질 수 있다. )

SELECT * 
FROM dba_rollback_segs;

 

system : 딕션리 갱신할 경우 

 

# ROLLBACK; 할 경우 

 

# putty 에서 rollback 실행 

 

SELECT * FROM dba_data_files;

 

# UNDO 공간 확보하기

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 
'/u01/app/oracle/oradata/ora11g/undotbs02.dbf' SIZE 10M AUTOEXTEND ON;

SELECT * FROM dba_data_files;

 

# 새로운 undo tablespace 생성한 후 지정

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/app/oracle/oradata/ora11g/undo1.dbf' SIZE 5M
AUTOEXTEND ON NEXT 2M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

 

# UNDO 생성시 

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/app/oracle/oradata/ora11g/undo1.dbf' SIZE 5M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO

 

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/app/oracle/oradata/ora11g/undo1.dbf' SIZE 5M AUTOEXTEND ON;

# 생성 확인

SELECT * FROM dba_tablespaces;

 

SQL> show parameter undo_tablespace

 

# 새로운 undo tablespace 지정

ALTER SYSTEM SET undo_tablespace = undo1;

SQL> show parameter undo_tablespace

NAME                                   TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                   string      UNDO1

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

PENDING OFFLINE 일 경우, 기다려준다.

 

SELECT * FROM dba_rollback_segs;

 

 

# OFFLIEN 으로 확인이 되었다면, 삭제 가능하다.

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;