About DATABASE

UNIX: EXAMPLE OF TURNING ON LISTENER TRACE
==========================================

PURPOSE

---

RDBMS Manual, Oracle Note에서 Oracle Net Listener Trace File 생성 방법을
설명하고 있으나 실예가 고객에 이해를 돕는데 유용하여
아래와 같이 그 실제 상황에 가까운 예를 적습니다.

Explanation

---

Example

---

listener trace를 생성하는 절차는 다음과 같습니다.

a. 문제가 발생하는 database server에서:
prompt$ echo $TNS_ADMIN

b. TNS_ADMIN 환경 변수가 설정되어 있으면
prompt$ cd $TNS_ADMIN

설정되어 있지 않으면
prompt$ cd $ORACLE_HOME/network/admin

prompt$ vi listener.ora
...
TRACE_LEVEL_<listener name>=16
TRACE_FILE_<listener name>=<filename>
TRACE_DIRECTORY_<listener name>=<directory>
TRACE_TIMESTAMP_<listener name>=ON
:wq
prompt$ lsnrctl stop <listener name>
prompt$ lsnrctl start <listener name>

Production 환경에서 trace file로 인한 disk full 없도록 trace file을 재사용할 수도 있습니다.

TRACE_LEVEL_<listener name>=16
TRACE_FILE_<listener name>=<filename>
TRACE_DIRECTORY_<listener name>=<directory>
TRACE_TIMESTAMP_<listener name>=ON
TRACE_FILENO_<listener name>=<# of trace files to switch>
TRACE_FILELEN_<listener name>=<length of each trace file in KB>

예)
TRACE_LEVEL_listener1=16
TRACE_FILE_listener1=listener1
TRACE_DIRECTORY_listener1=/u01/app/oracle/rdbms/10.2.0/network/trace
TRACE_TIMESTAMP_listener1=ON
TRACE_FILENO_listener1=3
TRACE_FILELEN_listener1=1024768

3. Reproduction
문제 발생 후 trace를 중단 하시기 바랍니다.

4. Stopping Oracle Net listener Tracing:
prompt$ vi listener.ora
...
TRACE_LEVEL_listener=0
...
:wq
prompt$ lsnrctl stop <listener name>
prompt$ lsnrctl start <listener name>
prompt$

4. Listener가 내려간 동안은 기존의 database session들은
Oracle Server Process의 의하여 계속 처리가 됩니다.
새로운 database connection request는 listener가 내려간 동안
처리되지 않아 그 동안 client는 database에 연결할 수 없습니다.

5. TAR를 통한 진행 중에는 생성된 Oracle Net Server Trace file들을
WinZip 또는 Unix의 compress (.tar.Z), GNU zip (tar.gz)으로
묽어 올려주시기 바랍니다.

6. TAR를 통한 진행 중에는 문제가 발생한 시:분도 같이 알려 주시면
Net engineer가 Trace 중에 해당 시간에 기록된 내용을 중심으로 보게 됩니다.

Reference Documents

# listprod1 is the name of the first listener
LISTPROD1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sameer)(PORT = 1521))
      )
    )
  )
 
# SID list of the listener listprod1
SID_LIST_LISTPROD1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = samor92)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = samor92)
    )
  )
 
# listprod2 is the name of the second  listener
LISTPROD2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sameer)(PORT = 1526))
      )
    )
  )
 
# SID list of the listener listprod2
SID_LIST_LISTPROD2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = samor92)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = samor92)
    )
  )

- How to start the multiple listeners?

C:\>lsnrctl
Starting listener listprod1
LSNRCTL> start listprod1
Starting listener lisprod2
LSNRCTL> start listprod2

In oracle 8.1.6 or lower, you have the following limitation
1) You can only have one active Listener.ora file.  If a SID is defined in more than one listener.ora, you will see the following error in the logfile:   "Multiple Listeners found for SID ..."
2) If multiple Listeners are defined in the Listener.ora, a SID can only be defined in one of the Listeners.  i.e.  No Support for Multiple Listeners for the same SID

- What are the available parameters, which I can set, while listener is running?
The list of the parameters which you can set can be viewed as

LSNRCTL> help set
The following operations are available after set
password                  raw mode                    display mode
trc_file                  trc_directory               trc_level
log_file                  log_directory               log_status
current_listener          startup_waittime            save_config_on_stop

For setting the log directory command is
LSNRCTL> set log_directory c:\temp\listlog1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
LISTENER parameter "log_directory" set to c:\temp\listlog1
The command completed successfully
LSNRCTL>

Now the listener.log will be created in c:\temp\listlog1 directory.
To record the changed permanently in the listener.ora use the save config command as

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
Saved LISTENER configuration parameters.
Listener Parameter File   D:\oracle\ora92\network\admin\listener.ora
Old Parameter File   D:\oracle\ora92\network\admin\listener.bak
The command completed successfully

If you wish to save current configuration automatically when ever you stop and restart the listener, you must add the parameter
SAVE_CONFIG_ON_STOP_LISTENER = ON   in the listener.ora prameter. Whenever you stop and restart the listener , the listener will be backuped up automatically with the extension .bak in $ORACLE_HOME/network/admin and new listener.ora will be created.
ADMIN_RESTRICTION_<LISTENER>  = ON|OFF , if it is ON , you cannot  change the listener setting while it is running.

