Error in invoking target 'agent nmhs' of makefile

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”를 선택하여 무시하지 말고 계속 진행.

리눅스7 버전에서 11g grid 설치시 root.sh 실패

root.sh 실행시 

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


[root@localhost /]# /u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Creating trace directory

User ignored Prerequisites during installation

LOCAL ADD MODE

Creating OCR keys for user ‘oracle’, privgrp ‘oinstall’..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user ‘root’, privgrp ‘root’..

Operation successful.

CRS-4664: Node localhost successfully pinned.

Adding Clusterware entries to inittab

ohasd failed to start

Failed to start the Clusterware. Last 20 lines of the alert log follow:

2016-01-01 02:14:46.806:

[client(11401)]CRS-2101:The OLR was formatted using version 3.

2016-01-01 02:14:49.572:

[client(11424)]CRS-1001:The OCR was formatted using version 3.


ohasd failed to start at /u01/app/11.2.0/grid/crs/install/roothas.pl line 377, line 4.




해결 방안


1. deinstall 해서 설치를 삭제


[root@localhost deinstall]# su – oracle

Last login: Fri Jan 1 02:17:02 EST 2016 on pts/1

[oracle@localhost ~]$ cd /u01/app/11.2.0/grid/deinstall

[oracle@localhost deinstall]$ ./deinstall


Checking for required files and bootstrapping …

Please wait …

Location of logs /tmp/deinstall2016-01-01_02-30-16AM/logs/


############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START ###########################

