11g 설치시 나오는 에러

Error in invoking target 'agent nmhs' of makefile ....

이라고 에러가 나오는 경우.

Oracle 11g R2를 Install하는 동안 “Error in invoking target 'agent nmhs' of makefile - during 11g Installation”과 같은 오류가 보고되는 경우 그냥 무시를 하고 진행해도 되지만 “$ emctl status dbconsole” 또는 오라클 에이젼트가 오작동을 일으키는 경우가 있습니다.

위와 같이 오류가 보고되는 순간 계속 진행하지 말고,

1. “$ORACLE_HOME/sysman/lib/ins_emagent.mk”을 vi로 Open

2. “$(MK_EMAGENT_NMECTL)”를 찾아서 “$(MK_EMAGENT_NMECTL) -lnnz11” 로 수정

3. 인스톨로 “Retry”를 선택하여 무시하지 말고 계속 진행.

root.sh 실행시 

아래와 같이 나오며 설치 실패 되는 경우

해결 방안

1. deinstall 해서 설치를 삭제

2. Oracle Support 홈페이지 (metalink)에서 패치 18370031 를 받는다.

3. Grid를 재설치 하고 root.sh 실행 바로전에 OPatch 18370031을 패치 한다.

 $ opatch napply -local ./18370031

4. 패치가 끝나면 root.sh 실행

◆ RAC에서 Datafile을 파일 시스템 경로에 생성 했을때 ASM으로 이동 방법

TABLESPACE_NAME      FILE_NAME

-------------------- ------------------------------------------------------------

SYSTEM               +DATA/nris/datafile/system.260.960750649

SYSAUX               +DATA/nris/datafile/sysaux.261.960750661

UNDOTBS1             +DATA/nris/datafile/undotbs1.262.960750671

UNDOTBS2             +DATA/nris/datafile/undotbs2.264.960750681

USERS                +DATA/nris/datafile/users.265.960750685

IMSI                 D:\ORADATA\IMSI.DBF

이렇게 잘못 만들었을때 DB는 반드시 문제가 생김.

C:\> rman target /

복구 관리자: Release 11.2.0.4.0 - Production on 화 12월 12 02:03:05 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

대상 데이터베이스에 접속합니다: NRIS (DBID=3002066671)

RMAN> copy datafile 'D:\ORADATA\IMSI.DBF' to '+DATA';

backup을(를) 17/12/12에서 시작 중

복구 카탈로그 대신 대상 데이터베이스 제어 파일을 사용하고 있습니다.

채널을 할당했습니다: ORA_DISK_1

ORA_DISK_1 채널: SID=63 인스턴스=nris1 장치 유형=DISK

ORA_DISK_1 채널: 데이터 파일 복사 시작 중

입력 데이터 파일 파일 번호=00006 이름=D:\ORADATA\IMSI.DBF

출력 파일 이름=+DATA/nris/datafile/imsi.286.962503455 태그=TAG20171212T020414 RECID=1 STAMP=962503456

ORA_DISK_1 채널: 데이터 파일 복사 완료. 경과 시간: 00:00:03

backup을(를) 17/12/12에서 완료

sqlpus 로 접속해서 

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 화 12월 12 02:04:40 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

다음에 접속됨:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database datafile 'd:\oradata\imsi.dbf' offline;

데이타베이스가 변경되었습니다.

SQL> alter database rename file 'D:\oradata\IMSI.dbf' to '+DATA/nris/datafile/imsi.286.962503455';

데이타베이스가 변경되었습니다.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME

-------------------- ----------------------------------------------------------------------

SYSTEM               +DATA/nris/datafile/system.260.960750649

SYSAUX               +DATA/nris/datafile/sysaux.261.960750661

UNDOTBS1             +DATA/nris/datafile/undotbs1.262.960750671

UNDOTBS2             +DATA/nris/datafile/undotbs2.264.960750681

USERS                +DATA/nris/datafile/users.265.960750685

IMSI                 +DATA/nris/datafile/imsi.286.962503455

그리고 

D:\oradata\imsi.dbf 로 가서 예전 데이터파일은 삭제한다.

1. LGWR 기록 지연 현상

 - Disk I/O 지연으로 오라클이 대기하는 경우 발생하는 이벤트 세가지중 하나.

 - 백그라운드 프로세스의 대기 이벤트, log file parallel write(로그 파일에 로그 기록)의 시간이 길어짐.

 - 서버 프로세스의 커밋을 기다리는 이벤트, log file sync(LGWR에 기록을 요청하고 회신을 기다림)의 시간이 길어짐.

2. DB의 성능과 디스크I/O

 - 우선 OS성능과 I/O성능은 별개임을 알아야 한다.

 - I/O성능은 단순하게 분석 할 수 없으며, 여러가지 상황을 고려 해야한다.

 - 디스크는 용량이 커지다보니 사용되는 디스크의 숫자는 줄어들고 있으나, IOPS(1초당 IO횟수)는 향상되지 않아, RDBMS의 관점에서 물리적 I/O는 거의 향상되지 않는다. 디스크의 개수가 줄어들다보니 병행액세스를 이용한 I/O성능을 향상하지 못하고, 사이즈당 I/O성능도 떨어지게 된다.

 - RDBMS에서 디스크에 기록할때, OS의 버퍼캐쉬, 스토리지의 캐쉬, 디스크의 캐시 순으로 기록한다. 그렇기 때문에 OS나 스토리지, 디스크의 캐시 성능에 의해서도 속도가 좌우되며, I/O의 구조상 오라클에 가까운 계층에서 캐시에 히트할수록 고속으로 처리한다.

 - I/O에서 특히 주의 하지않으면 안되는 것이 병렬도인데, 처리할 때 병렬도가 높아 순간적으로 큐가 생겼다가 사라짐을 반복해서 응답시간이 나빠지는 경우가 있다. 

 - I/O지연이 발생하는 것은 오라클에서도 알수 있지만, OS에서도 반드시 정보를 수집해야 한다. 세션수가 많거나, 대기시간이 길거나, 응답시간이 짧아도 처리량은 동일하기 때문에 RDBMS의 I/O는 인큐가 쌓여 대기시간이 늘어나도, 실제 OS에서는 I/O가 많지 않다고 나오는 경우도 있다.

3. I/O 성능의 판단

 - AWR 또는 Statspack, OS의 sar, iostat 같은 명령어를 사용하여 분석한다.

 - log file parallel write의 경우 같은 이벤트가 v$session_wait에 반복되어 표시되므로, P1,P2,P3가 같은지 확인해서 대기 여부를 판단해야 한다.

 - 부하가 없던 시간대의 응답시간에 비해 크게 나빠지지 않았는가 여부를 확인해야 한다.

  

4. LGWR의 대기 원인 분석

 - AWR이나 Statspack을 통해 LGWR의 대기 이벤트가 어디에서 부터 발생하는지 찾아야 한다.

 - 다음은 Statspack의 60분간의 top 5 time Events 이다.

  

  Top 5 Timed Events

  ~~~~~~~~~~~~~~~~~~                                       % Total

  Event                        Waits          Time (s) Ela Time

  --------------------------- ------------    -------- --------

  log file sync                1,350,499      27,021    50.04

  db file sequential read      1,299,154      13,633    25.25

  CPU time                                     7,034    13.03

  io done                      3,487,217       3,225     5.97

  latch free                     115,471       1,325     2.45

  

  Statspack은 1800초 가량 돌면서, 8 CPU에서 거의 14,400 CPU seconds를 사용 할 수 있다.

  이 서버안에 하나의 DB만 존재한다면, CPU의 사용량은 7034/14,400 : 50% 이다. 

  (statspack CPU Time/total CPU time)

  또 한, 60분간 log file sync가 27021 초의 대기시간을 가졌고, 27021/3600 = 7.5 평균 7.5프로세스들이 log file sync 이벤트를 발생했다. 이것은 중심이 되는 병목현상임을 나타낸다.

  

  만약에 SID 3이 LGWR를 발생했다면,

  

  select sid, event, time_waited, time_waited_micro

  from v$session_event where sid=3 order by 3

  SQL> /

  

     SID EVENT                          TIME_WAITED TIME_WAITED_MICRO

  ------ ------------------------------ ----------- -----------------

  ..

       3 control file sequential read        237848  2378480750

       3 enqueue                             417032  4170323279

       3 control file parallel write         706539  7065393146

       3 log file parallel write             768628  7686282956

       3 io done                           40822748  4.0823E+11

       3 rdbms ipc message                208478598  2.0848E+12

  

   rdbms ipc message가 가장 높게 나타나지만 일반적으로 무시할수있는 이벤트이고, 

   그 다음으로 높게 나타난 io done의 경우, LGWR의 I/O call complete와 LGWR의 기록이 동기식 기록를 하고난 후의 비동기식 I/O요청이다.

   oracle session snapper.sql 을 이용하면 SID 3 세션의 1초동안 일어난 LGWR을 확인할수 있다.

   

   ------------------------------------------------------------------

   SID, SNAPSHOT START  ,SEC, TYPE, STATISTIC            ,    DELTA,

   ------------------------------------------------------------------

   3, 20080513 11:44:32,   1, STAT, messages sent        ,        9,

   3, 20080513 11:44:32,   1, STAT, messages received    ,      153,

   3, 20080513 11:44:32,   1, STAT, redo wastage         ,    39648,

   3, 20080513 11:44:32,   1, STAT, redo writes          ,      152,

   3, 20080513 11:44:32,   1, STAT, redo blocks written  ,     1892,

   3, 20080513 11:44:32,   1, STAT, redo write time      ,       82,

   3, 20080513 11:44:32,   1, WAIT, rdbms ipc message    ,   169504,

   3, 20080513 11:44:32,   1, WAIT, io done              ,   813238,

   3, 20080513 11:44:32,   1, WAIT, log file parallel write ,  5421,

   3, 20080513 11:44:32,   1, WAIT, LGWR wait for redo copy ,     1,

   

   아니면 Level 12의 10046 trace를 통해 확인 할 수 있다.

   

   WAIT #0: nam='rdbms ipc message' ela= 7604 p1=223 p2=0 p3=0

   WAIT #0: nam='log file parallel write' ela= 35 p1=2 p2=16 p3=2

   WAIT #0: nam='io done' ela= 0 p1=0 p2=0 p3=0

   WAIT #0: nam='io done' ela= 639 p1=0 p2=0 p3=0

   WAIT #0: nam='io done' ela= 0 p1=0 p2=0 p3=0

   WAIT #0: nam='io done' ela= 605 p1=0 p2=0 p3=0

   WAIT #0: nam='io done' ela= 1 p1=0 p2=0 p3=0

   WAIT #0: nam='io done' ela= 366 p1=0 p2=0 p3=0

   

   다음은 Statspack의 특정 컬럼만을 조회한 항목이다.

   

   Statistic                             Total     per Second

   ------------------------------- ----------- --------------

   redo blocks written                 230,881        2,998.5

   redo buffer allocation retries            0            0.0

   redo entries                        285,803        3,711.7

   redo log space requests                   0            0.0

   redo log space wait time                  0            0.0

   redo ordering marks                       0            0.0

   redo size                       109,737,304    1,425,159.8

   redo synch time                      40,744          529.1

   redo synch writes                    38,141          495.3

   redo wastage                      5,159,124       67,001.6

   redo write time                       6,226           80.9

   redo writer latching time                 4            0.1

   user calls                          433,717        5,632.7

   user commits                         38,135          495.3

   user rollbacks                            1            0.0

   workarea executions - multipass           0            0.0

   

   1) 5,632 user calls / 495.3 user commits 의 값으로 초당 실행시간 평균을 구할 수 있다.

   2) LGWR에 의해 3000 redo block 을 썼다는 걸 알수 있는데, 솔라리스처럼 1block에 512bytes라면 초당 1.5mb/s 인것이다.

   3) redo가 1.4mb/s로 갱신 된다면 16mbps에 근접한다.

   4) 3000 redo block에서 38,155 commit이 일어났다.

   

   위의 redo 사이즈는 일반적으로 작은 사이즈가 아니지만, 초당 500 commit을 처리하기 위해서는 더 커야한다.

   

   적절한 사이즈의 redo 인가 확인 한 후, 다음으로 고려해야 할 사항은 CPU에 의한 LGWR 대기 현상이다.

   커밋 빈도 확인을 하고, 높다면 foreground 프로세스에서 일어나는 log file sync 이벤트만을 찾아 OS에서 LGWR의 CPU사용량을 체크한다.

   

   그다음은 아주 적게 발생하는 것중 하나인데, 인스턴스 프리징현상이 있는 경우 v$sysstat을 20초 간격으로 캡쳐하고, Oracle-supplied tool, LTOM을 이용하여 iostat를 캡쳐 한다.

   23-MAR-2007-04:29:43:Redo blocks written:1564176614:Delta: 8253

   23-MAR-2007-04:30:14:Redo blocks written:1564176614:Delta:    0

   23-MAR-2007-04:30:44:Redo blocks written:1564205771:Delta:29157   

  

   위와 같이 Commit 처리량이나, redo 사이즈 CPU사용량에 영향이 없음에도 log file parallel write, log file sync 가 크게 나타난다면 하드웨어의 I/O문제라고 파악 할 수 있다.

   

   r/s  w/s kr/s kw/s wait actv wsvc_t asvc_t %w  %b device

   0.0  0.0  0.0  0.0  0.0  1.0    0.0    0.0  0 100 d6

   0.0  0.0  0.0  0.0  0.0  1.0    0.0    0.0  0 100 d2

   0.0  0.0  0.0  0.0  0.0  1.0    0.0    0.0  0 100 d1

   0.0  0.0  0.0  0.0  0.0  9.0    0.0    0.0  0 100 d0

   0.0  0.0  0.0  0.0  0.0  3.0    0.0    0.0  0 100 d13

   0.0  2.0  0.0 24.0  0.0  2.0    0.0 1000.7  0 100 d12

   0.0  0.0  0.0  0.0  0.0  1.0    0.0    0.0  0 100 d11

  

   %b는 I/O가 디스크에 의존한 %를 알려주는데 요즘엔 디스크의 여러 이유로 인해 비지율을 전적으로 신뢰 할 수 없다.

      

   r/s     reads per second

   w/s     writes per second

   Kr/s    kilobytes read per second

   Kw/s    kilobytes written per second

   wait    average number of transactions waiting for service

           (queue length)

   actv    average number of transactions  actively  being serviced

           (removed  from  the queue but not yet completed)

   svc_t   average service time, in milliseconds

   %w      percent of time there are transactions  waiting for service

           (queue non-empty)

   %b      percent of time the disk is busy  (transactions in progress)

   

   wsvc_t  average service time in wait queue, in milliseconds

   asvc_t  average service time active transactions, inmilliseconds

   I/O를 체크했을때 LGWR이 발생할 당시 I/O의 속도가 15ms 보다 크게 나온다면 디스크 속도가 떨어진다고 할 수 있다.

   I/O자체가 늦어 LGWR에 대기가 발생하는 경우는 I/O 대역폭을 올려 해결 하는 수 밖에 없다.

   OS와 스토리지 간의 지연을 찾거나, 그밖의 디스크I/O문제, 여러 상황을 고려하고 문제를 풀어나가야 한다.

   스토리지 cache의 히트율이나, OS버퍼 cache의 영향도 고려 해야 하기 때문에 단순히 RDBMS 엔지니어 혼자 판단하기는 쉽지 않다. 

   

   

