[SQL*Net]TNS및 TNS Listener

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

TNS(Transparent Network Substrate)

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

TNS란 ORACLE社에서 개발한 Network 기술로써 서로 다른 Network 구성을 가지고 있는 Client/Server 또는 Server/Server 간에도 Data의 전송을 가능하게 해주는 기술을 말한다.

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

TNS Listener

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

TNS 기술을 사용하는 product인 SQL*NET V2가 사용하는 Listener를 TNS Listener라고 부르며 SQL*NET Client로부터 Connection을 받아 Server Process를 할당하는 역할을 한다. 다음의 2개의 다른 Community를 갖는 Network 구성의 경우에 TNS Listener가 어떠한 역할을 하는지를 보여준다.

1. Client쪽에서 Connection을 위해 요청한 request는 해당 TNS community를 통해 Server에 도달한다.

2. TNS community를 통해 Server에 도달한 request를 TNS Listener는 인식하게 된다.

3. TNS Listener는 connection에 Dedicated server process를 할당 할 것이고 MTS(Multi Threaded Server)의 경우에는 Shared dispatcher process의 address를 할당하여 Client가 요청한 사항에 대한 작업을 수행한다.

[SQL*Net]TNS Listener 다루기

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

SQL*NET V2인 경우

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

Process name

tnslsnr

ps -ef|grep tnslsnr 로 Process가 살아 있는지 확인할 수 있다.

LISTENER 상태보기

lsnrctl stat listener_name으로 Listener가 살아 있는지 확인 할 수 있다.

Ex) lsnrctl stat LISTENER

만약 lsnrctl stat listener_name에서 Listener가 살아 있지 않은 데 ps -ef|grep tnslsnr로 Process가 살아 있으면 다음과 같이 한다.

lsnrctl stop listener_name으로 Listener를 Shutdown시킨다.

Listener가 Shutdown되었음에도 Process(tnslsnr)가 살아 있다면 kill -9 tnslsnr을 이용해 강제로 Process를 Kill시킨다. lsnrctl start listener_name로 다시 Listener를 Startup 한다.

LISTENER 구동시키기

lsnrctl start listener_name로 Listener를 구동시킨다.

LISTENER 죽이기

lsnrctl stop listener_name으로 Listener를 죽인다.

[SQL*Net]Configuration File Administration

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

Client 쪽의 Configuration file들

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

1. TNSNAMES.ORA file이 필요하다. SQL*NET V2에만 존재하는 file 이다. 이 file에는 Service name과 Network를 통해 connect하고자 하는 수신지에 관한 사항을 기술한다. 즉, 연결을 하고자 하는 쪽의 computer machine상에 반드시 이 file이 존재해야 한다.

[예]
oratest.world = 
 (DESCRIPTION = 
   (ADDRESS_LIST = 
       (ADDRESS = 
         (COMMUNITY = tcp.world)
         (PROTOCOL = TCP)
         (Host = 200.200.177.51)
         (Port = 1521)
       )
       (ADDRESS = 
         (COMMUNITY = tcp.world)
         (PROTOCOL = TCP)
         (Host = 200.200.177.51)
         (Port = 1526)
       )
   )
   (CONNECT_DATA = (SID = oratest)
   )
 )
linux.world = 
 (DESCRIPTION = 
   (ADDRESS_LIST = 
       (ADDRESS = 
         (COMMUNITY = tcp.world)
         (PROTOCOL = TCP)
         (Host = 200.200.177.1)
         (Port = 1521)
       )
       (ADDRESS = 
         (COMMUNITY = tcp.world)
         (PROTOCOL = TCP)
         (Host = 200.200.177.1)
         (Port = 1526)
       )
   )
   (CONNECT_DATA = (SID = linux)
   )
 )

ORA8I.test.co.kr=
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.177.24)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = ora8i.test)
   )
 )

