내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 오라클 다른 포트 리스너 설정(로컬리스너 설정)
기존 리스너 1521이 있는 상태에서 1527이라는 새로운 포트로 리스너 생성하는 시나리오
기존 리스너 확인
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 11.2.0.4.0 - Production on 17-OCT-2019 21:42:23 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 15-OCT-2019 09:15:09 Uptime 2 days 12 hr. 27 min. 14 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ORCL11" has 1 instance(s). Instance "ORCL11", status READY, has 1 handler(s) for this service... The command completed successfully |
기존 리스너 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /oracle/app/oracle |
기존 tnsnames.ora 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 | $ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL11 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11) ) ) |
1527 리스너 추가
기존 리스너 파일에 아래 문장 추가
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /oracle/app/oracle LISTENER1527 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) ) ) |
tnsnames.ora 파일에 아래 문장 추가
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ cat /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL11 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11) ) ) ORCL11_NPORT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11) ) ) |
1527 리스너 기동
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 | $ lsnrctl start LISTENER1527 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:47:00 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527))) STATUS of the LISTENER ------------------------ Alias LISTENER1527 Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 17-OCT-2019 21:47:00 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527))) The listener supports no services The command completed successfully |
tnsping 확인
1 2 3 4 5 6 7 8 9 10 11 12 | $ tnsping ORCL11_NPORT TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:48:09 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11)(PORT = 1527)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL11))) OK (0 msec) |
tns 통해 접속 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ sqlplus / as sysdba@ORCL11_NPORT SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 17 21:49:33 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name, version, status from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ ORCL11 11.2.0.4.0 OPEN |
로컬 리스너 등록
1 2 3 4 5 6 7 8 9 | SQL> alter system set local_listener=ORCL11_NPORT; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string ORCL11_NPORT |
1521 리스너 확인
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 11.2.0.4.0 - Production on 17-OCT-2019 21:51:21 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 15-OCT-2019 09:15:09 Uptime 2 days 12 hr. 36 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully |
Service가 없음
1527 리스너 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | $ lsnrctl status LISTENER1527 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:51:49 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527))) STATUS of the LISTENER ------------------------ Alias LISTENER1527 Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 17-OCT-2019 21:47:00 Uptime 0 days 0 hr. 4 min. 49 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527))) Services Summary... Service "ORCL11" has 1 instance(s). Instance "ORCL11", status READY, has 1 handler(s) for this service... The command completed successfully |
Service ORCL11 이 등록됨
추가 자료
로컬 리스너 해제
1 2 3 4 5 6 7 8 9 | SQL> alter system set local_listener=''; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string |
이렇게 하면 기존 리스너로 service가 넘어가고 1527 리스너에는 no service라고 나온다
기존 리스너 확인
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 11.2.0.4.0 - Production on 17-OCT-2019 21:55:48 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 15-OCT-2019 09:15:09 Uptime 2 days 12 hr. 40 min. 38 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "ORCL11" has 1 instance(s). Instance "ORCL11", status READY, has 1 handler(s) for this service... The command completed successfully |
Service ORCL11 이 등록됨
1527 리스너 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | $ lsnrctl status LISTENER1527 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:55:56 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527))) STATUS of the LISTENER ------------------------ Alias LISTENER1527 Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 17-OCT-2019 21:47:00 Uptime 0 days 0 hr. 8 min. 55 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/ORACLE11/listener1527/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE11)(PORT=1527))) The listener supports no services The command completed successfully |
Service가 없음
RAC 환경의 경우 설정 방법
RAC 환경에서는 리스너를 grid가 서비스로 관리하기때문에 srvctl 명령으로 관리를 해주어야 함
grid 계정을 별도로 쓰지 않기 때문에 임시로
그리드 명령을 사용 할 수 있게끔 GRID_HOME 설정
1 2 | $ export GRID_HOME=/app/grid/11.2.0/grid $ export ORACLE_HOME=$GRID_HOME |
(grid 계정을 사용한다면 grid 계정으로 접속)
1527 포트의 리스너 추가
1 | $ srvctl add listener -l LISTENER_1527 -p 1527 |
상태확인
1 2 3 4 5 6 7 8 9 10 | $ crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE rac1 ora.LISTENER_1527.lsnr OFFLINE OFFLINE rac1 |
기존 리스너(LISTENER)과 신규 리스너(LISTENER_1527)이 함께 있는것을 확인할 수있다.
하지만 OFFLINE 상태
1527 리스너 시작
1 | $ srvctl start listener -l LISTENER_1527 |
상태확인
1 2 3 4 5 6 7 8 9 10 | $ crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE rac1 ora.LISTENER_1527.lsnr ONLINE ONLINE rac1 |
리스너 삭제 방법
1 | $ srvctl remove listener -l LISTENER_1527 |
리스너 포트 변경 방법
우선 리스너 정지
1 2 | $ srvctl stop listener -l LISTENER_1527 -n rac1 $ srvctl stop listener -l LISTENER_1527 -n rac2 |
리스너 포트 변경(1527에서 1530으로)
1 | $ srvctl modify listener -l LISTENER_1527 -p 1530 |
리스너 시작
1 2 | $ srvctl start listener -l LISTENER_1527 -n rac1 $ srvctl start listener -l LISTENER_1527 -n rac2 |
참조 : https://secmaster.tistory.com/18
https://positivemh.tistory.com/303
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 Table Compress 기능 중 oltp와 advanced 옵션 차이점 (0) | 2019.11.22 |
|---|---|
| 외부에서 오라클 db가 접속 안될 때 확인사항 (0) | 2019.11.19 |
| 오라클 30gb 정통 export import 테스트, 시간 측정 (0) | 2019.10.14 |
| 데이터 펌프 작업 모니터링 쿼리 (2) | 2019.02.26 |
| DB 관련 사이트 리스트 (0) | 2019.02.25 |