OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : ORA-02097: parameter cannot be modified because specified value is invalid
로컬 리스너(local_listener) 설정 시 아래 에러 발생
1 2 3 4 5 6 7 8 | SQL> alter system set local_listener=LISTENER1527; alter system set local_listener=LISTENER1527 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name 'LISTENER1527' |
현재 리스너는 1521과 1527을 등록해놓음 현재 둘다 기동중
1521 리스너
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 LISTENER LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:30:46 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. 15 min. 36 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:30:55 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:22:40 Uptime 0 days 0 hr. 8 min. 15 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가 없음
해결 방법 : 리스너 alias 가 아닌 tnsnames.ora에 등록된 이름으로 alter 문 실행
tnsnames.ora 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ cat 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) ) ) |
tnsnames.ora에 등록된 이름으로 로컬 리스너 설정 후 확인
1 2 3 4 5 6 7 8 9 | SQL> alter system set local_listener=ORCL11_NPORT; System altered. SQL> show parameter local_li 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 orcl11 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:37:34 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL11))) 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. 22 min. 24 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 orcl11_nport LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-OCT-2019 21:37:41 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE11)(PORT=1527))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL11))) STATUS of the LISTENER ------------------------ Alias LISTENER1527 Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 17-OCT-2019 21:22:40 Uptime 0 days 0 hr. 15 min. 1 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 이 등록됨
원인 : local_listener 값을 잘못 넣어서 발생한 문제
참조 : https://community.oracle.com/thread/2683446