2. TNSNAV.ORA file이 필요하다. Network구성이 여러 Community로 구성되어 있는 경우에 이 Community의 description에 대해 기술할 수 있는 file이고 Community간 Interchange의 사항도 기술하는 file이다. 반드시 필요한 file은 아니며 만약Network 구성이 단일의 Protocol을 갖는 하나의 Community로 되어 있다면 이 file은 필요치 않다.

3. SQL*NET.ORA file. 이 file은 SQL*NET을 사용함에 있어서 Default Parameter를 사용하지 않고 Parameter를 새로이 설정하여 사용할 경우에 그 Parameter들에 대한 사항을 기술한다.

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

Server 쪽의 Configuration file

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

1. LISTENER.ORA file이 필요하다.

만약 Server(A) 쪽에서 다른 Server(B)쪽의 Database에 연결하려 할 경우가 있다면 Server(A)쪽에 TNSNAMES.ORA file을 가지고 있어야 하고 Server(B)에는 LISTENER.ORA file이 존재해야 한다. LISTENER.ORA file은 machine 상의 모든 Listener의 name과 address를 포함하고 있으며 Database의 SID와 Listener를 control하는 Control Parameter에 관한 정보를 담고 있다.

LISTENER.ORA와 TNSNAMES.ORA

LISTENER.ORA file과 TNSNAMES.ORA 는 유사한 내용을 담고 있다. 특히 이 두 file의 ADDRESS와 ORACLE_SID section은 서로 일치해야 한다.

LISTENER.ORA             
[selsp2c:/admhome/oracle/product/network/admin> listener.ora
LISTENER =
  (ADDRESS_LIST =
     (ADDRESS =
        (PROTOCOL = tcp)
        (HOST = selsp2c)
        (PORT = 1521)
     )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 0
LOG_DIRECTORY_LISTENER = /admhome/oracle/product/network/log
LOG_FILE_LISTENER = /admhome/oracle/product/network/log/log
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
        (SID_NAME = HJSUSA_T)
  (ORACLE_HOME=/admhome/oracle/product)
     )
   )
TRACE_LEVEL_LISTENER = off


TNSNAMES.ORA
[selsp2c:/admhome/oracle/product/network/admin> tnsnames.ora
HJSUSA_T =
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS =
           (PROTOCOL = tcp)
           (HOST = 203.246.130.105)
           (PORT = 1521)
        )
     )
     (CONNECT_DATA =
        (SID = HJSUSA_T)
     )
  )

TNSNAME.ORA & LISTENER.ORA file의 관리

어떠한 Node에 Instance가 새로 생성되었거나 삭제 되었다면 먼저 LISTENER.ORA file에 변경 사항을 기록하고 다른 Node의 TNSNAMES.ORA file을 Edit해야 할 것이다.

[SQL*Net]LISTENER.ORA file에 들어가는 Parameter

1.PASSWORDS_listener_name = (passwd[,...passwd])

이 Parameter는 lsnrctl [stat,start,stop,service] listener_name 과 같은 Listener Control Utility에 대한 사용을 특정인(DBA)에게만 허용하기 위해 설정된다. 이 Parameter를 설정하지 않으면 default로 UNIX Id를 가진 모든 User는 Listener Control Utility를 수행할 수 있다.

Ex) PASSWORDS_LISTENER = (oraChoi, oraKim)

2.CONNECT_TIMEOUT_listener_name = number

이 Parameter는 Client에서 connection이 출발한 후에 유효한 connection request를 TNS Listener가 얻도록 일정한 시간을 할애 하기 위해 setting한다. Time out 시간에 도달하면 TNS Listener는 connection을 drop해 버린다. Default는 10초이며 만약 이 값을 0으로 설정하면 TNS Listener가 connection request를 완전히 인식할 때까지 계속 기다리게 될 것이다.

Ex) CONNECT_TIMEOUT_LISTENER = 0 (신정보의 경우)