[START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/11.2.0/grid

Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Standalone Server

Oracle Base selected for deinstall is: /u01/app/oracle

Checking for existence of central inventory location /u01/app/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/app/11.2.0/grid

Checking for sufficient temp space availability on node(s) : ‘localhost.localdomain’


## [END] Install check configuration ##

Traces log file: /tmp/deinstall2016-01-01_02-30-16AM/logs//crsdc.log

Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2016-01-01_02-30-16A/logs/netdc_check2016-01-01_02-30-40-AM.log

Network Configuration check config END

Asm Check Configuration START


ASM de-configuration trace file location: /tmp/deinstall2016-01-01_02-30-16AM/logs/asmcadc_check2016-01-01_02-30-41-AM.log

ASM configuration was not detected in this Oracle home. Was ASM configured in this Oracle home (y|n) [n]: n

ASM was not detected in the Oracle Home


######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/app/11.2.0/grid

The cluster node(s) on which the Oracle home deinstallation will be performed are:null

Oracle Home selected for deinstall is: /u01/app/11.2.0/grid

Inventory Location where the Oracle home registered is: /u01/app/oraInventory

ASM was not detected in the Oracle Home

Do you want to continue (y – yes, n – no)? [n]: y

A log of this session will be written to: ‘/tmp/deinstall2016-01-01_02-30-16AM/logs/deinstall_deconfig2016-01-01_02-30-35-AM.out’

Any error messages from this session will be written to: ‘/tmp/deinstall2016-01-01_02-30-16AM/logs/deinstall_deconfig2016-01-01_02-30-35-AM.err’


######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /tmp/deinstall2016-01-01_02-30-16AM/logs/asmcadc_clean2016-01-01_02-30-57-AM.log

ASM Clean Configuration END


Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2016-01-01_02-30-16A/logs/netdc_clean2016-01-01_02-30-57-AM.log

De-configuring backup files…

Backup files de-configured successfully.


The network configuration has been cleaned up successfully.

Network Configuration clean config END


—————————————->


Run the following command as the root user or the administrator on node “localhost”.


/tmp/deinstall2016-01-01_02-30-16AM/perl/bin/perl -I/tmp/deinstall2016-01-01_02-30-16AM/perl/lib -I/tmp/deinstall2016-01-01_02-30-16AM/crs/install /tmp/deinstall2016-01-01_02-30-16AM/crs/install/roothas.pl -force -deconfig -paramfile “/tmp/deinstall2016-01-01_02-30-16AM/response/deinstall_Ora11g_gridinfrahome1.rsp”


Press Enter after you finish running the above commands


<————————————-


This is the point where you have to execute the command highlighted and execute it from root user and wait for it to complete in order to deconfigure the Oracle Restart Stack.


[root@localhost ~]# /tmp/deinstall2016-01-01_02-30-16AM/perl/bin/perl -I/tmp/deinstall2016-01-01_02-30-16AM/perl/lib -I/tmp/deinstall2016-01-01_02-30-16AM/crs/install /tmp/deinstall2016-01-01_02-30-16AM/crs/install/roothas.pl -force -deconfig -paramfile “/tmp/deinstall2016-01-01_02-30-16AM/response/deinstall_Ora11g_gridinfrahome1.rsp”

Using configuration parameter file: /tmp/deinstall2016-01-01_02-30-16AM/response/deinstall_Ora11g_gridinfrahome1.rsp

CRS-2613: Could not find resource ‘ora.cssd’.

CRS-4000: Command Stop failed, or completed with errors.

CRS-2613: Could not find resource ‘ora.cssd’.

CRS-4000: Command Delete failed, or completed with errors.

CRS-4133: Oracle High Availability Services has been stopped.

Successfully deconfigured Oracle Restart stack



#########################

Setting the force flag to false

Setting the force flag to cleanup the Oracle Base

Oracle Universal Installer clean START


Detach Oracle home ‘/u01/app/11.2.0/grid’ from the central inventory on the local node : Done

Delete directory ‘/u01/app/11.2.0/grid’ on the local node : Done

Delete directory ‘/u01/app/oraInventory’ on the local node : Done

Delete directory ‘/u01/app/oracle’ on the local node : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##

Clean install operation removing temporary directory ‘/tmp/deinstall2016-01-01_02-30-16AM’ on node ‘localhost’

## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################

Oracle Restart was already stopped and de-configured on node “localhost”

Oracle Restart is stopped and de-configured successfully.

Successfully detached Oracle home ‘/u01/app/11.2.0/grid’ from the central inventory on the local node.

Successfully deleted directory ‘/u01/app/11.2.0/grid’ on the local node.

Successfully deleted directory ‘/u01/app/oraInventory’ on the local node.

Successfully deleted directory ‘/u01/app/oracle’ on the local node.

Oracle Universal Installer cleanup was successful.


Run ‘rm -rf /etc/oraInst.loc’ as root on node(s) ‘localhost’ at the end of the session.

Run ‘rm -rf /opt/ORCLfmap’ as root on node(s) ‘localhost’ at the end of the session.

Run ‘rm -rf /etc/oratab’ as root on node(s) ‘localhost’ at the end of the session.

Oracle deinstall tool successfully cleaned up temporary directories.

#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############


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


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


 $ opatch napply -local ./18370031


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

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

◆ 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 로 가서 예전 데이터파일은 삭제한다.



LGWR 대기에 따른 원인 분석 및 튜닝

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


ORA-04031

이 에러의 원인은 크게 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 이슈사항

SCN Bump UP이 이슈 
이 장애는 오라클 데이터베이스 버전이나 패치 적용 여부에 따라 작게는 원격 트랜잭션(distributed transaction) 거절(rejection)을, 크게는 데이터베이스 셧다운까지 야기시켜 문제가 됐다. 
단발성으로 끝나지 않기 때문에 가볍게 볼 수 없는 문제라고 할 수 있다. 
이에 SCN Bump UP이 무엇인지 알아보고 이를 예방하기 위한 대책과 방안에 대해 살펴본다.
우선 SCN이 무엇인지 살펴보자. 
SCN은 System Change Number로, 오라클 데이터베이스에서 Ordering의 근간이라고 할 수 있다. 
데이터베이스 내부가 변경됐을 때의 논리적 시점, 즉 타임스탬프라고 할 수 있다. 
쿼리 및 데이터베이스 변경사 항을 추적하는 데 SCN이 이용되며, 이를 통해 정확한 복구(recovery)가 가능하다. 
DBA가 SCN을 직접 활용하는 경우로는 플래시백 쿼리(Flashback Query), 데이터베이스 복구, 로그마이너(LogMiner)가 있다. SCN은 6바이트(48비트)로 구성돼 있다. 시퀀스(sequence)처럼 증가하는 방식인데, 최대 상한값은 281,474,976,710,656(약 281조, 2^48)이다. 
이 값은 시간 배분 방식의 배급제 체계를 이용해서 증가하므로 초당 16K(또는 32K)로 늘어난다. SCN은 앞으로 사용 가능한 최대 상한선인 MAX SCN 값(281조)와 현 시점에서 유효하게 사용할 수 있는 Maximum Reasonable SCN 값이 있다. 
실질적으로 사용 가능한 값의 범위(Maximum Reasonable SCN – Current SCN )는 'SCN Headroom' 이라고 부르며, Maximum Reasonable SCN의 경우 Reasonable SCN Limit라고도 불린다. SCN이 증가하는 케이스는 크게 두 가지가 있다.
<그림 1> SCN HEADROOM

● CASE 1 : 로컬 내부 프로세싱으로 인해 증가하는 경우 'Intrinsic SCN rate' 라고 한다. 

● CASE 2 : Extrinsic SCN rate는 외적 요소로 증가된 경우를 말한다. 대표 적으로 DB Link 작업 수행 시 증가되는 경우인데, 이를 흔히 SCN Propagation이 됐다고 말한다. 

분산 트랜잭션(distributed transaction) 환경에서는 Call 단위로 SCN을 전파(propagation)하는데, 트랜잭션에 참여했던 모든 DB 중에서 가장 큰 SCN으로 동기화된다. 
SCN 동기화의 경우 보통 분 산 트랜잭션의 시작과 끝인데, Heavy Traffic의 경우 동기화 작업 이 빈번하게 일어난다.
<리스트 1> DB Link로 조회와 갱신이 일어나면 가장 큰 SCN 값이 전파된다. 

조회 전 
##ORACLE A ## 
SQL> SELECT current_scn FROM v$database; 

CURRENT_SCN 
----------- 
2918527 

## ORACLE B## 
SQL> SELECT current_scn FROM v$database; 

CURRENT_SCN 
----------- 
3377931 

# A에서 B로 DBLINK을 이용하여 조회,갱신 
# ORACLE A


 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) 참고하여 진행.)

ORA-10873:FILE 1 NEEDS TO BE EITHER TAKEN OUT OF BACKUP MODE OR MEDIA RECOVERED

원인: 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 명령으로 올려준다. 

Block Corruption

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 수와 동일한 값을 가진다.