Oracle 에러 조치 사항

====================================================================== ORA-00030 : ALTER SYSTEM KILL SESSION에 대하여 ====================================================================== ◈ 현상 사용자는 다음과 같은 상황에서 session 을 kill 하려는 시도를 하게 된다. 1. os 에는 process 가 존재하지 않지만, v$session 에는 active 로 존재하고 있을 경우 2. shadow process 는 살아 있는데, client machine 을 rebooting 한 경우 3. session 이 걸고 있던 lock 을 release 해야 할 경우 4. OS 나 Oracle 의 자원을 지나치게 많이 사용하여 성능을 저하시키는 process 그런데, alter system kill session ('sid, serial#'); 후에 다음과 같은 에러가 발생할 경우가 있다. ora-00030, 00000, "user session ID does not exist" // *Cause: The user session id no longer exists, probably because the // session was logged out. // *Action: Use a valid session ID. ◈ 원인 kill session을 할 수 없는 이유는 PMON이 이미 이 session을 delete하고 있는 중이기 때문이다. 즉, PMON 이 dead session 을 clean-up 하고 있는 중에는 serial number의 값이 증가한다. 문제는 PMON이 process를 kill하는 시간인데, transaction의 크기에 따라, PMON의 rollback 시간이 결정된다. 먼저 PMON은 dead process를 찾아내어, 이 process가 사용한 resource 를 release하는 시도를 한다. PMON은 계속 이 작업을 시도하다가 마침내, free buffer의 부족으로 더 이상 resource를 free-up 하지 못하게 된다. 이 때, 이 process를 delete하고 있다는 message를 trace file에 출력하는데, 이것은 process를 delete하는 데 필요한 resource(data cache 내의 free buffer)의 부족으로 위의 작업이 지연되고 있다는 의미이다. ◈ 조치 PMON이 process 를 clean-up 할 때 걸리는 시간은, 5분에서 24 시간까지 소요될 수 있다. 문제는 이 process가 hold 하고 있는 lock으로 인해 특정 작업이 수행되지 못하는 데 있다. MTS 를 사용할 때는 configuration MTS setting, sqlnet.expire_time 사용)에 따라 다르지만, clean-up 작업을 하는데 72 시간 이 소요된 경우도 있다. 아직까지는 PMON이 작업을 마칠 때까지 기다리는 방법 또는 db를 restartup하는 방법 밖에는 없다. --- PMON 의 작업 PMON은 network failure 나 기타의 원인으로 생긴 old process connection을 clean-up하는 역할을 한다. 그런데, PMON 은 clean-up 해야 하는 connection 중에 정해진 개수 만큼의 transaction 을 rollback 할 수 있는데, 이 값은 initSID.ora 의 cleanup_rollback_entries(default = 20) 에 의해 결정된다. 예를 들어, 1000 개의 uncommitted update가 있다면, 일정한 시간마다 cleanup_rollback_entries의 개수 만큼의 record만 rollback 할 수 있으므로 이 작업 동안에 lock 은 그대로 유지된다. PMON 은 위의 작업 이외에 DB maintenance 역할이 있으므로, 위의 rollback 이 비교적 빠르게 처리 되지 못할 수도 있다. 이러한 rollback을 빠르게 처리하기 위하여 cleanup_rollback_entries 를 늘릴 수도 있다. 그러나, 그 만큼 일정시간 동안 PMON의 작업이 많아지게 되므로, 다른 사용자들의 작업 요청이 느려지게 되는 trade-off가 있으므로, 신중히 고려한 후에 수정하는 것이 바람직하다. alter system kill session 에 의해서도 위와 같이 rollback 이 이루어지는데, 이 session 이 완전히 clean-up 되기 전까지 v$session, v$process에 남아 있게된다. --- ALTER SYSTEM KILL SESSION 을 하기 전에 ... kill session 을 원할 경우는 다음의 순서대로 작업하는 것이 좋다. 1. kill the user process first 2. wait for 3 - 4 minutes 3. query v$session 4. if any information find in v$session, query v$lock like select count(*) from v$lock where SID ='sid'; 위의 count(*) 가 0 이 아니라면, 아직 PMON 이 rollback을 끝내지 못한 경우이므로 다시 얼마후에 v$lock 을 조회하여 lock 의 개수가 감소하였는지 반복적으로 확인한다. 만약, 이 값이 전혀 변하지 않았다면, ALTER SYSTEM KILL SESSION 을 수행하고 v$session, v$lock을 query 하여 변화가 있는지 확인하여 변화가 있다면, 좀 더 기다린다. 그래도, v$lock 의 count(*) 가 0 이 되지 않을 경우, 마지막으로 수행할 수 있는 유일한 방법은 instance 를 restartup 하는 것이다.   ====================================================================== ORA-00054 : TABLE에 TRANSACTION이 종료되지 않은 경우 ====================================================================== TABLE 을 DROP 하려고 할때 그 TABLE에 TRANSACTION이 종료되지 않아 ORA-54 ERROR가 나오는 경우가 있다. DB를 RESTART하면 되지만 더 효율적인 해결 방법은 다음과 같이 할수 있다. * 참고 : Serial Number 가 Negative 인 경우 그 값에 65536 을 더해야 함. rem sqlplus system/manager rem rem prompt Enter table name accept tname CHAR col type format a6 col object_name format a20 select a.sid,a.serial#,b.type,c.object_name 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=upper('&amp;tname'); Prompt Enter session ID(SID) ? accept sid Prompt Enter serial number(serial#) ? prompt -- if serial number < 0, prompt -- then serial number #=Serial number + 65536 accept serial alter system kill session '&amp;sid,&amp;serial' ================================================================================== ORA-00060 : DEADLOCK과 INITRANS (같은 TABLE내의 다른 범위의 DATA처리시 ORA-60) ================================================================================== deadlock에 관한 일반적인 사항은 <Bul:11742>에 정리되어 있다. 같은 data를 동시에 변경하는 transaction의 경우 deadlock이 발생하는 것은 application logic을 수정하여 해결해야 하는 경우가 대부분이다. 그런데 같은 table에 대해서 동시에 수행되는 transaction이 각자 서로 다른 data를 처리하는 경우에도 ora-60(deadlock detected while waiting for resource)이 발생할 수 있다. 예를 들어 한 transaction은 A table의 1월 data를 처리하고, 동시에 다른 transaction은 같은 A table의 2월 data를 처리하는 것과 같은 경우이다. 이러한 경우에도 initrans가 작게 설정되어 있으면, ora-60이 발생할 수 있는데, 이 자료에서는 이와 같이 다른 data를 처리하는 transaction들 사이에서의 ora-60이 발생하는 경우와 조치사항을 확인한다. 1. transaction entry에 대해서 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ table이나 index에 포함된 모든 block에 update/delete/insert와 같은 dml을 수행하기 위해서는 일단 그 block에 transaction정보를 저장시킬 transaction entry를 확보한 후에 원하는 작업이 수행가능하다. 이 transaction entry의 크기는 os dependent하기는 하나 대부분 23 bytes이며, table이나 index의 initrans option에 의해, 미리 확보되는 block당 transaction entry의 갯수가 결정된다. default는 table이 1, index가 2이다. 이 transaction entry가 특정 transaction에 할당되면 그 transaction이 commit이나 rollback되기 전까지는 다른 transaction에서 사용할 수 없다. 같은 block에 다른 transaction이 dml을 수행하려면, 남은 공간중에서 23 bytes의 transaction entry를 새로 할당하거나, 공간이 없으면 앞에서 먼저 사용중인 transaction이 commit/rollback되기를 기다려야 한다. 2. ORA-60이 발생하는 경우 ~~~~~~~~~~~~~~~~~~~~~~~~~ deadlock을 유발시키는 transaction이 서로간에 완전히 다른 data(같은 table)를 처리하더라도 그 data가 같은 block에 함께 들어가 있는 경우라면 ORA-60이 발생할 수 있다. 즉, transaction entry를 잡는 과정에서 block내에 남은 space가 부족한 경우, 서로 상대방의 transaction이 종료되기를 기다리는 deadlock이 발생가능하다는 것이다. 아래에 실제 예를 들어 자세한 발생 시나리오를 정리하였다. ① ORDER table은 날짜별로 data가 추가, 변경, 삭제되는 100만건 이상의 data를 가진 table이다. 이 table에 대해서 월별로 통계작업을 수행하는데, 6개월씩 처리하기 위해 6개 transaction을 동시에 수행하였다. 즉, T1은 1월 data, T2는 2월 data, T6는 6월 data를 처리하는 식이다. ② ORDER table은 initrans값이 1로 지정되어 있고 현재 1000개의 block이 이 table에 할당되어 있다. ③ 100번지 block에 2월, 3월, 5월 data가 함께 저장되어 있다. 200번지 block에는 2월, 3월 data가 저장되어 있다. ④ T2 transaction이 100번지 block에 이미 확보되어 있는 1개의 transaction entry를 사용하여 transaction정보를 저장하였다. 그리고, 2월달 data에 대한 작업을 수행하였다. ⑤ T3 transaction이 200번지 block에 initrans 1에 의해 확보되어 있는 23 bytes의 transaction entry를 이용하여 transaction정보를 저장한 후 3월달 data에 대한 처리를 수행하였다. ⑥ T2 transaction이 2월달 data중 나머지 부분을 처리하기 위해 200번지를 access하여 23 bytes의 T2 를 위한 transaction entry를 확보하려고 하였으나, block에 남은 공간이 없어서, T3 transaction이 commit할때까지 기다린다. 5번 단계에서, initrans에 의해 미리 확보되어 있는 공간을 사용하는 T3 transaction이 종료되면, 그 부분을 T2가 사용할 수 있게 되는 것이다. ⑦ T3 transaction도 100번지에 있는 3월 data를 처리하기 위해 100번지내에 transaction entry를 추가적으로 확보하려 하였으나, 공간이 없어서, 마찬가지로 미리 100번지 block을 사용하고 있는 T2 transaction이 종료되기를 기다리게 된다. ⑧ 6과 7상황에 의해 T2와 T3 transaction은 서로 상대방이 종료되기를 기다리는, deadlock이 발생하게 되므로 deadlock상황을 유발시킨 T3 transaction이 ORA-60을 발생시키면서 종료 되고, T3 transaction은 rollback된다. 200번지에 확보한 transaction entry부분도 반환하여 다른 transaction이 사용가능한 상태가 된다. ⑨ 8번에서 release된 200번지 block내의 transaction entry 23 bytes를 6번 단계에서 기다리고 있던 T2 transaction이 확보하고 작업을 진행한다. 3. deadlock을 피하기 위한 방법 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 결론적으로, 같은 table에 대해서 다른 data를 처리하는 transaction이라 하더라도 동시에 수행하는 transaction이 많은 경우라면 initrans 값이 작은 경우 ora-60이 발생할 수 있게 된다. 하나의 table에 대해서 performance등의 이유로 동시에 다른 data를 처리하는 transaction을 수행하고자 한다면, table의 initrans 값을 크게하여, 하나의 block에 여러 개의 transaction이 dml 처리를 수행하더라도 space가 부족하여 기다리는 상황은 없도록 하여야 한다. initrans를 n으로 지정한다면 23*n bytes가 항상 transaction entry로 미리 확보되어 있는 것이다. 이렇게 transaction entry로 초기에 확보된 공간은 data를 저장할 수 없는 공간이 되므로, 너무 크게 하는 것은 space 낭비가 초래된다. 하나의 block에 대해서 동시에 여러 transaction이 처리되지 않는 경우라면 미리 확보된 transaction entry는 사용도 되지 않고 낭비되어, full table scan 등의 작업에 성능 악화만 초래하게 된다. initrans값은 하나의 table에 대해서 동시에 처리하는 transaction의 갯수 이하로 지정하도록 한다. 해당 table에 대한 동시 transaction의 갯수만큼 initrans를 지정하면 앞에서 설명한 상황의 deadlock은 발생하지 않는 것이 보장되나 space를 고려할 때 그 보다는 약간 작게 하는 것이 일반적인다. initrans는 table이나 index에 대해서 create나 alter문장시 지정, 변경이 가능하나, alter의 경우 이미 확보된 block에는 영향을 미치지 못하므로 export/import를 이용하여 새로 지정하는 것이 필요하다. 다음에 scott.dept table에 대해서 예를 들었다. column정의 storage등은 임의의 값을 예로 사용한 것이며, initrans도 예로 3을 지정하였다. os> exp scott/tiger file=test.dmp tables=dept os> sqlplus scott/tiger SQL> drop table dept; SQL> create table dept (deptno number(2), dname varchar2(10)) initrans 3 storage(initial 10m next 2m pctincrease 0); os> imp scott/tiger file=test.dmp tables=dept ignore=y ====================================================================== ORA-00210 : TABLE에 TRANSACTION이 종료되지 않은 경우 ====================================================================== Sequent Symmetry or NUMA-Q platform의 Oracle 7.3.2, 7.3.3, 7.3.4 OPS Product 설치 및 DB 생성후 Master node startup parallel 후 다른 node를 startup parallel 시 다음과 같은 현상이 발생할 경우. ◈ 현상 SVRMGR> startup parallel ORACLE instance started. Total System Global Area 546275472 bytes Fixed Size 39108 bytes Variable Size 210167756 bytes Database Buffers 327680000 bytes Redo Buffers 8388608 bytes ORA-00210: cannot open control file '/dev/vx/rdsk/oracle/v_ctl1' ORA-07368: sfofi: open error, unable to open database file. SEQUENT DYNIX/ptx Error: 16: Device busy 발생하면서 startup fail 발생 ◈ 원인 Sequent Symmetry or NUMA-Q platform이 very large file (O/S에서 2GB 이상의 file system 지원)을 지원하기 위해 VLFS patch를 적용했거나 VLFS를 이미 지원하는 O/S Version일 경우 오라클 master node가 정상적으로 startup 되고 나서 다른 node가 startup parallel이 될 때 먼저 startup 된 master node가 shared disk 의 모든 오라클 관련 file을 none-shared mode로 open 하기 때문에 위의 현상 이 발생됨 ◈ 조치 1) PTX/Cluster V1.3.2일 경우 * Oracle V7.3.x : O/S상에서 VLFS patch적용하지 않았을 경우는 관계 없으나, 이미 적용 되었다면 추가적으로 O/S patch FP#23373 적용하여야 함 2) PTX/Cluster running DYNIX/PTX 4.4.x 일 경우 * Oracle V7.3.3 : 현재 fix된 patch는 없으며 다음과 같은 workaround 방법으로 해결이 가능함. (Workaround) $ORACLE_HOME/rdbms/lib/ins_rdbms.mk file에 아래의 추가된 부분만 삽입하여 오라클 kernel relink 실시 (예:make -f ins_rdbms ioracle) oracle: $(ORALIBD) $(CORELIBD) $(NETLIBD) $(KSMS) $(CONFIG) $(PSOLIBLIST) opimai.o @$(ECHO) $(LINK) -o $@ $(LDFLAGS) $(LDFLAGS_ORA) opimai.o $(CONFIG) \ -llkseqora \ ---> 추가된 부분 $(LLIBSERVER) $(LLIBORA) $(LLIBKNLOPT) $(LLIBSLAX) $(LLIBPLSQL) \ $(LLIBSICX) $(LLIBSOWSUTL) \ $(LLIBSICX) $(LLIBSOWSUTL) \ ........... ........... * Oracle V7.3.4 : Oracle V7.3.4 일 경우는 문제가 없으나 patchset을 적용할 경우 V7.3.4.2에서는 V7.3.3과 같은 방법으로 oracle kernel을 relink하면 문제가 해결됨. ====================================================================== ORA-00312, ORA-00313 : ONLINE REDO LOG CRASH ====================================================================== [ ONLINE REDO LOG가 손상되었을 때 DB에 OPERATION 이 없었던 경우는 다음과 같은 절차로 DB 을 OPEN 할 수 있다. - 확률 70% ] ------------------------------------------------------------------------- 1. CONTROLFILE 생성 -. 손상된 online log 는 포함시키지 않는다. -. resetlogs option 으로 생성한다. -. reuse option 은 생략하고 기존 controlfile 은 다른 이름으로 move 시킴. <V7 에서 CONTROLFILE 생성하는 방법> sqldba> startup mount sqldba> alter database backup controlfile to trace; 위와 같이 명령을 입력하면 ORACLE_HOME/rdbms/log 디렉토리에 트레이스 화일이다. 그 트레이스 화일에서 create controlfile 명령부분을 남기고 삭제한다. (7.3 이상에서는 cd $ORACLE_HOME cd ../../admin/SID dir/udump 에 있습니다) 콘트롤화일 생성 문장 예 - <cnt.sql> : GROUP 1 이 ONLINE LOG 라고 가정 -------------------------------------------------------------------------- CREATE CONTROLFILE DATABASE 'RC722' RESETLOGS NOARCHIVELOG MAXLOGFILES 32 ******** MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 8 MAXLOGHISTORY 800 LOGFILE GROUP 2 '/oracle/oracle/dbs/log2RC722.dbf' SIZE 5M, GROUP 3 '/oracle/oracle/dbs/log3RC722.dbf' SIZE 5M DATAFILE '/oracle/oracle/dbs/systRC722.dbf', '/oracle/oracle/dbs/rbsRC722.dbf', '/oracle/oracle/dbs/toolRC722.dbf', '/oracle/oracle/dbs/usrRC722.dbf', '/oracle/oracle/dbs/tempRC722.dbf', '/oracle/oracle/rcdata.dbf' ; 2.절차 $ sqldba lmode=y SQLDBA> connect internal SQLDBA> shutdown abort SQLDBA> startup nomount statement processed SQLDBA> @cnt SQLDBA> recover database using backup controlfile until cancel; .... ... CANCEL (Return) Recovery canceled SQLDBA> alter database open resetlogs; : 만일 정상적으로 open 되면 log file 추가 SQLDBA> alter database add logfile '?/dbs/log1ORA722.dbf' size 1M; ====================================================================== ORA-00600 : BLOCK 손상 해결 방법 (ORA-600[3339], ORA-600[3398], ORA-600[4519], ORA-1578) ====================================================================== ◈ 개요　 블럭의 손상 원인은 여러가지가 있는데, 메모리내의 블럭이 손상된 경우와 디스크상의 물리적인 블럭이 손상되는 경우가 있다. (1) 오라클 오류　 블럭 손상과 관계된 오라클 오류는 internal error인 ORA-600중 첫번째 인수가 3339,3398, 4519인 경우와 ORA-1578이 있으며, 이중 Ora-600[3398]은 메모리 블럭만 손상된 경우이고, ORA-1578은 물리적 블럭이 손상된 경우 발생한다. ORA-600[3339]와 ORA-600[4519]는 메모리와 디스크 손상 모두가 연관될 수 있다. 각각의 오류에 대한 자세한 설명은 아래의 3번에서 기술하였다. (2) DBA (database address)　 ORACLE 데이타 블럭은 블럭의 정보를 담은 Fixed Header를 갖고 있으며 이 정보를 이용하여 각각의 블럭과 데이타베이스 전체의 INTEGRITY를 유지한다. DBA는 Fixed Header의 한 부분으로 32bit 길이의 정수이며 데이타베이스의 화일 번호와 파일에서 블록위치를 나타낸다. 블럭 손상이 발생하면 오라클은 오류 메시지에 이 DBA를 나타내거나 파일 번호와 블럭 위치를 십진수로 나타내기도 한다. ◈ 블럭 손상의 원인　 오라클은 화일에 블럭을 읽고 쓸 때 lseek(), read(), readv(), write(), writev()와 같은 OS의 system function call을 이용한다. 블럭이 이러한 시스템콜을 이용하여 읽혀지거나 씌여진 후, 혹은 직전에 오라클은 DBA에 대한 검사를 하여 블럭이 손상되었는지 확인하게 된다. 그러나 대개 블럭의 데이타가 손상될 당시에는 그 사실이 드러나지 않다가 손상된 부분의 데이타가 사용될 때 비로소 손상된 사실이 알려지게 되므로 대부분 블럭 손상은 OS나 HW에 의해 손상된 블럭을 이후 오라클이 사용하고자 검사하는 과정에 발견하는 것이 대부분이다. 블럭 손상이 야기될 수 있는 주된 상황은 다음과 같이 요약될 수 있다. (1) ORACLE 블럭내의 첫번째 OS 블럭이 디스크상의 문제로 해서 이상이 있는 경우, OS 또는 디스크 복구 프로그램이 해당 블럭을 복구하려는 과정에서 블럭의 값이 모두 비정상적적으로 0이 되어 버릴 수 있다. (2) 매우 드문 경우이긴 하지만, 메모리상에서 이미 손상된 블럭을 디스크상에 그대로 기록하는 경우 물리적 블럭의 DBA가 틀리게 된다 (3) 블럭이 데이타 화일상에 기록될 위치를 잘못 찾아 손상이 발생하기도 하는데, 이러한 경우를 'write blocks out of sequence' 라고 불리운다. 이것은 ORACLE이 lseek()을 호출했을 때 OS가 블럭을 잘못된 곳에 기록해서 생기는 경우가 많다. 이러한 예는 4.2G를 넘는 큰 화일을 다룰 수 있는 기능을 제공하는 HW/OS의 경우 발생한다. 4.2G를 넘는 화일의 경우, 32bit unsigned number로 다룰 수 있는 범위를 벗어나기 때문에 OS는offset 값을 ORACLE이 사용할 수 있도록 적절히 변환시켜야 한다. ORACLE은 OS의 지원 여부와 관계없이 2G 이상의 화일을 지원하지 않으며 위와 같은 큰 파일을 다룰 수 있는 환경에서는 lseek() 시스템콜이 정확한 위치로의 변환을 시켜주지 못함에 따라 보다 작은 크기의 화일에서도 문제가 발생하는 경우도 있다. (4) 네번째 원인은 I/O기능이 전혀 작동하지 않는 경우이다. ORACLE은 lseek(), read() 시스템 콜이 리턴하는 에러 코드를 검사하며 read()가 읽어들인 바이트수가 BLOCK SIZE의 정수배인지를 검사한다. 이 검사를 통과하면 ORACLE은 성공적으로 READ가 수행되었다고 가정한다. 만약 DBA가 정확하지 않다고 체크되면 DATABASE에 대한 읽기 요구는 실패하기 때문에 실제의 블럭 읽기는 일어나지 않는다. (5) 다른 원인은 동일한 디바이스에서 다른 블럭을 읽어온 경우이다. 이것은 작업이 메우 바쁜 디스크에서 발생하곤 한다. 어떤 경우에는 수백개 이상 떨어진 곳의 블럭을 읽어오는 경우도 있다. 이러한 경우와 위의 (4)번의 경우에는 다시 한번 동작을 반복함으로써 문제는 해결 수 될 수 있으며 이것은 일반적으로 ORACLE의 문제가 아니라 OS나 HW의 문제인 경우가 많다. ◈ 블럭 손상과 관계된 오류의 종류와 가능한 조치방법 (1) ORA-600[3339]　 ORA-600[3339]에러는 ORACLE이 직접 버퍼로 데이타를 읽어들일 때 읽은 블럭의 DBA (Data Block Address)가 잘못되었음(INVALID)을 의미한다. 실제로 읽어들인 블록의 DBA와 ORACLE이 읽고자 하였던 블럭의 DBA가 다르면 위에서와 같은 에러가 발생하며 주로 OS나 HW의 문제가 그 원인이 되는 경우가 많다. 만약 메모리에 문제가 있다고 생각된다면 다음과 같은 Event Parameter를 initSID.ora에 추가함으로써 블록 검사를 할 수 있다. 이것은 ORA-600[3398], ORA-600[4519]에서도 마찬가지이나 이러한 event를 사용하기전에 먼저 한국오라클에 지원을 요청하는 것이 바람직하다. 　　event = '10210 trace name context forever, level 10' 　　event = '10211 trace name context forever, level 10' ORA-1578이 함께 발생하는 디스크 블럭 손상이 발생하였다면, 아래의 (4)의 ORA-1578해결 방법과 같이 조치 하면 된다. (2) ORA-600[3398] DBWR가 디스크에 데이타를 쓰기 전에 캐쉬에서 손상된 블럭을 발견하면 OERI(3398) 메시지를 출력하고 인스턴스를 정지시킬 것이다. 따라서 문제의 블록은 디스크에 저장되지 않으므로, 실제 디스크상의 블럭 손상은 야기시키지 않는다. 이때 DBA를 포함한 많은 인수들이 OERI(3398) 내부 에러 처리기에 전달되므로 이것을 확인하여 한국 오라클에 지원을 요청한다. (3) ORA-600[4519]　 메모리내의 블럭을 update/delete가 아닌 consistent read를 위해 읽고자 할때 블록이 손상됨을 발견한 경우, ORA-600[4519]가 발생한다. 이 오류는 메모리 손상과, 디스크 손상 모두의 경우 발생가능하며, 오류가 발생하는 즉시 한국오라클에 지원을 요청하는 것이 바람직하다. (4) ORA-1578 ORA-1578 에러는 ORA-600[3339] 에러와 함께 발생하곤 하며 디스크상에 물리적으로 블럭이 손상되었음을 의미한다. 이러한 디스크 블럭 손상의 복구방법을 살펴보자. (a)손상된 블럭을 포함하고 있는 세그먼트 확인 ORA-1578 에러가 발생하면 Corruption이 발생한 화일번호와 블럭번호를 알려준다. 여기서는 이 때의 파일번호를 f, 블럭번호를 b라고 부르기로 한다. 우선 해야할 일은 어떠한 오브젝트가 Corrupt되었는가를 알아내는 것으로써, 다음의 스크립트를 이용하면 알 수 있다. SQL> select segment_name, segment_type from dba_extents 　　　where file_id = f and b between block_id and block_id + blocks - 1; (b) 해당 세그먼트가 인덱스이면 Drop 시키고 다시 생성하면 된다. (c) 해당 세그먼트가 테이블이면 Corrupt 된 블럭의 데이타는 손상된 것이다.이렇게 테이블이 손상된 경우, 만약 해당 테이블이 들어있는　엑스포트 화일이 있다면 손상 된 테이블을 Drop 시키고 임포트 받는것이 제일 간단한 방법이다. 하지만 만약 엑스 포트 받은 화일이 없거나 백업해 둔 화일도 없다면 해당 테이블에 인덱스가 생성되어 있는 경우에 한해서 다음의 방법을 사용해서 복구를 하도록 한다. 예를 들어 다음과 같은 에러 메시지가 떨어졌다고 하자. 01578, 00000, 'ORACLE data block corrupted (file # 10, block # 4) <1> 먼저 (a)번의 script를 이용하여 손상된 블럭을 포함하는 세그먼트를 확인한다. 　SQL> select segment_name, segment_type　　 　 　　 from dba_extents　　 　　 　 where file_id = 10 and 4 between block_id and block_id + blocks - 1; SEGMENT_NAME SEGMENT_TYPE ------------ ------------ EMP TABLE <2> Rowid를 이용하여 손상된 블럭내의 데이타를 찾아낸다. 위의 결과값이 EMP table이 empno, ename, deptno 를 컬럼으로 가지며, empno 컬럼에 인덱스가 생성되어 있다고 하자.클러스터화되지 않은 모든 테이블은 유니크한Rowid를 가진다. Rowid는 총 18자로 블럭어드레스(8자), 점(1자), 로우 어드레스(4자), 점(1자), 화일 어드레스(4자)로 구성되어 있다. 이 rowid를 이용하여 다음과 같이 손상된 블록에 있는 employee 에 대한 empno를 구할 수 있다. 이때 empno가 char type 이라면 [where empno > 0] 대신 [where empno > ‘ ‘]를 사용하여 empno에 대한 인덱 스를 사용하도록 유도한다.　 SQL> select empno　　 from emp where empno > 0　 and rowidtochar(rowid) like '00000004.%.000A';　　 　　 EMPNO　　　　　 ROWID　　 　 　 ------------　 ------------------------------ 　　 500　　　　　　00000004.0000.000A 　　 501　　　　　　00000004.0001.000A <3> EMP 테이블과 같은 구조를 갖는 새로운 테이블을 만든다. SQL> create table temp　 as select * from emp　 where 1 = 2;　　 <4> 손상된 부분을 피해서 새로운 테이블에 손상된 테이블의 데이타를 추가한다. SQL> insert into temp select * from emp where empno < 500;　 SQL> insert into temp select * from emp where empno > 501; <5> 손상된 테이블인 EMP테이블을 Drop시키고 Temp테이블의 이름을 EMP로 변경한다. 그리고 백업된 자료나 문서자료를 통하여 손상된 부분에 대한 정보를　추가한다. SQL> drop table emp;　　 SQL> rename temp to emp; <6> 손상된 블럭에 대부분의 로우가　 존재하고 있다면 다음의 방법을 이용한다. 　SQL> create table empnos as　　 select empno from emp 　 where empno > 0　　 　　 and rowidtochar(rowid) not like '00000004.%.000A'; 이 스크립트를 이용하면 손상된　 블럭에 포함되지 않은 empno 들을 알 수 있다. 다음의 스크립트를 계속 실행시켜 복구를 한다. SQL> create table temp　 as select * from emp　 where 1 = 2; SQL> insert into temp select emp.empno, emp.ename, emp.deptno 　　　　 from emp, empnos 　　　　　where emp.empno > 0 　　　　　 and emp.empno = empnos.empno; <7> 만약 데이타 딕셔너리의 테이블이나 인덱스에서 손상된 블럭이 발생했다면 지원 을 요청해야 한다 ====================================================================== ORA-00604: 익스텐트가 가득 찬 경우 ====================================================================== 이 에러는 내부적으로 SQL명령이 실행될 때 발생한다. 예를 들어 현재 할당된 익스텐트가 가득 차서 다음 익스텐트를 할당 받으려고 할 때 오라클이 다음 익스텐트의 크기와 위치를 결정하기 위하여 SELECT명령을 내리게 되는 것과 같은 경우이다. * 이 문제가 발생하면 우선 alert.log 화일을 검사하여 ORA-600 과 같은 에러가 발 생했는가를 확인한다. ORA-600 에러가 발생했다면 오라클측에 지원을 요청 하도록 하고 그렇지 않다면 다른 원인을 검사해 봐야 한다. * 가장 먼저 고려할 사항은 init.ora 화일에 지정된 open_cursors의 크기를 알아보는 것이다. 이 값이 설정이 안되어 있으면 Default가 50이므로 open_cursors=255 와 같이 설정하도록 한다. 이 값은 단지 커서의 최대 값을 지정하는 것이므로 커서를 적게 쓰 는 프로그램에 아무런 영향을 끼치지 않는다. open_cursors를 변경하고 DB를 tdown 하고 Startup 시키면 된다. * 만약 이 방법으로 해결이 안되면 다음의 방법을 따른다. 정확한 에러의 원인을 찾기 위해서 init.ora 화일에 다음과 같은 라인을 추가한다. events = '604 trace name errorstack' 이렇게 init.ora를 변경하고 DB를 Shutdown 하고 Startup 하면 ORA-604 에러가 발생하는 경우에 자세한 정보를 Trace 화일에 기록해 주므로 이 화일을 검사하여 에러의 원인을 찾을 수 있다. * 에러의 다른 원인으로는 init.ora 화일의 파라미터 가운데 DC_FREE_EXTENTS나 ROW_CACHE_ENQUEUES의 값 이 너무 작게 설정된 경우를 생각해 볼 수 있다. 이와같은 경우는 이들 값을 크게 설정해 주도록 한다. * 테이블 스페이스가 가득 차거나 Extent 갯수의 최대 허용값을 초과해서 에러가 발생하는 경우 ORA-604 에러가 함께 발생할 수가 있는데 이와같은 경우에는 이들 문제를 먼저 해결하면 ORA-604 에러는 함께 해결 된다. ====================================================================== ORA-01046, ORA-01050, ORA-01051 : CONTEXT SIZE & CURSORS ====================================================================== 1. Context size 에 관련한 error message .ora-1046 :can't acquire space to extend context area. .ora-1050 :can't acquire space to open context area. .ora-1051 :maximum context area extents exceeded. 2. Context size란 무엇인가? (1) 쉽게 말하면 Cursor의 initial size이다 . 즉, Cursor 에 allocate 되는 user memory 이다. (2) 이는 init.ora 의 CONTEXT_SIZE 에 의해 결정된다. (3) Cursor 에 할당되는 additional space 는 CONTEXT_INCR 에 의하며 50 extents를 갖는다. (4) Recommended context size 와 increment 는 4096 bytes(4K) 이다. (5) SQL statement가 수행시마다 cursor 가 open 되며,같은 cursor가 reuse 되도록 design 되어 SQL*PLUS session은 2-3 개 이상 open 되어지지 않는다. 그러나 SQL*FORMS 는 여러 다른 task 를 수행하므로 많은 cursor를 open한다. (100 or more) (6)Cursor 가 hold 하는 item * the SQL statement * the parsed SQL statement * one row of the result 3 ORA-1051 은 무엇이 문제인가? (1) cursor 의 size 를 줄인다 (2) CONTEXT_SIZE,CONTEXT_INCR 를 늘린다. 4 OPEN_CURSORS 수를 줄이는 전략 . (1) Commit을 자주한다. (2) Synonym이나 view 를 사용하지 않음으로써 implicit cursor 수를 줄인다. (3) SQL*FORMS 에서 select 문대신 #COPY 로 바꿔 사용한다. (4) SQL*FORMS 에서 large forms를 여러개의 작은 forms 로 나눈다 (5) ASAP, EXEC SQL CLOSE C1; 을 수행한다. (6) HOLD_CURSOR=NO &amp; RELEASE_CURSOR=YES 를 사용한다. ====================================================================== LK FILE에 대하여 (ORA-1102에 대한 원인 설명) ====================================================================== > Unix 용 Oracle7.3.3 이전의 version에서는 parallel server mode(OPS)로 운용하지 않더라도 서로 다른 ORACLE_SID를 이용하는 두개의 instance가 하나의 database를 동시에 mount하는 것이 경우에 따라서는 가능할 수도 있었다 (<Bug:272030>). 이 경우, 서로 독립적인 두개의 instance가 동일한 database file들을 동기화 (synchronisation)없이 access할 수 있기 때문에 database corruption을 유발시킬 수 있었다. Unix system에서의 이러한 문제를 회피하기 위하여 7.3.3부터 'mount lock' file이 이용된다. 이 file은 그 size가 0 byte, 생성되는 위치는 $ORACLE_HOME/dbs 이며 그 이름은 lk<DB_NAME> 이다. Oracle이 database를 mount할 때 lk<DB_NAME> file과 관련하여 다음과 같은 절차를 수행한다. 1. file name의 'DB_NAME' 부분은 db_name parameter를 이용한다. 예를 들어, db_name=V803 이라면 사용되는 lock file의 위치와 이름은 '$ORACLE_HOME/dbs/lkV803'이 된다. 2. 만약 해당 file이 존재하지 않는다면 생성한다. 존재한다면 file을 open한다. 3. 이 file에 exclusive Unix file lock을 설정한다. 위의 과정 상에서 문제가 발생된다면 해당 instance는 db를 mount할 수 없으며 ORA-1102('cannot mount database in exclusive mode')가 return된다. Releasae 7.3.3 이전에서는 동일한 $ORACLE_HOME, 동일한 db_name을 이용하는 두 개의 database를 $ORACLE_SID만 다르다면 동시에 open하여 사용할 수 있었다. 그러나 lock file이 사용되는 7.3.3부터는, lock file의 이름에 db_name이 이용되기 때문에 어느 한쪽의 db_name을 변경해야만 동시에 open하여 사용할 수가 있다.(db_name의 변경은 controlfile을 재생성함으로써 가능하다.) 이렇게 database의 db_name이 변경되면 각각의 lk<DB_NAME> file을 생성하여 이용하게 된다. 참고 사항 -------- 1. database를 구동하고 있는 instance가 있을 때에는 lk<DB_NAME> file은 삭제하지 않도록 한다. 2. database가 shutdown되더라도 lk<DB_NAME> file은 삭제되지 않고 존재한다. 따라서 이 file의 유무를 가지고 database의 구동 유무를 판단할 수 없다. 3. lk<DB_NAME>의 DB_NAME은 SID와는 다를 수 있다. SID가 아닌 DB_NAME을 이용함에 주의. 4. lock file과 관련하여 다음의 error들이 발생될 수 있다. ORA-9992 scumnt: failed to open <FILENAME> ORA-9993 scumnt: failed to lock <FILENAME> ORA-1102 cannot mount database in exclusive mode ====================================================================== ORA-1118 조치 방법 : MAXDATAFILES와 DB_FILES PARAMETER ====================================================================== 테이블 스페이스를 만들거나 데이타 화일을 추가하다 보면 ORA-01118: cannot add any more database files: limit of XXX exceeded와 같은 에러가 발생하는 경우가 있다. 오라클의 데이타 화일의 최대 갯수는 MAXDATAFILES와　DB_FILES 에 의해서 제한을 받는데 여기서는 이 에러를 해결하는 방법을 알아보기로 한다. 　* 데이타베이스를 처음 만들 때 사용되는 CREATE DATABASE 명령에서 MAXDATAFILES 라는 파라미터를 찾아볼 수 있다. 여기서 지정된 값(명시를 안하면 디폴트로 설정)은 콘트롤 화일에 기록된다. 이 값은 데이타 베이스에 대해서 설정된 최대 데이타화일 갯수이다. 이 값을 변경하려 면 콘트롤 화일을 다시 만들어야 한다.　 　* 한편, init.ora(UNIX에서는 init<SID>.ora, VMS에서는 <node>_<ora_sid>_init.ora) 에는 DB_FILES라는 파라미터가 있는데 이 값은 해당 인스턴스에 대해서 지정된 최대 데이타화일 갯수이다. DB_FILES는 단순히 에디터 상에서 init.ora 화일을 수정한 다음 에DB를 Restartup 하면 새로운 값이 적용된다. 1. 왜 MAXDATAFILES 와 같은 한계값을 설정하는가?　 　 * O/S는 오라클 화일의 갯수를 지정하기 위하여 특정한 갯수의 bit를 사용하며 이 값은 Platform에 따라 다르다. MAXDATAFILES는 이 값의 영향하에 있게 된다. 일반적인 최대 값은 다음과 같다.　 V6 V7 UNIX 62 1022 VMS 254 1022 DOS 254 NA　 [참조]일부 유닉스 Platform상의 V7.0.16 이전 버전의 경우 1022보다 작은 경우도 있다. 2. 왜 MAXDATAFILES 값을 가능한 최대로 설정해 두지 않는가?　 　 * MAXDATAFILES 를 크게 지정하면 그만큼 콘트롤 화일의 크기도 늘어나기 때문이다. 3. 왜 DB_FILES를 MAXDATAFILES 의 크기만큼 크게 설정해 두지 않는가?　 　* DB_FILES를 늘리면 각 User Process 에 할당되는 PGA(Program Global Area)의 크기가 커지기 때문이다.　 4. 사용하는 시스템의 MAXDATAFILES를 알 수 있는 방법은?　 　 * 시스템에 따라서 다르므로 Installation Guide 를 참조해야만 한다.　 5. 콘트롤 화일의 위치를 아는 방법은?　 　 *　SQLDBA> show parameter control_files; 　　　　　 또는　 　　 SQLDBA> select * from v$controlfile; (7.0.16 이상) 　 위의 Query 를 이용하거나 $ORACLE_HOME/dbs/config.ora 화일을 보면 알 수 있다. 　[ORA-1118 해결방법]　 * ORA-1118 에러는 데이타 화일의 갯수가 MAXDATAFILES 값에 도달한 경우 발생한다. DB_FILES 값에 도달한 경우라면 ORA-59 에러가 발생한다. ORA-59 에러는 init.ora 의 DB_FILES 를 늘려주고 DB 를 Restartup 하면 해결 되지만 ORA-1118 에러는 이와같이 간단하게 해결되지는 않는다. 다음과 같은 방법이 있다. 1. 여러개의 데이타화일로 구성된 테이블 스페이스가 있으면 이를 Export 받고 테이블 스페이스를 Drop한 다음 하나의 큰 데이타화일을 갖도록 테이블 스페이스를 만들고 Import를 한다.　 2. V6.0.33 이전 버젼을 사용중이라면 MAXDATAFILES를 늘리기 위해서는 DB를 새로 만들어야 하며 그 이후 버전을 사용중이라면 콘트롤 화일을 새로 만들어서 MAXDATAFILES를 늘릴 수 있다. <V7에서 콘트롤화일을 만드는 방법> * DB가 mount 또는 open 된 상태에서 SQLDBA>alter database backup controlfile to trace;　　 와 같은 명령을 내리면 <user_dump_dest>에 지정된 디렉토리에 트레이스 화일이 하나 생긴다. 이 화일을 찾으려면 해당 디렉토리에서 가장 최근에 생긴 트레이스 화일을 찾으면 된다.　 * 이 화일을 다른 이름으로 복사한 다음 에디터로 열어서 CREATE CONTROLFILE 명령부분 외의 불필요 한 부분은 지우고 MAXDATAFILES를 늘려 준다. 이 화일의 이름을 newctl.sql로 하기로 한다.　 * DB를 NORMAL 또는 IMMEDIATE로 Shutdown하고,　콘트롤 화일 생성시 DB 영향을 줄 수 있기 때문에 만약을 위해서 DB 전체를 백업 받도록 한다.　 * 현재 사용중인 콘트롤 화일을 다른 이름으로 옮기고 다음을 실행한다.　 SQLDBA> startup nomount　　 SQLDBA> @newctl　　 SQLDBA> alter database open noresetlogs; * 이제 필요한 모든 작업은 끝났지만 여기서 다시 한번 Full Bakcup 을 받는 것이 좋다. [ 참고 ]　 다음은 콘트롤 화일을 생성하는 명령의 예이다. SQLDBA> CREATE CONTROLFILE 　 　　 DATABASE ORACLE 　　 　 LOGFILE '/users/oracle/dbs/log1ORACLE.dbf',　　　　　　　 '/users/oracle/dbs/log2ORACLE.dbf',　　　　　　　 '/users/oracle/dbs/log3ORACLE.dbf'　　　 NORESETLOGS　　 DATAFILE '/users/oracle/dbs/systORACLE.dbf',　　　　　　　 '/users/oracle/dbs/rbsORACLE.dbf',　　　　　　　 '/users/oracle/dbs/tempORACLE.dbf',　　　　　　　 '/users/oracle/dbs/toolORACLE.dbf',　　　　　　　 '/users/oracle/dbs/usrORACLE.dbf' MAXDATAFILES 121; </p> <p align="right"><a href="#top"><img src="../images/top.gif" width="33" height="37" border="0"></a></p> </td> </tr> <tr> <td width="578"> <p> ======================================================================<br /> <b><a name="ORA-01157">ORA-01157</a>, <a name="ORA-01110">ORA-1110</a></b> :OS 명령으로 DATAFILE을 삭제한 경우<br /> ======================================================================</p> <PRE><XMP> DATABASE RECOVERY에 앞서 ORACLE INSTANCE(즉, ORACLE RDBMS)의 STARTUP단계를 우선 살펴보기로 하자. 첫번째 단계로 INSTANCE를 START시키며, 여기서는 initORACLE_SID.ora 화일의 파라미터를 참조하여 SGA (SYSTEM GLOBAL AREA)를 할당하고 백그라운드 프로세스를 START 시킨다. 두번째 단계로 DATABASE의 MOUNT 이며 파라미터 화일에 명시된 CONTROL FILE을 오픈한다. CONTROL FILE로부터 DATABASE NAME과 REDO LOG FILE의 이름을 읽는다. 세번째 단계로 CONTROL FILE 내의 정보를 이용하여 모든 데이타 파일을 오픈한다. SVRMGR> CONNECT INTERNAL; Connected. SVRMGR> STARTUP; ORACLE instance started. Database mounted. Database opened. Total System Global Area 1913196 bytes Fixed Size 27764 bytes Variable Size 1787128 bytes Database Buffers 65536 bytes Redo Buffers 32768 bytes RDBMS의 STARTUP시 문제의 데이타 화일이 CONTROL FILE 정보에서는 존재하지만, 실제로 O/S상에서는 존재하 지 않으므로 DATABASE OPEN 단계에서 삭제된 데이터 화일을 OPEN할 수 없다. 따라서 다음과 같은 데이타 화 일 오픈에 관련된 에러가 발생된다 : SVRMGR> STARTUP; ORACLE instance started Database mounted ORA-01157 : cannot identify data file 11 - file not found ORA-01110 : data file 11 : '/user1/oracle7/dbs/user2.dbf' Attempting to dismount database .... Database dismounted Attempting to shutdown instance .... ORACLE instance shut down DATABASE OPEN단계에서 CONTROL FILE에서는 ORA-1157에러에서 나타난 11번 데이타 화일이 존재하는 것으로 인식하지만, 실제로 O/S상의 데이터 화일 (ORA-1110에러에 명시된 '/user1/oracle7/dbs/user2.dbf' 파일)이 삭제된 상태이다. 이러한 경우에는 DATABASE STARTUP 시 STARTUP MOUNT 단계까지 실행한 후,문제의 데이 터 화일을 OFFLINE시킨 다음 데이타베이스를 오픈한다. 단, 데이타베이스 오픈이 정상적으로 수행되면 문제가 발생한 데이타 파일을 포함하고 있는 TABLESPACE를 DROP하지 않을 경우에는 DATABASE STARTUP 시 항상 데이타 파일의 오픈 단계에서 에러가 발생된다. 따라서, 문제의 데이타 화일의 OFFLINE과 TABLESPACE의 DROP 전에 반드시 해당 TABLESPACE를 사용하고 있는 USER의 데이타 백업을 수행해야 한다. 데이타 화일의 OFFLINE과 관련된 명령은 다음과 같다. 먼저 SVRMGR을 Line Mode로 기동시킨다. $ svrmgrl SVRMGR> CONNECT INTERNAL; SVRMGR> STARTUP MOUNT; ORACLE instance started. Database mounted. SVRMGR> ALTER DATABASE DATAFILE '/user1/oracle7/dbs/user2.dbf' OFFLINE DROP; Statement processed. SVRMGR> ALTER DATABASE OPEN; Statement processed. SVRMGR> DROP TABLESPACE tablespace_name INCLUDING CONTENTS; Statement processed. (이와 같이 offline drop된 datafile을 포함하는 tablespace는 drop하여야 한다. 이 tablespace에 다른 datafile도 포함되어 있다면 export를 받아낸 후 tablespace를 drop하고 재 생성 후 import하도록 한다.) 정상적으로 DATABASE가 Open된 후 CONTROL FILE로부터의 데이타베이스 정보를 갖는 DATA DICTIONARY TABLE 인 V$DATAFILE(SYS USER에서 액세스 가능)의 내용과 데이타베이스 화일에 관한 정보를 가지고 있는 DATA DICTIONARY VIEW인 DBA_DATA_FILES(SYSTEM USER)을 조회하면 아래와 같은 내용을 확인할 수 있다 : (1) SQL> SELECT * FROM V$DATAFILE ; FILE# STATUS NAME ----- -------- -------------------------------------- 9 ONLINE /user1/oracle7/dbs/tools.dbf 10 ONLINE /user1/oracle7/dbs/user1.dbf 11 RECOVER /user1/oracle7/dbs/user2.dbf (2) SQL> SELECT * FROM DBA_DATA_FILES ; FILE_NAME FILE_ID TABLESPACE_NAME STATUS ---------------------------- -------- -------------------------- /user1/oracle7/dbs/tools.dbf 9 TOOLS AVAILABLE /user1/oracle7/dbs/user1.dbf 10 TEST AVAILABLE /user1/oracle7/dbs/user2.dbf 11 TEST AVAILABLE ====================================================================== ORA-01403 조치 방법 : EXPORT 실행시 ====================================================================== EXPORT 실행시 ORA-1403이 발생되는 경우가 있는 데, 이 에러는 테이블 혹은 인덱스에 문제가 있는 경우 발생할 수 있다. 여기서는 ROWID를 사용하여 테이블을 복구시키는 방법을 소개한다. 1. 기존 테이블과 같은 구조를 갖는 테이블을 만든다. SQL> CREATE TABLE TEMP 2> AS SELECT * 3> FROM EMP 4> WHERE 1=2; 2. 기존 테이블에서 RECORD를 FETCH하여 새로운 테이블에 입력. 이 때, INDEX가 설정되어 있는 COLUMN을 WHERE 조건에 부여함. <CREATE.SQL> declare row_id char(18); cursor c1 is select rowid from emp where empno > 0; --empno에 index begin open c1; loop fetch c1 into row_id; insert into temp select * from emp where rowid=row_id; exit when c1%notfound; end loop; end; / 3. create.sql file실행 sql> @create </XMP</PRE> <p align="right"><a href="#top"><img src="../images/top.gif" width="33" height="37" border="0"></a></p> </td> </tr> <tr> <td width="578"> <p> ======================================================================<br /> <b><a name="ORA-01547">ORA-01547</a></b> 조치방법 :ORACLE 블럭을 할당받지 못할 경우<br /> ======================================================================</p> <PRE><XMP> ORA-1547 에러 발생의 원인으로는, TABLESPACE가 에러에 명시된 만큼의 연속된 ORACLE BLOCK 수의 FREE SPACE를 갖고있지 못해서 새로운 EXTENT를 할당하지 못 하기 때문이다. ORA-1547 에러는 일반적으로 다음과 같은 과정에서 발생할 수 있다. 1) 데이타 INSERT나 UPDATE시 DATA SEGMENT가 차지하게될 연속적인 ORACLE 블럭을 할당 받지 못할 경우에 발생한다. 2) 인덱스를 생성할 경우에 발생한다. - ROLLBACK SEGMENT가 사용할 RBS 또는 USER TABLESPACE의 영역이 부족 하여 발생할 수 있다. - 인덱스 생성시 SORT 영역으로 사용되는 TEMPORARY TABLESPACE내의 SPACE의 부족 으로 발생할 수 있다. 3) SQL*FORMS30, SQL*REPORTWRITER등의 프로그램을 데이타베이스에 [SAVE]시 관련 테이 블들을 포함하고 있는 SYSTEM 또는 TOOLS TABLESPACE등의 영역이 부족한 경우에 발생 된다. 이러한 경우 EXTENT에 관련된 DATA DICTIONARY VIEW인 USER_TABLES, USER_EXTENTS, USER_SEGMENTS와 DBA_FREE_SPACE등을 조회해서 관련 내용을 확인한다. 예를 들어, 데이타 INSERT시 ORA-1547 : Failed to allocate extent of size 'num' in tablespace 'TOOLS' 에러가 발생될 경우를 고려해 보자. 1) [USER_TABLES]에서 INSERT에 관련된 테이블의 NEXT_EXTENT 크기를 확인한다. SQL> SELECT initial_extent, next_extent, pct_increase, min_extents, max_extents FROM user_tables WHERE table_name = 'EMP'; INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE MIN_EXTENTS MAX_EXTENTS ---------------- ----------- -------------- ------------- ------------- 10240 190464 50 1 121 (A) (A) : 다음에 할당되는 EXTENT의 크기를 나타내며 BYTES 단위이다. 2) [DBA_FREE_SPACE]에서 현재 TABLESPACE에 존재하는 FREE SPACE 중 가장 큰 연속된 영역을 확인한다. SQL> SELECT MAX(bytes) MAX_CONTIGUOUS_SPACE FROM dba_free_space WHERE tablespace_name = 'TOOLS'; MAX_CONTIGUOUS_BYTES ---------------------- 19730432 (B) (B) : 현재 TABLESPACE에 남아있는 FREE SPACE 중 가장 큰 연속된 영역으로 BYTES 단위로 나타난다. 3) 위에서 살펴본바와 같이 2)-(B)의 MAX(BYTES) 크기가 1)-(A)의 NEXT_EXTENT 크기보다 작기 때문에 ORA-1547이 발생하게 되는 것이며 이를 해결하는 방법으로는 다음의 몇 가지가 있다. ① 최소 1)-(A)의 NEXT_EXTENT크기 이상의 데이타 화일을 'TOOLS' TABLESPACE에 추가한다. ALTER TABLESPACE tools ADD DATAFILE *file_name* SIZE integerM ; ② TABLE의 STORAGE PARAMETER에서 INITIAL EXTENT, NEXT EXTENT의 크기를 조정하여 TABLE을 재구축할 수 있다. 즉, TABLE의 STORAGE PARAMETER 중에서 NEXT를 현재 TABLESPACE에 남아있는 FREE SPACE 중 가장 큰 연속된 영역( DBA_FREE_SPACE의 MAX(BYTES))보다 작게 변경할 수 있다. SQL> ALTER TABLE emp STORAGE ( NEXT 100K ); ③ 다음으로는 관련 TABLESPACE내의 OBJECT들을 EXPORT후 TABLESPACE를 재생성하고 IMPORT하여 DISK FRAGMENTATION을 없애서 결과적으로 해당 TABLESPACE에 활용공간을 확보할 수 있다. ====================================================================== ORA-01552 조치방법 : SYSTEM ROLLBACK SEGMENT를 사용할 경우 ====================================================================== <PRE> 본 내용은 ORACLE 7.3 이전의 VERSION에 해당하는 내용입니다. SYSTEM ROLLBACK SEGMENT 는 SYSTEM TABLESPACE에서 발생하는 ROLLBACK 정보들만을 가질수 있으므로, SYSTEM TABLESPACE 이외의 TABLESPACE에 대해서 발생하는 OPERATION (TABLE의 생성 등)을 위하여 SYSTEM ROLLBACK SEGMENT를 사용할 경우에는 ORA-1552가 발생한다. ORA-1552 발생 원인을 해소하기 위해 우선, SYSTEM TABLESPACE에 하나 이상의 ROLLBACK SEGMENT를 임시로 추가한 다음, NON-SYSTEM TABLESPACE에 ROLLBACK SEGMENT를 생성하고, 다른 데이타베이스 오브젝트(TABLE등 )를 생성하는 작업을 진행하여 이들 NON-SYSTEM TABLESPACE의 ROLLBACK SEGMENT를 이용하도록 유도한다. CREATE ROLLBACK SEGMENT 문에서 PRIVATE/PUBLIC으로 ROLLBACK SEGMENT를 생성한 후, ORACLE 데이타베이스를 SHUTDOWN, STARTUP 한다. PRIVATE으로 생성된 ROLLBACK SEGMENT는 INITSID.ORA 화일의 'ROLLBACK_SEGMENTS' PARAMETER에 등록한다. 1) SQLDBA> CONNECT INTERNAL; 2) SQLDBA> CREATE ROLLBACK SEGMENT r0 TABLESPACE SYSTEM ; 임시로 사용할 ROLLBACK SEGMENT를 SYSTEM TABLESPACE에 생성 3) SQLDBA> ALTER ROLLBACK SEGMENT r0 ONLINE ; 생성한 ROLLBACK SEGMENT를 ONLINE시킨다. 4) SQLDBA> CREATE ROLLBACK SEGMENT r1 TABLESPACE rbs ; 계속해서 사용할 ROLLBACK SEGMENT를 NON-SYSTEM TABLESPACE에 생성한다. 5) $ORACLE_HOME/dbs/initSID.ora 화일의 'ROLLBACK_SEGMENTS' PARAMETER에 생성된 ROLLBACK SEGMENT 이름을 등록한다. 6) ORALCE 데이타베이스를 SHUTDOWN 및 STARTUP을 수행한다. SYSTEM TABLESPACE에 임시로 추가해 주었던 ROLLBACK SEGMENT는 NON-SYSTEM TABLESPACE의 ROLLBACK SEGMENT를 생성하기 위해 필요한 것이었으므로 작업이 끝난 후 DROP한다. SQLDBA> ALTER ROLLBACK SEGMENT r0 OFFLINE ; SQLDBA> DROP ROLLBACK SEGMENT r0 ; ====================================================================== ORA-01555 : Rollback segment의 정보가 다른 transaction에 의해 overwrite된 경우 ====================================================================== 변경을 일으킨 트랜잭션 슬롯이 재사용되었을 때, 롤백 세그먼트의 이전 이미지가 다른 트랜잭션에 의해 겹 쳐 쓰여졌을때, 01555, 00000, 'snapshot too old: rollback segment number %s with name '%s' too small' // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: Use larger rollback segments ORA-1555가 발생하는 원인은 여러가지가 있지만 기본적으로는 사용자가 필요로 하는 롤백 세그먼트의 정보가 다른 트랜잭션에 의해 overwrite되어, 존재하지 않을 때 발생한다.　 이 문서를 읽기 전에 기본적으로 알아야 하는 오라클의 read consistency와 관련된 다음 내용들은 이 문서의 마지막에 별첨으로 용어 및 개념에 대해 설명하였으므로 참고할 수 있다.　 (1)　 SCN (System Change Number)　 (2)　 statement-read level read consistent　 (3)　 read consistent snapshot　 (4)　 rollback segment의 wrap around/overwrite　 ORA-1555에 관한 자세한 설명에 앞서, 데이타 블럭과 롤백 세그먼트 사이의 구조에 대해 간단히 알아보도록 한다. 데이타 블럭 헤더에는, 이 블럭내에 포함된 데이타를 변경한 트랜잭션의 정보와, 롤백 세그먼트내의 해당 active transaction을 가리키는 영역이 존 재한다. 롤백 세그먼트는 세그먼트의 첫번째 블럭을 헤더 블럭으로 사용하는데, 그 안에 이 롤백세그먼트를 최근에 사용한 트랜잭션들의 정보와, undo record들이 저장되어 있는 롤백 세그먼트내의 주소가 저장되어 있는 트랜잭션 테이블이 포함되어 있다.　 다음 예의 그림을 통해 다음과 같은 사항을 알 수 있다.　 (1)　데이타 블럭 500번지의 row 2를 변경한 xid1 트랜잭션은 아직 commit되지 않은 상태이다.　 　 블럭의 헤더에는 트랜잭션이 아직 cimmit되지 않았다는 정보와 5번 롤백 세그먼트 헤더내의 3번째 엔트리에 트랜잭션의 정보와, undo record를 얻을 수 있는 자세한 정보가 있음을 알려준다.　 (2) 롤백 세그먼트 5번의 3번째 슬롯은 이 트랜잭션이 변경한 undo record가 롤백 세그먼트내의 7109번지에 저장되어 있음을 나타낸다. 2,4,nn번 엔트리의 경우는 이미 트랜잭션이 commit되었으므로, 다른 트랜잭션이 이 엔트리를 overwrite 할 수 있다.　 (3) xid1트랜잭션에 의해 변경된 undo record가 포함되어 있는 6900, 7109블럭은 link로 연결되어 있어 xid1 트랜잭션이 변경한 모든 record들의 before image를 구성할 수 있다. ORA-1555가 발생하는 주요 원인과, 이 오류 발생을 최소화할 수 있는 방법은 다음과 같다. 1. 데이타베이스에 변경을 가하는 트랜잭션은 많고, 롤백 세그먼트는 크기도 작고, 갯수도 적은 경우　 다음과 같은 상황을 가정할 수 있다.　 (1) 약 30분이 걸려서 A 테이블의 대부분을 읽어야 하는 긴 query 하나를 수행시켰다. 이때의 SCN이 10이었다. (2) 위의 query가 결과값을 찾고 있는 동안, xid1 트랜잭션은 A 테이블에 대해서 update 작업을 수행하고 commit하여 A table이 저장되어 있는 블럭 중 하나인 500번지 블록의 SCN이 20으로 변경되었다　 (3) query가 진행중인 동안 매우 많은 트랜잭션들이 database를 변경하고 commit하였다. (4) 이 query가 500번지 블럭을 읽고자 할 때 SCN이 20임을 확인하고, xid1 트랜잭션에 의해 변경된 undo record를 찾기 위해 롤백 세그먼트를 참조하였다.　 (5) 그러나 xid1 트랜잭션은 이미 commit된 상태이고, query가 진행되는 동안 매우많은 트랜잭션이 데이타베이스 변경작업을 수행한 결과 롤백 세그먼트내의 xid1 트랜잭션의 undo record가 저장되어 있는 블럭이 다른 트랜잭션들에 의해 overwrite 된 상태였다. (6) ORA-1555가 발생한다. 해결 방법:　 (1) 롤백 세그먼트의 크기를 크게 하고 갯수를 늘리면, 롤백 세그먼트가 wrap around /overwrite되는 주기가 늦추어진다.　 (2) 트랜잭션의 수행이 많은 때에는 수행시간이 오래걸리는 query문은 수행시키지 않도록 한다. 2. fetch across commit　 프로그램내에서 cursor를 선언하고 loop를 수행하면서 fetch하고 데이타를 변경하는 경 우 많은 프로그래머들은 롤백 세그먼트의 사용량을 줄이기 위해서 매 loop시마다 commit 을 한다. 그러나 cursor의 loop내에서 commit하는 것은 ANSI standard에서는 제공하는 것이 아니며, ORA-1555를 발생시킬 가능성이 있다.　 ORA-1555가 발생하는 경우는 (1)의 경우와 유사하다. cursor는 선언하고, open시에 데이 타를 읽는 것이 아니고 fetch 때마다 읽게 되므로 fetch를 수행하는 것은 long query를 시작하는 것과 같다. 즉, fetch문의 loop를 수행하는 동안, 처음 fetch문 수행시점의 SCN보다 작거나 같은 SCN의 데이타를 읽어야 한다. 그런데 loop 수행시마다 데이터를 변경하고 commit하게 되면, commit한 block의 SCN은 증가되고 변경된 정보도 다른 트랜 잭션에 의해 재사용되어질 수 있다. 이렇게 블럭은 변경되었으나, 변경된 정보가 이미 다른 트랜잭션에 의해 overwrite된 블럭의 데이타를 fetch하고자 하면, 오라클은 read consistent snapshot을 구성할 수 없게 되므로 ORA-1555가 발생하게 된다.　 해결 방법:　 (1) cursor내에서 commit하는 횟수를 줄인다. 예를 들어 첨자를 이용해 5만건에 한번씩 commit할 수 있으며, 이렇게 되면 5만건의 데이타를 저장할 수 있는 큰 롤백 세그먼트가 있어야 한다.　 (2) cursor 선언시 구성될 active set의 범위를 줄인다. 즉 한번에 모든 데이타를 읽어 처리하기 보다는, where절을 이용하여 데이타를 나누어, 여러번에 걸쳐 수행한다.　 (3) 1번의 경우와 마찬가지로, commit된 정보가 overwrite되는 주기를 늦추기 위해서 롤백 세그먼트의 갯수를 증가시키고 그 크기도 크게하면 도움이 된다.　 3. delayed block clean out　 오라클은 기본적으로 transaction이 commit하면, fast commit을 수행한다. 즉, 트랜잭 션이 데이타를 변경시키고 commit하면, 변경된 데이타 블럭의 header부분에 트랜잭션이 commit되었음을 기록하는 것이 아니고 일단 롤백세그먼트의 헤더부분에만 commit되었음 을 기록한다. 이후 그 데이타 블럭을 다른 트랜잭션이 access하게 되면, 그때 롤백 세그 먼트의 정보를 이용하여 데이타 블럭에 commit된 상태를 반영하여 clean out시키는 것을 delayed block clean out이라고 한다. 이 delayed block clean out이 어떻게 ORA-1555를 발생하게 되는지 다음의 상황을 살펴보면 된다.　 (1) 다음과 같은 초기 상태를 가정할 수 있다. 500번지 데이타 블럭의 데이타를 변경하는 트랜잭션은 존재 하지 않고, rollback segment 5번 header의 3, 4, nn번째 트랜잭션 엔트리는 다른 트랜잭션에 의해 재사용 되어 질수있다.　 (2) xid1 트랜잭션이 update문을 이용하여 500번지 데이타 블럭의 2번째 데이타를 변경 하였다. 500번지 데이타 블럭의 헤더에는 xid1 트랜잭션의 정보가 저장되고, 롤백 세그먼트 5번의 트랜잭션 슬롯 3 (5,3)을 가리키게 된다. COMMITTED로 표시되었던 트랜잭션 슬롯 3번은 이제 ACTIVE 상태로 변경되었다.　 (3) xid1 트랜잭션이 commit을 수행하였다.　 오라클은 롤백 세그먼트 헤더의 트랜잭션 테이블에서 xid1 트랜잭션의 정보를 찾아서 commit되었다고 기록 하였다. 그러나 500번지 블럭의 헤더에는 commit 되었다는 정보를 기록하지 않는다. (fast commit)　 (4) 데이타베이스에 변경을 가하는 매우 많은 트랜잭션이 수행되었다.　 매우 많은 트랜잭션이 수행되어 롤백 세그먼트 헤더내에 있는 트랜잭션 테이블의 엔트리가 대부분 재사용되 었다. 트랜잭션 xid50이 롤백 세그먼트 5번의 3번째 슬롯이 COMMITTED로 표시되어 있으므로, 비어있는 엔트리로 인식하여 xid50에 관한 정보를 저장하였다.　 (5) 다른 트랜잭션이 데이타 블럭 500번지를 방문하였다.　 새로운 트랜잭션인 xid70 트랜잭션이 500번지 블럭을 읽고자 하였다. (3)번의 그림 에서 보듯이, 500번지 블럭 헤더에는 아직 commit되지 않은 트랜잭션이 이 블록을 변경하였으며, before image를 구성할 수 있는 정보가 롤백 세그먼트 5번, 엔트리 3번에 있음을 나타낸다. 그러나 5번 롤백 세그먼트 헤더내에 있는 트랜잭션 테이블의 3번 슬롯은 xid1번이 아닌 xid50번의 정보가 저장되어 있다. 즉, delayed block cleanout이 이루어지기 전에 롤백 세그먼트 헤더가 overwrite 된 것이다.　 (6) xid7 트랜잭션은 read consistent snapshot을 구성할 수 없으므로 ORA-1555가 발생한다.　 해결 방법:　 (1) ORA-1555를 발생시킬 상황 이전에 읽고자 하는 테이블에 대해 full scan을 실시한 다면, 롤백 세그먼트 안의 정보가 overwrite되기 전에 delayed block cleanout이 이루어지도록 할 수 있다.　 (2) 1 ~ 4번의 모든 원인에 대해서 롤백 세그먼트를 크게 유지하면, 롤백 세그먼트의 정보가 overwrite되는 주기를 늦출 수 있어 ORA-1555를 피하는데 도움이 될 수 있다. 4. OPTIMAL 크기가 아주 작을 때 롤백 세그먼트는 트랜잭션의 사용에 의해 한번 크기가 늘어나면 기본적으로 그 롤백세그먼트를 지우고 다시 만들기 까지는 크기가 줄어들 지 않는다. 그러나 optimal size 를 지정하게 되면, 롤백 세그먼트에서 새로운 extent 를 요구하는 시점에, 현재 할당된 롤백 세그먼트의 크기와 optimal에 지정된 크기를 비교하게 된다. 할당된 공간이 optimal 크기보다 큰 경우, 할당된 extent중 active 한 트랜잭션이 사용하고 있지 않은 extent들은 release시켜, 롤백 테이블스페이스의 공간으로 환원된다.　 그러므로 이 optimal size가 지나치게 작다면, 트랜잭션이 commit되자마자 롤백 세그 먼트내의 정보는 잃게 될 것이다. 그러나, 위의 1 ~ 4번에서 살펴보았듯이 이미 commit된 트랜잭션의 정보라 하더라도 이후에 필요하게 되는 경우가 발생하므로 이렇 게 빈번히 commit된 트랜잭션의 정보가 포함되어 있는 롤백 세그먼트의 extent를 release시키는 것은 바람직하지 않을 수 있다.　 해결 방법:　 (1) optimal을 지정할 때는 20개의 extents정도의 크기정도로 지정하는 것이 적당하며, 그것보다 더 작게 지정하지 않도록 한다.　 (2) 롤백 세그먼트를 많이 필요로 하는 batch job의 경우 set transaction use rollback segment rollback_segment_name 구문을 이용하여 특정 롤백 세그먼트를 사용하게 하고 나머지 롤백 세그먼트들은 OLTP job이 사용하도록 한다. 이렇게 하면 OPTIMAL을 지정하지 않아도 모든 롤백 세그먼트가 불필요하게 확장되는 일을 막을 수 있다.　 별첨: 용어 및 기본 개념 설명----------------------------------------------------- (1) SCN(System Change Number)　 오라클은 특정한 시점의 데이타베이스 상태를 SCN으로 관리한다. 트랜잭션이 commit 되면, SCN은 최근의 SCN보다 크고 유일한 값이 할당되며, 이 값은 그 트랜잭션이 변경시킨 블록에 반영되고, 그 데이타화일의 가장 최근의 SCN은 데이타화일의 헤더 (header)에 기록된다. (2) statement-level read consistent　 하나의 query는 그 query가 시작되어 데이타를 읽기 시작하면, 모든 데이타를 읽어 query가 끝날 때까지 일관된 상태를 유지한다. 즉 query가 진행되는 동안 다른 트랜잭 션이 읽고자하는 데이타를 변경하더라도 그 query는 변경 이전의 데이타 값을 읽게 된다.　 데이타들이 query가 시작될 때와 같은 시점인지는 SCN을 통해 관리된다. 즉 SCN이 10인 상태에서 query가 시작되었다면 query가 진행되는 동안 항상 SCN이 10이하 상태의 데이 타만을 읽게되며, 이것은 롤백 세그먼트(rollback segment)를 이용하여 read consistent snapshot을 구성함으로써 가능하다.　 (3) read consistent snapshot (read consistent view)　 트랜잭션이 변경작업을 수행할 때 마다, 오라클은 변경 작업이 이루어지기 전의 before image(snapshot)을 롤백 세그먼트에 저장해둔다. 한 트랜잭션이 commit되기 전에 변경된 데이타를 다른 트랜잭션이 읽거나 수정하고자 한다면, 롤백 세그먼트의 정보를 이용하여 read consistent snapshot을 구성한 후 이 데이타값 을 이용하여 operation을 수행한다. 또한 (2)에서 설명한 statement-level read consistent를 이루기 위해 서도 query가 진행되는동안 읽고자 하는 블럭의 SCN이 증가하면, 롤백 세그먼트의 정보를 이용하여 원하는 SCN상태의 read consistent snapshot을 구성한 후 데이타를 읽게 된다.　 (4)　 rollback segment의 wrap around/overwrite　 롤백 세그먼트는 하나의 롤백 세그먼트를 여러개의 트랜잭션이 함께 사용하며, 하나의 extent도 여러개의 트랜잭션이 동시에 사용가능하다. 단 각 블럭은 하나의 트랜잭션에 할당된다. 트랜잭션들이 사용 중인 extent에 정보를 저장하고 다음 extent가 필요하면, 해당 롤백 세그먼트에 이미 할당되어 있는 다음 extent가 active한 undo 정보를 가지고 있는지를 검사한다. active한 undo 정보를 담고 있지 않은 다음 extent가 current extent가 되며, 트랜잭션들은 이 extent에 undo image를 저장한다. 할당된 맨 마지막 extent를 확인하게 되면, 다시 첫번째 extent부터 extent로 돌아와 다시 사용하는 것을 wrap around라고, 모두 commit된 트랜잭션의 정보만 담고 있는 extent는 overwrite된다. 이렇게 롤백 세그먼트의 undo image를 담고있는 블럭뿐 아니라 롤백 세그먼트 헤더내의 트랜잭션 테이블의 엔트리도 wrap around/overwrite될 수 있다. 트랜잭션 테이블은 고정된 수의 엔트리를 가지고 있으며, 트랜잭션이 이미 COMMITTED된 엔트리는 비어있는 것으로 인식하여 다음 트랜잭션이 사용 가능하게 된다. 01560 : tablespace에 충분한 공간이 없을때 01560, 00000, 'global hash table size mismatch for %s (%s != %s)' // *Cause: The specified 'gc_' INIT.ORA parameter was incompatible // with that of another instance which already has the database mounted. // *Action: Fix the 'gc_' parameter and restart. ====================================================================== ORA-01562 조치방법 : MAXEXTENTS (DEFAULT 121)에 도달한 경우 ====================================================================== 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, maxextents, 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 결과 MAXEXTENT에 도달하지도 않았고, 오류가 발생한 TRANSACTION을 처리할 만큼 큰 크기의 RSSIZE가 있는 ROLLBACK SEGMENT도 없는 경우에는 1번의 조회 결과 나타난 RBS의 DATAFILE이외에 추가적으로 RBS TABLESPACE에 새로운 DATAFILE을 추가하여야 한다. sqlplus system/manager SQL> ALTER TABLESPACE 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라고 할때, SQLPLUS 상에서는 다음과 같이 하면된다. SQL> SET TRANSACTION USE ROLLBACK SEGMENT r04 ; 일반적으로 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를 재생성할 수 있다. 참고) 각 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); ====================================================================== ORA-01578 조치 방법 : seq=0 이고 inc<>0(새로운 블럭이 아님)일 때 ====================================================================== <PRE> 모든 오라클　 데이타 블럭은 Sequence　 번호(seq)와 Incarnation 번호(inc)를 갖고 있다. ORA-1578　 에러는 seq=0 이고 inc <> 0(새로운 블럭이 아님)일 때 발생한다.　 ORA-1578 에러는 ORA-600[3339] 에러와 함께 발생하곤 한다.　 * ORA-1578 에러가 발생하면　 Corruption이 발생한 화일번호와 블럭번호를 알려준다. 여기서는 이 때의 화일번호를 f, 블럭번호를 b 라고 부르기로 한다.　 <해결방법> 1. 우선 해야 할 일은 어떠한 오브젝트가 Corrupt　 되었는가를 알아내는 것이다.　 　 다음의 스크립트를 이용하면 알 수 있다.　 　 SQL> select segment_name, segment_type　　 　　　　 from dba_extents　　 　　　　 where file_id = f and　　 　　　 between block_id and block_id + blocks - 1; 2. 만약 해당 세그먼트가 인덱스이면 Drop 시키고 다시 생성하면 된다.　 3. 만약 해당 세그먼트가 테이블이면 Corrupt 된 블럭의 데이타는 손상된 것이다.　 4. 만약 해당 테이블이 들어있는 엑스포트 화일이 있다면 손상된 테이블을 Drop 시키고 임포트 받는 것이 제일 간단한 방법이다. 하지만 만약 엑스포트 받은 파일이 없거나 백업해 둔 화일도 없다면 해당 테이블에 인덱스가 생성되어 있는 경우에 한해서 다음의 방법을 사용해서 복구를 하도록 한다. * empno, ename, deptno 를 컬럼으로 가지는 EMP 테이블이 Corrupt되었다고 가정하자. 그리고 empno 컬럼에 인덱스가 생성되어 있다고 하자. 클러스터화되지 않은 모든 테이블은 유니크한 Rowid 를 가진다. Rowid를 Varchar2/hexadecimal 형식으로 표현하려면 Rowidtochar 함수를 이용한다. SQL> select rowid to_char(rowid) from emp; * Rowid는 총 18자로 블럭어드레스(8자), 점(1자), 로우 어드레스(4자), 점(1자), 화일 어드레스(4자)로 구성되어 있다. 　SQL> select empno, rowid　　 　 　 from emp　　 　　 　 where empno > 0　　 　 위의 스크립트를 실행시키면 다음과 같은 결과를 얻게 된다.　 　　　 EMPNO　　　　　　　　　　 　 ROWID　　 　　 ------------　　　　 -------------------------------- 　　　　 100　　　　　　 　　 00000003.0000.0006　　 　　 　 101　　　　　 　　　 00000003.0001.0006　　 　　　 　 102　　　　　　 　　 00000003.0002.0006　　 　 　　　 103　　　　 　　　　 00000003.0003.0006　　 　　 500　　　 　　　　　00000004.0000.000A　　 　　　　 501　　　　 　　　　 00000004.0001.000A　　 　　　　 755　　　　　　　　　0000001A.0005.000A　　 　　　　 756　　　　　　 　　 0000001A.000C.000A　　 * 만약 인덱스가 Character 컬럼에 대한 것이었다면, 위의 Query 문장을 다음과 같이 바꿀 수 있다.　 SQL> select empno, rowid　　　　　　　　　 from emp　　 　 where empno > ''; 　　　 * 예를 들어 다음과 같은 에러　 메시지가 떨어졌다고 하자.　 　01578, 00000, 'ORACLE data block corrupted (file # 10, block # 4)　　 그러면 다음의 스크립트를 사용하여　 손상된 블럭에 있는 employee 에 대한 empno를 구할 수 있다. 　 SQL> select empno　 from emp　　 where empno > 0 and rowid tochar(rowid) like '00000004.%.000A'; 　　　　 EMPNO　　　 　　 　 ROWID　　 　　　　 ----------　　-------------------------------- 　　　　 500　　　　　 0000004.0000.000A　　 　　　　 501　　　　　 00000004.0001.000A　　 * 이제 EMP 테이블과 같은 구조를 갖는 새로운 테이블을 만든다. 　　　 SQL> create table temp　　　 　　 as select * from emp where 1 = 2;　　 * 그리고는 손상된 부분을 피해서 새로운 테이블에 손상된 테이블의 데이타를 추가한다. 　　　 SQL> insert into temp select * from emp where empno < 500;　　 　　　 SQL> insert into temp select * from emp where empno > 501;　　 손상된 테이블을 Drop시키고　 Temp 테이블의 이름을 EMP 로 변경한다.　 그리고 백업된 자료나 문서자료를 통하여 손상된 부분에 대한 정보를 추가한다. 　　　 5. 손상된 블럭에 여러개의 로우가 존재하고 있다면 다음의 방법을 이용한다. 　SQL> create table empnos as　　 　　　　　 select empno from emp　　 　　　　　 where empno > 0　　 　　　　　　 and rowid to_char(rowid) not like '00000004.%.000A'; 이 스크립트를 이용하면 손상된 블럭에 포함되지 않은 empno 들을 알 수 있다. * 다음의 스크립트를 계속 실행시켜 복구를 한다.　 SQL> create table temp as select * from emp where 1 = 2; SQL> insert into temp　　　 　　　select emp.empno, emp.ename, emp.deptno　　 　　　　　 from emp, empnos　　 　　　 where emp.empno > 0　　　　　　 and emp.empno = empnos.empno;　　 6. 만약 데이타 딕셔너리의 테이블이나 인덱스에서 손상된 블럭이 발생했다면 지원을 요청해야 한다. ====================================================================== ORA-01628 ORA-01630 ORA-01631 ORA-01632 조치 방법 : MAXEXTENTS에 도달했을때 ====================================================================== 다음 ORA 에러들은 오라클의 오브젝트들이 MAXEXTENTS에 도달했을 때 발생하는 것들이다. 01628, ' max # extents (%s) reached for rollback segment %s ' 01630, ' max # extents (%s) reached in temp segment in tablespace %s ' 01631, ' max # extents (%s) reached in table %s.%s ' 01632, ' max # extents (%s) reached in index %s.%s ' 또한 ORA-1628 다음에는 ORA-1562 에러도 함께 발생한다. 이 에러들은 다음 모든 LEVEL 에서 발생될 수 있다. . 에플리케이션 LEVEL (GL, AOL, Financials, Etc) . TOOLS LEVEL (Reports, Forms, Etc) . Kernel LEVEL (Insert, Update, Delete) 이 에러의 이유는 오브젝트의 익스텐트가 MAX # 에 도달했기 때문에 발생되며, 오브젝트의 MAXEXTENTS는 STORAGE의 MAXEXTENTS 파라미터에 의해 결정된다. 다음 예를 보기로 하자. SQL> INSERT INTO TAB1 SELECT * FROM TAB1; ORA-01631 : max # extents (2) reached in table JANE.TAB1 SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS FROM USER_TABLES WHERE TABLE_NAME = 'TAB1'; INITIAL_EXTENT NEXT_EXTENT MAX_EXTENT --------------- ------------ ---------- 6144 10240 2 위 예에서 오브젝트의 MAXEXTENTS 는 2 인데 이 값은 HARDCORD된 MAX 값이 아니다. HARDCORD 된 MAXEXTENTS의 최대값은 데이타베이스가 생성될 당시 지정된 DB_BLOCK_SIZE 의 값에 따라 다르다. DB_BLOCK_SIZE 최대 MAXEXTENTS 값 ------------------- ------------------ 512 25 1K 57 2K 121 4K 249 8K 505 만일 최대 MAXEXTENTS 값보다 더 큰 MAXEXTENTS를 Storage 에서 지정하면 다음 에러가 발생한다. ORA-02226, 00000, ' invalid MAXEXTENTS value (max allowed: %s) ' 다음은 ORA-0163x 에 대한 해결 방법이다. 만일 platform의 최대 MAXEXTENTS 에 도달이 안 되었으면 ALTER TABLE .. STORAGE (MAXEXTENTS n); 를 사용하여 최대 MAXEXTENTS 값보다 작은 수로 MAXEXTENTS를 늘려준다. 만일 최대 MAXEXTENTS 값에 도달했으면 해결할 수 있는 방법은 MAX 제한에 도달되지 않도록 EXTENT SIZE 를 더 크게하여 오브젝트를 다시 생성하는 것이다. 만일 이 에러가 ROLLBACK SEGMENT 에서 발생되면 DROP하고 다시 생성한다. 만일 에러가 TEMPORARY TABLESPACE에서 발생되면 TEMPORARY TABLESPACE의 STROAGE를 변경한다. TEMP SEGMENT 는 그것이 생성된 TABLESPACE의 Default Storage Parameter를 사용하기 때문에 다음과 같은 방법으로 해결할 수 있다. ALTER TABLESPACE 'tempname' DEFAULT STORAGE (INITIAL n NEXT n); . n 은 기존 지정된 값보다 큰 값을 지정한다. ALTER TABLESPACE 'tempname' DEFAULT STORAGE (PCTINCREASE m); . m 은 기존 지정된 값보다 큰 값을 지정한다. 만일 에러가 TABLE에서 발생되면 export/import utility를 사용하여 그 TABLE을 다시 생성한다. [ 단계 ] 예를 들어 scott user의 emp table이 121개의 extent에 도달했다고 가정 1. table을 export한다. 예) $ exp scott/tiger file=emp.dmp tables=emp 2. table을 drop하거나 export가 실패한 경우를 대비해서 기존 TABLE을 RENAME 한다. 예) SQL> drop table emp; 또는 SQL> RENAME EMP TO EMP_OLD; 3. storage절을 변경하여 table을 생성한다. SQL> create table emp(empno.....) storage (intial 10M next 1M pctincrease 0); 여기에서 initial 10M과 next 1M은 예로 든 것이므로 고객 환경에 적당하게 설정한다. pctincrease는 0로 한다. 4. 사용자의 SCHEMA 에 임포트를 실행하여 TABLE을 생성함. 이 때 위에서 생성된 table에 import가 되도록 ignore=y option을 사용 예) $imp scott/tiger file=emp.dmp tables=emp ignore=y commit=y 5. 2번에서 table을 rename하였다면 import가 잘 수행되었는지 확인하고 기존 테이블은 DROP 함. 예) SQL> DROP TABLE EMP_OLD; ====================================================================== ORA-0162x ORA-0163x조치 방법 : MAXEXTENTS에 도달 했을때 발생 ====================================================================== INITIAL_EXTENT　 다음 ORA 에러들은 오라클의 오브젝트들이 MAXEXTENTS에 도달 했을때 발생하는 것들이다. 01628, 00000, 'max # extents (%s) reached for rollback segment %s'　　　　　 01630, 00000, 'max # extents (%s) reached in temp segment in tablespace %s' 01631, 00000, 'max # extents (%s) reached in table %s.%s'　 01632, 00000, 'max # extents (%s) reached in index %s.%s'　 또한　 ORA-1628 다음에는 ORA-01562도 함께 발생한다. 이 에러들은 다음 모든 LEVEL에서　 발생 될 수 있다. 　 &sect;　 에플리케이션 LEVEL (GL, AOL, Financials, Etc) 　 &sect;　 TOOLS LEVEL (Reports, Forms, Etc)　　　　　 　 &sect;　 커널　 LEVEL (Insert, Update, Delete)　　　　 이 에러의 이유는 오브젝트의 익스텐트가 MAX #에 도달 했기 때문에 발생되며 오브젝트의 MAXEXTENTS는 STORAGE의 MAXEXTENTS 파라미터에 의해 결정된다.　　 다음 예를 보기로 하자.　　 SQL>; INSERT INTO TAB1 SELECT * FROM TAB1;　　　　　　　　　　　 　　　ORA-01631: max # extents (2) reached in table JANE.TAB1 SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS　　　　　　　 FROM USER_TABLES　　　　 　　　　　　　　　　　 WHERE TABLE_NAME = 'TAB1'; 　　 INITIAL_EXTENT　NEXT_EXTENTS　MAX_EXTENT -------------- ------------ ---------- 　 6144 10240　　 2 위 예에서 오브젝트의 MAXEXTENTS는 2 인데 이 값은 HARDCORD된 MAX 값이 아니다.　　　　 HARDCORD 된 MAXEXTENTS의 최대값은 데이타베이스가 생성될 당시 지정된 DB_BLOCK_SIZE 의 값에 따라 다르다. ====================================================================== ORA-01632 조치 방법 : INDEX REBUILD ====================================================================== ORA-01632 에러는 index가 확장하려고 할 때 maxextents 값의 제한에 도달하여 더이상 extents를 일으키지 못하는 경우입니다. 이 에러의 경우 보통은 index의 storage 절의 initial, next가 작아서 발생하기 때문에 근본적으로 storage의 initial, next를 크게 키워 주면서 다시 만드는 것이 좋습니다. 그러나 현재 다시 생성하는 것이 어렵다면 일단 maxextents만 키워서 사용을 하다가 나중에 작업을 할 수도 있습니다. maxextents를 키우려면 (이 기능은 7.3 이상부터 가능) SQL> alter index i_dept_deptno storage (maxextents 200); 과 같이 실행하면 됩니다. 위와 같이 index가 일반 index가 아니라 primary key index인 경우는 index만 drop 했다가 다시 생성할 수는 없습니다. 그러므로 primary key를 다시 만들면서 지정하거나 index를 rebuild 해야 합니다. 일반 index의 경우는 index를 다시 생성하거나 rebuild하면 되는데, 보통 다시 생성 하는 것보다 rebuild하는 것이 속도가 좋습니다. 1. index를 rebuild하는 방법 SQL> alter index pk_dept rebuild 2 tablespace ind_data 3 storage (initial 1M next 1M); 2. primary key 생성 시 storage 지정하는 방법 SQL> alter table dept drop primary key; SQL> alter table dept add constraint pk_dept 2 primary key (deptno) 3 using index tablespace ind_data 4 storage (initial 1M next 1M); ====================================================================== ORA-01652 조치 방법 : tablespace에 space가 부족 ====================================================================== ORA-165X error는 tablespace에 space가 부족해서 table이나 rollback segment extent가 할당되지 못해서 발생하는 error이다. 다음의 에러들은 tablespace에 space가 부족해서 발생하는 사항들이다. 01652, 00000, 'unable to extend temp segment by %s in tablespace %s' 01653, 00000, 'unable to extend table %s.%s by %s in tablespace %s' 01654, 00000, 'unable to extend index %s.%s by %s in tablespace %s' 01655, 00000, 'unable to extend cluster %s.%s by %s in tablespace %s' 1. Tablespace space 부족 현상의 예 다음의 테이블 생성 문장을 보자. SQL> CREATE TABLE FEATURE 2> (feature_code varchar2(4) primary key, 3> feature_desc varchar2(3) ); ORA-01652, 00000, 'unable to extend temp segment by 6144 in tablespace VESSEL' 테이블 스페이스 VESSEL 에 남아있는 가장 큰 연속된 공간을 확인해 보면 SQL> SELECT MAX(blocks), MAX(bytes) 2> FROM DBA_FREE_SPACE 3> WHERE TABLESPACE_NAME = 'VESSEL'; blocks bytes 6143 12,580,864 위의 결과를 보면 현재 VESSEL 에 남아있는 가장 큰 연속된 공간은 6143 블록인데 오라클은 6144 블럭이 사용하려다 이를 할당받지 못하여 에러가 발생하게 된 것이다. 2.tablespace space 부족현상의 조치 tablespace에 space가 부족해서 에러가 발생하는 경우 아래의 몇 가지 방법을 이용해 조치가 가능하다. (1) 데이타 화일을 추가하여 테이블스페이스의 크기를 확장한다. SVRMGR> ALTER TABLESPACE data ADD DATAFILE '/usr/../oracle/data2.dbf' SIZE 100M; 이때의 tablespace 가 SYSTEM 일 경우는 user 의 default tablespace가 잡혀있지 않기 때문에 근본적인 해결이 필요하다. 이 경우는 무작정 tablespsace 를 늘리지 말고 user의 default tablespace를 create 후 user에게 할당해 주도록 한다. 예) SVRMGR> CREATE TABLESPACE tablespace_name datafile '......' size 100m; SVRMGR> ALTER USER user_name IDENTIFIED BY passwd > DEFAULT TABLESPACE tablespace_name > TEMPORARY TABLESPACE temp ; (2) 테이블(rollback segment)의 storage parameter를 조정하여 현재 남아있는 영역에 들어갈 수 있도록 한다. SQLDBA> ALTER TABLE emp STORAGE(NEXT 1M); 이 경우 tablespace가 fragmentation이 심한 경우가 아니면 효과적이지 못하다. (3) 테이블스페이스가 fragmentation이 심한 상태이면 exp/imp를 이용하여 테이블 스페이스를 재구성 한다. 3. V7.1에서의 ORA-1652 에러 테이블이나 인덱스 등을 만들 때 자신의 TEMP TABLESPACE가 아닌 곳에서 ORA-1652(temp tablespace가 부족함) 에러가 발생하는 경우가 있다. 이와 같은 문제는 V7.1에서만 발생하는데 V7.1에서는 테이블, 인덱스 등을 병렬로 생성할 수 있다. 이를 위하여 실제로 테이블 등이 생성될 공간에 Temporary Segment를 만들게 되는데 이 과정에서 Temporary Segment를 만들 공간이 부족하게 되면 실제의 테이블이 생성되는 테이블 스페이스에 대하여 ORA-1652 에러가 발생하게 되는 것이다. 이 에러를 해결하는 방법은 에러메시지에서 보여주는 대로 해당 테이블스페이스에 Temporary Segment가 생성 될 만한 연속된 공간을 마련하여 주는 것이다. ===================================================== ORA-1653, ORA-1658 : TABLESPACE 크기를 확장하는 방법 ===================================================== 오라클 7.1 이하에서는 tablespace를 확장하려면 해당 tablespace에 데이타 화일을 추가하는 방법을 사용한다. 이 때 추가하는 데이타 화일의 이름은 기존의 화일과 동일한 이름이 아니기만 하면 되지만, 편의상 기존의 화일에 일련 번호를 붙여서 사용하는 것이 일반적이다. 예를 들어 tablespace TOOLS 를 확장한다고 가정하면 $sqlplus system/manager SQL> select file_name, bytes from dba_data_files where tablespace_name = 'TOOLS'; 이와 같이 하면 현재 TOOLS tablespace를 구성하고 있는 화일 이름과 크기 (bytes)가 출력된다. 여기서 출력된 file_name 이 /oracle/dbs/toolsORA.dbf 라고 한다면 다음과 같이 하여 tablespace를 확장한다. SQL> alter tablespace tools add datafile '/oracle/dbs/tools2ORA.dbf' size 50M; 여기서는 화일의 크기를 50M 로 주었는데 이것은 디스크의 FREE SPACE 와 기존의 데이타 화일의 크기 및 앞으로 들어갈 데이타의 크기 등을 고려하여 적절한 값으로 결정하도록 한다. 오라클 7.2 에서는 위의 방법 외에도 기존의 데이타화일의 크기를 변경시켜서 확장시킬 수 있다. 예를 들어 TOOLS tablespace가 현재 50M 크기의 /oracle/dbs/toolsORA.dbf 화일로 구성되어 있다면 다음과 같이 해서 이 화일의 크기를 100M 로 늘릴 수 있다. SQL>alter database datafile '/oracle/dbs/toolsORA.dbf' resize 100M; RESIZE 옵션은 V7.2 에서 추가된 것으로 기존의 데이타 화일을 확장 또는 축소할 수 있다. 축소하는 경우는 데이타가 들어 있는 경우 하한선 이하로 내려가지는 않는다. 한편, 데이타가 계속 들어가서 tablespace를 꽉 채우게 되면 다음과 같은 명령을 이용하여 자동적으로 tablespace를 확장할 수도 있다. SQL> alter database datafile '/oracle/dbs/toolsORA.dbf' autoextend on next 10M maxsize 200M; 이렇게 하면 데이타가 늘어나면서 자동적으로 10M 씩 데이타화일의 크기가 늘어나게 된다. 여기서는 최대 200M 까지 늘어날 수 있도록 설정하였다. ====================================================================== ORA-01654 : INDEX SEGMENT ====================================================================== 01654, 00000, 'unable to extend index %s.%s by %s in tablespace %s' 예) unable to extend index owner.object by 40964 in tablespace INDEX; 1. tablespace에 남아 있는 공간 중 가장 큰 연속된 공간의 사이즈를 구합니다. SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = 'TABLESPACE NAME'; ora-1654 에러가 났던 tablespace 이름을 대문자로 위에 써줍니다. 위에 나온 수치는 연속된 block들 가운데 가장 큰 사이즈의 extent를 보여주는 것인데, next extent를 할당하기 위해서는 위에 나온 수치보다 더 큰 사이즈를 필요로 하는 것입니다. 'The above query returns the largest available contiguous chunk of space.' 2. index의 storage parameter인 next_extent 값과 pct_increase 값을 확인합니다. SELECT next_extent, pct_increase FROM dba_indexes WHERE index_name = 'INDEX NAME' AND owner = 'OWNER'; ora-1654 에러가 발생한 index의 next extent 값과 pct_increase 값이 얼마인지 확인해 보십시오. 위에서 나타난 next_extent 값과 max(bytes) 값을 비교해 보세요. 3. 인스턴스의 db_block_size를 확인합니다. vi $ORACLE_HOME/dbs/initSID.ora db_block_size = 2048 또는 4096 또는 8192일 것입니다. ora-1654 에러에 나타난 by 다음의 수치(예:40964) * db_block_size 만큼의 사이즈가 next_extent(byte 단위) 값과 같을 것이며, 이 만큼의 extent 영역을 할당할 수 없다는 뜻입니다. 따라서 datafile을 추가시 이 byte 값 이상의 사이즈를 추가해야 합니다. 4. ora-1654 에러를 해결하는 방법 There are several options for solving failure to extend. Manually Coalesce Adjacent Free Extents --------------------------------------- ALTER TABLESPACE <tablespace name> COALESCE; The extents must be adjacent to each other for this to work. Add a Datafile: --------------- ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> < |k|m>; Lower 'next_extent' and/or 'pct_increase' size: ----------------------------------------------- For non temporary segment problem: ALTER <object> <object name> STORAGE ( next <integer> < |k|m> pctincrease <integer>); For a temporary segment problem: ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> next <integer> <|k|m> pctincrease <integer>); Resize the Datafile: -------------------- ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer><k|m>; ====================================================================== ORA-04031 조치 방법 : Shared pool에서 연속적인 메모리 부분을 찾지 못해 발생 ====================================================================== 우리는 다음과 같은 작업수행 시 Oracle 이 Shared pool에서 연속적인 메모리 부분을 찾지 못해 ORA-4031 Error를 발생시키는 것을 볼 수 있다. - PL/SQL Routine - Procedure 수행시 - Compile 시 - Form Generate 또는 Running 시 - Object 생성하기 위해 Installer 사용시 1. Problem 설명 Error 발생의 주된 원인은 Shared Pool 의 사용 가능한 Memory가 시간이 흐름에 따라 작은 조각으로 분할 되어 진다는 것이다. 그래서 큰 부분의 Memory를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가 발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space 가 있다하더라도 충분한 양의 연속적인 Space가 없으면 이 Error가 발생한다. 2. Problem 해결 방안 이 Error 해결 방안을 살펴 보면 다음과 같다. (1) Shared Pool 의 Size를 적절히 조절한다. 이 Size는 Default 값이 3.5M~9M로 되어 있지만 실제 운용 데이타베이스의 경우에는 이 이상으로 이용하는 곳이 많다. 이 Size를 수정시는 DB를 Shutdown 후 다시 Start 시켜야 하므로 항상 가능한 해결 방법이 될 수는 없다. (2) Object를 Shared Pool에 맞추어 Fragmentation을 줄인다. (Dbms_Shared_Pool Procedure 이용) (3) Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다. DBMS_SHARED_POOL STORED PROCEDURE 이 stored pakage는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다. (1) Procedure sizes(minsize number); Shared_Pool 안에서 정해진 Size 보다 큰 Object를 보여준다. (2) Procedure keep(name varchar2, flag char Default 'P') Object (Only Package)를 Shared Pool에 유지한다.또한 일단 Keep한 Object는 LRU Algorithm에 영향을 받지 않으며 Alter System Flush Shared_Pool Command 에 의해 Package 의 Compiled Version 이 Shared Pool 에서 Clear 되지 않는다. (3) Procedure unkeep(name varchar2);keep() 의 반대기능이다. 이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는 $ORACLE_HOME/rdbms/admin/dbmspool.sql script를 참조 바랍니다. ====================================================================== ORA-07329 ORA-07331 ORA-07279: SHARED MEMORY 문제 ====================================================================== 1. 왜 Problem 이 생기나? * Oracle 은 Process와 SGA(System Global Area) 간의 Communication를 위해 Shared Memory와 Semaphore를 사용한다. Oracle Instance 가 뜰 때 SGA를 Create하기 위해 Main Memory의 임의의 부분을 할당하는데 이 때 Shared Memory 나 Semaphore 가 적절하지 않으면 이에 관련한 Error가 발생한다. 2. 해결 방안 SGA는 Shared Memory 안에 생기므로 Shared Memory 는 각 Process에게 사용 가능해야 한다. Shared memory 와 Semaphore parameter 는 - SHMMAX = 1개의 shared memory segment 의 maximum size, SGA 크기 이상 - SHMMIN = 1개의 shared memory segment 의 minimum size, 1 byte - SHMMNI = shared memory identifier의 숫자, 100 이상 - SHMSEG = 1개의 process에 attach되는 shared memory segment의 maximum 갯수, 10 이상 - SEMMNS = system의 semaphore 갯수, 200 이상 - SEMMNI = 시스템에서 identifier를 setting하는 semaphore 수, 70 이상 - SEMMSL = semaphore set 당 최대 semaphore 갯수, initSID.ora 의 processes값 이상 * 추천하는 Semaphore와 Shared Memory Parameter Operating System Shared Memory Parameters Semaphore ===================== ======================== =================================== Sun OS SHMSIZE= 32768 SEMMNS= 200 SHMMNI= 50 SEMMNI= 50 Solaris SHMMAX= 8388608 SEMMNS= 200 SHMSEG= 20 SEMMSL= 50 SHMMNI= 100 SEMMNI= 70 HP/UX SHMMAX= 0x4000000(64Mb) SEMMNS = 128 SHMSEG= 12S EMMNI= 10Digital Unix (DEC AlphaOSF/1) SHMMAX= 4194304 SEMMNS= 60 SHMSEG= 32S EMMSL= 25 UltrixUse System DefaultSEMNS SEMMSL= 5 AT&T Unix SHMMAX= RAM-Dependant SEMMNS= 200 8 or 16Mb RAM SHMMAX= 5 MbFor All RAM 32 Mb RAM SHMMAX= 8 MbValues 64 Mb RAM SHMMAX= 16 Mb 128 Mb RAM SHMMAX= 32 Mb 256 Mb RAM SHMMAX= 64 Mb 512 Mb RAM SHMMAX= 128 Mb 1024 Mb RAM SHMMAX= 256 Mb 2048 Mb RAM SHMMAX= 512 Mb SHMSEG= 6 for all RAM Values SHMMIN= 1 for all RAMValues Dynix/PTX SHMMAX= 11010048 SEMMNS= 200 SHMSEG= 20 SEMMSL = 85 Other ParameterNOFILES = 128 DG/UX SHMMAX= 4194304 SEMMNS= 200 SHMSEG= 15 Shared Memory 와 Semaphore Parameter는 OS 의 Kernel Configuration 화일에 반드시 지정되어야 하며, File의 위치는 OS마다 차이가 있다. 현재의 Shared Memory와 Semaphore Configuration 을 알기 위해서는 다음의 Command를 이용한다. $ sysdef |more * HP-UX (relevant sections only) 에서의 예: Semaphore 관련 Parameters - maximum value for semaphores(semaem)= 16384 - Semaphore map(semmap)= 4098 - number of semaphore identifiers(semmni) = 4096 - total number of semaphores in the system(semmns) = 8192 - number of semaphore undo structures(semmnu) = 1536 - semaphore undo entries per process(semume) = 512 - semaphore maximum value(semvmx) = 32767 Shared Memory 관련 Parameters - maximum shared memory segment size in bytes(shmmax) = 536870912 - minimum shared memory segment size in bytes(shmmin) = 1 - maximum shared memory segments in system (shmmni) = 512 - maximum shared memory segments per process(shmseg) = 512 NOTE: SHMMAX는 현 system에 8개의 instance가 수행될 수 있는 충분한 값이다. * Shared memory 또는 semaphore parameters 를 변경하기 위해서는 ... 1. Oracle Instance를 Shutdown 한다. 2. OS의 Kernel Configuration File이 있는 곳으로 간다. 3. System Utility 또는 Editor를 이용해서 필요한 값을 바꾼다. System Utility는 다음과 같다 ---------------------------- | OS | Utility | ---------------------------- | HP/UX | SAM | | SCO | SYSADMSH | | AIX | SMIT | | Solaris | ADMINTOOL | ---------------------------- 4. Kernel 을 Reconfigure 한다. 5. System을 Reboot 한다. 6. Oracle Instance를 startup시킨다. [ 예제 ] Solaris 2.3/2.4 parameters and commands: 1. SQLDBA 에서 : SQLDBA> shutdown SQLDBA> exit 2. Superuser(root)로 login 하고 : # cd /etc 3. /etc/system file 에 다음을 추가 한다: set shmsys:shminfo_shmmax=8388608 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=20 set semsys:seminfo_semmns=200 set semsys:seminfo_semmni=70 4. Kernel을 reconfigure 한다: # touch /reconfigure 5. Machine 을 reboot 한다: #init 6 6. SQLDBA 에서 : SQLDBA> startup SQLDBA> exit Oracle의 init<SID>.ora 파라미터 화일에는 SGA에 영향을 주는 Parameter들이 있다. OS의 Shared Momory와 Semaphore Parameter에 연결된 이 Parameter의 setting은 System과 Oracle의 Performance에 중요한 영향을 미친다.