- How do I see the current setting without opening the listener.ora?
With the help of show command, you can see the listing of commands whose setting you can see without physically opening of the listener.ora file.

LSNRCTL> show help
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode                   displaymode                 trc_file
trc_directory             trc_level                   log_file
log_directory             log_status                  current_listener
startup_waittime          snmp_visible                save_config_on_stop

Check the value of currently setting of log_directory

LSNRCTL> show log_directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
LISTENER parameter "log_directory" set to c:\temp\listlog1
The command completed successfully

Check the value of currently setting of trc_level

LSNRCTL> show trc_level
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
LISTENER parameter "trc_level" set to off
The command completed successfully

trace event를 세팅에 대하여

init.ora에 특별한 파라메터를 세팅하므로서 memory나 디스크 corruption에대해
분석할 수있는 방법이 있다. 이러한 파라메터들은 performance에 영향을 줄 수
있으므로 정상적인 상태에서는 세팅하지 않는다. 여기서는 event information을
모으기 위한 시간을 절약하는데 목적이 있으며 몇개의 event 세팅의 보기를 보여
준다. 여기서 보여지는 것 말고도 많은 event들이 있으며 그것들은 그때그때의
필요에 의해 세팅되어 진다.
Event trace를 하기 위한 방법에는 두 가지가 있다. 첫번째는 init.ora에 세팅
하는 것이며 이것은 모든 세션에 영향을 미친다. 두번째 방법은 alter session
set events 명령어를 이용하는 것이며 현재 세션에만 영향을 미친다.

Syntax는 다음과 같다.
Event = "event syntax | , LEVEL n | : event syntax | , LEVEL n | .. "

sql 문장을 이용한 syntax는 다음과 같다.
SQL> alter session set events 'event syntax LEVEL n: event syntax
LEVEL n: ...';

예를들어 콘트롤 화일의 전체 내용을 보기 위한 syntax는 다음과 같다.
SQL> alter session set events 'IMMEDIATE TRACE NAME CONTROLF LEVEL 10';

event syntax는 여러개의 키워드를 가지고 있다. 첫번째의 키워드는 event
number이거나 special keyword, 즉 IMMEDIATE일 수 있다. Event number는
오라클 에러 번호이거나 internal error code일 수 있다. Event code는
그 값에 따라 action이 발생되는 커널의 logic에 의해 결정된다. 이러한 내부
event code는 /rdbms/mesg/oraus.msg 화일에 있다. 다른 operating system에
서는 상기 화일이 binary format이거나 text가 아닐 수 있다. Internal event
code는 10000 - 20000의 범위에 있다.
만약 IMMEDIATE라는 키워드가 첫번째 나온다면 이것은 unconditional event
임을 나타낸다. 그리고 그 결과는 이 명령어가 사용된 직후 발생된다. 이러한
명령어는 alter session 라. 명령어에서만 효과가 있고 init.ora에서 설정된
문장은 효과가 없다.
두번째와 세번째 키워드는 각각 대부분 TRACE와 NAME이다. TRACE라는 키워드는
output이 trace file로 dump될 것을 의미하며 NAME이라는 키워드는 실제 event
name 바로 앞에 온다. TRACE말고도 다른 키워드가 올 수 있으나 그것들은 오라
클의 개발팀을 위해 사용된다. 마지막 키워드는 event name으로 실제 dump할
내용을 정한다.
만약 IMMEDIATE 키워드를 사용하지 않는다면 얼마나 오래동안 trace를 할지를
지정해야 한다. FOREVER 키워드를 사용했을때에는 해당 session이나 instance가
살아있는 시점까지 event는 살아있게 된다.
Event 문장 다음에는 대부분의 event에서 LEVEL 키워드가 세팅된다. 만약 level
이 없다면 errorstack을 dump하는 동안 exception이 발생할 수 있다. 일반적
으로 LEVEL은 1에서 10까지의 범위를 갖는다. 10은 그 event에 대해서 전체
내용을 전부 dump할 것을 의미한다.
예를들어 LEVEL을 1로 했을때에는 control file을 dump할때 control file
header만을 dump하지만 10이면 전체 내용을 dump한다. BLOCKDUMP키워드 일때
에는 LEVEL은 특별한 의미를 가지며 그것은 datablock의 실제 주소가 십진수로
표시된다.

이제 몇개의 예를 들어 보자. 다음은 init.ora 에 세팅될 수 있는 내용이다.

EVENT = "604 TRACE NAME ERRORSTACK FOREVER"
EVENT = "10210 TRACE NAME CONTEXT FOREVER, LEVEL 10"

첫번째 문장은 process가 ORA-604를 만날때마다 error stack을 dump하게 될 것
이다. 두번째 문장은 디스크에서 캐쉬로 블록을 읽을때 block integrity를 체크
하는 event이다.
다음 문장들은 SQL을 통해서 event를 세팅하는 예를 보인다.

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BLOCKDUMP
LEVEL 67109037';
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF
LEVEL 10';
SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE
LEVEL 10';