Log file sync check.sql

oracle session snapper.sql

이 에러의 원인은 크게 3가지로 나뉘어 진다.

1. 다수의 사용자로 인한 SharedPoolSize부족문제

2. 구동중인 App에 비해 현저히 부족한 SharedPool사용으로 인한 문제

3. 덩치 큰 SQL 구동을 위한 연속된 SharedPool할당 불가로 인한 문제

이중 1,2는 같은 맥락에서 접근할 수 있으므로 크게 2가지라고 볼 수도 있다.

이 문제는 OTN 에서 꽤나 유명한 에러로서 아래와 TechBulletin에는 아래와 같이 언급되어 있다.

No. 10095

ORA-4031 조치 방법 과 DBMS_SHARED_POOL STORED PROCEDURE 사용법
==============================================================

Purpose
-------

다음과 같은 작업 수행 시 Oracle 이 Shared pool 에서 연속적인 메모리 부분을 찾지 못해 ORA-4031 에러를 발생시키는 것을 볼 수 있다.

. PL/SQL Routine
. Procedure 수행 시
. Compile 시
. Forms Generate 또는 Running 시
. Object 생성하기 위해 Installer 사용 시

본 자료에서는 이러한 에러에 대한 대처 방안을 설명 하고자 한다.

Problem Description
-------------------

Error 발생의 주된 원인은 Shared Pool의 사용 가능한 Memory 가 시간이 흐름에 따라 작은 조각으로 분할되어 진다는 것이다. 그래서 큰 부분의
Memory 를 할당하려 한다면 Shared Memory가 부족하다는 ORA-4031 Error가 발생한다. 즉, 전체적으로는 많은 양의 사용 가능한 Space가 있다 하더라도
충분한 양의 연속적인 공간이 없으면 이 Error가 발생한다.

1. Shared Pool과 관련된 인스턴스 파라미터
다음 3가지 파라미터는 본 자료를 이해 하는데 매우 중요하다.

* SHARED_POOL_SIZE - Shared Pool 의 크기를 지정 한다. 정수를 사용하며 "K" 나 "M" 을 덧붙일 수 있다.

* SHARED_POOL_RESERVED_SIZE - 공유 풀 메모리에 대한 대량의 연속 공간 요청에 대비해서 예약하는 영역의 크기를 지정한다. 이 영역을 사용하기
위해서는 SHARED_POOL_RESERVED_MIN_ALLOC 보다 큰 영역 할당 요청이어야 한다. 일반적으로 SHARED_POOL_SIZE 의 10% 정도를 지정한다.

* SHARED_POOL_RESERVED_MIN_ALLOC - 예약 메모리 영역의 할당을 통제한다.
이 값보다 큰 메모리 값이 할당 요청되었을 때 공유 풀의 free list 에 합한 메모리 공간이 없으면 예약된 메모리 공간의 리스트에서 메모리를 할당해 준다.
이 값은 8i부터는 내부적으로만 사용된다.

Workaround
-----------
Re-start the instance

Solution Description:
---------------------
이 Error 해결방안을 살펴 보면 다음과 같다.

1. 혹시 알려진 제품 문제에 해당 되지 않는지 확인 한다.

* BUG 1397603: ORA-4031 / SGA memory leak of PERMANENT memory occurs for buffer handles. (Workaround: _db_handles_cached=0, Fixed: 8172,901 )
* BUG 1640583: ORA-4031 due to leak / cache buffer chain contentionfrom AND-EQUAL access. (Fixed: 8171,901 )
* BUG 1318267: INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031. (Workaround: _SQLEXEC_PROGRESSION_COST=0, Fixed: 8171, 8200)
* BUG 1193003: Cursors may not be shared in 8.1 when they should be (Fixed: 8162, 8170, 901)


2. Object를 Shared Pool에 맞추어 Fragmentation을 줄인다.
(Dbms_Shared_Pool Procedure 이용)

다음은 크기가 크고 빈번히 access되는 package들임.

standard packages
dbms_standard
diutil
diana
dbms_sys_sql
dbms_sql
dbms_utility
dbms_describe
pidl
dbms_output
dbms_job


3. Shared Pool 을 효율적으로 사용하도록 Application Program을 조절한다.


4. 메모리 할당을 조정한다.

우선 다음 쿼리로 library cache 문제인지 shared pool reserved space 문제인지 진단한다.

SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved;

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 Shared Pool 의 연속 공간 부족의 결과이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 값을 증가 시켜서 shared pool reserved space 에 올라가는 오브젝트의 수를 줄인다. 그리고
SHARED_POOL_RESERVED_SIZE 와 SHARED_POOL_SIZE 를 충분히 확보해 준다.

만일 REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
이거나
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
이면 ORA-4031 은 library cache 내의 연속된 공간 부족의 결과 이다.

해결책: SHARED_POOL_RESERVED_MIN_ALLOC 을 줄여서 shared pool reserved space 를 보다 쉽게 사용할 수 있도록 해준다. 그리고 가능하면 SHARED_POOL_SIZE 를 증가시킨다.


5. DBMS_SHARED_POOL STORED PROCEDURE 사용법

이 stored package는 dbmspool.sql을 포함하며 7.0.13 이상 version에서 사용가능하다. 이는 다음과 같이 3가지 부분으로 나누어 진다.

Procedure sizes(minsize number):-> Shared_Pool_size 안에서 정해진 Size 보다 큰 Object를 보여준다.

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되지 않는다.

Procedure unkeep(name varchar2):-> keep() 의 반대 기능이다

이 Procedure들과 사용법에 대해 보다 더 자세한 정보를 위해서는 $ORACLE_HOME/rdbms/admin/dbmspool.sql script 또는 오라클 레퍼런스 매뉴얼을 참조하기 바람.


Reference Documents
-------------------
Diagnosing and Resolving Error ORA-04031.

위의 내용과 관련하여 SharedPoolSize 조절 방법은 아래와 같다.

이미 공지의 사실이지만 서두로 언급하면 Oracle은 Background Process와 SGA영역으로 구분된다.

그중 SGA는 SharedPool과 RedoLogBuffer, BufferCache로 이루어져 있다.

이중 SharedPool은 SQL Area와 Data Structure로 이루어져 있다.

SharedPool Size를 산정하는 방법은 아래와 같다.

계산 공식　　　　　　　　

Session당 최대메모리사용량(Max Session Memory) * 동시 접속하는 User의 수　
+　 Shared SQL 영역으로 사용되는 메모리양　　　　　　　　　
+　 Shared PLSQL을 위해 사용하는 메모리 영역　　　　　　　　　　
+　 최소 30%의 여유 공간　

계산 예제　　　　　　　　　

　 (1) 적당한 user session에 대한 session id를 찾는다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sid from v$process p, v$session s　　　　　　　　　　
　　　　　　　　　　　　 where p.addr=s.paddr and s.username='SCOTT';　　　　　　　　　

　　　　　　　　　　　　　 SID　　　　　　　　　
　　　　　　　　　　 ----------　　　　　　　　　
　　　　　　　　　　　　　　 29　　　　　　　　　
　　　　　　　 1 rows selected.　　　　　　　　　

　 (2) 이 session id에 대한 maximum session memory를 찾는다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA> select value from v$sesstat s, v$statname n　　　　　　　　　　
　　　　　　　　　　　 where s.statistic# = n.statistic#　　　　　　　　　　
　　　　　　　　　　　 and n.name = 'session uga memory max' and sid=29;　　　　　　　　　

　　　　　　　　　　 VALUE　　　　　　　　　　　　　　
　　　　　　　　　　 -----------　　　　　　　　　
　　　　　　　　　　　 273877　　　　　　　　　　　　　　　　　
　　　　　　　 1 rows selected.　　　　　　　　　
　　　　　　　　　
　 (3) Total shared SQL area를 구한다.　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$sqlarea;　　　　　　　　　

