가브레알토시

ORA-12516: TNS:리스너가 프로토콜 스택과 일치하는 처리기를 찾을 수 없습니다
ORA-12516: TNS:listener could not find available handler with matching protocol stack.
ORA-12520 TNS : 리스너가 요청 된 유형의 서버에 대해 사용 가능한 처리기를 찾을 수 없습니다

ORA-12520: TNS:listener Could Not Find Available Handler For Requested Type Of Server
( 두에러가 각기 다른상황에서  동일하게 뜰수있음)

금요일까지만해도 잘 되던 서버가 갑자기 월요일 아침부터 동작하지 않는다.

연락을 받았을땐 이미 서버를 물리적으로 재부팅 한 후였다. 서버 프로그램을 켜니 ORA-12520 메세지가 나올때도 있고 ORA-12516 이 나오는 경우도 있었다.

SQLPLUS "/AS SYSDBA" 를 통해 DB 접근을 시도해봐도 아래와 같은 에러가 발생된다.

ORA-00020: 최대 프로세스 수 (500)를 초과했습니다

ORA-00020: maximum number of processes (500) exceeded

일단 물리적인 서버를 재부팅 한 이후에도 접속이 되지않아 당황스러웠지만 급한대로 오라클 서비스 부터 재시작 해본다.

윈도우 서비스에 들어간후 oracleoradb11g_home1tnslistener 를 찾아서 오른쪽 클릭후 중지 버튼을 누른다.
중지가 완료되면 이후 시작 버튼을 누른다.

이후 OracleService로 시작하는 서비스도 중지 후 시작을 눌러준다.

이후 정상적으로 접속되는것을 확인

이후

show parameter processes ;

show parameter sessions ;

각 명령어를 통해 설정되어있는 세션값을 확인

// 프로세스 세션 늘리기 (1천개)
alter system set processes=1000 scope=spfile;

// open_cursors 늘리기 (1천개)
alter system set open_cursors=1000 scope=both;

//오라클 immediate(현재 처리중인 SQL Statement가 있다면, 다 STOP 시킵니다.) ※추천 방법 : shutdown immediate한후 종료가 안될시 새창을 띠워서 shutdown abort 시킴
shutdown immediate

//시작
startup

이후 잘 해결되는거같았으나 갑자기 다시 세션이 꽉차는 상황이 발생

ORA-12516: TNS:리스너가 프로토콜 스택과 일치하는 처리기를 찾을 수 없습니다
ORA-12516: TNS:listener could not find available handler with matching protocol stack.

그리하여 프로그래머에게 프로그램 점검요청 결과 아래와 같은 에러가 발생했음.

ORA-28002 : 암호는 7 일 이내에 만료됩니다

ORA-28002: the password will expire within 7 days

원래대로라면 프로그램에서 세션연결후 끊어야하는데 ORA-28002 암호 만료 에러가 발생하면서 정상적으로 접속이 안되었다고 프로그램이 판단하여 계속 접속을하고 세션을 끊지않아 단시간안에 프로세스가 꽉차는 증상이 발생됨

암호만료제한 해제 방법

alter profile default limit password_life_time unlimited; 

패스워드 만료기가 해제 명령어
이후

계정 패스워드 변경 명령어

alter user 계정명 identified by 패스워드;

이후 정상동작 확인

ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

최근에 DB테이블 정리를 한 곳에서 문자가 한통 왔다..
서버 재부팅후 데몬시작을 눌럿더니
ORA-12528: TNS: Listener: all appropriate instances are blocking new connections. 에러창이 난다는 것이였다.

상황을 정리해보면

상황

1. 인터넷이 동작하지 않고있었음

2. 데몬시작을 눌러도 한동안 반응이 없음
3. 데몬시작 프로그램을 연속으로 두번 누름

4. 오라클DB, 리스너는 시작되어 있었음

5. 위 두개를 연결하는 데몬은 방치된 상태였음

위와같은 상황이였습니다. 그래서 제가 해결한 방법

1. 인터넷 회선을 분리후 재결합
2. 데몬을 종료후 재시작
3. 데몬시작을 한번만 누름

위와같은 해결방법으로 해결이 되었습니다.

유추되는 문제는 " 서버부팅 직후 리스너가 제대로 시작되기도전에 데몬을 연속으로 두번 클릭실행 하여 발생한 에러로 추정" 입니다.

그래도 해결이 안되시는 분들을 위해서

2019/06/03 - [데이터베이스(DATABASE)/오라클(ORACLE)] - 오라클 DB, lsnrctl, 리스너(Listener),DB 재시작 방법

