About DATABASE

Applies to:

Oracle Application Object Library
Information in this document applies to any platform.
Checked for relevance on 15-Feb-2010

Solution

goal: How to Run and View a Listener Trace


fact: Oracle Application Object Library 10.7 G


fact: Oracle Application Object Library 10.7 N


fact: Oracle Application Object Library 11






fix:

1. Set the parameter TRACE_LEVEL_LISTENER=16 in the listener.ora file and
bounce the listener to start the trace.

(or)

At the command line type: 

lsnrctl trace 16

Note: This level of tracing generates a huge amount of information in the trace 
file. Turn it off once enough trace information is obtained.

2. Attempt to view a report from the client. This will produce a very detailed 
trace file of the connection attempt.

3. Set the parameter TRACE_LEVEL_LISTENER=0 in the listener.ora file and
bounce the listener to stop the trace.

(or)

At the command line type: 

lsnrctl trace OFF

4. Navigate to $ORACLE_HOME/bin

5. Run trcasst 

Ex: trcasst filename.trc>[outfile]

Where [outfile] is the name of the output file to contain the modified trace 
file.

6. View the output file

Reference:
See the Net8 Administrator's manual for details on Trace Assistant.

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Net Services - Version: 9.2.0.1.0 to 11.2.0.1 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 7-MAY-2010.

Goal

When attempting to limit the amount of trace generated when TNS listener tracing is enabled, cyclic tracing can be used. But to further restrict the amount of time TNS listener is enabled, tracing can be enabled and disabled, dynamically via the listener control, lsnrctl. This means tracing can be enabled only when required and allows the listener to run without generating trace until required.But when enabled via this method, Time stamp is missing from the trace file(s) generated. The following steps explain how to workaround the time stamp restriction.

Solution

To workaround the current restriction of TNS listener tracing enabled via listener control, generating traces without time stamp, the following steps can be used. Steps 1 to 3 must be inplaced before attempting to capture any problem with tracing.

 1. Backup the Listener.ora file, add the following parameters and save.

TRACE_LEVEL_<Listener Name> = 16
TRACE_FILE_<Listener Name> = <File Name>
TRACE_DIRECTORY_<Listener Name> = <Directory>
TRACE_TIMESTAMP_<Listener Name> = ON
TRACE_FILELEN_<Listener Name> = <Size in KiloBytes>
TRACE_FILENO_<Listener Name> = <Number>

TRACE_FILELEN and TRACE_FILENO are the cyclic tracing parameters.Ensure values used for these are large enough to capture information,without being overwritten.

2.Reload the listener, with command lsnrctl reload <listener name>

With a default listener name of "listener", the listener name can be excluded from commands.

3. Stop listener tracing, run set trc_level 0 via lsnrctl

If your listener name is not using the default name of "listener", then run command "set current_listener <listener name> in listener control, before any other command is used.

4. When TNS listener tracing is required to be re-enabled. run set trc_level 16 via lsnrctl.

 
5. Turn off tracing as soon as the problem / issue has been captured with command "set trc_level 0" from lsnrctl. (As per step 3). Remove or hash out the parameters added to the listener.ora in Step 1 and run reload command. This will stop tracing from being re-activated.

Problem Description:
====================

Established connections fails intermittently.
ORA-03113 is returned to the application.
Application must start a new connection.
No errors are generated in the database alert.log


Solution Description:
=====================

WARNING: Misuse of Windows Registry/Editor can cause serious problems
that may require you to reinstall your operating system.

Increase TcpMaxDataRetransmissions parameter in the Windows Registry


On Windows NT/2000:

HKEY_LOCAL_MACHINE
      SYSTEM       
        CurrentControlSet         
          Services
            Tcpip             
             Parameters

add a key "TcpMaxDataRetransmissions" (REG_DWORD) and set its value to 10.

On the Edit menu, click Add Value, and then add the following:

      Value Name: TcpMaxDataRetransmissions
      Value Type: REG_DWORD - Number
      Valid Range: 0 - 0xFFFFFFFF
      Default Value: 5 Decimal
      New Value: 10 to  Decimal

The value may vary as it is dynamically adjusted.


On Windows 95:

HKEY_LOCAL_MACHINE     
   SYSTEM       
     CurrentControlSet         
       Services
            Winsock

add a key "TcpMaxDataRetransmissions" and set its value to 10.


On the Edit menu, click Add Value, and then add the following:

      Value Name: TcpMaxDataRetransmissions
      Value Type: REG_DWORD - Number
      Valid Range: 0 - 0xFFFFFFFF
      Default Value: 5 Decimal
      New Value: 10  Decimal


Solution Explanation:
=====================

When a TCP/IP packet in a given segment is sent, the sender waits for
an acknowledgment (ACK) before proceeding to the next task.
If an ACK is not received before the retransmission timer expires,
the retransmission timer value is doubled and the packet is resent.

A retrans counter is incremented and the process repeats until the
retrans counter is equal to the TcpMaxDataRetransmissions value.
At this point the network is considered timed-out and the socket is
closed by the Microsoft Operating System.

Oracle SQLNET/Net8 tracing reports this as an ntt2err: SOC xxxx error
as well as several other Oracle network transport errors (NT).

The default initial retransmission timer value is set to 3 seconds.
This value is doubled and it's product is doubled on the next iteration,
until iterations equal the TcpMaxDataRetransmissions value.

The total time to time-out with default values looks like this:
  (3s)+(3s*2)+(3s*4)+(3s*8)+(3s*16)=93s OR 1 min 33 seconds to network
  time-out.

If you see something very close to the following a SQL*Net/Net8 trace
file, the most likely cause would be that the network has timed-out.
The ntt2err SOC error is the real error - all errors appearing after
this are secondary or misleading errors.

ntt2err: soc xxxxx error - operation=5, ntresnt[0]=517, ntresnt[1]=54,
ntresnt[2]=0

Following the appearance of ntt2err, you may expect to see something like the
following. These errors are symptoms of the real problem.  They tell us
that the network or server went down when it only timed-out.

-<ERROR>- osnqrc:  wanted 1 got 0, type 0
-<ERROR>- osnqper:  error from osnqrc
-<ERROR>- osnqper:    nr err code: 0
-<ERROR>- osnqper:    ns main err code: 12547
-<ERROR>- osnqper:    ns (2)  err code: 12560
-<ERROR>- osnqper:    nt main err code: 517
-<ERROR>- osnqper:    nt (2)  err code: 54
-<ERROR>- osnqper:    nt OS   err code: 0
osnqer: entry
osnqer:  incoming err = 12151


A little later on you may see the following:
osnqer:  returning err = 3113

A clear sign of a busy network is:
ntt2err: soc xxxx - operation=5,
ntresnt[0]=517, ntresnt[1]=54, ntresnt[2]=0
This is very consistent.
       
The ORA-3113 tells us:
Text:   end-of-file on communication channel

You can look in the trace but it will be impossible to find an EOF
packet dump. You must review the second meaning of ora-3113 - the
network or server machine went down. Once again somewhat misleading
as the network was only busy and the server is really fine.


Note:
Increasing TcpMaxDataRetransmissions is only a workaround for a bad or over
stressed network. The workaround should gives you some time to address the
underlying network issues.
It is time to get out the network sniffer and measure the degree of the
problem.
It is more than likely that it will get worse over time, particularly with
increased network usage.


Search words:

Tcp Max Data Retransmissions
TcpMaxDataRetransmissions
3113
ntt2err: soc error -
operation=5, ntresnt[0]=517,
ntresnt[1]=54, ntresnt[2]=0


ref: {4950.102}    BUG-561277

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;

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