[Oracle] XDB가 뭘까, dedicated server mode에서 dispatcher의 역할, XDB 비활성화 방법

Oracle XDB란?

• XML 데이터를 Oracle Database에서 네이티브하게 저장, 조회, 처리하기 위한 XML 엔진
• Oracle 9i부터 도입되었으며, 이후 버전에서 지속적으로 강화됨
• XDB는 Oracle DB에 내장되어 있으며 별도 설치 없이 사용 가능

Dedicated / Shared Server and XDB

-> Doc ID 1396825.1

 

Shared Server Mode일 때만 Dispatcher를 사용하는 것은 아님.

XDB 접근을 위해 dispatcher가 핸들러 역할을 함(local listener 같이).

따라서, dispatcher를 비활성화하기 전에 XDB의 사용 여부를 반드시 확인해야 함.

XDB에 접근하는 방법

XDB를 사용할 때 XDB 리포지토리에 접근하기 위해 HTTP, FTP, WebDAV 등의 프로토콜을 사용할 수 있음.

HTTP 프로토콜은 8080 포트를,

FTP 프로토콜은 2100 포트를 default로 사용함.

웹 브라우저를 통해 http://localhost:8080 으로 접근하거나
FTP 클라이언트를 통해 ftp://localhost:2100 으로 접근할 수 있음.
HTTP로 접근하면 Apache Webserver 리스트 처럼 뜨고, 
FTP로 접근하면 탐색기 형태로 나타남. 

리스너를 통해 XDB에 접근할 수도 있음.
리스너 상태를 조회했을 때 SID 뒤에 XDB가 붙은 서비스가 동적으로 등록되어 나타나는 것을 볼 수 있는데,
SID+XDB 정보는 dispatchers 파라미터에 명시되어 있음.
(local listener 와 같은 원리라고 보면 됨)

 

SQL> show parameter dispatchers
 
NAME                     TYPE     VALUE
------------------------ -------  -----------------------------------------
dispatchers              string   (PROTOCOL=TCP) (SERVICE=ORCL19XDB)
max_dispatchers          integer

 

리스너 서비스 상태를 살펴보면
SID+XDB 형태의 서비스가 떠있고,
D000 라는 이름의 dispatcher가 XDB 핸들러로 존재함.

$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 15:56:10
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE19)(PORT=1521)))
Services Summary...
Service "ORCL19" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL19XDB" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ORACLE19, pid: 3250>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=16171))
The command completed successfully

XDB의 HTTP 포트 변경 방법

자바 개발을 하는 경우 톰캣이 기본적으로 8080포트를 사용하기 때문에 XDB 포트와 서로 충돌함.
이때 8080을 다른 포트로 변경해 주거나 XDB를 비활성화 하는 방안이 있음.

 

다음 쿼리를 통해 현재 사용중인 XDB 포트를 확인.

SQL> SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

 

다음 프로시저를 통해 포트를 변경

SQL> EXEC DBMS_XDB.SETHTTPPORT(8081);

dispatcher 포트 변경 방법

* 긍정형 포스팅 참고 : https://positivemh.tistory.com/718

 

dispatcher 파라미터 변경 시도(포트 20000으로 고정)

SQL> alter system set dispatchers="(address=(PROTOCOL=TCP) (SERVICE=ORCL19XDB) (PORT=20000))" scope=both;
 
System altered.
 
SQL> show parameter dispatchers
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP)(SERVICE=ORCL19X
                                                 DB), (address=(PROTOCOL=TCP) (
                                                 SERVICE=ORCL19XDB) (PORT=20000
                                                 ))
max_dispatchers                      integer

 

예상과는 다르게 ORCL19XDB의 PORT가 변경되는것이 아닌
ORCL19에 D001이 생기면서 포트가 20000으로 할당됨

$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 16:51:00
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE19)(PORT=1521)))
Services Summary...
Service "ORCL19" has 1 instance(s).
  Instance "ORCL19", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D001" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ORACLE19, pid: 7556>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=20000))
Service "ORCL19XDB" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ORACLE19, pid: 7307>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=25951))
The command completed successfully

Removing XDB Handlers for HTTP and FTP

-> Doc ID 274508.1

XDB 핸들러 비활성화

* 긍정형 포스팅 참고 : https://positivemh.tistory.com/718

 

현재 dispatcher가 16171 포트를 사용하고 있음.

$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 15:56:10
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE19)(PORT=1521)))
Services Summary...
Service "ORCL19" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL19XDB" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ORACLE19, pid: 3250>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=16171))
The command completed successfully

 

dispatcher 파라미터 비활성화(XDB 핸들러 비활성화)

SQL> alter system set dispatchers='' scope=both;
 
System altered.
 
SQL> 
SQL> show parameter dispatchers
 
NAME                     TYPE     VALUE
------------------------ -------  -----------------------------------------
dispatchers              string   
max_dispatchers          integer

 

* pfile일 경우 dispatchers 파라미터 삭제

 

XDB 핸들러(D000 프로세스)가 없어짐.

$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 16:52:29
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE19)(PORT=1521)))
Services Summary...
Service "ORCL19" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL19XDB" has 1 instance(s).
  Instance "ORCL19", status READY, has 0 handler(s) for this service...
The command completed successfully

XDB Install / Deinstall

-> Doc ID 1292089.1

 

How to Determine if XDB is Being Used in the Database?

-> Doc ID 733667.1
위 문서의 스크립트를 실행하면 XDB가 사용하는 오브젝트 리스트를 볼 수 있음.