OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 정적 리스너 이용하여 원격지 db에 접속 및 재시작
일반적으로 tns를 통한 연결은 리스너가 기동상태일때 서비스가 리스너에 물려있고, 이 서비스 이름을 tns에 넣어서 db에 접속함
tnsnames.ora -> 리스너 -> 서비스 확인 -> db 접속
그렇기 때문에 리스너에 서비스가 안붙어있으면 tns 접속 자체가 안됨
본문에서는 2개의 19c 서버를 이용해 tns 접속을 시도해보고 동적, 정적 리스너일때 각각 어떻게 동작하는지 확인해봄
서버정보
A서버 : ip : 192.168.137.50 / db버전 : 19.27db / sid : ORA19DBFS
B서버 : ip : 192.168.137.51 / db버전 : 19.3db / sid : ORA19ACT
A서버에서 B서버로 접속할 예정
테스트
1. 동적 리스너 사용시
2. 정적 리스너 사용시
테스트
1. 동적 리스너 사용시
B서버 리스너 파일 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ cat listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19adg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
|
정상적으로 동적 리스너 설정 되어 있음
A서버의 tnsnames.ora에 B서버 접속 정보 입력
|
1
2
3
4
5
6
7
8
9
|
$ cat tnsnames.ora
ORA19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.51)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19ACT)
)
)
|
B서버에서 리스너 상태 확인
|
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 16-NOV-2025 12:44:39
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19adg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-NOV-2025 12:44:32
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19adg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19ACT" has 1 instance(s).
Instance "ORA19ACT", status READY, has 1 handler(s) for this service...
Service "ORA19ACTXDB" has 1 instance(s).
Instance "ORA19ACT", status READY, has 1 handler(s) for this service...
The command completed successfully
|
정상적으로 서비스가 READY로 물려있음
A서버에서 B서버 db로 접속 시도
|
1
2
|
$ sqlplus sys/oracle@ora19 as sysdba
(정상 접속됨)
|
db 중지
|
1
2
3
4
5
6
7
8
9
10
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Warning: You are no longer connected to ORACLE.
SQL>
|
db는 정상적으로 내려갔지만 tns 연결이 끊김
B서버에서 리스너 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2025 12:48:29
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19adg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-NOV-2025 12:44:32
Uptime 0 days 0 hr. 3 min. 56 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19adg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
|
db가 shutdown 상태이기때문에 서비스가 안붙어 있음
더이상 B서버에 붙을수가 없음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> conn sys/oracle@ora19 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
$ sqlplus sys/oracle@ora19 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 16 13:02:24 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Enter user-name:
|
2. 정적 리스너 사용시
B서버 db 기동
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
|
B서버 리스너 중지
|
1
2
3
4
5
6
7
8
|
$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2025 12:55:22
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19adg1)(PORT=1521)))
The command completed successfully
|
B서버 리스너 파일 수정
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora19adg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /app/oracle/product/19c)
(SID_NAME = ORA19ACT)
)
)
|
정적 리스너 설정으로 변경 후 저장
A서버의 tnsnames.ora에 B서버 접속 정보 입력(미변경)
|
1
2
3
4
5
6
7
8
9
|
$ cat tnsnames.ora
ORA19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.51)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19ACT)
)
)
|
B서버에서 리스너 기동 및 상태 확인
|
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
28
29
30
31
32
33
|
$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2025 13:11:18
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /app/oracle/product/19c/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /app/oracle/product/19c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/ora19adg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19adg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-NOV-2025 13:11:18
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19adg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19ACT" has 1 instance(s).
Instance "ORA19ACT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
|
서비스가 UNKNOWN으로 물려있음
A서버에서 B서버 db로 접속 시도
|
1
2
3
4
5
6
|
$ sqlplus sys/oracle@ora19 as sysdba
SQL> select instance_name, status, version from v$instance;
INSTANCE_NAME STATUS VERSION
---------------- ------------ -----------------
ORA19ACT OPEN 19.0.0.0.0
|
정상 접속됨
db 중지
|
1
2
3
4
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
|
db도 정상적으로 내려갔고 tns 연결도 안끊김
B서버에서 리스너 상태 확인
|
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
|
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2025 13:29:31
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19adg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-NOV-2025 13:11:18
Uptime 0 days 0 hr. 18 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/oracle/product/19c/network/admin/listener.ora
Listener Log File /app/oracle/diag/tnslsnr/ora19adg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19ACT" has 1 instance(s).
Instance "ORA19ACT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
|
정적 리스너이기 때문에 여전히 서비스가 UNKNOWN으로 붙어있음
이상태에서 db 기동 시도
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
|
정상적으로 기동도됨
shutdown 후 tns 재접속
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
$ sqlplus sys/oracle@ora19 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 16 13:31:04 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
|
db가 내려간 상태에서도 tns를 통한 db접속(sqlplus 접속)이 되고 idle instance로 표시됨, 기동도 가능함
리스너 로그 확인
A서버에서 B서버 db로 tnsping 시도
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ tnsping ora19
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-NOV-2025 17:21:48
Copyright (c) 1997, 2025, Oracle. All rights reserved.
Used parameter files:
/app/oracle/product/19c/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.51)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19ACT)))
OK (0 msec)
|
잘됨
B서버 리스너 로그 확인
|
1
2
3
|
$ tail -300f /app/oracle/diag/tnslsnr/ora19adg1/listener/trace/listener.log
2025-11-16T17:20:36.283944+09:00
16-NOV-2025 17:20:36 * ping * 0
|
ping으로 표시됨
접속 시도
A서버에서 B서버 db로 접속 시도
|
1
|
$ sqlplus sys/oracle@ora19 as sysdba
|
연결 잘됨
B서버 리스너 로그 확인
|
1
2
3
4
5
|
$ tail -300f /app/oracle/diag/tnslsnr/ora19adg1/listener/trace/listener.log
2025-11-16T17:21:44.056163+09:00
16-NOV-2025 17:21:44 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA19ACT)(CID=(PROGRAM=sqlplus@ora19fs)(HOST=ora19fs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.50)(PORT=24206)) * establish * ORA19ACT * 0
2025-11-16T17:21:46.131139+09:00
16-NOV-2025 17:21:46 * service_update * ORA19ACT * 0
|
접속 성공했다고 나옴(establish)
B서버 db 재기동 시도
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup force
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 318767104 bytes
Database Buffers 922746880 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
|
B서버 리스너 로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ tail -300f /app/oracle/diag/tnslsnr/ora19adg1/listener/trace/listener.log
2025-11-16T17:22:14.969928+09:00
16-NOV-2025 17:22:14 * service_died * ORA19ACT * 12537
2025-11-16T17:22:18.112620+09:00
16-NOV-2025 17:22:18 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA19ACT)(CID=(PROGRAM=sqlplus)(HOST=ora19fs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.50)(PORT=30146)) * establish * ORA19ACT * 0
2025-11-16T17:22:24.131011+09:00
Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19adg1)(PORT=1521)))
16-NOV-2025 17:22:24 * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.51)(PORT=51468)) * service_register * ORA19ACT * 0
16-NOV-2025 17:22:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA19ACT)(CID=(PROGRAM=sqlplus)(HOST=ora19fs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.50)(PORT=59388)) * establish * ORA19ACT * 0
2025-11-16T17:22:27.123445+09:00
16-NOV-2025 17:22:27 * service_update * ORA19ACT * 0
2025-11-16T17:22:28.548445+09:00
16-NOV-2025 17:22:28 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORA19ACT)(CID=(PROGRAM=sqlplus)(HOST=ora19fs)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.137.50)(PORT=59404)) * establish * ORA19ACT * 0
16-NOV-2025 17:22:29 * service_update * ORA19ACT * 0
2025-11-16T17:22:32.215749+09:00
16-NOV-2025 17:22:32 * service_update * ORA19ACT * 0
|
신기하게 service_died가 표시되고 다시 붙는 모습이 포착됨
일반적으로 리스너를 통해 세션이 붙으면 붙을때는 establish 메세지가 표시되지만 끊길때는 별도의 메세지가 남지 않음
결론 :
일반적으로 사용하는 동적 리스너를 사용중인 경우 타겟 db가 내려가면 더이상 tns를 통해 db에 접근할 수 없음
하지만 정적 리스너를 사용하면 다켓 db가 내려가도 tns를 통해 붙어서 기동을 시킬수 있음
원격지 db를 sysdba로 붙어서 재기동도 할수 있다는 자체가 보안상 위험해 보임, 동적 리스너를 쓰는게 보안에도 좋을듯함
참조 :
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c varchar2 최대 길이 32767로 변경 (1) | 2025.12.01 |
|---|---|
| 오라클 19c expdp impdp 상태 n초마다 자동 확인 (0) | 2025.11.24 |
| 오라클 19c opatch debug 방법 (0) | 2025.11.19 |
| 오라클 19c 특정 테이블, 인덱스 용량 증가 추이 확인 (0) | 2025.11.17 |
| 오라클 19c ahf 기동 중 ru 패치 테스트 (0) | 2025.11.14 |