첫번째 문장은 데이타블록 67109037을 trace file로 dump할 것이다. 모든
오라클 데이타베이스의 데이타블록은 block number와 file number의 유일한
값으로 되어 있다. 상기예에서 67109037은 block number와 file number와의
십진표현법이다. 이러한 정보는 operating system dependent하다. 두번째
문장은 전체 콘트롤화일의 내용을 trace file로 dump한다.
세번째 문장은 system state 를 trace로 dump한다. (system state는 현재
RDBMS에 의해 잡혀있는 object에 대한 정보를 보여주며 process state dump는
특정 process에 잡혀있는 object에 대한 process state dump를 보여준다)
이러한 정보는 system hang problem과 같은 문제의 분석에 유용할 수 있다.

EVENT NAMES
여기서는 사용가능한 event에 대한 감각을 익히기 위해서 몇개의 event name을
들어 설명한다.

- ERRORSTACK
오라클은 어떤 process가 특별한 에러를 만났을때 그에 관련된 정보를 저장하기
위해 "error stack"을 생성한다. Oracle foreground process는 에러 메세지를
받는다. Application 운영중에는(Developer/2000 Forms) 에러와 관련된 자세한
정보를 foreground process는 받지 못한다.
이 event는 전체 에러 스택을 trace로 dump해 주며 oracle error를 debugging
하는데 유용하게 이용할 수 있다. 예를들어 application이 ora-604와 함께
실행이 중단되면

SQL> alter session set events '604 trace name errorstack forever';
은 에러 스택을 trace file로 생성시켜 줄 것이다.

- SYSTEMSTATE
이 event는 전체 system state를 dump하며 이것은 모든 프로세스의 state dump
를 포함한다.
Performance degradation, process hang이나 system hang을 분석하는데 있어서
유용하다.
SQL> alter session set events 'immediate trace name systemstate
level 10';

- EVENT CODE 10013 AND 10015
이것들은 corrupted rollback segment 문제를 분석하는데 사용될 수 있다.
이러한 경우에 데이타베이스는 startup 할 수 없고 ora-1578에러를 발생한다.
만약 원인이 rollback에 있는 것으로 판단됐을때 init.ora에 상기 event를 세팅
하면 trace 화일을 생성할 것이다. syntax는 다음과 같다.
Event = "10015 trace name context forever"

- EVENT CODES 10210 AND 10211
이것들은 block checking,가 event checking event들이다. 일반적으로 디스크
블록이 캐쉬로 읽어들여질때 기본적인 integrity checking이 수행된다. 상기
event들을 세팅하므로서 오라클은 부가적인 check을 하게되며 이것은 block
corruption을 분석하는데 결정적이다. PMON은 항상 block checking을 가능한
상태로 유지하고 있다. 정상적인 상태에서도 block-checking이나 index-
checking을 수행하는 것이 좋을 수도 있으나 over head가 있다.
Event = "10210 trace name context forever, level 10"

- EVENT CODES 10231 AND 10232
정전으로 인해 디스크의 한 블록 전체의 내용이 없어질 수 있다. 그런 상황에서
그 테이블의 자료를 살려내기 위해 일반적으로 export를 받는다. 그러나 full
table scan은 bad block을 만났을때 실패할 것이다. 이러한 상황을 피해가기
위해 event 10231의 세팅이 필요하다. 이 event는 full table scan시
corrupted block을 skip할 것이다. Event 10232가 세팅된다면 corrupted
block을 trace file로 dump된다. 이러한 event를 세팅하기 위해서는 몇몇 조건
들이 필요하다.

. 이러한 블록은 오라클에 의해 soft-corrupted되어야 한다. 즉 오라클이
corrupt block을 발견할 때 그 블록에 어떤 bit을 세팅하므로서 손상되었다고
표시한다. 오라클이 soft-corrupt block을 하기 위해서는 event 10210을 세팅
해야 한다. 그러므로 event 10210과 함께 10231이 쓰이는 것이 권장된다.
. 인덱스를 이용하여 해당 블록을 접근하는 것은 안되며, 단지 full table
scan만이 수행 되어야 한다. 만약 손상된 테이블을 export하려면 이러한 event
를 init.ora에 세팅해 놓아야 한다.

SQL> alter session set events 10231 trace name context off;
Event = "10231 trace name context forever, level 10"
첫번째 문장은 세션에서 lock-checking 을 더 이상 하지 않게 하며 두번째는
block-checking을 실행하게 된다.

Reference Documents

---

<Note:1051056.6>
<Note:21184.1>

TABLE(INDEX)을 다른 TABLESPACE로 옮기는 방법(8I,ALTER TABLE .. MOVE)
====================================================================

방법은 두 가지가 있다.

첫째, "alter table X move tablespace Y" and "alter index X rebuild
tablespace Y" 구문을 사용하는 것이고,

두번 째는 8 version까지 사용했던 exp/imp utility를 이용하는 것이다.

(두번째 방법은 Bul : 11204, 10087 / Note : 1012307.6 참조)

본 문서에서는 첫번 째 방법만을 언급하고자 한다.

아래 script를 이용하여 tablespace 내에 있는 table / index에 대한
"Move tablespace" command를 확보한다.


----------------------- cut here -----------------------

Moveall.sql
set echo off

column order_col1 noprint
column order_col2 noprint

set heading off
set verify off
set feedback off
set echo off

spool tmp.sql