3.TRACE_LEVEL_listener_name = OFF|USER|ADMIN

이 Parameter는 Trace를 설정하는 것으로 Default는 OFF이고 USER는 제한된 level의 tracing을 제공한다. ADMIN은 USER로 설정하는 것보다 더 자세한 Trace를 보여준다. 이 Parameter는 아래의 두 Parameter를 참조하여 Trace를 형성한다.

Ex) TRACE_LEVEL_LISTENER = ADMIN

4.TRACE_DIRECTORY_listener_name = path_to_trace_dir

이 Parameter는 Trace file이 위치하게 될 Directory를 설정한다. Default는 OS마다 다르며 UNIX의 경우에는 $ORACLE_HOME/network/trace이다.

Ex) TRACE_DIRECTORY_LISTENER = $HOME/trace

5.TRACE_FILE_listener_name = trace_filename

이 Parameter는 Trace 정보가 쓰여질 file의 이름을 지정하기 위해 setting될 수 있다. Default는 listener_name.trc이다.

Ex) TRACE_FILE_LISTENER=listener_name_V2.trc

6.LOG_DIRECTORY_listener_name = path_to log_directory

이 Parameter는 Listener event가 발생할 때마다 즉 Client가 Server의 TNS Listener에 connection request를 요청하여 TNS Listener가 connection을 받아들일 때마다 이 Parameter에서 지정한 Directory에 자동적으로 listener event의 사항을 기록하게 된다. Default는 OS마다 다르며 UNIX의 경우에는 $ORACLE_HOME/network/log이다.

Ex) LOG_DIRECTORY_LISTENER= $HOME /network/log_dir

7.LOG_FILE_listener_name = log_filename

이 Parameter는 Listener에 대한 log file의 이름을 setting한다. Default는 listener_name.log이다.

Ex) LOG_DIRECTORY_FILE= listener_name_V2.log

[SQL*Net]Server에서 다른 Server의 Database로 접속

Server에서 다른 Server의 Database로 접속

1. SQL*NET V2에서 다른 Server의 Database에 접속하기

[형식] @SERVICE_NAME (SERVICE_NAME은 TNSNAMES.ORA에 기술되어 있는 SERVICE_NAME임) 다음의 예는 selsp2d Node에서 selsp2e Node의 Database(HJSTEST)에 접속할 경우를 보여준다.

Ex) [selsp2d:/admhome] sqlplus hansis/hansys@HJSTEST

SQL> select * from v$database;
   NAME        CREATED               LOG_MODE 
------------ ------------------- ------------- 
HJSTEST     08/23/96 16:40:19    NOARCHIVELOG 

[Table]Table 관리

4. Table생성시 참조되는 Storage Parameter

-INITIAL

초기 테이블이 생성될 때 차지하는 크기를 정한다.

미리 데이타가 저장될 사이즈 만큼의 값을 계산하여 사용하는 것이 제일 좋다. 테이블이나 인덱스 마다 값이 다르다.

-NEXT

initial 에서 정한 만큼의 공간이 다 사용되었을 때 다음 번으로 추가되는 공간의 크기를 정한다. 테이블이 달라도 가능한 동일한 값을 통일하여 사용하면 테이블이 삭제되고 다시 생성하는 과정에서 발생하는 공간의 활용도를 최대한 높일 수 있다.

-PCTINCREASE = 0

next 값이 사용될 때 증분되는 비율값을 설정한다. 디폴트 값은 50 으로 설정되어있으나 꼭 0 으로 셋팅하여 사용하기를 권한다.

-FREELIST

insert 작업 시 미리 사용 가능한 블럭을 리스트하고 있다가 할당하는 곳이다.

insert 작업이 많이 발생하는 테이블이나 인덱스에서는 이 값을 증가시켜 빈 블럭을 할당받기 위해 대기하는 일이 없도록 한다.

-PCTFREE