제가 이전에 쓴 DB재시작, 리스너 재시작 명령어 입니다. 이를 통해 진행하시면 대부분 해결되시는걸로 확인 됩니다.

감사합니다.

ORA-12571: TNS:패킷 기록자 실패. 예전에 한번 언급했던 문제 입니다. 
https://softwcgcg.tistory.com/1

이번에도 발생 하여 상황을 점검해보았습니다.

프로그램 로그를 보면서 확인한 결과 발생한 시간대에 로그에 Network Connection Fail 이 찍혀있었습니다.

예전 게시물에 언급했던것과 같이 SQLNET.EXPIRE_TIME = 3 으로 설정했던것이 동작한것이였습니다.
실제로 그당시 PC 인터넷이 잠시동안 끊어져있었습니다. 위 증상이 특정 클라이언트에서 지속적으로 발생하시는 분들은 클라이언트와 오라클 서버 간에 네트워크를 점검해볼 필요성이 있을거같습니다.

LISTENER.ORA 파일에서

CONNECT_TIMEOUT_LISTENER = 0 

추가시 증상이 안나올수는 있을거 같습니다. 
TNS Listener 가 연결요청이 될때까지 지속적으로 기다린다는 뜻입니다. 하지만 인터넷이 영구히 끊긴경우는 위 에러메세지가 나오지 않아 발생여부 확인이 불가능 할수도 있을거 같습니다.

ORA 12518 TNS 리스너는 클라이언트 연결을 처리할 수 없습니다.
ORA-12518, TNS:listener could not hand off client connection

2019-10-05 토요일 아침 장기출장의 피로로 자고 있는 와중에 갑작스럽게 서버가 동작하지 않는다고 연락이 왔습니다.

급하게 접속해보니 위 사진과 같은 에러가 발생하고 있었습니다.

이벤트 로그 또한 특이점이 없는상태였다..
당시에 cmd 창에 lsnrctl status 를 쳐도 리스너가 반응이 없는 상태여서 바로 강제 서비스 재시작을 진행 하였다..

재시작후 문제를 찾던 와중에 다른 커뮤니티 사이트에서 OracleOraHome92CliendCache , OracleOraDb11g_home1ClrAgent 를 시작하면 해결된다는 이야기도 있었지만 둘다 서비스에 없었기에 해결방법이 되지 않았고 또다른 해결방법으로
select * from v$resource_limit;
명령어를 통해 세션 할당 상태를 봣어야 했지만 급한대로 오라클을 재시작해버려서 확인할 길이 없어졌다.. 제일 의심가는 부분이기도 하여  sqlnet.ora 에 들어가서 SQLNET.EXPIRE_TIME = 3 를 추가해주었다. 위 명령어는 유령세션을 죽이는 역활을 하는데 3분 이상 방치된 세션을 끊어버리기도 합니다. 위 문제로 해결이 되면 다행이겠지만 .. 오라클의 리스너가 올라 오지 않아서 프로세스 나 세션을 연결할수 없어서 에러가 난 상태라면 이거또한 해결방법을 찾아봐야 될거 같습니다. 

1.시작 -> CMD 실행후 관리자 로그인  (오라클 sysdba 계정 접속)
sqlplus "/as sysdba"


2.시작 -> CMD 새로실행 후 리스너 종료 명령어 실행
lsnrctl stop

3. SQLPLUS 로그인 된 CMD 창으로 돌아가 오라클 명령어 실행
shutdown immediate

4. 종료 메시지 발생 후 명령어 실행
startup

5. lsnrctl stop 한 창으로 돌아가서 명렁어 실행
lsnrctl start

제가 이용중인 방법 입니다.

더 좋은방법이 있으시면 댓글 부탁 드립니다.

보통 오라클을 사용하시다보면 TOAD, GOLDEN , SQL Developer 등을 사용하게 됩니다.

이번 방법은 ui 를 통해 index rebuild 하는 방법이 아닌 sql문을 통해 인덱스 리빌드 방법에 대해서 알려드리겠습니다.

일단 cmd 창을 키고 index rebuild 가 필요한 계정에 접속 합니다.

Microsoft Windows [Version 10.0.15063]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\Users\>sqlplus 해당하는계정/해당하는계정비밀번호 

SQL*Plus: Release 11.2.0.2.0 Production on 수 5월 29 19:25:30 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

이후에

 1. select index_name from user_indexes;
   // 인덱스 조회하는 명령어 

2. alter index 인덱스명 rebuild
   // 인덱스 리빌드하는 명령어 

명령어를 통해 1번으로 인덱스를 조회 후 2번으로 인덱스를 리빌드 합니다.