select decode( segment_type, 'TABLE',
segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || segment_name ||
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
chr(10) ||
' tablespace &1 ' || chr(10) ||
' storage ( initial ' || initial_extent || ' next ' ||
next_extent || chr(10) ||
' minextents ' || min_extents || ' maxextents ' ||
max_extents || chr(10) ||
' pctincrease ' || pct_increase || ' freelists ' ||
freelists || ');'
from user_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
order by 1, 2
/

spool off

set heading on
set verify on
set feedback on
set echo on

REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit tmp.sql, modify as needed and run it
REM @tmp

------------------------------- cut here ----------------------------

위 script를 실행함으로써, "alter table move" and "alter index rebuild"
구문을 생성할 수 있고, 더불어 storage parameter 값 또한 얻을 수 있다.

생성 구문을 보면 table 먼저 alter .. move 하고,
이후 alter index ... rebuild 작업을 수행함을 확인할 수 있다.
alter table ... move 수행 결과로 index 상태가 unusable 로 변환되기 때문에
index rebuild 작업이 필요한 것이고, table의 downtime을 최소화하기 위해
필요한 조치이다.


비 교
=====

1. "ALTER TABLE .. MOVE" 방법(A)이 기존 exp/imp 방법(B)보다 빠르고 유연성을
제공한다.

2. A 방법은 기존 objects를 drop할 필요가 없으나, B 방법은 필요하다.

3. A 방법을 사용할 때 LONG / LONG RAW datatype을 갖고 있는 table은 위 구문을
적용할 수 없다.(only B method)

4. B 방법을 사용할 때 작업 도중 변화되는 block 정보를 Oracle이 보장하지 못한다.


Reference
=========

<Note:147356.1> , <Note:1012307.6>

pga_aggregate_target를 설정하고 workaread_size_policy=auto
로 되어 있다면 pga에서 sort run을 만들고 sort대상이 클 경우에는
temp를 쓰면서 쓰게 되어 있습니다.
workarea_size_policy=auto이면 sort_area_size가 아무리
설정되어 있더라도 무시되어요..

우선..
sort를 하는 대상을 찾아봐야 할것 같네요..
혹시 full scan해서 sort하는 것이 있지 않은지요?

## sort_sess.sql
doc
sort중인 세션을 출력
#
set line 150
col username format a10
col osuser format a10
col tablespace format a15
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
c.spid,
a.username,
a.osuser,
a.status
FROM v$session a,
v$sort_usage b,
v$process c
WHERE a.saddr = b.session_addr and a.paddr=c.addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

## sort_usedblock.sql
doc
sort하고 있는 블록을 출력
#
col tablespace_name format a10
set linesize 120
select TABLESPACE_NAME,
current_users,
TOTAL_BLOCKS,
USED_BLOCKS,
FREE_BLOCKS
from v$sort_segment;

CPU부하 급증 SQL 찾기
먼저 시스템 자원현황을 살펴보기 위해서 unix에서 top을 실행한다.
[KAMCO:/oracle/app/oracle/product/806/work]# top
load averages:  1.54,  1.47,  2.07                                                         12:24:08
1461 processes:1457 sleeping, 2 stopped, 2 on cpu
CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap
Memory: 9216M real, 211M free, 9434M swap in use, 7976M swap free
  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
17334 oracle     1  51    0 2510M 2488M sleep  36:46  2.24% oracle
29538 root       5  55    0 4808K 3632K sleep   3:50  1.48% save
29536 root       5  53    0 8048K 6864K sleep   3:34  1.47% save
29537 root       5  60    0 4768K 3648K sleep   0:22  1.35% save
24582 root       1   0    0  414M 1288K sleep 150.0H  0.86% rtf_daemon
9781 oracle    11  58    0 2510M 2481M sleep 933:20  0.74% oracle
6993 oracle     1  20    0 2509M 2485M cpu9   83.3H  0.57% oracle
2208 oracle     1  50    0 2515M 2492M sleep   0:01  0.52% oracle
2211 oracle     1   0    0 2592K 1712K cpu8    0:00  0.36% top
  476 tuxkigum  11  50    0 2524M 2491M sleep  45:13  0.32% oracle
  470 tuxkigum  12   2    0 2522M 2491M sleep  45:24  0.12% oracle
  474 tuxkigum  12  58    0 2524M 2490M sleep  41:19  0.10% oracle
25911 kamzone   11  14    2 2510M 2486M sleep   2:00  0.10% oracle
8824 xwnts     39  23   12  322M   51M sleep  82:17  0.10% java
17692 oracle     1  25    0 2515M 2491M sleep 111:29  0.09% oracle

이중에서 cpu의 사용량이 많은 프로세스(17334)에 대해서 어떤 SQL이 사용되고 있는지
살펴보자. 아래의 SQL을 cpu_overhead.sql로 저장하고 실행한다.
---------------------------------------------------------------------------------------
-- programed by Lee Chang Kie --
ttitle 'Cpu Overhead SQL Check'
clear screen
set verify  off
set pagesize 200
set linesize 110
set embedded off
set feedback off
col col0 format a25 heading "Sid-Serial"
col col1 format a10 heading "UserName"
col col2 format a10 heading "Schema"
col col3 format a10 heading "OsUser"
col col4 format a10 heading "Process"
col col5 format a10 heading "Machine"
col col6 format a10 heading "Terminal"
col col7 format a20 heading "Program"
col col8 format 9 heading "Piece"
col col9 format a8 heading "Status"
col col10 format a64 heading "SQL"
!rm -f ./cpu_overhead.lst
spool cpu_overhead.lst
Select A.sid||','||A.serial# col0,
       A.username col1,
       A.schemaname col2,
       A.osuser col3,
       A.process col4,
       A.machine col5,
       A.Terminal col6,
       upper(A.program) col7,
       C.piece col8,
       A.status col9,
       C.sql_text col10
  From v$session A, v$process B, v$sqltext C
Where B.spid = '&1'
   and A.paddr = B.addr
   and C.address = A.sql_address
order by C.piece;
spool off
----------------------------------------------------------------------------------------
[KAMCO:/oracle/app/oracle/product/806/work]# sqlplus internal
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Mar 4 13:01:40 2005
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @cpu_overhead
그러면 다음과 같이 프로세스 번호를 입력하라고 뜰 것이다.
Enter value for 1:
top명령을 실행했을 때 가장 상위에 나타는 프로세스ID(17334)를 입력한다.
그러면 아래와 같이 부하를 가중시키는 SQL이 검출될 것이다.
필요시 힌트, 인덱스정책, 실행계획등이나 트레이스를 떠서 필요한 튜닝을
수행해야 할 것이다.
                                            Cpu Overhead SQL Check
Sid-Serial                UserName   Schema     OsUser     Process    Machine    Terminal
------------------------- ---------- ---------- ---------- ---------- ---------- ----------
Program              Piece Status   SQL
-------------------- ----- -------- ----------------------------------------------------------------
664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      0 ACTIVE   SELECT A.LOAN_NO LOAN_NO,A.LOAN_TYPE LOAN_TYPE,NVL(A.SANGYE_DATE
V1-V3)

664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      1 ACTIVE   ,' ') SANGYE_DATE,NVL(A.RUPT_DATE,' ') RUPT_DATE,NVL(A.SANSIL_DAV1-V3) 