수정 시 늘어나는 데이타를 수용하기 위한 공간이다.

디풀트는 10 이나 빈번히 수정이 되면서 null 이었다가 데이타가 채워지는 경우는 이 값을 약 20 혹은 30 까지 크게 설정한다.

-PCTUSED

재사용되기 위해 필요한 블럭의 충진도 값을 설정한다. 디폴트는 60이나 입력, 삭제가 자주 발생하지 않는 경우는 90 정도로 큰 값을 설정하고, 수정작업이 자주 발생하면서 로우 사이즈가 증가할 때에는 40 정도로 낮은 값을 설정한다.

Storage Paramter에 대한 설명은 강좌의 앞부분에도 자세히 나와있다.

5. Table Create 예제

CREATE TABLE info (

nm VARCHAR2(20) NOT NULL,

pyoung NUMBER(5) NOT NULL,

from_ho NUMBER(5) NULL,

to_ho NUMBER(5) NULL,

price NUMBER(12) NOT NULL,

underground VARCHAR2(1) NOT NULL,

regi_ilsi date NULL,

CONSTRAINT PK_INFO

PRIMARY KEY(nm)

USING INDEX TABLESPACE users_idx

STORAGE(INITIAL 1M NEXT 500K)

) TABLESPACE users

STORAGE(INITIAL 2M NEXT 1M);

위의 예제에서는 Table을 만들면서 Primary Constraints도 생성을 하였는데, 물론 Table만 먼저 만들고 Primary Key Constraints는 나중에 만들어도 된다. Primary Key를 만들면서 이를 Index로 이용하겠다는 의미이며 Index는 Table이 생성된 Device(Disk)가 아닌 다른 Disk에 저장하는 예제이다.

아래의 예를 보자

CREATE TABLE info (

nm VARCHAR2(20) NOT NULL,

pyoung NUMBER(5) NOT NULL,

from_ho NUMBER(5) NULL,

to_ho NUMBER(5) NULL,

price NUMBER(12) NOT NULL,

underground VARCHAR2(1) NOT NULL,

regi_ilsi date NULL

) TABLESPACE users

STORAGE(INITIAL 2M NEXT 1M);

alter table info add (constraints primary key pk_info (nm)

using index tablespace user_idx

storage(initial 1m next 500k));

이 경우엔 Table만 먼저만들고 나중에 Primary Key를 추가하였다.

6. Table변경시는 Alter Table 명령을 이용한다.

아래는 사용예이다.

- 하나의 Column을 추가 하는예(맨뒤에 추가된가._

alter table info add (test_cilumn varchar2(2) null);

- next값을 바꾸는 예

alter table info storage (next 2m);

- 참고로 Table의 이름을 바꾸고자 할때는 아래와 같이 한다.

rename info to myInfo;

myInfo로 Table의 이름을 변경하였다.

7. Table의 삭제

- Drop Table명령을 사용한다.

- Cascade Constraints Option을 이용하면 관련된 Constraints를 조건을 삭제한다. 예를들면 삭제할 Table이 다른 Table의 외래키로 언급되는 Primary Key등을 포함하거나 자식 Table의 Foreugn Key 제약 조건등이 삭제된다.

- Table삭제되면 관련된 Index및 Trigger도 같이 삭제된다.

- 동의어(Synonym)은 남아 있으나 사용하면 오류가 나겠죠.

- 삭제된 Table에 대한 종속된 View나 PL/SQL 프로그램 등은 남지만 사용할수가 없겠죠.

- 삭제된 Cluster화되지 않은 Table에 할당된 모든 확장 영역은

TableSpace의 빈영역으로 되돌려지며 새 확장영역이나 개체가 필요한 개체에의해 사용할수 있게 된다.

- Cluster화된 Table인 경우 Table에 해당되는 모든 행은 클러스터 블록에서 삭제된다.

- 예

drop table info cascade constraints;

drop table info;