‘oracle’ 태그가 지정된 글

좋은 자료 찾았다.
ORA00054 발생시 참고하면 좋은 문서.

 

출처 :  http://arjudba.blogspot.kr/2009/01/ora-00054-resource-busy-and-acquire.html

 

Problem Description
In my production database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below.

SQL> alter table student add b number;
alter table student add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Description of the Problem
Let’s try to produce the problem in our development environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it.
SQL> create table a (a number);

Table created.

SQL> insert into a values(1);

1 row created.

I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce.

In another session,
SQL> alter table a add b number;
alter table a add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> lock table a in exclusive mode nowait;
lock table a in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Cause of the Problem
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it,

-Re run the DDL at a later time when the database become idle.
or,

-Kill the sessions that are preventing the exclusive lock.
or,

-Prevent end user to connect to the database and then run the DDL.

You have different views to see locking information about the table.
1)DBA_BLOCKERS: Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting. In our scenario this view will not help.

2)DBA_DDL_LOCKS: It lists all DDL locks held in the database and all outstanding requests for a DDL lock.

3)DBA_DML_LOCKS: It lists all DML locks held in the database and all outstanding requests for a DML lock.
If you query from it in the mode_held field you will see ‘row exclusive lock’.
SQL> select mode_held from dba_dml_locks where owner=’MAXIMSG’;

MODE_HELD
————-
Row-X (SX)

4)DBA_LOCK: It lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

5)DBA_LOCK_INTERNAL: It displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

6)DBA_LOCKS is a synonym for DBA_LOCK.

7)DBA_WAITERS: Shows all the sessions that are waiting for a lock.

8)V$LOCK: It lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

9)V$LOCK_ACTIVITY: This view is deprecated.

10)V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.

In order to see locked object query,

SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/

USERNAME             SESS_ID    OBJECT                    OBJECT_TYPE         STATUS       MODE_HELD
-------------------- ---------- ------------------------- ------------------- ------------ ----------
MAXIMSG (A)          142,232    MAXIMSG.A                 TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T                     TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T1                    TABLE               Not Blocking Row-X (SX)

Here we see there is 3 types of locking. In our case the object is A. And we see the sid=142 and serial#=232 is preventing us from locking the table in exclusive mode. So to achieve our task we can kill the session as below.

Advertisements

오라클 데이터베이스를 운영하다보면 종종 cursor: pin S wait on X 이벤트가 계속 발생하는 경우가 있다. 그저 그때 대처할 수 있는 거라곤 해당 대기 이벤트들을 KILL 해주는게 전부이지만 근본적으로 해당 mutex 를 가지고 놓아주지 않는 세션을 잡아서 KILL 해 주는게 제일 좋다.

(더 보기…)

Transaction Monitoring SQL

게시 날짜: 2011/10/11, 카테고리: ADMIN, ORACLE
태그:, ,

트렌젝션 모니터링 스크립트를 작성해 보았다. 예전 오라클용 오렌지 툴을 사용할때 편하게 사용했었던 트랜젝션 모니터링 기능을 조금 따라해 보기 위해 작성해 보았다. 꽤 긴 시간동안 돌아가는 트랜젝션을 발생시켰을 때 이 트랜젝션 모니터링 기능은 어떻게 트랜젝션이 잘 돌아가는지 확인하는데 꽤 유용했었던 것으로 기억한다.

(더 보기…)

Role Transition on physical standby database

게시 날짜: 2011/10/02, 카테고리: DGA, ORACLE
태그:, , , , ,

SWITCHOVER

Switchover 는 일반적으로 계획적으로 시스템을 다운시킬 시 수행하는 방법이다.  OS 나 HW 의 업그레이드, 혹은 오라클 데이터베이스 소프트웨어에 대한 롤링 업그레이드 혹은 패치셋을 적용하기 위해 사용한다. (더 보기…)

Full UNDO Tablespace

게시 날짜: 2011/09/23, 카테고리: ORACLE, Ref
태그:, ,

OTN에서 undo 와 관련된 좋은 자료가 있어서 퍼옵니다. 보기 좋게 하기 위해 정렬 및 수정을 좀 하였습니다. Undo 의 할당 과정과 공간에 관한 기본 지식으로 운영시 참고하면 좋을 자료 같습니다. 원본 주소는 밑에 추가하겠습니다.

(더 보기…)

SERVER GENERATED ALERT

게시 날짜: 2011/09/20, 카테고리: ADMIN, ORACLE
태그:, , ,

DBMS_SERVER_ALERT 패키지를 이용하여 특정한 이슈에 관하여 Threshold 를 설정하여 이를 위반하였을 시 경고를 할 수 있게 설정할 수 있다. (더 보기…)

ASSM & LMT.

게시 날짜: 2011/09/08, 카테고리: ADMIN, ORACLE
태그:, ,

오라클을 배우면서 아직도 ASSM 과 LMT 에 대한 개념이 햇갈린다. 자꾸 깊게 파고드려는 욕심때문에 (그리고 종국엔 대충 읽어버리는 바람에) 잊어먹고 머리속에서 햇갈리게 된다. 그래서 이번엔, 아주 기초적인 구분과 개념 정리만 하려 한다. 참고 자료는 당연히 오라클 Text book. 10g Administration Workshop I 이다. 일단은 여기에 소개된 내용 그 이상은 알려하지 않는게 좋을 것 같다.

ASSM (Automatic Segment Space Management)

