ORA-12516: TNS:listener could not find available handler with matching protocol stack
테스트용으로 설치해 두었던 Oracle XE 서버를 재설치한 이후로, 희한한 현상이 발생했습니다. 동일한 클라이언트 측 응용 프로그램 환경임에도 불구하고 이번에는 다음과 같은 오류가 발생하는 것이었습니다.
[Oracle.DataAccess.Client.OracleException] ORA-12516: TNS:listener could not find available handler with matching protocol stack
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
at Oracle.DataAccess.Client.OracleConnection.Open()
at WebSiteTest.odpnet20test.Page_Load(Object sender, EventArgs e) in d:\...[생략]...\odpnet20test.aspx.cs:line 176
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
사실, 간단한 부하 테스트용으로 구성한 시스템이라서 쿼리도 간단하고 서버 환경도 아래의 글에 설명했던 hr 계정 활성화 정도밖에는 없는 상태였습니다.
.NET 응용 프로그램에서 Oracle XE 사용
; https://www.sysnet.pe.kr/2/0/821
ODP.NET을 이용한 웹 페이지를 여러 서버에 두었는데, 무작위로 특정 서버에서만 발생했습니다. 재미있게도, 일단 오류가 발생하는 웹 서버는 계속 발생하는데 만약 그 와중에 다른 서버를 내리면 더 이상 오류가 발생하지 않았습니다. 이런 현상으로 볼 때, 해당 서버의 ODP.NET 구성이 잘못된 것 같지는 않았습니다.
어쨌든, 검색을 해서 찾은 아래 글의 문답을 보면,
Thread: TNS:listener could not find available handler witht matching protocol stack
; http://forums.oracle.com/forums/thread.jspa?threadID=360226&tstart=-1
"the processes parameter is set to 200"으로 바꾸어서 해결했다고 나오는데... ^^; 어떻게 설정해야 하는지 구체적인 방법이 없더군요. 그래서 좀 더 검색을 해보니, 아래의 글이 나오는데,
ORA-12516: TNS:리스너가 프로토콜 스택과 일치하는 처리기를 찾을 수 없습니다
; http://rothmans.wordpress.com/2009/10/20/ora-12516-tns%EB%A6%AC%EC%8A%A4%EB%84%88%EA%B0%80-%ED%94%84%EB%A1%9C%ED%86%A0%EC%BD%9C-%EC%8A%A4%ED%83%9D%EA%B3%BC-%EC%9D%BC%EC%B9%98%ED%95%98%EB%8A%94-%EC%B2%98%EB%A6%AC%EA%B8%B0%EB%A5%BC-%EC%B0%BE/
애석하게도 위의 글에 나온 "job_queue_processes" 값 변경은 도움이 안 됐습니다. 아마도 위의 2개의 글에서 나온 "processes"가 같은 것이 아니라는 판단하에, 처음의 "processes" 파라미터 값을 변경하기 위한 방법을 찾아보았습니다.
Oracle XE "Processes" Parameter
; http://sriki77.blogspot.com/2008/11/oracle-xe-processes-parameter.html
위의 글대로 리소스 점유율을 확인하기 위해
http://127.0.0.1:8080/apex에 접속해서 명령을 내렸는데 다음과 같은 결과가 나왔습니다.
select * from v$resource_limit where resource_name in ('processes','sessions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
processes 39 40 40 40
sessions 47 47 49 49
아하~~~ 이제야 해답이 나온 것 같습니다. 위의 "processes" 값을 조정하면 된다는 것이군요. 문제는 "processes" 값을 변경하기 위한 명령을 내려야 하는데, 이 과정에서 또다시 시간을 소비하게 되었습니다.
C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus.exe를 실행하고 계정과 암호를 입력하는데... 웬일인지 SYSTEM 계정으로의 로그인이 계속 거부되는 것이었습니다.
SQL*Plus: Release 10.2.0.1.0 - Production on 목 4월 7 00:58:52 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
사용자명 입력: SYSTEM
암호 입력: [...암호입력...]
ERROR:
ORA-12154: TNS:지정된 접속 식별자를 분석할 수 없음
반면에, 테스트 계정인 "hr"로 로그인을 하면 잘 되고... ^^; 또다시 검색을 하던 끝에 아래의 글에서 겨우 힌트를 얻게 되었습니다.
SYS connect
; http://www.dbasupport.com/forums/archive/index.php/t-36782.html
결국 다음과 같이 실행해서 성공하게 되었고, (희한한 보안 구조군요... 비밀번호가 필요없다늬!)
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>sqlplus sys/change_on_install@xe as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 목 4월 7 01:06:41 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
다음에 접속됨:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
이어서, processes 파라미터 값을 한번 확인해 보고,
SQL> show parameter processes
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
aq_tm_processes integer
0
db_writer_processes integer
1
gcs_server_processes integer
0
job_queue_processes integer
4
log_archive_max_processes integer
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
2
processes integer
40
이 값을 다음의 명령어로 수정해주었습니다.
alter system set processes=100 scope=spfile;
나중에 검색해서 알고 보니, XE의 경우에는 기본값을 늘려주라는 글이 있더군요.
Oracle XE Tuning
; https://rothmans.wordpress.com/2009/10/
이후, Oracle 서비스를 재시작하고,
net stop OracleXETNSListener
net stop OracleServiceXE
net start OracleServiceXE
net start OracleXETNSListener
이제는 연결 자체는 잘 되는데 전혀 다른 오류 현상이 발생했습니다.
select department_name from departments
[Oracle.DataAccess.Client.OracleException] ORA-12571: TNS:packet writer failure
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
at Jennifer40.WebSiteTest.odpnet20test.Page_Load(Object sender, EventArgs e) in d:\...[생략]...\odpnet20test.aspx.cs:line 277
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
재미있는 점은, ORA-12571 오류는 처음 서비스가 올라오는 과정에서만 잠시 발생하다가 테스트가 일정 시간 이상 진행되고 나서는 더 이상 발생하지 않는다는 것입니다. 일단, 테스트 시스템이었고, 더 이상 여기에 시간을 허비할 수 없었기 때문에 오늘은 여기까지만!!! (휴~~~ 오라클... 힘들군요.)
참고로, 알아둘 몇 가지.
1. 몇몇 파일의 위치 정리.
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\log\listener.log
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora
2. OracleXETNSListener, OracleServiceXE 서비스가 시작되었다고 해서 곧바로 모든 리스너가 동작하지는 않는다는 점! 실제로, 서비스 재시작후 lsnrctl.exe 명령어를 이용해서 확인해 보면 다음과 같은 리스닝 목록이 출력됩니다.
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>LSNRCTL.EXE status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-4월 -2011 22:02:42
Copyright (c) 1991, 2005, Oracle. All rights reserved.
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))에 연결되었습니다
리스너의 상태
------------------------
별칭 LISTENER
버전 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
시작 날짜 06-4월 -2011 22:02:33
업타임 0 일 0 시간. 0 분. 8 초
트레이스 수준 off
보안 ON: Local OS Authentication
SNMP OFF기본 서비스 XE
리스너 매개변수 파일 C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
리스너 로그 파일 C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
끝점 요약 청취 중...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb.testad.com)(PORT=1525)))
서비스 요약...
"CLRExtProc" 서비스는 1개의 인스턴스를 가집니다.
"CLRExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"PLSExtProc" 서비스는 1개의 인스턴스를 가집니다.
"PLSExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다
좀 더 기다린 다음에 확인을 해야, 다음과 같이 정상적으로 초기화되는 것을 확인할 수 있습니다.
C:\oraclexe\app\oracle\product\10.2.0\server\BIN>LSNRCTL.EXE status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 06-4월 -2011 21:59:11
Copyright (c) 1991, 2005, Oracle. All rights reserved.
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))에 연결되었습니다
리스너의 상태
------------------------
별칭 LISTENER
버전 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
시작 날짜 06-4월 -2011 21:50:21
업타임 0 일 0 시간. 8 분. 49 초
트레이스 수준 off
보안 ON: Local OS Authentication
SNMP OFF기본 서비스 XE
리스너 매개변수 파일 C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
리스너 로그 파일 C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
끝점 요약 청취 중...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb.testad.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
서비스 요약...
"CLRExtProc" 서비스는 1개의 인스턴스를 가집니다.
"CLRExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"PLSExtProc" 서비스는 1개의 인스턴스를 가집니다.
"PLSExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"XEXDB" 서비스는 1개의 인스턴스를 가집니다.
"xe" 인스턴스(READY 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"XE_XPT" 서비스는 1개의 인스턴스를 가집니다.
"xe" 인스턴스(READY 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"xe" 서비스는 1개의 인스턴스를 가집니다.
"xe" 인스턴스(READY 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]