　　　　　　　 SUM(SHARAB　　　　　　　　　
　　　　　　　 ---------------------　　　　　　　　　
　　　　　　　　　　　　　　 8936625　　　　　　　　　
　　　　　　　 1 row selected.　　　　　　　　　
　　　　　　　　　
　 (4) PLSQL sharable memory area를 구한다.　　　　　　　　　
　　　　　　　　　
　　　　　　　 SQLDBA>　 select sum(sharable_mem) from v$db_object_cache;　　　　　　　　　

　　　　　　　 SUM(SHARAB　　　　　　　　　
　　　　　　　 ------------------　　　　　　　　　
　　　　　　　　　　　 4823537　　　　　　　　　
　　　　　　　 1 row selected.　　　　　　　　　
　　　　　　　　　
　　　　　　　　　
　 (5) Shared pool size를 계산한다.　　　　　　　　　
　　　　　　　　　　　　　　　　　
　　　　　　　　　　　　 274K shared memory　 *　 400 users　　　　　　　　　
　　　　　　　 +　　　　　 9M Shared SQL Area　　　　　　　　　　　　　　
　　　　　　　 +　　　　　 5M PLSQL Sharable Memory　　　　　　　　　　
　　　　　　　 +　　　　　 60M Free Space (30%)　　　　　　　　　　　　　　

　　　　　　　 =　　　 184M Shared Pool　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　
　　　　　　　　　　
　　 이 예제에서는 Shared pool의 size는 184M가 적당하다고 할 수 있다. 이때 Free Space(60M) 계산 방법은 전제 184 M 에 대한 30 % 정도의 추정치 이다.　　　　　　　　
　　　　　　　　　
Shared Memory부족 (ORA-4031)에 대한 대처

　다음과 같은 방법으로 에러를 피해 갈 수 있다.- "Sys.dbms_shared_pool.keep" procedure사용.　　　　　　　　

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,prvtpool.sql를 수행시켜 package를 create시킨 후 사용한다.　　　　　　　　
　　　 (자세한 사항은 bulletin 10095,Oracle7 Server Tuning 4장12를 참조한다.)　　　　　　　　　
　　　　　　　　　

ORACLE_HOME/dbs/initSID.ora에 보시면 Processes=???값과 sessions=?????라는
값에 좌우가 되는데 시스템에서 허락되는
User별 process값이 있고 이 범위내에서 허용이 된다.
SQL> show parameter process;

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
job_queue_processes integer 0
log_archive_max_processes integer 1
processes integer 300
SQL> show parameter session;

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
mts_sessions integer 330
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 335
SQL>

 SCN 헤드룸(Headroom) Bump Up 관련 내용

컴퓨터 네트워크에서 시간이 지속적으로 동기화되는 것과 비슷하게, 두 데이터베이스 간에 데이터베이스 링크를 통해서 서로 통신을 할 때는 두 데이터베이스에서 사용 중인 가장 큰 SCN을 값을 택해서 각자의 SCN값을 동기화한다. 그래서 몇몇 경우엔 데이터베이스들이 SCN 헤드룸이 빠르게 줄어드는 상황을 경험하곤 한다. 이것은 해당 데이터베이스의 버그 때문이 아니라, 그 데이터베이스가 연결된 한 두군데 이상의 데이터베이스에서 그 버그가 이미 활성화되었기 때문이다. 

현재 최대 SCN을 넘어서는 SCN은 언제나 그 데이터베이스가 거부해버리기 때문에, 500년 이상 오라클 데이터베이스를 운영할 수 있도록 SCN을 제공하는 것은 어느 경우에도 영향을 받지 않았다.

 SCN 헤드룸(Headroom) Bump Up 관련 조치 방법

- January 2012 CPU (및 관련 PSU)을 적용한다.

- 적용 후 scnhealthcheck.sql을 이용하여 SCN 헤드룸(Headroom) 상태에 대해 확인한다.

  (Patch:13498243 및 마스터 노트: SCN 이슈 (문서 ID 1902993.1) 참고하여 진행.)

원인: DB가 Backup 모드에 들어간 상태에서 종료 되었을때 Startup을 하면 발생.

$ sqlplus / as sysdba

SQL> startup mount;

SQL> select * from v$backup;

쳐보면 데이터 파일들의 상태가 Active로 되어 있다.

SQL> alter database end backup;

SQL> alter database open;

해주면 끝.

RAC라면 1번 노드는 위 방식으로 올려주고,

나머지 노드는 sqlplus 에서 startup 또는 OS에서 srvctl 명령으로 올려준다. 

1. DBVERIFY 를 이용한 Block 관리

DBVerify는 Data file block, index file block, undo block 등을 점검해주는 유틸리티 이다.(복구 유틸리티가 아니다)

database가 open 상태에서 사용할 수 있는 유틸리티로 database의 중단 없이 점검이 가능하다. 대신 점검 중인 data file은 read-only 가 되기 때문에 만약 점검 중일 때 DML 작업이 발생하면 잠시 작업이 중단되었다가 다시 실행된다.

data file에 대해서만 점검이 가능하다.

기본 사용 법

dbv  file=<file name> [options]

options

- file : 점검해야 할 파일명

- start : 점검을 시작할 블록 번호(기본값은 해당 파일의 첫 번째 블록)

- end : 점검을 종료할 블록 번호(기본값은 해당 파일의 마지막 블록)

- blocksize : 점검하기를 원하는 파일의 block 크기(기본값은 2048 byte)

- logfile : 점검 결과를 저장할 파일명을 지정(기본값은 none이며 결과를 화면으로 출력함)

- feedback : 0 이상의 숫자로 설정할 수 있으며 검사가 진행되는 동안에 화면에 ... 을 찍어서 진행 과정을 보여줌(기본값은 0)

- parfile : dbv를 실행할 때 적용하는 각종 설정들을 파일에 저장해두고 불러와서 사용

- userid : ASM 기반의 파일을 점검할 경우 ASM 인스턴스에 접속해야 하기 때문에 반드시 userid를 사용해야 함

- segment_id : 특정 세그먼트(table, index, undo) 만 골라서 검사할 수 있음(9i 이상)

case 1. 특정 data file 검사

[oracle@server111 ~]$ which dbv

~/product/10g/bin/dbv

[oracle@server111 ~]$ dbv file=/home/oracle/oradata/testdb/test01.dbf

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 22:51:20 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/oradata/testdb/test01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 1280  -> 테스트 한 총 블록의 개수

Total Pages Processed (Data) : 0  -> 테스트 한 총 테이블 블록 개수

Total Pages Failing   (Data) : 0  -> 문제가 있는 블록 개수

Total Pages Processed (Index): 0  -> 테스트 한 총 인덱스 블록 개수

Total Pages Failing   (Index): 0  -> 문제가 있는 블록 개수

Total Pages Processed (Other): 8  -> 테이블이나 인덱스 외 다른 블록 개수

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1272  -> 비어있는 블록 개수

Total Pages Marked Corrupt   : 0  -> 문제가 있어서 corrupt marked 된 블록 개수

Total Pages Influx           : 0  -> 다른 사용자가 먼저 데이터를 변경하고 있어서 dbv를 하기 위해 다시 읽은 블록 개수

Highest block SCN            : 504316 (0.504316)

[oracle@server111 ~]$

case 2. 특정 세그먼트만 점검

점검해야 하는 data file의 용량이 커서 검사에 많은 시간이 소요되고 점검이 필요한 테이블에 대한 점검만 진행할 경우 특정 세그먼트만 점검을 수행할 수 있다.

SQL> create table scott.tt100 (no number) tablespace test;

Table created.

SQL> begin 

  2   for i in 1..100000 loop

  3    insert into scott.tt100 values (i);

  4   end loop;

  5   commit;

  6  end; 

  7  /

PL/SQL procedure successfully completed.

SQL> select t.ts#, s.header_file, s.header_block

  2  from v$tablespace t, dba_segments s

  3  where s.segment_name = 'TT100'

  4  and owner='SCOTT'

  5  and t.name = s.tablespace_name;

       TS# HEADER_FILE HEADER_BLOCK

---------- ----------- ------------

  7      6   11  -> segment id

SQL>

SQL> host

[oracle@server111 ~]$ dbv userid=scott/tiger segment_id=7.6.11

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 22:59:19 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 7.6.11

DBVERIFY - Verification complete

Total Pages Examined         : 256

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 0

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 256

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 0 (0.0)

[oracle@server111 ~]$

case 3. DML 도중 강제 offline 된 data file 점검

2개의 터미널을 열고 하나는 update를 수행하고, 다른 터미널에서는 update 되고 있는 table이 속한 tablespace를 강제로 offline 시킨 후 block corruption을 점검하는 실습

step 1. 1번 터미널

SQL> select count(*)

  2  from scott.tt100;

  COUNT(*)

----------

    400000

SQL> 

SQL> update scott.tt100

  2  set no=2222;

-> update 수행

step 2. 2번 터미널

SQL> @df

t/s name      NAME        STATUS    MB     scn

-------------------- --------------------------------------------- -------------- ---------- ----------

SYSTEM       /home/oracle/oradata/testdb/system01.dbf    SYSTEM   470  504806

UNDOTBS1      /home/oracle/oradata/testdb/undotbs01.dbf    ONLINE    55  504806

SYSAUX       /home/oracle/oradata/testdb/sysaux01.dbf    ONLINE   250  504806

USERS       /home/oracle/oradata/testdb/users01.dbf    ONLINE        11.25  504806

EXAMPLE       /home/oracle/oradata/testdb/example01.dbf    ONLINE   100  504806

TEST       /home/oracle/oradata/testdb/test01.dbf    ONLINE    10  506788

6 rows selected.

SQL> 

SQL> alter tablespace test offline immediate;

-> update되고 있는 table의 tablespace를 강제로 offline

Tablespace altered.

SQL> 

step 3. 1번 터미널에서 에러 발생

SQL> update scott.tt100

  2  set no=2222;

update scott.tt100

             *

ERROR at line 1:

ORA-00372: file 6 cannot be modified at this time

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

-> update 도중 에러 발생

SQL> 

step 4. DBVerify로 점검

[oracle@server111 ~]$ dbv file=/home/oracle/oradata/testdb/test01.dbf

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 23:33:37 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/oradata/testdb/test01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 1280

Total Pages Processed (Data) : 182

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 20

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1078

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 506733 (0.506733)

[oracle@server111 ~]$ 

step 5. segment id로 점검

SQL> select t.ts#, s.header_file, s.header_block

  2  from v$tablespace t, dba_segments s

  3  where s.segment_name = 'TT100'

  4  and owner='SCOTT' 

  5  and t.name = s.tablespace_name;

       TS# HEADER_FILE HEADER_BLOCK

---------- ----------- ------------

  7      6   11

SQL>

[oracle@server111 ~]$ dbv userid=scott/tiger segment_id=7.6.11

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 23:36:05 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 7.6.11

DBV-00111: OCI failure (3427) (ORA-01002: fetch out of sequence)

-> 현재 해당 segment가 속한 data file이 offline이기 때문에 조회가 되지 않는다.

-> tablespace 복구 후 online하고 다시 시도해 본다.

SQL> recover tablespace test;

Media recovery complete.

SQL> alter tablespace test online;

Tablespace altered.

[oracle@server111 ~]$ dbv userid=scott/tiger segment_id=7.6.11

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 23:47:10 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 7.6.11

DBVERIFY - Verification complete

Total Pages Examined         : 1152

Total Pages Processed (Data) : 1126

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 25

Total Pages Processed (Seg)  : 1

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 0

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Highest block SCN            : 508673 (0.508673)

[oracle@server111 ~]$

-> 현재 해당 segment에 속한 block에는 corruption이 발생하지 않은 상태이기 때문에 에러가 검출되지 않는다.

case 4. 백업 data file corruption 발생 시킨 후 점검

[oracle@server111 backup]$ dd if=/dev/zero of=/home/oracle/backup/test01.dbf

-> block corruption 발생

189641+0 records in

189641+0 records out

[oracle@server111 backup]$ 

[oracle@server111 backup]$ 

[oracle@server111 backup]$ dbv file=/home/oracle/backup/test01.dbf

DBVERIFY: Release 10.2.0.5.0 - Production on Mon Mar 12 23:49:20 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/backup/test01.dbf

Page 1 is marked corrupt

Corrupt block relative dba: 0x00000001 (file 0, block 1)

Completely zero block found during dbv: 

Page 2 is marked corrupt

Corrupt block relative dba: 0x00000002 (file 0, block 2)

Completely zero block found during dbv: 

Page 3 is marked corrupt

Corrupt block relative dba: 0x00000003 (file 0, block 3)

.........(생략)

Page 11776 is marked corrupt

Corrupt block relative dba: 0x00002e00 (file 0, block 11776)

Completely zero block found during dbv: 

DBV-00600: Fatal Error - [25] [134999752] [134999752] [134816776]

[oracle@server111 backup]$ 

-> block corruption이 발생한 block에 대해서 위와 같이 corruption 발생한 내역이 출력된다.

DBMS_REPAIR 패키지 사용.

DBMS_REPAIR 패키지는 Oracle 8i 부터 포함되어 block corruption을 detection하고 repair하는 패키지 이다.

table block과 index block을 조사하여 문제가 있는 block을 수정해주는 data corruption repair 패키지를 가지고 있으며, sys 계정으로 작업해야 한다. 엄연히 말하면 dbms_repair 패키지는 block corruption을 복구하는 것이 아니라 block corruption이 발생한 해당 block을 사용하지 않도록 하고 나머지 block들을 사용가능 하게 하는 것이다.(block corruption이 발생하면 그 이후의 block 들 모두 사용할 수 없기 때문에)

1. dbms_repair 프로시저

제약 사항 및 한계점

- lob 이나 cluster index는 지원하지 않는다.

- dump_orphan_keys 프로시저는 bitmap index, function-based index는 지원하지 않으며 3950 bytes 이상은 지원하지 못한다.

다음은 dbms_repair 패키지에 포함된 프로시저를 설명한다.

admin_table

block repair를 하기 위해 필요한 관리 작업(create, drop, purge)을 제공한다. 이런 작업을 하기 위한 테이블 들은 항상 sys schema 소유로 생성되며, 이 테이블에 손상이 발생한 블록들의 리스트를 저장하게 된다.

check_object

table 이나 index의 block corruption을 체크하고 문제가 있는 block은 admin_table 프로시저로 만든 repair table에 기록한다.

corrupt된 블록을 찾아서 corrupt 되었다고 marking까지 한다.

dump_orphan_keys

corrupted 된 블록들이 테이블과 관련된 것이라면 admin_table에서 생성한 곳에 기록이 되지만 index와 관련 있는 블록들이라면 이 테이블에 기록한다.

rebuild_freelists

object의 freelist를 재생성 한다.

segment_fix_status

ASSM 기능을 사용하고 있는 bitmap index가 corrupt 되었다면 이 프로시져가 fix해 준다

skip_corrupt_blocks

table이나 index scan 할 때 기존에 mark 된 corrupt block 들은 확인하지 않고 건너 뛴다

2. dbms_repair 사용 준비

① repair_table 생성

SQL> show user

USER is "SYS"

SQL> 

SQL> begin

  2   dbms_repair.admin_tables(

  3    table_name=>'REPAIR_TABLE',  -> 이름변경하면 안된다.

  4    table_type=>dbms_repair.repair_table,

  5    action=>dbms_repair.create_action,

  6    tablespace=>'TEST');  -> admin_table이 저장될 tablespace로 변경 가능하다.

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> set line 50

SQL> desc repair_table;  (또는 dba_repair_table)

 Name    Null?   Type

 ----------------------- -------- ----------------

 OBJECT_ID   NOT NULL NUMBER

 TABLESPACE_ID   NOT NULL NUMBER

 RELATIVE_FILE_ID  NOT NULL NUMBER

 BLOCK_ID   NOT NULL NUMBER

 CORRUPT_TYPE   NOT NULL NUMBER

 SCHEMA_NAME   NOT NULL VARCHAR2(30)

 OBJECT_NAME   NOT NULL VARCHAR2(30)

 BASEOBJECT_NAME    VARCHAR2(30)

 PARTITION_NAME     VARCHAR2(30)

 CORRUPT_DESCRIPTION    VARCHAR2(2000)

 REPAIR_DESCRIPTION    VARCHAR2(200)

 MARKED_CORRUPT   NOT NULL VARCHAR2(10)

 CHECK_TIMESTAMP  NOT NULL DATE

 FIX_TIMESTAMP     DATE

 REFORMAT_TIMESTAMP    DATE

SQL>

② orphan_key_table 생성

장애가 발생한 table과 관련있는 object( 관련 index, FK 등)를 저장하는 테이블

테이블 검사를 진행하다가 관련있는 인덱스 등이 문제가 있을 수 있기 때문에 미리 생성해 둔다.

SQL> begin

  2   dbms_repair.admin_tables(

  3    table_name=>'ORPHAN_KEY_TABLE',

  4    table_type=>dbms_repair.orphan_table,

  5    action=>dbms_repair.create_action,

  6    tablespace=>'TEST');

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> 

SQL> desc orphan_key_table;

 Name    Null?   Type

 ----------------------- -------- ----------------

 SCHEMA_NAME   NOT NULL VARCHAR2(30)

 INDEX_NAME   NOT NULL VARCHAR2(30)

 IPART_NAME     VARCHAR2(30)

 INDEX_ID   NOT NULL NUMBER

 TABLE_NAME   NOT NULL VARCHAR2(30)

 PART_NAME     VARCHAR2(30)

 TABLE_ID   NOT NULL NUMBER

 KEYROWID   NOT NULL ROWID

 KEY    NOT NULL ROWID

 DUMP_TIMESTAMP   NOT NULL DATE

SQL>

③ db_block_checking 파라미터

block이 문제가 있는지 없는지를 확인하려면 오라클 파라미터 중에서 block checking 이라는 파라미터를 true 값으로 변경해주어야 한다.

db_block_checking=true 로 설정할 경우 오라클은 모든 블록을 체크하기 시작하기 때문에 어느정도의 overhead 발생한다.

이 파라미터 값이 false 일 경우 system tablespace만 체크하고 나머지 tablespace는 체크하지 않는다.

db_block_checking 파라미터는 즉시 변경하여 적용가능한 파라미터이다 (issys_modifiable = IMMEDIATE)

SQL> alter system set db_block_checking=true;

System altered.

SQL> 

3. dbms_repair 사용(table)

실습을 하기 위해서는 먼저 data file에 block corruption을 발생시켜야 한다.

발생시키는 방법은 먼저 해당 tablespace를 offline 시키니 후 data file을 윈도우로 옮겨서 ultra editor와 같이 hex값 수정 가능한 에디터로 열어서 (테이블에 입력된 데이터 중 아무 데이터나 골라서 복사한 후) 원하는 데이터를 찾고 수정한다. 그다음 저장 후 다시 Oracle에 넣고 tablespace online 시키면 block corruption이 발생한다.

SQL> create table scott.test01 (

  2  no number,

  3  name varchar2(10)) tablespace test;

Table created.

SQL> 

SQL> begin  

  2   for i in 1..10000 loop

  3    insert into scott.test01 values (i, dbms_random.string('A',10));

  4   end loop;

  5   commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> alter tablespace test offline;         

Tablespace altered.

SQL> 

test01.dbf 파일을 윈도우로 이동시켜서 block 장애를 발생한 후 다시 리눅스로 복사한다.

SQL> alter tablespace test online;

-> online이 되지 않는다면 test tablespace를 복구한 후 online 한다.

Tablespace altered.

SQL> @df

t/s name      NAME        STATUS    MB     scn

-------------------- --------------------------------------------- -------------- ---------- ----------

SYSTEM       /home/oracle/oradata/testdb/system01.dbf    SYSTEM   470  511693

UNDOTBS1      /home/oracle/oradata/testdb/undotbs01.dbf    ONLINE    55  511693

SYSAUX       /home/oracle/oradata/testdb/sysaux01.dbf    ONLINE   250  511693

USERS       /home/oracle/oradata/testdb/users01.dbf    ONLINE        11.25  511693

EXAMPLE       /home/oracle/oradata/testdb/example01.dbf    ONLINE   100  511693

TEST       /home/oracle/oradata/testdb/test01.dbf    ONLINE    10  511753

6 rows selected.

SQL> select count(*) from scott.test01;

select count(*) from scott.test01

                           *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 32)

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

SQL> select * from scott.test01;

 NO NAME

---------- ---------------------------------------------

       369 bmJAWMCtTx

       370 ZIoUbsiQFq

..........(생략)

      1444 nfhorhlzLX

      1445 lIUNVRvlpS

ERROR:

ORA-01578: ORACLE data block corrupted (file # 6, block # 32)

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

-> 해당 table을 조회하는 도중 block corruption이 발생한 block을 만나고 그 이후의 블록은 읽지 못하고 에러가 발생한다.

1440 rows selected.

-> test01 table에는 총 10000건의 데이터가 존재하였는데 지금은 block corruption으로 인하여 1440 건의 데이터만 조회되고 있다.

SQL>

=========================================================================================================

장애블록 검색 : check_object

=========================================================================================================

corrupt 발생한 block을 찾고, 해당 block에  corrupt가 발생하였다고 marking한다.

SQL> set serveroutput on;

SQL> declare n_corrupt int;

  2  begin

  3   n_corrupt := 0;

  4   dbms_repair.check_object(

  5    schema_name=>'SCOTT',

  6    object_name=>'TEST01',

  7    repair_table_name=>'REPAIR_TABLE', 

  8    corrupt_count=>n_corrupt);

  9   dbms_output.put_line('장애블록 수: '|| to_char(n_corrupt));

 10  end;

 11  /

장애블록 수: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select object_name, block_id, corrupt_type, marked_corrupt, corrupt_description 

  2  , repair_description

  3  from repair_table;

OBJECT_NAM   BLOCK_ID CORRUPT_TYPE MARKED_CORRUPT CORRUPT_DESCRIPTION  REPAIR_DESCRIPTION

--------------------- ---------------- ----------------------- --------------------------------- -------------------------------------- ----------------------------------

TEST01         32             6148                        TRUE                                                         mark block software  corrupt

=========================================================================================================

장애블록 skip 처리 : skip_corrupt_blocks

=========================================================================================================

위에서 작업한 check_obejct 프로시저를 통해서는 corrupt가 발생한 block에 marking하는 기능까지는 되지만 여전히 나머지 블록들은 읽을 수 없는 상태이다. 때문에 corrupt 발생한 block을 skip하고 다음 블록부터 읽을 수 있도록 해야 한다.

SQL> begin

  2   dbms_repair.skip_corrupt_blocks(

  3    schema_name=>'SCOTT',

  4    object_name=>'TEST01',

  5    object_type=>dbms_repair.table_object,

  6    flags=>dbms_repair.skip_flag);

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> 

SQL> select count(*) from scott.test01;

  COUNT(*)

----------

      9632

SQL> 

SQL> select owner, table_name, skip_corrupt

  2  from dba_tables

  3  where owner='SCOTT'

  4  and table_name='TEST01';

OWNER            TABLE_NAME         SKIP_CORRUPT

------------------------------------------------------------ ------------------------------------------------------------ ----------------

SCOTT            TEST01         ENABLED

-> test01 table에 corrupt 된 블록을 skip 하도록 설정되어 있는지 확인

SQL>

4. dbms_repair 사용(index 포함)

SQL> begin

  2   for i in 1..10000 loop

  3    insert into scott.test01 values (i, dbms_random.string('A',10));

  4   end loop;

  5   commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> 

SQL> select table_name, owner, num_rows, blocks

  2  from dba_tables

  3  where table_name='TEST01';

TABLE_NAME OWNER NUM_ROWS     BLOCKS

-------------------- --------------- --------------------- ----------

TEST01             SCOTT    10000                28

-> test01 table에 10000 row가 존재하고 28 개의 block을 사용하고 있다는 것을 확인

SQL>

SQL> create tablespace indx

  2  datafile '/home/oracle/oradata/testdb/indx01.dbf' size 10M;

-> index 저장을 위한 tablespace 생성

Tablespace created.

SQL> 

SQL> create index scott.idx_test01_name on scott.test01(name) tablespace indx;

-> test01 table의 name 컬럼에 index 생성

Index created.

SQL> 

SQL> select owner, index_name, table_name, num_rows, leaf_blocks

  2  from dba_indexes

  3  where table_name='TEST01';

OWNER    INDEX_NAME     TABLE_NAME   NUM_ROWS   LEAF_BLOCKS

---------- --------------------------------------- -------------------- ------------------- ----------------------

SCOTT    IDX_TEST01_NAME    TEST01         10000            31

-> 생성된 index도 10000건의 row를 가지는 것을 확인

SQL> 

SQL> alter tablespace test offline;

Tablespace altered.

test01 data file을 윈도우로 옮겨서 block 장애를 일으킨 다음 다시 리눅스로 복사한다.

SQL> 

SQL> alter tablespace test online;

alter tablespace test online

*

ERROR at line 1:

ORA-01113: file 6 needs media recovery if it was restored from backup, or END BACKUP if it was not

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

-> test tablespace 복구 후 다시 online 해야 함

SQL> recover tablespace test;

Media recovery complete.

SQL> alter tablespace test online;

Tablespace altered.

SQL>

SQL> select * from scott.test01;

select * from scott.test01

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 12)

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

SQL> select count(*) from scott.test01;

select count(*) from scott.test01

                           *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 12)

ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'