1. top
select  lpad(to_char(s.sid),6,' ') sid,p.spid,to_char(s.LOGON_TIME,'mm/dd hh24:mi:SS') Time, s.username,
        s.program, s.status, lpad(to_char(s.command),3,' ') cmd
  from v$session s,
       v$process p
 where s.paddr = p.addr and
       p.spid=&1
;

2. 위에서 나온 sid를 확인하여 아래 쿼리 수행 후 cpu 부하 유발 sql을 골라내어 튜닝대상으로 지정한다.

select s.sid, q.sql_text        
  from v$sql q, v$session s       
  where q.address=s.sql_address   
    and q.hash_value=s.sql_hash_value
    and s.sid in (&sid)
    ;

ORACLE의 bdump 폴더의 alert_[DB NAME].log

Thread 1 cannot allocate new log, sequence   [squence num]
Checkpoint not complete

Alert 로그·파일에 상기의 메세지가 빈발하고 있는 경우, 트랜잭션(transaction)량에 대해서
온라인 REDO 로그·파일의 사이즈, 또는 개수가 부족한 것을 나타낸다.

* 체크 포인트는 메모리상의 갱신이 끝난 데이터(더티 블록)를 디스크상의 데이터·파일에 반영하는 처리이며
반영 처리 완료 후에는 제어 파일과 각 데이터·파일·헤더의 갱신 처리도 실시한다.
체크 포인트의 발생 빈도는 초기화 파라미터의 설정 가능하다.

 

데이터베이스 로그 모드              아카이브 모드
자동 아카이브             사용 안함
아카이브 대상            C:\oracle\ora92\RDBMS
가장 오래된 온라인 로그 순서     11707
아카이브할 다음 로그   11707
현재 로그 순서           11709

 

보니깐 자동아카이브 부분이 걸리더라구요..

지식인에 찾아보니 아카이브 모드일경우에는 반드시 자동아카이브가 true로 되어있어야한다고..

그래서 눈 딱감고 (운영중인 시스템이라.. ^^)

자동아카이브 부분 true로 수정하고 오라클 내렸다 올렸습니다..

 

그리고 다시 조회해보니

 

데이터베이스 로그 모드              아카이브 모드
자동 아카이브             사용

아카이브 대상            C:\oracle\ora92\RDBMS
가장 오래된 온라인 로그 순서     11707
아카이브할 다음 로그   11707
현재 로그 순서           11709

 

alter system archive log stop/start

 

OS : Linux Fedora 9

Oracle Release 11.2.0.1.0

오라클 11g(Release 11.2.0.1.0)를 성공적으로 설치하였음에도 불구하고 서버가 설치된 머신 에서는 sqlplus등의 Tool로 정상적으로 접속이 이루어졌지만, 원격으로 접속하는 경우 ORA-12514오류가 발생하면서 접속이 이루어 지지 않아 다음의 내용을 오라클 서버의 listener.ora를 수정함으로 해결 되었다. 수정이 된 이후에는 오라클 리스너를 새로 시작해야 한다.

 

대상파일 : /Oracle/Database/oracle/product/11.2.0/dbhome/network/admin/listener.ora

 

수정 前

+1 LISTENER =  +2   (DESCRIPTION_LIST =  +3     (DESCRIPTION =  +4       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  +5       (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))  +6     )  +7   )  +8  +9 ADR_BASE_LISTENER = /Oracle/Database/oracle

 

수정 後(붉은색 부분이 추가된 부분)

