안녕하세요, Surmin입니다.
오늘은 service name 변경 및 추가 관련 내용을 준비했습니다. 참고하시어 업무에 도움이 되셨으면 합니다.
Test
- 시나리오: ERP팀과 웹팀이 같은 DB를 사용하지만 다른 서비스명으로 접속
Step 1: 서비스명 추가
-- DB 서버에서 실행
sqlplus / as sysdba
-- 기존 확인
SHOW PARAMETER service_names
-- 서비스명 추가
ALTER SYSTEM SET service_names='ORA19C,erp_svc,web_svc';
-- 확인
SHOW PARAMETER service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORA19C,erp_svc,web_svc
--리스너 확인 (동적등록임)
[oracle@ORA19C ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JAN-2026 11:58:21
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA19C)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-JAN-2026 11:56:29
Uptime 0 days 0 hr. 1 min. 51 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/ORA19C/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19C)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19C" has 1 instance(s).
Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "ORA19CXDB" has 1 instance(s).
Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "erp_svc" has 1 instance(s).
Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "web_svc" has 1 instance(s).
Instance "ORA19C", status READY, has 1 handler(s) for this service...
The command completed successfully- tnsnames.ora 설정 (클라이언트 측)
- 타 server 이용
# ERP팀용 접속 정보
ERP_CONN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.37)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = erp_svc)
)
)
# 웹팀용 접속 정보
WEB_CONN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.37)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = web_svc)
)
)- listener.ora 등록
vi $ORACLE_HOME/network/admin/listener.ora
===
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = erp_svc)
(ORACLE_HOME = /oracle/product/19c/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = web_svc)
(ORACLE_HOME = /oracle/product/19c/dbhome_1)
(SID_NAME = orcl)
)
)
===- 접속 및 확인
- /etc/hosts 설정 필요없음
# ERP 서비스로 접속
sqlplus scott/tiger@ERP_CONN
# 접속 후 확인 (어떤 서비스로 접속했는지)
SQL> SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM dual;
SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
erp_svc
# WEB 서비스로 접속
sqlplus scott/tiger@WEB_CONN
# 접속 후 확인 (어떤 서비스로 접속했는지)
SQL> SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') FROM dual;
SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
web_svc#세션별 서비스 확인
SET LINESIZE 120
SET PAGESIZE 50
COL service_name FORMAT A20
COL sid FORMAT A10
COL username FORMAT A15
SELECT service_name,
CASE WHEN sid IS NULL THEN '합계' ELSE TO_CHAR(sid) END AS sid,
serial#,
username,
COUNT(*) cnt
FROM v$session
WHERE username IS NOT NULL
GROUP BY GROUPING SETS (
(service_name, sid, serial#, username),
(service_name)
)
ORDER BY service_name, sid NULLS LAST;
SERVICE_NAME SID SERIAL# USERNAME CNT
-------------------- ---------- ---------- --------------- ----------
SYS$BACKGROUND 13 55028 SYS 1
SYS$BACKGROUND 합계 1
SYS$USERS 58 44553 SYS 1
SYS$USERS 합계 1
web_svc 35 51079 SCOTT 1
web_svc 54 11125 SCOTT 1
web_svc 합계 2
7 rows selected.