SQL>

=========================================================================================================

장애블록 검색 : check_object

=========================================================================================================

SQL> set serveroutput on;

SQL> 

SQL> declare num_corrupt int;

  2  begin

  3   num_corrupt := 0;

  4   dbms_repair.check_object(

  5    schema_name=>'SCOTT',

  6    object_name=>'TEST01',

  7    repair_table_name=>'REPAIR_TABLE',

  8    corrupt_count=>num_corrupt);

  9   dbms_output.put_line('=============================');

 10   dbms_output.put_line('corrupted blocks: '|| to_char(num_corrupt));

 11  end;

 12  /

=============================

corrupted blocks: 1

PL/SQL procedure successfully completed.

SQL>

SQL> select object_name, block_id, corrupt_type, marked_corrupt, corrupt_description, repair_description

  2  from repair_table;

OBJECT_NAM   BLOCK_ID   CORRUPT_TYPE MARKED_CORRUPT CORRUPT_DESCRIPTION    REPAIR_DESCRIPTION

--------------------- ---------------- ------------------------ ------------------------------- ----------------------------------------- ----------------------------------

TEST01         12              6148                             TRUE                                                          mark block software corrupt

SQL> 

=========================================================================================================

index block 상태 조회

=========================================================================================================

SQL> declare num_index int;

  2  begin

  3   num_index := 0;

  4   dbms_repair.dump_orphan_keys(

  5    schema_name=>'SCOTT',

  6    object_name=>'IDX_TEST01_NAME',

  7    object_type=>dbms_repair.index_object,

  8    repair_table_name=>'REPAIR_TABLE',

  9    orphan_table_name=>'ORPHAN_KEY_TABLE',

 10    key_count=>num_index);

 11   dbms_output.put_line('================================');

 12   dbms_output.put_line('index key count : '|| to_char(num_index));

 13  end;

 14  /