+1 SID_LIST_LISTENER =  +2 (SID_LIST =  +3     (SID_DESC =  +4         (SID_NAME = PLSExtProc)  +5         (ORACLE_HOME = /Oracle/Database/oracle/product/11.2.0/dbhome)  +6         (PROGRAM = extproc)  +7     )  +8     (SID_DESC =  +9         (ORACLE_HOME = /Oracle/Database/oracle/product/11.2.0/dbhome) +10         (SID_NAME = mjs) +11     ) +12 ) +13 +14 +15 LISTENER = +16   (DESCRIPTION_LIST = +17     (DESCRIPTION = +18       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) +19       (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521)) +20     ) +21   ) +22 +23 ADR_BASE_LISTENER = /Oracle/Database/oracle

 

No. 12032

CHECKPOINT NOT COMPLETE에 대해서
================================

database가 전체적으로 멈춘것처럼 어떠한 새로운 작업도 진행이 안되는 경우,
alertSID.log file를 확인해 보면 아래와 같은 message가 적힌 경우가 있다.

Checkpoint not complete

이러한 message가 의미하는 것은 무엇이며, 왜 발생하는지, 그리고 어떠한 조치가
가능한지 자세히 살펴본다.

1. checkpoint란 무엇인가?
~~~~~~~~~~~~~~~~~~~~~~~~~
checkpoint는 memory내의 block buffer의 내용과 disk상의 data block간의 내용을
맞추는 database event라고 할 수 있다.
checkpoint가 발생하면, 그때까지 memory내의 block에 가해진 모든 변경 사항을
disk상의 datafile내에 반영하게 된다. database crash에 의해 recovery가 필요
하게 되면 이 checkpoint이후의 변경사항에 대해서만 recovery를 시도하면 되므로,
recovery를 쉽고 효율적으로 수행할 수 있다.

checkpoint중에 수행되는 작업은 다음 두 가지로 정리할 수 있다.
- DBWR가 buffer cache내의 모든 변경된 block을 datafile에 기록한다.
- LGWR(혹은 CKPT)가 현재 발생한 checkpoint의 SCN값을 datafile과 controlfile
  에 기록한다.

여기에서 중요한 것은 checkpoint event가 발생하여 block buffer에 어느 block이
disk로 씌여져야 하는가를 표시하면 dbwr는 이렇게 표시된 buffer를 순서와 관계없이
disk로 write하게 된다. 즉 redo log group1에 대한 buffer를 checkpoint하고 있는
중에 redo log group2에 대한 checkpoint요청이 들어오면 redo log group1에 대한
checkpoint를 끝내고 group 2에 대한 처리를 하는 것이 아니고, redo log group1에
대한 checkpoint가 진행 중에 redo log group2에 해당하는 buffer도 buffer cache
내에 모두 표시한 후 이것도 모두 group 1과 group 2의 구별이나 순서는 유지하지
못한채 같이 checkpoint가 이루어지는 것이다.
이것은 이후에 설명할 Checkpoint not complete가 발생하는 원인을 밝혀주는
중요한 근거가 된다.

checkpoint가 발생되는 경우는 다음 4가지 경우이다.
(1) log switch
(2) log_checkpoint_interval 만큼의 os block 간격
(3) log_checkpoint_timeout 초 간격
(4) alter system checkpoint명령문 수행시
앞의 세가지 경우에 대해서는 아래 별도의 section으로 자세히 설명하였다.


2. checkpoint와 성능
~~~~~~~~~~~~~~~~~~~~

checkpoint를 자주하면 db crash후 recovery시간을 줄일 수 있는 대신에, 운영
중에서는 속도를 저하시킨다. checkpoint중에는 해당 datafile의 header내용이
고정되어 datafile header에 대한 다른 작업이 진행되지 못한다. 또한
checkpoint간격이 너무 짧으면 뒤에서 설명할 'checkpoint not complete'
현상이 발생할 가능성도 커지게 되어 checkpoint에 대한 tuning이 필요하게
되는 것이다.


3. CKPT process
~~~~~~~~~~~~

checkpoint 수행 중 checkpoint SCN의 값을 controlfile과 datafile header에
반영시키는 작업을 수행하는 background process는 LGWR 혹은 CKPT process이다.
LGWR와 CKPT중 어느 process가 작업하는지는 Oracle version과 checkpoint_process
라는 parameter에 의해 결정된다. CKTP process를 사용하여 LGWR process의
작업을 줄이는 것이 성능 향상에 상당한 도움이 되기 때문에 oracle version이
증가할수록 CKPT process의 사용이 자동으로 유도된다.

(1) Oracle 7.0 - 7.3.2
    initSID.ora file에 CHECKPOINT_PROCESS=TRUE 로 지정되어 있는 경우만,
    CKPT process가 구동된다.

(2) Oracle 7.3.3, 7.3.4
    db_files가 50이상이거나, 혹은 db_block_buffers값이 10,000 이상이면,
    CHECKPOINT_PROCESS parameter에 관계없이 항상 CKPT process가 구동된다.
    CHECKPOINT_PROCESS를 TRUE로 지정하면 db_files나 db_block_buffers값과
    무관하게 CKPT process가 기동된다.

(3) Oracle8 8.0.3이상
    CKPT process 는 항상 기동되어, CHECKPOINT_PROCESS를 지정하면, 다음과
    같은 오류가 발생한다.

    LM-101 "unknown parameter name checkpoint_process"

