| 리스너에 IP 또는 포트 여러개 등록 - listener.ora 파일에 아래와 같이 여러개 등록 해준다 - 리스너 재기동 필요 - local_listener 사용 등록 시에도 listener.ora 파일에 IP와 포트는 등록되어 있어야 한다 |
listener.ora 파일 수정
[ora19ent@svr ~]$ cat /ora19ent/product/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.108)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
listener.ora 파일에 등록한 주소와 포트 리스닝 상태 확인
-- 리스너 기동
[ora19ent@svr ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:22:46
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /ora19ent/product/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /ora19ent/product/network/admin/listener.ora
Log messages written to /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.105)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.105)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 26-JUL-2023 21:22:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora19ent/product/network/admin/listener.ora
Listener Log File /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.105)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[ora19ent@svr ~]$
-- 리스너 상태 확인
[ora19ent@svr ~]$ lsnrctl stat
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:23:05
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.105)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 26-JUL-2023 21:22:46
Uptime 0 days 0 hr. 0 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora19ent/product/network/admin/listener.ora
Listener Log File /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.105)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ent19" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Service "ent19XDB" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
The command completed successfully
[ora19ent@svr ~]$ lsnrctl servi
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:23:08
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.105)(PORT=1521)))
Services Summary...
Service "ent19" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ent19XDB" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: svr, pid: 3375>
(ADDRESS=(PROTOCOL=tcp)(HOST=svr)(PORT=41856))
The command completed successfully
[ora19ent@svr ~]$
local_listener 사용하고 있다면 신규 IP/PORT 추가해준다
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.105)(PORT=1522)))
-- local_listener 파라미터 변경하여 신규 IP/PORT 추가
SQL> alter system set local_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.105)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))' scope=both ;
System altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.105)(PORT=1521))(ADDRESS(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))
TNSPING 테스트
[ora19ent@svr admin]$ cat tnsnames.ora
test1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 19ent)
)
)
test2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.108)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = 19ent)
)
)
[ora19ent@svr admin]$
[ora19ent@svr admin]$ tnsping test1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:29:07
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ent19)))
OK (0 msec)
[ora19ent@svr admin]$ tnsping test2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:29:09
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.108)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ent19)))
OK (0 msec)
[ora19ent@svr admin]$
DB 접속 테스트
[ora19ent@svr admin]$ sqlplus sys/oracle@test1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 26 21:29:37 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[ora19ent@svr admin]$ sqlplus sys/oracle@test2 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 26 21:29:43 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[ora19ent@svr admin]$
기존 IP 삭제 테스트
-- local_listener 에서 제거
SQL> alter system set local_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP)(HOST=192.168.56.108)(PORT= 1522)))' scope=both ;
System altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP)(HOST=192.168.56.108)(PORT= 1522)))
listener.ora 파일 변경
-- 기존 IP 주석 처리
[ora19ent@svr ~]$ cat /ora19ent/product/network/admin/listener.ora
# listener.ora Network Configuration File: /ora19ent/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.108)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
리스너 재기동
[ora19ent@svr ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:43:55
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))
The command completed successfully
[ora19ent@svr ~]$
[ora19ent@svr ~]$
[ora19ent@svr ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:43:59
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /ora19ent/product/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /ora19ent/product/network/admin/listener.ora
Log messages written to /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 26-JUL-2023 21:43:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora19ent/product/network/admin/listener.ora
Listener Log File /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[ora19ent@svr ~]$
-- 리스너 상태 확인
[ora19ent@svr ~]$ lsnrctl stat
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:44:22
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 26-JUL-2023 21:43:59
Uptime 0 days 0 hr. 0 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora19ent/product/network/admin/listener.ora
Listener Log File /ora19ent/diag/tnslsnr/svr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.108)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ent19" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Service "ent19XDB" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
The command completed successfully
[ora19ent@svr ~]$ lsnrctl servi
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:44:27
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.108)(PORT=1522)))
Services Summary...
Service "ent19" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ent19XDB" has 1 instance(s).
Instance "ent19", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: svr, pid: 3375>
(ADDRESS=(PROTOCOL=tcp)(HOST=svr)(PORT=41856))
The command completed successfully
[ora19ent@svr ~]$
TNSPING 테스트
[ora19ent@svr admin]$ tnsping test1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:44:35
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.105)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ent19)))
TNS-12541: TNS:no listener
[ora19ent@svr admin]$ tnsping test2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-JUL-2023 21:44:37
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.108)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = ent19)))
OK (0 msec)
[ora19ent@svr admin]$
DB접속 테스트
[ora19ent@svr admin]$ sqlplus sys/oracle@test1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 26 21:44:47 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
Enter user-name: ^C
[ora19ent@svr admin]$
[ora19ent@svr admin]$ sqlplus sys/oracle@test2 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 26 21:44:51 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
'DBMS > ORACLE' 카테고리의 다른 글
| 오라클 숫자(number), 날짜(date) 타입 컬럼 byte size 구하기 (0) | 2023.09.05 |
|---|---|
| Orange, QueryBox 등 쿼리툴에서 xplan 안될 때 해결법 (0) | 2023.08.31 |
| WITH 이용한 TEMP TABLE 사용 테스트 (0) | 2023.07.21 |
| table move online 테스트 (0) | 2023.07.06 |
| 테이블 nologging / append insert 복구 불가(ORA-26040) (0) | 2023.07.05 |
댓글