================================

index key count : 368

-> test01 table의 block corruption으로 인하여 index 문제가 발생했음을 확인

PL/SQL procedure successfully completed.

SQL>

=========================================================================================================

장애블록 skip 처리: skip_corrupt_blocks

=========================================================================================================

SQL> begin

  2   dbms_repair.skip_corrupt_blocks(

  3    schema_name=>'SCOTT',

  4    object_name=>'TEST01',

  5    object_type=>dbms_repair.table_object,

  6    flags=>dbms_repair.skip_flag);

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> 

SQL> select count(*) from scott.test01;

  COUNT(*)

----------

      9632

-> block corruption 발생한 block을 skip하고 난 후 row 조회

SQL>

=========================================================================================================

장애 index 처리

=========================================================================================================

테이블에서 block corruption이 발생한 block은 index 에도 영향을 준다는 것을 위에서 확인하였다.

장애가 발생한 index block은 위에서 처럼 skip 하고 다음 블록으로 넘어가도록 할 수 없다. 때문에 해당 index를 rebuild하고, 안되면 index 재생성을 해야 한다.

SQL> select count(*) from orphan_key_table;

  COUNT(*)

----------

       368

-> 368개의 index key에 문제가 있음을 확인

SQL> 

SQL> truncate table orphan_key_table;

-> table 초기화

Table truncated.

SQL> 

SQL> alter index scott.idx_test01_name rebuild;

-> 장애 발생한 index rebuild

Index altered.

SQL>

SQL> declare num_index int;

  2  begin

  3   num_index := 0;

  4   dbms_repair.dump_orphan_keys(

  5    schema_name=>'SCOTT',

  6    object_name=>'IDX_TEST01_NAME',

  7    object_type=>dbms_repair.index_object,

  8    repair_table_name=>'REPAIR_TABLE',

  9    orphan_table_name=>'ORPHAN_KEY_TABLE',

 10    key_count=>num_index);

 11   dbms_output.put_line('================================');

 12   dbms_output.put_line('index key count : '|| to_char(num_index));

 13  end;

 14  /

================================

index key count : 0

PL/SQL procedure successfully completed.

SQL>

SQL> select count(*) from orphan_key_table;

  COUNT(*)

----------

  0

-> 

SQL>

SQL> select owner, index_name, table_name, num_rows, leaf_blocks

  2  from dba_indexes

  3  where table_name='TEST01';

OWNER    INDEX_NAME        TABLE_NAME   NUM_ROWS  LEAF_BLOCKS

------------- ------------------------------- --------------------- ------------------- -----------

SCOTT    IDX_TEST01_NAME        TEST01     9632              30

-> rebuild된 index도 skip 처리된 후의 test01 table의 row 수와 동일한 값을 가진다.

1. AVR CR BLOCK RECEVIE TIME < 15ms 

한 Node에서 다른 Node의 데이터를 무결성 읽기를 하기 위해서 기다리는 

시간을 의미 하며 정상적인 경우 15ms보다 작아야 하고, 만약 시간이 많이 

걸리는 경우는 다음과 같은 사항들을 점검해서 문제를 해결해야 한다.

(1) 각 Node의 CPU 부하가 심하거나, CPU 용량이 부족한지 점검 함 

(2) 각 Node간 네트웍 성능에 문제가 없는지 점검 함 

(3) 각 Node간 불필요한 데이터 이동이 많게 데이터 구성이나 SQL이 

수행되는지 확인 함 

SQL> 

select b1.inst_id,

b2.value "CR BLOCKS RECEIVED",

b1.value "CR BLOCK RECEIVE TIME",

((b1.value/b2.value)*10)"AVG CR BLOCK RECEIVE TIME (ms)"

from gv$sysstatb1, gv$sysstatb2

where b1.name = 'globalcache crblock receive time'

and b2.name = 'globalcache crblocksreceived'

and b1.inst_id = b2.inst_id ;

2. GLOBAL CACHE LOCK GET TIME < 20ms 

RAC 환경에서 Cache에 있는 LOCK을 획득하기 위해서 걸리는 시간이 

정상적인 경우 20ms 보다 작아야 하며, 이보다 큰 경우가 자주 발생하는 

경우 이를 해결하기 위해서 다음과 같은 사항들을 점검해야 한다. 

(1) 서로 다른 Node에서 동일 DB Objects를 자주 Access해서 발생하는 

Application Lock이 심한지 점검 함 

(2) 각 Node간 네트웍 성능에 문제가 없는지 점검 함 

(3) 각 Node의 CPU 부하가 심하거나, CPU 용량이 부족한지 점검 함 

SQL> 

select b1.inst_id,

(b1.value + b2.value)"GLOBAL LOCK GETS",

b3.value "GLOBAL LOCK GET TIME",

(b3.value /(b1.value + b2.value)*10)"AVG GLOBAL LOCK GET TIME (ms)"

from gv$sysstatb1,gv$sysstatb2,gv$sysstatb3

where b1.name = 'globallock sync gets'

and b2.name = 'globallock async gets'

and b3.name = 'globallock gettime'

and b1.inst_id = b2.inst_id

and b2.inst_id = b3.inst_id;

RAC 환경에서는 1,2 번을 주기적으로 점검하여 결과치가 적정한 범위를 벗어

나는지 아닌지 모니터링을 하는 것이 바람직하다.

ORA-03137: TTC protocol internal error : [12333] [4] [195] [2] [] [] [] []

_optim_peek_user_binds Parameter 변경

1. 에러현상

▶ Forms 화면 쿼리 시 Ora-03114 not connected to oracle 에러 발생.

 

2. 체크사항

2.1 DB 서버 alert 파일 확인

▶ 파일

alert.log

▶ 에러

 ORA-03137: TTC protocol internal error

2.2 DB 서버 trace 파일 확인

▶ora_9896_i184522.trc

▶ 내용 생략.

2.3  메타링크

     ▶ 오라클 메타링크에서 ORA-03137를 검색하니 다음과 같은 버그가 알려져 있음.

Bug 9703463  ORA-3137 [12333] or ORA-600 [kpobav-1] When Using Bind Peeking

This note gives a brief overview of bug 9703463.
The content was last updated on: 17-SEP-2011
Click here for details of each of the sections below.

Affects:
Product (Component)
   
Oracle Server (Rdbms)
Range of versions believed to be affected
   
Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected

    11.2.0.1
    11.1.0.7

Platforms affected
   

Generic (all / most platforms affected)

Fixed:

This issue is fixed in
   

    12.1 (Future Release)
    11.2.0.2 (Server Patch Set)
    11.2.0.1 Bundle Patch 12 for Exadata Database
    11.1.0.7.8 Patch Set Update
    11.2.0.1 Patch 8 on Windows Platforms
    11.1.0.7 Patch 37 on Windows Platforms

Symptoms:
·         
·         Error May Occur
·         Internal Error May Occur (ORA-600)
·         ORA-3137
·         ORA-600 [kpobav-1]
·         Stack is likely to include kxsPeekBind

Description
               Frequent errors like ORA-3137 [12333] are raised. If the patch for bug:9243912
               has been applied, you will see ORA-600 [kpobav-1] errors.
 
               Rediscovery Notes:
              Optimizer bind peeking in use
              The call stack includes "kpobav opibvg kxsPeekBinds kkscsCompareBinds"

              See note:1243836.1 for a detailed description of the diagnostics

     Workaround

              Disable bind peeking by setting:
              SQL> alter system set "_optim_peek_user_binds"=false;

3. 조치사항

11.2.0.2 (Server Patch Set) 또는 11.2.0.1 Patch 6 on Windows Platforms 버전으로
패치를 해야 하나 할 수 없는 상황이므로 권고한 바 대로 옵티마이저 히든 파라매터 변경 함.
ALTER SYSTEM SET _optim_peek_user_binds=FALSE

4. 조치이후
  현재까지 alert파일에 에러가 기록되지 않았으며, forms 조회시에도 에러 없음.

5. 참고사항 
http://wiki.ex-em.com/index.php/OPTIM_PEEK_USER_BINDS
파라매터명 : _OPTIM_PEEK_USER_BINDS
기본값    :  TRUE
출시버전  :  9.0.1
Scope    :  Instance/Session

Bind Variable Peeking (이하 BVP. 바인드 변수 엿보기) 기능을 활성화할지의 여부를 결정한다.
BVP란 Bind 변수를 포함한 SQL 문장에 대한 최적화 작업을 수행할 때, Bind 변수가 가리키는 값을
참조하는 것을 의미한다.
Oracle은 BVP를 통해 SQL 문장이 실행될 당시의 Bind 값을 이용하기 때문에 최적의 실행 계획을
수립할 수 있다.
BVP를 사용할 경우 Explain Plan 명령문을 통해서 확인한 실행 계획(Execution Plan)이 실제 운영
환경에서는 적용되지 않을 수 있다. Explain Plan 명령문에서는 BVP가 적용되지 않기 때문에 BVP가
적용된 런타임의 실행 계획과는 다른 실행 계획을 보고할 가능성이 있다.

만일 테스트 환경에서 성공적으로 수행된 SQL 문장이 런타임에 느린 성공을 보인다면 일차적으로
BVP에 의한 사이드 이펙트가 아닌지 검증해보아야 한다. 런타임의 실행 계획은 V$SQL_PLAN 뷰를 통해 확인 가능하다.

 Alert log File information:

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_22426.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

Trace File Information:

*** 2015-03-26 08:30:18.342
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OPS$ORACLE"','"ET$016FA3770001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.


Cause:
The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:
1. Temporary Data pump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
2. An External table has been removed without clearing up the corresponding data dictionary information. For example: Oracle Demo Schema Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been removed but the dictionary has not been updated to reflect this. The "SALES_TRANSACTIONS_EXT" table is an external table in the "SH" schema which is one of Demo Schema provided by Oracle.

Note:Our issue is due to point one. External tables are not cleaned properly.

Solutions:  To clean up the Orphaned datapump jobs.

Check and cleanup orphaned datapump jobs:

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;


To identify the external tables.

SQL> conn / as sysdba
Connected.

SQL>
set linesize 200 trimspool on
set pagesize 2000
col owner form a30
col created form a25
col last_ddl_time form a25
col object_name form a30
col object_type form a25

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/


OWNER                          OBJECT_NAME                    OBJECT_TYPE               STATUS  CREATED                   LAST_DDL_TIME
------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------
OPS$ORACLE                     ET$000E4FF90001                TABLE                     VALID   16-oct-2012 13:10:15      16-oct-2012 13:10:15
OPS$ORACLE                     ET$007360190001                TABLE                     VALID   18-sep-2012 23:17:32      18-sep-2012 23:17:32
OPS$ORACLE                     ET$00F39F430001                TABLE                     VALID   16-oct-2012 13:33:10      16-oct-2012 13:33:10
OPS$ORACLE                     ET$016FA3770001                TABLE                     VALID   16-oct-2012 13:57:36      16-oct-2012 13:57:36

8 rows selected.

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables order by 1,2
 /
OWNER                          TABLE_NAME                     DEFAULT_DIRECTORY_NAME         ACCESS_
------------------------------ ------------------------------ ------------------------------ -------
OPS$ORACLE                     ET$000E4FF90001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$007360190001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$00F39F430001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$016FA3770001                PRODUCT_REFRESH_DIR            CLOB