4. checkpoint가 발생되는 시점
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
checkpoint가 발생하는 시점은 아래와 같이 4가지 경우가 있다.

(1) log switch
    log switch가 발생하면 항상 checkpoint가 발생한다. 단, checkpoint가
    발생한다고 하여 log switch가 되는 것은 아니다. log swtich가 되는 경우는
    사용 중인 log file이 모두 씌여졌거나, 명시적으로
    'alter system switch logfile' 문장을 수행한 경우이다.

    online redo log file의 크기는 성능과 복구에 중요한 영향을 미친다.

(2) LOG_CHECKPOINT_INTERVAL
    이 parameter의 단위는 OS block size로, 이 크기만큼의 redo log file에
    기록한 후에 checkpoint가 발생하는 것이다.
    예를 들어 log_checkpoint_interval로 10,000이 지정되어 있고, 대부분의
    Unix의 경우처럼 한 os block size가 512 bytes이면, 10,000 * 512 bytes =
    5m가 된다.
    redo log file의 크기가 20m라고 가정할 때, 5m마다 checkpoint가 발생하므로,
    redo log file하나를 기록하는 동안 4번의 checkpoint가 발생하는 것이다.

    그러나 반대로 log file이 이 크기보다 작은 3m라면 log_checkpoint_interval
    parameter는 무시되고 log swtich발생마다, 즉 log file에 3m만큼 기록한
    후에는 항상 checkpoint가 발생하게 된다.

(3) LOG_CHECKPOINT_TIMEOUT

  이 parameter의 단위는 초(second)이며, 이 시간 간격으로 checkpoint가 발생
  하게 된다. log swtich전에 checkpoint가 발생하길 원한다면
  log_checkpoint_timeout보다는 log_checkpoint_interval을 사용하도록 권장한다.
  log_checkpoint_timeout은 transaction이 발생하지 않아도 일정 시간이 지나면,
  무조건 checkpoint를 발생하게 하여 불필요한 checkpoint를 발생시키기 때문이다.

5. 그 외의 checkpoint관련 parameter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
(1) DB_BLOCK_CHECKPOINT_BATCH

  이 parameter는 Oracle7과 Oracle8.0까지만 사용되는 것으로 checkpoint시에
  DBWR가 한번에 disk에 write하는 block buffer의 갯수를 지정한다.
  이것을 32정도의 큰 값으로 지정하면 checkpoint속도를 향상시킬 수 있다.

(2) LOG_CHECKPOINTS_TO_ALERT
 
  log swtich는 항상 alert.log에 기록이 남는다. log switch보다 자주
  checkpoint가 발생하도록 한 경우 checkpoint발생 시간과 간격을 확인하려면
  log_checkoiint_to_alert=true로 init.ora file에 지정하면 확인이 가능하다.


6. redo log file과 checkpoint
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

앞에서 언급한 바와 같이 log switch시에는 항상 checkpoint가 발생한다. 일반적
으로, log_checkpoint_interval이나 log_checkpoint_timeout을 log swtich보다
간격을 크게 하여 checkpoint가 log swtich시에만 발생하도록 사용한다.

redo log file의 크기가 너무 작으면 불필요한 checkpoint작업이 자주 발생할 수
있다. alert.log를 보면 아래와 같은 형태의 log switch에 대한 기록이 남게
되는데, 이것을 통해 log switch 발생 간격을 확인할 수 있다.

Thread 1 advanced to log sequence 248
  Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log

log switch가 자주 발생하게 되면 성능 저하를 가져올 수 있으며, 일반적으로
oracle에서는 한시간에 한번 정도의 log swtich를 권장하고 있다.
오라클 사용자의 경우 log file을 작게 유지하는 이유 중 current redo log file의
corruption시 잃게되는 정보의 양이 log file이 클 수록 비례적으로 큰것을
염려하는 경우가 많은데 이러한 경우는 redo log file에 member를 두고,
그 member를 다른 disk에 위치시킴으로써 위험을 줄일 수 있다.

log file의 크기가 너무 작아 log switch가 자주 발생하게 되는 경우의 또 다른
문제점은 log file의 status가 ACTIVE인 상태로 오래 지속되는 것이다. 이것은 아래
설명할 checkpoint not complete같은 현상에도 직접적인 영향을 미친다.

redo log file은 log swtcih가 발생하고 checkpoint가 진행되는 동안 status가
ACTIVE 상태가 된다. 이것은 해당 redo log에 대한 checkpoint가 완전히 끝나면,
INACTIVE 상태로 변경된다.
그런데 log switch에 의해 checkpoint event가 발생된 경우 아직 이전 redo log
file에 대한 checkpoint가 끝나지 않았다면 이전 checkpoint와 새로운
checkpoint가 구별되어 수행되는 것이 아니고 이전 checkpoint와 새로운
checkpoint가 하나로 묶여 새로운 checkpoint까지 끝나야만 이전 redo log
file도 같이 INACTIVE 상태로 변경이 된다. 이미 1번 checkpoint란 무엇인가
에서 이미 언급한 내용이다.

