프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c XDB service dispatcher 포트 변경 및 비활성화

dedicate mode 로 설치된 db에서

lsnrctl service 로 확인 시 나오는 xdb를 포트를 변경하거나 비활성화 하는 방법에 대해 설명함

 

 

리스너 상태 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$ lsnrctl status
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 15:55:42
 
Copyright (c) 19912020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE19)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                09-MAR-2021 09:17:36
Uptime                    44 days 6 hr. 38 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19.0.0/network/admin/listener.ora
Listener Log File         /app/oracle/product/19.0.0/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL19" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
Service "ORCL19XDB" has 1 instance(s).
  Instance "ORCL19", status READY, has 1 handler(s) for this service...
The command completed successfully

리스너는 1521 포트를 사용중임

 

 

리스너 서비스 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 15:56:10
 
Copyright (c) 19912020, 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의 dispatcher가 16171 포트를 사용하고 있음

 

 

OS에서 사용포트 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ netstat -ntlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      -                   
tcp        0      0 192.168.122.1:53        0.0.0.0:*               LISTEN      -                   
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::16171                :::*                    LISTEN      3250/ora_d000_ORCL1 
tcp6       0      0 :::111                  :::*                    LISTEN      -                   
tcp6       0      0 :::1521                 :::*                    LISTEN      8099/tnslsnr        
tcp6       0      0 :::22                   :::*                    LISTEN      -                   
tcp6       0      0 ::1:631                 :::*                    LISTEN      -                   
tcp6       0      0 ::1:25                  :::*                    LISTEN      -             

 

 

dispatcher 파라미터 확인

1
2
3
4
5
6
7
SQL> show parameter dispatchers
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
dispatchers                 string     (PROTOCOL=TCP) (SERVICE=ORCL19
                         XDB)
max_dispatchers              integer

 

 

db 재기동 시 dispatcher 포트는 변경됨

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> shutdown immediate
SQL> startup
SQL> alter system register;
$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 16:10:09
 
Copyright (c) 19912020, 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: 4669>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE19)(PORT=25523))
The command completed successfully

 

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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

기존에 있던 (PROTOCOL=TCP)(SERVICE=ORCL19XDB) 가 변경되는게 아니라

신규로 (address=(PROTOCOL=TCP) (SERVICE=ORCL19XDB) (PORT=20000)) 이 추가됨

 

 

리스너 서비스 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 16:51:00
 
Copyright (c) 19912020, 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

예상과는 다르게 ORCL19XDB의 PORT가 변경되는것이 아닌

ORCL19에 D001이 생기면서 포트가 20000으로 할당됨

XDB의 포트는 변경을 하지 못하는듯함

 

 

dispatcher 파라미터 변경 비활성화

1
2
3
4
5
6
7
8
9
10
11
SQL> alter system set dispatchers='' scope=both;
 
System altered.
 
SQL> 
SQL> show parameter dispatchers
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
dispatchers                 string
max_dispatchers              integer

 

 

리스너 서비스 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-APR-2021 16:52:29
 
Copyright (c) 19912020, 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

ORCL19XDB 서비스가 사용하던 PORT가 없어짐

 

 

OS에서 사용포트 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ netstat -ntlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      -                   
tcp        0      0 192.168.122.1:53        0.0.0.0:*               LISTEN      -                   
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      -                   
tcp6       0      0 :::111                  :::*                    LISTEN      -                   
tcp6       0      0 :::8080                 :::*                    LISTEN      4568/tnslsnr        
tcp6       0      0 :::1521                 :::*                    LISTEN      4568/tnslsnr        
tcp6       0      0 :::22                   :::*                    LISTEN      -                   
tcp6       0      0 ::1:631                 :::*                    LISTEN      -                   
tcp6       0      0 ::1:25                  :::*                    LISTEN      -              

정상적으로 D000이 사용하던 포트가 제거됨

 

 

참조 : 2673196.1, 1552327.1

www.orafaq.com/forum/t/179630/0/