To Drop the external temporary datapump tables.
SQL> drop table OPS$ORACLE.ET$000E4FF90001;
SQL> drop table OPS$ORACLE.ET$007360190001;
SQL> drop table OPS$ORACLE.ET$00F39F430001;
SQL> drop table OPS$ORACLE.ET$016FA3770001;
5.To ensure there is no datapump temporary tables.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects where object_name like 'ET$%'
/

no rows selected

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE from dba_external_tables order by 1,2
 /


Reference doc   1274653.1 

Oracle 11g R2 RAC Datapump error while import, ORA-31693, ORA-29913, ORA-31640, ORA-19505, ORA-27037

While importing in Oracle 11g R2 two node RAC setup on Linux machine following error occured –

ORA-31693 ****************

ORA-29913 ****************

ORA-31640 ****************

ORA-19505 ****************

ORA-27037 ****************

Cause

From 11.2, DataPump new parameter CLUSTER is introduced.

  

CLUSTER : Default=Y

Purpose :

 Determines whether Data Pump can use Oracle Real Application Clusters (RAC) resources and start workers on other Oracle RAC instances.Syntax and Description : CLUSTER=[Y | N]

Solution

To force DataPump to use only the instance where the job is started and to replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=N.

Example:

#> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_clus%U.dmp CLUSTER=N PARALLEL=3

Above Cause and Action is available in the metalink note :

Cannot Run DataPump With PARALLEL > 1 On 11.2 RAC [ID 1071373.1]

주의: 여기에 사용된 스크립트는 오라클에서 제공되어진 스크립트를 베이스로 작성되었으나, 사용함에 있어서 리스크가 있음을 명심해야 하며, 사용 전 자신의 DB에 맞는 정확한 내용인지 확인하시기 바랍니다.


SYSAUX에 용량은 있는데 SYS.WRH$_ACTIVE_SESSION_HISTORY 테이블를 확장 할 수 없다고 나올때 처리하는 방법입니다.



SYSAUX의 사용 내역을 확인.

SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants order by 2;

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES ---------------------------------------------------------------- ------------------ AUDIT_TABLES                                                                      0 ORDIM/ORDPLUGINS                                                                  0 ORDIM/SI_INFORMTN_SCHEMA                                                          0 ULTRASEARCH                                                                       0 TSM                                                                               0 ULTRASEARCH_DEMO_USER                                                             0 STATSPACK                                                                         0 AUTO_TASK                                                                       384 ORDIM                                                                           448 EM_MONITORING_USER                                                              768 STREAMS                                                                        1024 LOGSTDBY                                                                       1408 JOB_SCHEDULER                                                                  1472 PL/SCOPE                                                                       1600 SQL_MANAGEMENT_BASE                                                            1728 SMON_SCN_TIME                                                                  3328 WM                                                                             3584 EXPRESSION_FILTER                                                              3712 TEXT                                                                           3840 SM/OTHER                                                                       8448 XSAMD                                                                          9024 LOGMNR                                                                        13696 ORDIM/ORDDATA                                                                 13888 SM/ADVISOR                                                                    22272 AO                                                                            39104 XSOQHIST                                                                      39104 EM                                                                            47168 SDO                                                                           76032 SM/OPTSTAT                                                                    76288 XDB                                                                          130176 SM/AWR                                                                       550336

쿼리뿐만 아니라 @?/rdbms/admin/awrinfo.sql 스크립트를 이용해 조회 가능

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE --------- --------- --------------------------------------------------------------------- --------------- ... ASH            19.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_2469019682_0          -  94%  TABLE PARTITION ASH             3.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_2469019682_0       -  79%  INDEX PARTITION RAC             8.0 WRH$_DLM_MISC.WRH$_DLM_MI_2469019682_0                        -  93%  TABLE PARTITION

해당 문제관한 자료는 아래 문서에서 찾을 수 있음.
AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)

해당 쿼리를 통해 ASH ROW를 확인할수 있다.

SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);

ORPHANED_ASH_ROWS -----------------             62819

해당 예제에는 row가 몇개 없지만 이 것이 굉장히 많을 경우...
아래 쿼리를 이용해서 공간을 정리 할 수 있다.

DELETE FROM wrh$_active_session_history a WHERE NOT EXISTS  (SELECT 1  FROM wrm$_snapshot  WHERE snap_id = a.snap_id  AND dbid = a.dbid  AND instance_number = a.instance_number  ); alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

위 과정은 단지 정리를 통해 해결 하는 방법으로, 패치를 하지 않으면 지속적으로 발생 할 수 있음.

아래와 같은 스크립트로 문제가 발생하는 것에 대한 추적 로그를 남길수 있는데, 각자의 DB에 맞게 수정해서 사용.

spool ash_purge_job.lst prompt 2013/10/28 by Jed S. Walker prompt This procedure is to handle an 11.2.0 bug that results in orphaned records in wrh$_active_session_history prompt this procedure runs against SYS data so be careful. prompt As a pre-caution it should not run as the SYS user prompt Due to this the procedure generates a trace file on completion so that there is prompt something available to make sure it isn't totally forgotten. prompt prompt Hit Enter to continue prompt accept continue -- Do user level stuff whenever sqlerror exit accept running_user prompt "Enter the name of the user that will own/run this job: " accept running_pwd prompt "Enter the password for user &&running_user : " -- grant necessary privileges only connect / as sysdba grant select on sys.wrm$_snapshot to &&running_user; grant select,delete on wrh$_active_session_history to &&running_user; grant execute on dbms_system to &&running_user; -- get password, connect, and install spool off connect &&running_user/&&running_pwd spool ash_purge_job.lst append whenever sqlerror continue -- create the procedure create procedure clean_ash is  v_proc_name varchar2(35):='clean_ash';  v_user_name varchar2(35):='&&running_user';  v_commit_size number:=10000;  v_commit_count number;  v_total_count number;  v_notes varchar2(4000); begin  v_total_count:=0; --initialize  loop  DELETE  FROM sys.wrh$_active_session_history a  WHERE NOT EXISTS (SELECT 1  FROM sys.wrm$_snapshot  WHERE snap_id = a.snap_id  AND dbid = a.dbid  AND instance_number = a.instance_number  )  and rownum <= v_commit_size;  v_commit_count:=sql%rowcount;  commit;  v_total_count:=v_total_count+v_commit_count;  if v_commit_count = 0 then  exit; -- end loop for now  end if;  end loop;  -- write a note for the trace file  v_notes:=''; -- start variable  v_notes:=v_notes || 'Written by Jed S. Walker October 28th, 2013 ' || chr(10);  v_notes:=v_notes || 'WARNING! The author of this script has no liability for any damage caused by your use of this script.' || chr(10);  v_notes:=v_notes || 'Please reference MOS ID 387914.1' || chr(10);  v_notes:=v_notes || 'This is a trace file from an automated job running ' || v_user_name || '.' || v_proc_name || chr(10);  v_notes:=v_notes || 'This job removes orphaned records in WRH$_ACTIVE_SESSION_HISTORY due to an Oracle 11.2.0.x bug' || chr(10);  v_notes:=v_notes || 'You should make sure the bug still applies if you continue to run this job in releases above 11.2.0.3 ' || chr(10);  v_notes:=v_notes || 'If your SYSAUX tablespace, specifically component SM/AWR, has grown too large for comfort, you should run' || chr(10);  v_notes:=v_notes || 'alter table WRH\$_ACTIVE_SESSION_HISTORY shrink space;' || chr(10);  v_notes:=v_notes || 'to recover the space after this script has cleaned out the orphaned records.' || chr(10);  v_notes:=v_notes || '' || chr(10);  v_notes:=v_notes || '' || chr(10);  if v_total_count = 0 then  v_notes:=v_notes || 'ORA-20444: This run removed ' || v_total_count || ' orphaned records.' || chr(10);  v_notes:=v_notes || 'If the count remains zero for several days, then chances are' || chr(10);  v_notes:=v_notes || 'you are on a version higher than 11.2.0.3 and this bug may have been fixed.' || chr(10);  v_notes:=v_notes || 'Verify the bug fix and if good then you should remove this job to avoid any unintended consequences.' || chr(10);  else  v_notes:=v_notes || 'This run removed ' || v_total_count || ' orphaned records.' || chr(10);  v_notes:=v_notes || 'It appears the bug is not fixed so you should continue to let this run unless Oracle Support recommends otherwise.' || chr(10);  v_notes:=v_notes || 'Have a great day!' || chr(10);  end if;  -- create the trace file  sys.dbms_system.ksdwrt(1,v_notes); exception  when others then  v_notes:=v_notes || 'ORA-20445: ' || v_user_name || '.' || v_proc_name || ' failed.' || chr(10);  v_notes:=v_notes || 'Oracle error "' ||SQLERRM|| chr(10);  sys.dbms_system.ksdwrt(1,v_notes); end clean_ash; / show errors -- create the job to run once per day so table never grows too big begin  dbms_scheduler.create_job(job_name=>'CLEAN_ASH_JOB',  job_type=>'STORED_PROCEDURE',  job_action=>'clean_ash',  number_of_arguments=>0,  repeat_interval=>'FREQ=Daily;BYHOUR=12;BYMINUTE=0',  enabled=>true,  comments=>'Special job to mitigate MOS ID 387914.1 in Oracle 11.2.0.3.x, see trace files for notes.'); end; / -- verify set pages 50 linesize 120 col owner format a20 col object_type format a20 col object_name format a20 col status format a20 select owner, object_type, object_name, status from dba_objects where object_name in ('CLEAN_ASH','CLEAN_ASH_JOB'); prompt Done, please check for errors prompt You are currently logged in as: show user spool off

만약 RAC에서 사용하길 원한다면 각각의 노드에서 한번씩 사용.

또는 아래 쿼리를 이용해서 job 스케쥴러를 확인

select log_date, owner, job_name, status, error#, actual_start_date,run_duration, instance_id
from dba_scheduler_job_run_details
where job_name = 'CLEAN_ASH_JOB'
order by actual_start_date;

원인: object statistics are locked. It turns out that in 10gR2, when you import (imp or impdp) table without data i.e. structure only, oracle will lock the table statistics.

lock 확인

SQL> select table_name, stattype_locked from dba_tab_statistics where owner = '계정' and stattype_locked is not null;

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('owner','table name');

테이블이 너무 많은 경우

SQL> set head off

SQL> set feedback off

SQL> set pages 100

SQL> set line 200

SQL> spool unlock_tb.sql