실제 운영중에 redo log file의 대부분이 ACTIVE인 상태로 유지되다가 batch작업이
끝날때쯤 한꺼번에 모두 INACTIVE로 되는 현상이 바로 이러한 원인때문이다.
즉, 이것은 맨 처음 redo log file에 대한 내용도 여전히 checkpoint가 끝나지 않아,
나머지 redo log 들이 모두 기다리고 있는 현상이 아니라, 빈번한 log switch로
인해 바로 직접의 log에 대한 checkpoint가 끝나기 전에 다음 checkpoint가 요청
되고 이러한 일들이 계속 이어짐에 따라 checkpoint작업이 연속적으로 묶여
발생하는 것이다. 이러한 이유로 실제로는 redo log file의 내용의 대부분이
checkpoint가 이루어졌지만, 다수가 ACTIVE인 상태로 존재하는 현상이
발생하는 것이다.

checkpoint중인 datafile header는 다른 작업이 blocking되기 때문에 checkpoint
중인 상태로 오래 지속되는 것은 바람직하지 않으며, 그래서 적정한 redo
log file의 크기와 갯수가 중요하게 된다.

7. CHECKPOINT NOT COMPLETE
~~~~~~~~~~~~~~~~~~~~~~~~~
다량의 data를 import하거나 load하는 경우 혹은 batch 작업을 진행하는 경우,
database작업이 중간 중간 멈추는 hang현상이 발생하면서, alert.log file에
다음과 같이 'Checkpoint not complete'라는 message가 여러번 적힌 것을 발견
하게 되는 경우가 있다. 

Thread 1 advanced to log sequence 248
  Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log
Thread 1 cannot allocate new log, sequence 249
Checkpoint not complete

이 message가 나타내는 것은 LGWR가 redo log를 재사용하려고 하는데, 아직 그
redo log file이 checkpoint가 끝나지 않은 상태임을 나타낸다. 예를 들어,
LGWR가 1번 redo log group에 내용을 기록하고, 2번으로 switch하면서 1번에
대한 checkpoint가 진행중인 상태에서 2번 3번 redo log도 모두 사용하고 다시
1번을 사용하려고 하면 이러한 메시지가 alert.log에 적히게 되고, 1번을 다시
사용할 수 있을 때까지 db의 모든 변경 작업은 waiting상태가 된다.

이러한 현상은 database에 변경을 가하는 dml이 한꺼번에 빠르게 수행되는 경우
LGWR가 redo log file에 빠른 속도로 많은 내용을 기록하는 경우 발생한다.

redo log file의 크기나 갯수가 작아 log switch가 빈번히 발생하는 경우,
LGWR가 redo log에 redo정보를 적으면서 한바퀴 cycle을 도는 시간이 너무
빠르면 이러한 현상은 쉽게 발생 가능하다.
앞 section에서 언급했듯이 oracle에서는 한시간에 한번 정도의 log switch와
log switch시에만 checkpoint가 발생하는 것을 기본적으로 권장한다.

그런데 redo log file의 갯수를 몇개 더 추가하고 size를 증가하였는데도,
마찬가지로 이러한 현상이 발생하는 경우가 있다. redo log group을 추가하여도,
redo log group을 추가할때만 사용이 되다가 다시 추가한 redo log group까지
모두 status가 ACTIVE이다가 어느 정도 시간이 지나거나 작업이 끝나면 동시에
모든 redo log group이 INACTIVE상태로 변경되는 것을 볼 수 있다.

이것은 이미 앞에서 여러차례 설명했듯이 이미 수행중인 checkpoint가 끝나지
않은 경우 다음 checkpoint가 들어오면 이것이 하나로 묶이게 되기 때문이며,
아래에 정리한 방법을 이용하여 tuning하여야 한다.

Checkpoint not complete현상을 해결하기 위한 방법을 정리해 보면 다음과 같다.

(1) LGWR가 redo log file을 한바퀴 도는 cycle을 지연시킨다.
    -  redo log group을 추가시킨다.
    -  redo log file의 크기를 증가시킨다.

(2) checkpoint를 자주 발생하지 않도록 한다.
    - LOG_CHECKPOINT_INTERVAL의 값을 증가시킨다.
    - online redo log file의 크기를 증가시킨다.

(3) checkpoint 작업의 효율성을 증가시킨다.
    - CHECKPOINT_PROCESS=TRUE 로 지정하여 CKPT process를 기동시킨다 (Oracle7)
    - db_block_checkpoint_batch의 값을 증가시킨다 (7.X ~ 8.0)
    - db_block_size를 증가시켜 dbwr가 checkpoint시 disk에 write하는 속도를
      향상시킨다.
      이것은 database를 완전히 다시 만드는 작업이어서 쉽게 조치하기는
      어려우나 이 문제가 자주 발생하여 심각한 영향을 미치는 경우 고려해
      볼 수 있다.
   

Reference Documents
-------------------

<Note:147468.1> Checkpoint Tuning and Troubleshooting Guide

## for unix
$ _DATE=`date +%y%m%d`
$ _DIR=/data/exp
$ exp asd/asd@asd file = ${_DIR}/file_name_${_DATE}.dmp

## for window
c:\> exp asd/asd@asd file = file_name_"%DATE%".dmp

#################################################################################
crontab 설정 파일

#!/bin/bash
datetime=$(date +%Y%m%d)

exp mediasp/mediasp file="/backup/phoenix/phoenixdb_$datetime.dmp" log="/backup/phoenix/phoenixdb_$datetime.log" full=y direct=y