1.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------------------------------------
PK_TEST1
IDX_TEST2
PK_TEST3
IDX_TEST4
IDX_TEST5
PK_TEST6
PK_TEST7
SYS_IL0000032323C00004$$
PK_TEST8
PK_TEST9
PK_TEST10

INDEX_NAME
------------------------------------------------------------
PK_TESTINFO
PK_TESTINFO2
PK_TESTINFO3
PK_TESTINFO_TEST
PK_TESTINFOTEST 
PK_TESTINFO4

17 rows selected.

2.

SQL> ALTER index PK_TEST1 rebuild;

Index altered.

인덱스 리빌드 완료 입니다.

하지만 이럴경우 17개의 인덱스를 일일이 쳐야 하기 때문에 좀더 쉬운 SQL 문이 있습니다.

3. 

select 'alter index '||index_name||' rebuild ;' from user_indexes;
    
// 인덱스 리빌드 조회 + 인덱스 리빌드 실행시 사용되는 명령어 자동 생성 쿼리 (1,2 번 따로 진행 안하고 3번 부터 진행 해도 문제 없음)

3번을 sql에 칠 경우

'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index PK_RTBS rebuild ;
alter index IDX_TTBERGTDT_AVRTIME rebuild ;
alter index IDX_THDGHDT_COUNT rebuild ;
alter index IDX_TIDFGDYR_LIST rebuild ;
alter index PK_TGST rebuild ;
alter index PK_THIS rebuild ;
alter index PK_TJTRST rebuild ;
alter index SYS_IL0000032323C00004$$ rebuild ;
alter index PK_TGDFGO rebuild ;
alter index PK_TVBTRYT rebuild ;
alter index PK_TBDFBGYNFO rebuild ;

'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index PK_ rebuild ;
alter index PK_HFGUTRNGFO rebuild ;
alter index PK_TDHGFHFGINFO rebuild ;
alter index PK_DFHFGHFGO_NS rebuild ;
alter index PK_TSGHSHGLINFO rebuild ;
alter index PK_HDGHFFT rebuild ;

17 rows selected.

이런식으로 자동으로 명령어를 복사 붙혀넣기 할수있게 나옵니다.

저 내용들을 긁어서 실행하면 자동으로 인덱스 리빌드가 진행 됩니다.

4. 마지막으로 COMMIT; 은 필수 입니다.

SQL> COMMIT;

Commit complete.

월요일 부터 전화 폭탄이 터지기 시작 했습니다.

제일 먼저 걸려온 전화로는 "시스템이 작동이 안되고 있습니다." 바로 확인하려 접속하자마자 반겨주는 에러창들

이렇게 다수의 에러창이 발생하고 있었습니다. 처음에는 당황했습니다. 

리스너가 죽은건가 ? tnsname 이 바뀐건가?? tns로그가 쌓인게 터진건가?

이렇게 생각하면서 하나하나 차근차근 진행중 찾게되었습니다.

일단.. 서버에 들어가볼수 있을까? 싶어 접속 시도를 해보았지만..

네 그렇습니다. 접속이 안됩니다.

lsnrctl status 명령어로 살펴보아도 .. 문제는없었습니다.

리스너를 재 시작 해보아도 같은 에러가 발생하기에 오라클을 강제로 재시작 하려고 서비스 창을 들어간 순간...

네.. 그렇습니다. 서비스가 죽어있었습니다. 일단.. 서비스를 오른쪽 키로 실행 버튼을 눌러 실행 시켰습니다.

무슨 문제일까 싶어 윈도우 이벤트 로그를 분석 해보았습니다.

원인은 가상 메모리 부족으로 판단하고 있습니다.

많은 경고들과 함께..

죽었습니다.. 휴일이라 오라클 서비스가 죽었던게 월요일 되어서야 확인이 된겁니다.

오라클 재시작 , 리스너 재시작 후 정상가동을 확인 하였습니다.

이런경우 여유가 되시면 가상메모리를 가장 많이 차지하는 프로그램을 점검 후 서버 재부팅을 하여 사용해보시면 될거 같습니다.

기존에 존재하는 서버램이 8GB 였는데 이번에는 서버에 8GB 를 추가로 달면서 16GB 램으로 변경 되었습니다.

하지만 오라클은 8GB 때 깔아놓은 것이라 기본적으로 50% 인 4GB램 으로 세팅 해놓은 상황이라 이번에는 8GB로 할당 하려 합니다.

SGA 설정을 통해 기존에 4GB로 할당해놧던 램을 8GB로 변경하는 명령어 입니다. (오라클 11G는 자동할당 메모리를 사용하기에 SGA_MAX_TARGET, SGA_TARGET 등을 수정할 필요 없이 alter system set memory_max_target=메모리양 scope=spfile; 명령어를 통해 간편하게 수정 하도록 하겠습니다.

위와같이 show parameter memory; 를 통해 조회시 3872MB 메모리가 잡혀있는것을 확인 하실수 있습니다.

이것을 8192MB 로 바꿔보겠습니다. (8GB)

ALTER SYSTEM 명령어 뒤에 scope에 대하여

memory : 변경이 현재 상태에만 영향영향을 미침(1회성) db가 restartup 되면, 이전 값으로 돌아감 

spfile : 변경 내용을 spfile에만 저장하고 현재 상태에는 영향을 미치지 않게 합니다. Static Parameter의 경우는 이 scope만 지정 가능 합니다.             Restart없이 반영하는 것은 불가능

both : 변경 내용을 현재 상태에도 바로 반영하고, spfile에도 반영시켜, 이후 rebooting 시에도 영향을 미치도록 합니다.

shutdown immediate; 입력 후 시작 -> 검색 -> service 를 입력 합니다.

서비스 클릭 후 OracleService 로 시작하는 서비스 오른쪽 클릭 후 중지

중지가 완료 되면 시작 을 눌러 다시 실행중 으로 변경 합니다.

두개 명령어로 조회를 해보면 최대 메모리가 8GB 로 할당 된것을 볼수 있습니다.

이번에 고객사에서 연락이 왔다. " 서버 상태가 좋지 않으니 서버를 새로 구성해야 겠습니다. "

나는 그리하여 기존에 납품나간 서버를 분석(?) 하고 있었다.

데이터를 최종적으로 백업 받으려고 EXPORT를 진행 한 순간 로그에 에러가 쌓이면서 백업이 Export 가 되지 않아 에러 내용을 보는순간 캐릭터 셋 문제인것을 확인 했습니다.

당시 Export시 화면

EXP-00008: ORACLE error 6552 encountered

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-553: character set name is not recognized

EXP-00000: Export terminated unsuccessfully

원인 : 캐릭터셋(문자셋) 중복 으로 인해  발생한 문제

조회결과

이 서버의 경우는 KO16MSWIN949 , US7ASCII 두 캐릭터셋이 중복되어 발생한 문제였습니다.

TYPES_USED_IN 이 두가지 이상 중복된경우 문제가 발생합니다.

KO16MSWIN949                             CLOB         

US7ASCII                                 CLOB  

KO16MSWIN949                             CHAR   

US7ASCII                                 CHAR

KO16MSWIN949                             VARCHAR2

KO16MSWIN949 , US7ASCII 이두가지 에서 문제가 발생한것 입니다.

해결방법

완료시 정상 캐릭터셋

저는 문제없이 해결이 완료 되었으나 이 작업을 시도중 에러가 생기실수 있습니다.

에러에 대해서는 차후에 포스팅 하겠습니다.

오랜만에  글을 쓰게 되었습니다.. (그동안 바쁘기도 하고 서버가 터지는 일이 많이 줄어서..)

앞선 글에 ORA-01653 오류 대처에 대한 정보만 남겼는데 더 간단하고 쉽게 설명하기 위해서 글을 다시 씁니다.

(사실 최근에 이 문제로 한번 더 터졌습니다 서버가..)

고객사에서 기능들이 작동하지 않는다고 차장님에게 전화가 왔습니다.

차장님이 확인 후 혹시 이 문제에 대한 해결방법을 알고있냐고 물었고 발생한 에러를 보니

ORA-00604 : error occurred at recursive SQL level 1

ORA-01653 : unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-02002 : error while writing to audit trail

ORA-00604 : error occurred at recursive SQL level 1

ORA-01653 : unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

에러가 뿜어져 나오고 있었습니다.. 

이 에러에 원인을 알고 있던 저는 바로 자리에서 원격을 접속하여 (윈도우버전)

명령어를 입력 후 서버데몬을 시작하니

정상 작동 되었습니다.

이 문제가 리눅스 버전에서도 발생 할수 있기 때문에 리눅스에서 발생시 해결 방법을 알려드립니다.

리눅스 터미널에 접속하여 (리눅스버전)

명령어를 입력하면 정상적으로 오라클 이용이 가능 합니다.

추가로 윈도우 에서는 이렇게 지속적으로 명령어 치기가 힘든경우 윈도우 배치파일 과 작업 스케줄러 를 이용하면 더욱 쉽게 사용 가능 합니다.

추가적인 방법은 차후에 또 다른 글로 설명 드리겠습니다.