SQL> select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| owner ||''','''|| table_name ||''');' from dba_tab_statistics where owner = '계정' and stattype_locked is not null;

SQL> spool off

해서 생성 되는 스크립트를 돌려준다.

Session Full 해결 방안

ORA-00018: maximum number of sessions exceeded

세션의 상태 확인방법
sql문 : select * from v$resource_limit
         // 세션과 프로세스를 확인할 수 있다.

  processes : 프로세스 개수(백그라운드 + dedicate process)

  session : 동시 접속 세션 수

  current_utilization : 현재 접속 카운트

  max_utilization : 오라클을 시작한 이래로 최대로 접속 했을 때의 수치

  initial_allocation : init.ora 파라미터에서 설정한 수치

-----------------------------------------------------------------------------------
해결 방안

       - 오라클 재기동 하지 않는 방법

       - 오라클 재기동 하는 방법



 

* 오라클을 재기동 하지 않는 방안

 

    - 비활성중인 세션을 Kill 합니다. 근본적인 원인을 해결하는 방법은 아니나

정상화 할 수 있도록 할 수 있습니다.

           - 툴을 이용하는 방법과 쿼리를 이용하는 방법 2가지가 있습니다.

 

   1. 토드 이용 시 툴에서 session 접속하여 inactive인 세션들을 kill 합니다.

   2. 쿼리를 이용하여 해당 세션을 Kill

SQL> alter system kill session 'sid, serial#'; // 한 개 지정

 

* 예외상황 *

- alter session으로 kill이 안될 때 (ORA-00031:session marked for kill이 생길때)

  (kill을 하여도 락이 해제 되지 않는 것은 rollback하는 프로세스가 동작을 하여 kill을 해도 mark만 하고 rollback 중인 상태이므로 락이 풀렸다고 볼 수 없기 때문입니다. 어느 정도 시간을 갖고 기다리면, 결국 테이블 락이 해제는 됩니다.)

 

다음 쿼리를 이용하여 pid를 찾아서 서버에서 직접 kill 을 합니다.

SQL>  select vs.sid, vs.username, vs.osuser, vs.process fq_pid, vp.spid bg_pid

from v$session vs, v$process vp

where vs.paddr = vp.addr

order by sid;

오라클 서버 계정에서 kill -9 [bg_pid]

 

* 오라클을 재기동 하는 방안

 

    - 오라클을 재기동해야 하는 단점이 있으나 근본적인 해결방안이 될 수 있습니다.

    - 관련 파일은 init.ora 또는 spfile[SID].ora 입니다.

          오라클 기동 시 spfile[SID].ora 파일을 보고 환경 셋팅을 하여 기동하게 됩니다.

          이 파일이 없으면 기본 파일인 init.ora 이 사용됩니다.

 

1. 쿼리를 이용하여 세션의 수를 수정한 뒤 오라클 재기동

SQL> alter system set processes=[지정하고 싶은 수] scope=spfile;

SQL> alter system set open_cursors=[지정하고 싶은 수] scope=both;

ORA-27090:  Unable to reserve kernel resources for asynchronous disk I/O

Linux 에서 발생하는 에러로 aio limit 제한에 걸렸을때 나오는 에러 메세지.

Cause:  The system call to reserve kernel resources for asynchronous I/O has failed.

   -> 비동기 I/O를 위한 커널 리소스를 예약 할 수 있는 시스템 호출에 실패.

-> Disk I/O에 제한이 걸려 DB의 성능 저하 발생.

 

Solution:  /etc/sysctl.conf 수정 후 적용

다음 파라미터 값 추가

fs.aio-max-nr = 3145728

 

저장 후 Shell 에서 다음 명령 실행.

 

# sysctl –p

 

재부팅 필요 없음.

INVALID OBJECT 를 위한 스크립트 실행


select *
from dba_objects
where STATUS  = 'INVALID';


있는지 확인하고 INVALID 된것이 있으면 모든 INVALID OBJECT 를 recompile 해주는 스크립트를 실행시켜 줍니다.




SQL> @?/rdbms/admin/utlrp.sql

Linux 64bit 5.x 버전에 oracle 10g 설치후

vipca 나 srvctl 실행시

/oracle/base/product/10g/db/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

에러 발생

$ vi ORA_CRS_HOME/bin/vipca

# if [ "$arch" = "i686" -o "$arch" = "ia64" -o "$arch" = "x86_64" ]
# then
#   LD_ASSUME_KERNEL=2.4.19
#   export LD_ASSUME_KERNEL
# fi

해당 부분 주석처리

$ vi $ORA_CRS_HOME/bin/srvctl

# LD_ASSUME_KERNEL=2.4.19
# export LD_ASSUME_KERNEL

$ vi /$ORACLE_HOME/bin/srvctl

# LD_ASSUME_KERNEL=2.4.19
# export LD_ASSUME_KERNEL

해당부분 주석처리


1. 가장 많은 원인은 서버의 Oracle 쉐도 프로세스가 예기치 않게 종료된 경우 입니다.

따라서 수행중에 갑자기 ORA-3113과 3114가 발생했다면, 우선 서버의 alert.log를 점검하여 다른 Oracle 오류가 발생했는지 알아보십시요.

<< alert.log >> 서버가 UNIX 인경우 $ORACLE_HOME/rdbms/log/alert_.log 화일에 ORA-3113 에러가 발생했던 시점에서 다른 에러가 발생했는지 점검 합니다.

특히 ORA-600[],[]이 발생했으면 에러 내용을 Oracle Technical Support Center로 연락 하십시오.

2. ORA-3113의 원인 중 그 다음으로 많은 것은 SQL*NET 드라이버가 Unix의 ORACLE 실행 파일과 연결되지 않아 발생한 경우입니다.

연결을 공식적으로 수신하고 그것을 ORACLE 쉐도 프로세스에 전달한다 해도, 쉐도 프로세스는 처리방법을 모르기 때문에 어떤 방법으로도 응답하지 못할 수 있습니다.

그러므로 클라이언트는 연결순간에 ORA-3113을 보게 됩니다.

3. 세번째로 많은 원인은 서버쪽의 기계 손상이나 네트워크 고장입니다.

4. 자주 있는 것은 아니지만 같은 네트워크에서 두 서버가 같은 노드 이름을 가질 때에도 이 오류가 발생합니다.

5. ORA-3113은 토큰링 카드의 공유 RAM 크기가 16KB가 아니라 8KB로 설정 되었음을 나타내기도 합니다.

토큰 링을 사용중이라면 공유 버크 크기를 점검하고 키워 보십시요.

6. ORA-3113은 INIT.ORA 매개변수 CONTEXT_AREA와 CONTEXT_INCR이 4096이라는 값으로 설정된 경우에도 발생합니다.

그럴때는 값을 8192로 키우면ORA-3113이 해소됩니다.

이상 말한 모든 원인은 결국 클라이언트가 서버로부터 어떤 정보를 읽으러 갔다가 거기서 더 이상 연결이 없음을 발견했다는 뜻입니다.

ORA-3113은 좀 더 진단해야 추적 가능한 더 큰 문제가 있음을 알리는 신호탄에 불과합니다.

다행히도 앞서 말한 여섯가지 정보를 참고하면 해결책을 찾는 방향은 잡힐 것입니다.

우선 ORA-3113을 디버깅하려면, 루프백을 수행중에 같은 CONNECTING을 여러번 시도해 보는 것이 좋습니다.

즉, 서버의 어떤 툴이든 데스크탑 클라이언트에서 지정하는 것과 같은 연결 스트링을 사용하여 연결할 수 있습니다.

루프백을 수행중에도 똑같은 문제가 발생하면 데스크탑 클라이언트 쪽이 아니라 서버쪽에 문제가 있다고 보아야 합니다.

루프백을 수행하려면 서버에서 SQLPLUS 또는 SQLDBA를 호출하고, 서버의 SQLPLUS 또는 SQLDBA 프롬프트에서 다음과 같이 입력하십시요.

　CONNECT USERNAME/PASSWORD@t:/:

예를 들어, SQL*NET TCP/IP를 통해 Unix 서버에 연결돼 있고 SQL*Plus를 호출하고, 같은 "t::" 연결 스트링을 사용하여, 같은 SELECT 문을 내서 루프백을 해 보십시요.

오라클에는 7445, 600 에러에 대한 Trouble shooting을 위한 검색 툴을 메타링크를 통해제공하고 있다. 원인 분석이라기 보다는 입력한 값에 대한 내부 검색을 통해 관련 자료들만 찾아주는 형식이지만 비교적 정확한 검색 값을 출력한다는 점에서 쓸모가 있을 것 같다. [메타링크 바로가기]

  

Lookup By Code :

- ORA-00600: internal error code, arguments: [17113], [0x000000000]

- 위 메시지 발생한 경우 First Argument 에 17113을 대입

- ORA-07445: exception encountered: core dump [kkehbs()+44] [SIGSEGV] [Address not mapped to object]

- 위 메시지 발생한 경우 First Argument 에 kkehbs를 대입

  

Search Call Stack :

- 7445 에러의 경우 아직 지원하지 않으며 Call Stack의 내용을 입력하고 검색을 누르면메타링크의 고급검색 기능이 대신 작동한다.

- 600 에러의 경우 Argument/Register Dump 항목 이전까지의 trace파일을 입력하고 검색을 시작.

  

  

이 밖에 ORA-00600 에러는 아래 기재된 METALINK 문서를 확인하여 Trouble shooting을진행할 수 있다.

  

ORA-600 Errors 1 to 3000

Note 138300.1 "ORA-600 [105]"

Note 263295.1 "ORA-600 [106]"

Note 28104.1 "ORA-600 [504]"

Note 138871.1 "ORA-600 [510]"

Note 138888.1 "ORA-600 [525]"

Note 138939.1 "ORA-600 [711]"

Note 39308.1 "ORA-600 [723]"

Note 31056.1 "ORA-600 [729]"

Note 131490.1 "ORA-600 [733]"

Note 217860.1 "ORA-600 [1013]"

Note 138123.1 "ORA-600 [1100]"

Note 41767.1 "ORA-600 [1113]"

Note 40514.1 "ORA-600 [1114]"

Note 137262.1 "ORA-600 [1115]"

Note 66387.1 "ORA-600 [1158]"

Note 138354.1 "ORA-600 [1236]"

Note 28045.1 "ORA-600 [2103]"

Note 28929.1 "ORA-600 [2662]"

Note 31057.1 "ORA-600 [2845]"

Note 138733.1 "ORA-600 [2865]"

  

  

ORA-600 Errors 3001 to 6000

Note 30866.1 "ORA-600 [3020]"

Note 93665.1 "ORA-600 [3668]"

Note 47456.1 "ORA-600 [4000]"

Note 96642.1 "ORA-600 [4036]"

Note 43914.1 "ORA-600 [4137]"

Note 28226.1 "ORA-600 [4146]"

Note 39282.1 "ORA-600 [4193]"

Note 39283.1 "ORA-600 [4194]"

Note 138822.1 "ORA-600 [4400]"

Note 29702.1 "ORA-600 [4414]"

Note 138836.1 "ORA-600 [4454]"

Note 39553.1 "ORA-600 [4511]"

Note 73455.1 "ORA-600 [4512]"

Note 27955.1 "ORA-600 [4519]"

Note 204536.1 "ORA-600 [4820]"

Note 41840.1 "ORA-600 [4882]"

  

  

ORA-600 Errors 6001 to 9000

Note 47449.1 "ORA-600 [6002]"

Note 116552.1 "ORA-600 [6034]"

Note 40640.1 "ORA-600 [6101]"

Note 99300.1 "ORA-600 [6122]"

Note 138913.1 "ORA-600 [6193]"

Note 39399.1 "ORA-600 [6731]"

Note 41719.1 "ORA-600 [6856]"

  

  

ORA-600 Errors 9001 to 15000

Note 138325.1 "ORA-600 [12209]"

Note 33174.1 "ORA-600 [12235]"

Note 138332.1 "ORA-600 [12261]"

Note 138340.1 "ORA-600 [12304]"

Note 35928.1 "ORA-600 [12333]"

Note 28229.1 "ORA-600 [12700]"

Note 67496.1 "ORA-600 [13009]"

Note:28185.1 "ORA-600 [13013]"

  

  

ORA-600 Errors 15001 to 17000

Note 138428.1 "ORA-600 [15160]"

Note 138430.1 "ORA-600 [15201]"

Note 138431.1 "ORA-600 [15203]"

Note 131186.1 "ORA-600 [15212]"

Note 137266.1 "ORA-600 [15419]"

Note 216277.1 "ORA-600 [15456]"

Note 138457.1 "ORA-600 [15709]"

Note 67490.1 "ORA-600 [15851]"

Note 76528.1 "ORA-600 [15868]"

Note 138499.1 "ORA-600 [16201]"

Note 106607.1 "ORA-600 [16365]"

Note 138523.1 "ORA-600 [16515]"

Note 138526.1 "ORA-600 [16607]"

  

ORA-600 Errors 17001 to 30000

Note 138537.1 "ORA-600 [17003]"

Note 138541.1 "ORA-600 [17012]"

Note 41472.1 "ORA-600 [17034]"

Note 138554.1 "ORA-600 [17059]"

Note 39616.1 "ORA-600 [17069]"

Note 29616.1 "ORA-600 [17090]"

Note 138565.1 "ORA-600 [17099]"

Note 47411.1 "ORA-600 [17112]"

Note 39453.1 "ORA-600 [17113]"

Note 34782.1 "ORA-600 [17114]"

Note 138576.1 "ORA-600 [17128]"

Note 138580.1 "ORA-600 [17147]"

Note 34781.1 "ORA-600 [17148]"

Note 138586.1 "ORA-600 [17172]"

Note 263110.1 "ORA-600 [17175]"

Note 34779.1 "ORA-600 [17182]"

Note 45725.1 "ORA-600 [17271]"

Note 138597.1 "ORA-600 [17274]"

Note 134139.1 "ORA-600 [17280]"

Note 39361.1 "ORA-600 [17281]"

Note 138602.1 "ORA-600 [17285]"

Note 138621.1 "ORA-600 [17585]"

Note 138640.1 "ORA-600 [18209]"

Note 216278.1 "ORA-600 [18261]"

Note 138678.1 "ORA-600 [20084]"

Note 100073.1 "ORA-600 [25012]"

  

  

ORA-600 Errors kc

Note 138981.1 "ORA-600 [kcbgcur_2]"

Note 70097.1 "ORA-600 [kcbgcur_3]"

Note 114058.1 "ORA-600 [kcbgcur_9]"

Note 138990.1 "ORA-600 [kcbgtcr_4]"

Note 138991.1 "ORA-600 [kcbgtcr_5]"

Note 261264.1 "ORA-600 [kcbgtcr]"

Note 248874.1 "ORA-600 [kcbgtcr_6]"

Note 233612.1 "ORA-600 [kcbgtcr_12]"

Note 204512.1 "ORA-600 [kcbnew_3]"

Note 216104.1 "ORA-600 [kcbrbo1]"

Note 139011.1 "ORA-600 [kcbzpb_1]"

Note 139012.1 "ORA-600 [kcbzpb_2]"

Note 229467.1 "ORA-600 [kcbzwb_4]"

Note 139013.1 "ORA-600 [kccsbck_first]"

Note 216108.1 "ORA-600 [kcllcu_0]"

Note 263225.1 "ORA-600 [kclwcrs_15]"

Note 76434.1 "ORA-600 [kcoapl_blkchk]"

Note 248718.1 "ORA-600 [kcratr1_lostwrt]"

  

  

ORA-600 Errors kd to kw

Note 139037.1 "ORA-600 [kdddgb2]"

Note 139042.1 "ORA-600 [kdisle:nrows]"

Note 139051.1 "ORA-600 [kghalo2]"

Note 139052.1 "ORA-600 [kghasp1]"

Note 139066.1 "ORA-600 [kghxhdr1]"

Note 229809.1 "ORA-600 [kgliep_1]"

Note 66501.1 "ORA-600 [kkrqtab2]"

Note 139095.1 "ORA-600 [kkslgop1]"

Note 139116.1 "ORA-600 [kohdtf048]"

Note 264061.1 "ORA-600 [kqludp2]"

Note 139162.1 "ORA-600 [kssrmp1]"

Note 247822.1 "ORA-600 [ksmals]"

Note 139153.1 "ORA-600 [ksmguard2]"

Note 233864.1 "ORA-600 [kteuproptime-2]"

Note 139180.1 "ORA-600 [ktsgsp5]"

Note 139193.1 "ORA-600 [kttmupd_segment-]"

Note 228480.1 "ORA-600 [kwqitnmptme:read]"

Note 228480.1 "ORA-600 [kwqitnmptme:ready]"

Note 228364.1 "ORA-600 [kwqitnmptme:wait]"

  

  

ORA-600 Errors q to z

Note 248095.1 "ORA-600 [qctcte1]"

Note 216273.1 "ORA-600 [qctstc2o1]"

Note 209363.1 "ORA-600 [qerrmofbu1]"

Note 237598.1 "ORA-600 [qertqtableallocate2]"

Note 226887.1 "ORA-600 [qkagby4]"

Note 222876.1 "ORA-600 [qmxiunppacked2]"

Note 244365.1 "ORA-600 [rworupo.1]"

Note 139263.1 "ORA-600 [srsget_1]"

Note 260951.1 "ORA-600 [ttcgcshnd]"

Note 216452.1 "ORA-600 [ttcgcshnd-1]"

Note 216453.1 "ORA-600 [ttcgcshnd-2]"

클라이언트 프로그램으로 DB 접속시에 이런 에러가 나는 경우가 있습니다.

ORA-00604: error occurred at recursive SQL level 1
ORA-20101: You are not allowed to login to the <오라클계정> user!!
ORA-06512: at line 12 (<-strings)

이것 때문에 완전 애먹었는데요.

sqlnet.ora 에서 ip단위가 아닌, DB에서 유저 단위로 접속 제한을 걸어놓으면 이런 현상이 생깁니다.

유저 제한 트리거만 만들어놓고 allow_ip 테이블을 생성해두지 않아 허용 ip의 정보는 없는 상태라면 이런 현상이 발생합니다.

<DB에서 유저 단위로 접속 제한>

sys계정에서 작업 해줘야 합니다.

1. ip-address 를 저장할 Table생성

SQL> create table allow_ip ( ip_address varchar2(15), real_user varchar2(30) )

2. Scott user에 login 할 때 client 의 ip-address를 check하는 trigger 생성

SQL> create or replace trigger check_log_오라클계정 after logon on 오라클계정.schema declare flag number; begin if ora_client_ip_address is not null then select 1 into flag from allow_ip where ip_address = ora_client_ip_address;

if flag = 1 then null;

end if;

end if;

exception when no_data_found then raise_application_error (-20101, 'You are not allowed to login to the 오라클계정 user!!');

end;
/

3. 실행한 후에 table에 저장되지 않은 ip-address 를 가진 client 에서 접속하면 다음과 같이 에러가 발생하면서 접속이 실패합니다.

SQL> connect 오라클계정/비번@oracledb

ERROR: ORA-00604: error occurred at recursive SQL level 1

ORA-20101: You are not allowed to login to the Scott user!!

ORA-06512: at line 12

server 에서 터미널로 접속하는 경우에는 ora_client_ip_address 의 값이 null 이 되므로 이 경우에는 무조건 접속됩니다.

접속 제한 트리거의 조회

SELECT * FROM all_source WHERE text LIKE '%ora_client%';

로 검색을 하면 접속 제한이 걸려있는 계정을 알수 있습니다.

트리거 내용 조회

SELECT line, text FROM all_source WHERE name = '트리거이름' order by line;

1 trigger check_log_오라클계정
2 after logon on seromedu.schema
3 declare
4 flag number;
5 begin
6 if ora_client_ip_address is not null then
7 select 1 into flag from allow_ip where ip_address = ora_client_ip_address;
8 if flag = 1 then
9 null;
10 end if;
11 end if;
12 exception
13 when no_data_found then
14 raise_application_error (-20101, 'You are not allowed to login to the 오라클계정 user!!');
15 end;

트리거에 대한 자세한 정보 보기

SQL> SELECT * FROM ALL_TRIGGERS where TRIGGER_NAME='CHECK_LOG_오라클계정';

조회를 하면 권한, 소유자, 소유된 테이블, 트리거의 이름이나 트리거의 대한 모든 정보를 알수 있습니다.

특정 컬럼 값만 선택해서 셀렉트를 날려주면, 원하는 값만 조회가 가능하죠.

status에 ENABLED 되어 있으면 비활성화 해줍니다.

SQL> select distinct a.name, b.status from all_source a, ALL_TRIGGERS b where a.text like '%ora_client_ip_address%' and a.NAME=b.TRIGGER_NAME;

유저 접속 제한 풀기

SQL> alter trigger system.CHECK_LOG_오라클계정 disable;

스키마 자체는 system이 소유하고 있습니다.

이렇게 해주면 allow_ip가 없어 접속 제한이 걸린 계정으로 접속할수가 있습니다.

** Archive Hang : Archive파일이 저장되는 디렉토리에 여유공간이 없거나, 권한문제로 쓸 수 없는 경우에 발생하며,
                             redo log를 덮어 쓸 수 없으므로, DB가 멈춰버리는 현상
해결방법 순서
1. 여유공간을 만들어주거나, 권한문제 해결
2. 파라미터 변경 후 Archiver restart(DB는 끄지마세요)
alter system set log_archive_dest_state_1=defer;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=defer;
alter system set log_archive_dest_state_2=enable;
(저장공간으로 지정한 디렉토리 전부 defer로 설정했다가, enable로 설정해야합니다. 여기서는 2군데(arc 다중화개념) )
alter system archive log stop;
alter system archive log start;
alter system switch logfile;

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

오라클의 데이터 파일과 엔진의 버전이 맞지 않기 때문에 생기는 오류.

SQL> startup upgrade;

모드를 이용해서 접속 후 

SQL> @ $ORACLE_HOME/rdbms/admin/catupgrd.sql;

실행시킨후 DB 재구동을 해주면 된다.

실제 호스트 명과 /etc/hosts 안의 호스트 명이 다를때 발생

/etc/hosts 안데

127.0.0.1     (호스트명)    localhost.localdomain localhost

저 사이에 호스트명을 넣어줍니다.

ORA-07217 : sltin : environment variable cannet be evaluated

ORACLE_SID 설정이 안됐을때 나오는 에러

archive Destination Full 로 인해 DB가 Hang이 걸리고 더이상 작업을 할 수 없는 경우가 발생 할 경우,

해당 directory의 Free space영역을 늘리더라도 여전히 Hang이 걸려 있게 됩니다.

이때, sqlplus 로 login 시

ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed

에러가 발생

이후 

$ sqlplus internal

SQL> alter system archive log all;

를 하여도

ORA-16020: less destinations available than specified by

LOG_ARCHIVE_MIN_SUCCEED_DEST

와 같은 에러를 계속 발생

<원인>

Archive Destination이 Full 나면 Arch process의 작업이 중단되고 이후 directory에

여유공간이 있다 하더라도 자동으로 archiving을 재시작할 수 없기 때문.

SQL> select * from V$archive_dest;

.....

해당 Archive destination에

ORA-19504: failed to create file %s

<조치사항>

SQL> alter system set LOG_ARCHIVE_DEST_1 = 'location=/archivelogpath reopen';

solution

./runInstaller -ignoreSysPrereqs –ignoreInternalDriverError

Reference:

Cause: Unexpected error occurred while trying to check for group existence.

Action: Refer to the logs or contact Oracle Support Services. Note for advanced users: Launch the installer by passing the following flag 

-ignoreInternalDriverError.

xhost +local:oracle로 넘겨서 엔진 설치를 끝내고 나면

 

Xlib: connection to ":0.0" refused by server 에러

 

가 뜨면서 DBCA 구동이 안됩니다.

 

일반사용자가 GUI프로그램을 실행시키니 나타난 것으로, root계정으로 전환해서

# xhost +local:root
# export XAUTHORITY=/home/oracle/.Xauthority

그리고 다시 오라클계정에서 실행하면 됩니다.

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

원인 : 이는 오라클 프로그램 예외에 대한 일반적인 내부 에러 번호이다. 이는 프로세스가 로우레벨의 예상치 못한 상황에 놓였음을 뜻한다. 이 메세지의 원인은 다음과 같다.

타임아웃
파일 파손(corruption)
메모리상의 데이터 체크 실패
하드웨어, 메모리 또는 I/O 에러
잘못 저장된 파일들

처음 인자(argument)는 내부 메세지 번호이고, 다음 인자들은 다양한 숫자, 이름, 문자열이다. 그 숫자들은 오라클 버전에 따라 그 의미가 다르다.

조치방법 : 오라클 지원 서비스에 다음 정보를 첨부하여 이 에러를 보고한다.

이 에러를 유발시킨 사건들(event)
이 에러를 유발시킨 오퍼레이션
이 에러가 발생했을 당시의 운영체제와 데이터베이스 상태
ORA-00600 메세지를 받기전에 발생한 특별한 상황이나 문제점
이 에러로 인해 생성된 trace 파일 내용
Alert 파일에 이 에러와 관계된 부분

Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. Causes of this message include:

timeouts
file corruption
failed data checks in memory
hardware, memory, or I/O errors
incorrectly restored files
The first argument is the internal message number. Other arguments are various numbers, names, and character strings. The numbers may change meanings between different versions of Oracle.

Action: Report this error to Oracle Support Services after gathering the following information:

events that led up to the error
the operations that were attempted that led to the error
the conditions of the operating system and databases at the time of the error
any unusual circumstances that occurred before receiving the ORA-00600 message
contents of any trace files generated by the error
the relevant portions of the Alter files

Note: The cause of this message may manifest itself as different errors at different times. Be aware of the history of errors that occurred before this internal error.

1. 오라클 10g 패치 후(10.2.0.2 -> 10.2.0.5) startup 명령어로 DB를 open 하려고 할 때,

ORA-01092: ORACLE instance terminated. Disconnection forced 에러 발생

 

발생 원인

오라클 엔진만 패치되고 DB에 패치가 제대로 적용되지 않았을 경우

 

SQL> sqlplus / as sysdba

 

SQL> startup upgrade;

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

                             

                     시간 약 15~20분걸림

                             

SQL> shutdown immediate;

 

SQL> startup;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

SQL> !

 

$ORACLE_HOME/bin에 존재하는 파일에 권한 부여

[oracle@localhost ~] cd $ORACLE_HOME/install

[oracle@install ~] ./changePerm.sh  

SQL> select * from v$version;

2. pfile 이용시 tablespace 경로 설정이 적용이 안되어 DB가 구동이 안될때 발생

initSID.ora 파일을 수정해 준다.