ASSM은 테이블스페이스의 세그먼트를 관리한다. ASSM 의 반대는 FLM(Free List Management) 이다. 내가 종종 햇갈려 했던 부분이 세그먼트의 자동 관리라는 부분인데, 결국 관리되는 대상을 살펴보면 Block 이다. 용어를 그대로 읽으면 ‘세그먼트 공간 자동 관리’ 이지만 실제로 관리되는 부분이 ‘블록’이기 때문에 자꾸 이 개념이 혼란스러웠던 듯 싶다. 즉 하나의 블록에 여유공간이 얼마나 있고, 해당 블록을 재사용 해도 되는지 혹은 아직 사용할 수 없는지 등을 관리하는 메커니즘이란 것이다.

FLM  이든 ASSM 이든 어쨌든 블록을 관리하기 위한 첫 정보는 Segment Header 에 위치하게 된다. 기본적으로  FLM은 Freelist 의 갯수를 1개를 기본으로 가져간다. 하지만 storage 옵션을 이용하여 Freelist 의 갯수를 직접 사용자가 조정할 수 있다는 것이다.

CREATE TABLE xxx (xxx varchar2(10)) STORAGE (freelist n); — where n is number

세그먼트의 크기가 커지면 그만큼 Freelist 의 크기도 커질테고, 그렇게 되면 Freelist 의 경합도 그만큼 늘어나게 될 것이다. 따라서 Freelist 방식을 사용할 경우 DBA가 경합을 최소화 해 주기 위한 노력을 해 주어야 한다.

딱 여기까지만 개념을 잡도록 하자. 그럼 ASSM 은 어떻게 관리가 되나. 물론 이 ASSM 도 깊게 들어가면 어렵다. (물론 쉬운건 없다..-_-;) 그래서 여태 개념조차 햇갈려 했으니 FLM 만큼 아주 간단하게 개념만잡자.

ASSM 을 사용할 경우엔 더이상 DBA가 Freelist 등을 관리할 필요도, 관리할 수도 없다. ASSM은 비어있는 블록의 사용량을 비트맵 (Bitmap)으로 관리를 한다. Bitmap을 사용하므로 당연히 속도가 빠르다. 또한 PCTUSED 옵션을 사용하지 않는다.

ASSM은 세그먼트를 관리하기 위해 3단계의 Bitmap Block 을 사용한다. L1BMB(Bitmap Block)는 각 블록의 여유도 상태를 관리한다. L2BMB 는 L1BMB의 목록을 관리하며 L3BMB는 L2BMB의 목록을 관리한다. 하나의 L1BMB는 세그먼트의 크기에 따라 16 개에서 1024개의 블록 상태를 관리한다. L3BMB는 세그먼트 헤더에 존재하며 세그먼트의 크기가 지나치게 커서 세그먼트 헤더에 관리가 불가능할 경우 별도의 물리적 공간에서 관리된다.

오라클은 L3->L2->L1 의 순서로 비어있는 블록을 찾게 된다. ASSM에 대해 좀더 알고 싶을 경우 엑셈 오라클 백과사전을 참조하는 것이 가장 좋을 듯 싶다.

LMT (Locally Managed Tablespace)

LMT와 DMT의 큰 차이점 역시 Bitmap 을 이용한 부분이다. ASSM 과 함께 마찬가지로 이 LMT 를 그냥 그대로 읽을 경우 “지역관리 테이블스페이스”이다. 테이블스페이스란 단어가 들어가 혼란스러운데 자세히 보면 LMT 란 것은 테이블 스페이스에 추가되는 Extent 들을 어떻게 할당하고 삭제하는지에 대한 방법이다.

  • Locally managed tablespaces: 테이블스페이스에서 익스텐트를 비트맵으로 관리한다. 익스텐트를 새로 할당하거나 블록을 재사용 할때 오라클 서버는 블록의 새로운 상태를 비트맵 값을 변경한다.
  • Dictionary managed tablespaces: 익스텐트는 딕셔너리 테이블에 의해서 관리된다. 익스텐트가 새로 할당되거나 삭제될때 데이터 딕셔너리에 있는 관련 테이블을 업데이트 한다. 전 버전의 호환성을 위해 존재하며 LMT 사용을 권장한다.

LMT를 사용할 경우의 이점을 Text Book 에서는 다음과 같이 설명하고 있다.

  • 공간관리를 위한 재귀 호출을 피할 수 있다. 이는 딕셔너리 테이블에서 발생되며 익스텐트를 추가하거나 할당하는 모든 테이블 스페이스와 관련하여 발생한다.
  • 빈 공간을 딕셔너리에 기록하지 않음으로 딕셔너리 테이블에 대한 경합을 감소시킨다.
  • 지역 관리되는 익스텐트는 이웃된 빈 공간을 자동으로 탐색하고 비어있는 익스텐트들을 자동으로 병합한다.
  • 지역관리 되는 익스텐트의 크기는 오라클에 의해 그 크기가 자동으로 설정되게 할 수 있다.
  • 익스텐트의 정보를 가지고 있는 비트맵이 변경된다고 해서 언두가 생성되지 않는다. 이는 데이터 딕셔너리를 업데이트 하지 않기 때문이다. (테이블 스페이스의 쿼타 정보변경등 특별한 경우를 제외)

테이블스페이스를 DMT 에서 LMT 로 변경하고자 하면 다음 패키지의 프로시저를 이용한다.
exec dbms_space_admin.tablespace_migrate_to_local(tablespace=>’USERS’);

반대로 LMT 에서 DMT 로 변경하고자 할 경우
exec dbms_space_admin.tablespace_migrate_from_local(tablespace=>’USERS’);

CREATE TABLESPACE space_name DATAFILE 'datafilepath/datafilename' SIZE x M 
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO