'Oracle(오라클)'에 해당되는 글 10건

스케줄러에 등록해서 사용할 것

참고로 zip 명령어는 Win 버전따라 실행되지 않을 수 있으므로, 참고할 것

사용 환경 : Windows Server 2012

어느날 한 서버의 DB가 죽었다고 해서 한번 확인해봤다. 방치한지 좀 됐다고 한다...

Alert Log는 왜인지 몰라도 존재하지 않아서 어떤 문제가 발생했는지 확인하기가 어려웠다.

최초 현상

1. 오라클 관련 서비스는 실행 상태

2. sqlplus /nolog -> connect / as sysdba 로 접속 시 휴지 인터페이스로 접속

3. 아무래도 모종의 이유로 DB가 Shutdown 되어있을것으로 추측

4. DB가 꺼져있을것이라는 단순한 추측에 Startup 실시

5. DB 실행 도중 ORA-03113: 통신 채널에 EOF가 있습니다. 오류가 발생하며 DB가 다시 Shutdown 됨.

일단 어디에서 문제가 발생했는지 확인하기 위해 순서대로 올려보기로 함.

> sqlplus /nolog

> connect / as sysdba

> startup nomount;

> alter database mount;

> alter database open;

> ORA-03113: 통신 채널에 EOF가 있습니다.

open하는 과정에서 오류가 발생한것을 확인하였다.

http://egloos.zum.com/repository/v/5790800 를 참고하여 해결을 진행하였다.

해당 게시글에 따르면 open 시 오류가 발생하는 경우는 아래와 같다고 한다.

 1) DATA파일이 깨졌을 때

바로 복구를 진행해보았다.

> recover database until cancel;

> alter database open resetlogs;

오류를 해결한 후 다시 진행하자 정상적으로 진행되었다.

바로 Shutdown 후 다시 정상적인 방법으로 Startup을 진행하자 정상적으로 올라오는것을 확인했다.

후담으로 위에서 4가지의 경우를 언급했는데, 내가 볼 땐 아카이브 로그가 제일 문제가 아니었나.. 싶다.

아님 말고.

이번에 DB 먹통 현상이 발생해 복구 진행하다가 오류가 나서 확인했던 절차 정리해둠.

> recover database until cancel;

> alter database open resetlogs;

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 100359680 bytes disk space from 8589934592 limit

에서 오류가 난다.

해결법은 db_recovery_file_dest_size를 늘려주던지, Archive Log를 정리하여 여유공간을 확보해주면 된다.

1. db_recovery_file_dest_size를 늘려주는 방법

> alter system set db_recovery_file_dest_size=10G;

> alter database open resetlogs;

2. archive log 정리 방법

> Archive Log가 있는 경로로 이동하여 필요 없는 Archive Log 정리

> Oracle_home 내 Bin 폴더 안에 있는 RMAN 을 실행

> connect target /

> crosscheck copy of archivelog all;

> delete expired copy of archivelog all;

> sqlplus 실행하여 sysdba 권한으로 입장

이후

> select * from v$flash_recovery_area_usage;

조회 시 파일 타입별로 현재 사용중인 퍼센트를 보여준다.

혹시 궁금할까봐 적어놓음

1일 : 1

1시간 : 1*24 | 1/24

1분 : 1*24*60 | 1/24/60

1초 : 1*24*60 | 1/24/60/60

막 휘갈겨 썼으니 적당히 조절해서 써야지

타 부서 DB개선 요청이 들어와서 SQL Developer에서 접속하여 함수, 프로시저 등 상세정보 확인을 하려고 하니 나타난 오류다.

확인 결과 4.1.x 버전부터 JDK8을 사용하는데, 9i 이하 버전은 호환이 안된단다.

SQL Developer 프로그램 특성 상 사용하는 JDK 버전을 바꾸는건 불가능하고, 

SQL Developer 4.0.x 이하 버전을 사용하면 된다.

4.0.3 경로 : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-downloads-403-2529784.html

만약 경로가 깨졌다면 오라클 공홈 - Download - SQL Developer - Previous Version을 4.0.3이 나올때까지 클릭하면 찾을 수 있다.

시스템 사용 중 엑셀 출력기능을 이용하는데, 특정 데이터 순서가 틀어진것을 확인.

이게 틀어진 상태로 계속 출력되는게 아니라, 5번 중 2번이 틀어진 순서로 출력됨.

쿼리를 보다보니 의심스러운 부분 발견

보통 제일 밑에 있어야 할 ORDER BY가 중간에 있는것

즉 순서가

A 테이블 내 쿼리 실행-> B 테이블 내 쿼리 실행 -> 두개 테이블 조인 -> 조건에 부합하는 데이터 체크 -> 정렬 -> 출력

이 정상적인 순서일텐데, 

A 테이블 내 쿼리 실행-> A 테이블 정렬 -> B 테이블 내 쿼리 실행 -> 두개 테이블 조인 -> 조건에 부합하는 데이터 체크 ->  출력

이렇게 돌아가고 있다.

(물론 실제 오라클 돌아가는건 좀 다르다. 나중에 봤을 때 이해를 쉽게하기 위해 위와같이 적음)

JOIN을 진행할 때 정렬 순서에 이상이 생길 가능성이 있다고 판단, ORDER BY를 가장 뒤쪽으로 빼내었음.

현재는 경과를 지켜보는중. 캐시가 남아있어서 그러는지, 진짜 잘 해결되서 문제가 없는지는 판단이 서지 않지만, 일단 엑셀 출력 시 순서는 이상없이 돌아가는듯하다.

오라클쪽 문제는 아니고, 운영중인 SW의 java 오류다... 카테고리 따로 만들기는 귀찮기 때문에 그냥 여기에 작성중

[ERROR]|java.io.IOException: Too many open files (errno:24)

        at sun.nio.ch.ServerSocketChannelImpl.accept0(Native Method)

        at sun.nio.ch.ServerSocketChannelImpl.accept(ServerSocketChannelImpl.java:150)

        at com.ubimeta.lib.net.manager.ServerSocketManager.acceptPendingConnections(ServerSocketManager.java:222)

        at com.ubimeta.lib.net.manager.ServerSocketManager.run(ServerSocketManager.java:159)

현재 시스템에서 나고있는 오류다.

계속 발생하는건 아니고, 비 주기적으로 발생하는 오류.

물론 이 오류는 어떠한 근본적인 문제가 발생해서 나는 부가적인 오류다.

단순하게 ulimit에서 한계를 65535로 늘리는건 전혀 해결법이 되지 못한다.

lsof에서 확인해보니 Close_wait 상태의 항목이 계속 늘어나고있다.

미들웨어 SW쪽에 버그가 있는듯한데, 이게 지금에와서야 발생한 이유도 모르겠거니와, SW쪽 유지보수가 어떤 이유로 인해 종료, 재계약을 할 수 없는 바람에 수정도 불가능한 상황이다.

-----------------------------------------------------------------------------------------------------------------

해결은 결국 못했다. 아무것도 효과가 없다. SW쪽을 건들여야하는데, 소스마저도 받지 못한 상태라 아무것도 할 수가 없다.

일단은 매일마다 crontab을 이용하여 SW 재기동을 하고있는데, 이건 완전 임시방편... 뭐, 이렇게만해도 위의 문제가 발생할 확률이 적긴하다.

하지만 완전히 고쳐진건 아니기 때문에, 언제 다시 문제가 터질지 모르는 일.

하아............

일단 올해 내에 미들웨어 개발도 진행할 예정이니 내년 중순쯤에는 신규 미들웨어로 교체할 수 있고, 그러면 문제가 자연스럽게 해소될것으로 생각된다.

정리해둡니다. 참고하실분들은 참고하세요.

--예측 실행계획-------------------------------------------------------------------

/*

1. EXPLAIN PLAN FOR 뒤에 쿼리문 작성 후 실행

2. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 실행

3. 끝. 단, 해당 실행계획은 예상 실행계획이므로 실제 실행계획과는 다를 수 있음

*/

EXPLAIN PLAN FOR

;

SELECT * FROM TABLE(DBMS_XPLAN_DISPLAY);

/*예시*/

EXPLAIN PLAN FOR

SELECT SYSDATE FROM DUAL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--실제 실행계획-------------------------------------------------------------------

/*

1. 쿼리 실행 전 STATISTICS_LEVEL을 ALL로 변경

2. 실행계획을 보고자하는 쿼리 실행

3. 하단 V$SQL에서 방금 돌린 쿼리를 검색하여 SQL_ID, CHILD_NUMBER를 확인

  + PARSING_SCHEMA_NAME은 해당 스키마명, MODULE은 알고있다면 쓰는게 좋으며(TOAD 등), SQL TEXT는 쿼리내용 입력(일부분만)

4. dbms_xplan.display_cursor를 이용하여 SQL_ID, CHILD_NUMBER 입력 후 쿼리실행

5. 실행계획 확인 후 STATISTICS_LEVEL을 BASIC로 변경

PS. SQL_ID, CHILD_NUMBER를 NULL로 할 경우 최근 실행된 쿼리의 실행계획을 보여준다.

    단, 운영중인 시스템에서는 사용이 힘들다.(마지막 실행된 쿼리가 계속 바뀌기 때문)

*/

ALTER SESSION SET STATISTICS_LEVEL = ALL;

select * from table(dbms_xplan.display_cursor(/*SQL_ID*/'',/*CHILD_NUMBER*/'0','ALLSTATS LAST'));

ALTER SESSION SET STATISTICS_LEVEL = BASIC;

/*예시*/

SELECT COUNT(2) FROM TN_PW00_APT_CD;

SELECT SUBSTR(SQL_TEXT, 1, 100) SQL_TEXT, SQL_ID, CHILD_NUMBER

FROM V$SQL

WHERE PARSING_SCHEMA_NAME = 'CITYWASTE'

AND MODULE = 'SQL Developer'

AND SQL_TEXT LIKE '%SELECT A.CTZN_SRNO%'

;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(/*SQL_ID*/'fyaw7c6z5t935',/*CHILD_NUMBER*/'0','ALLSTATS LAST'));

--SQL TRACE---------------------------------------------------------------------

/*

1. USER_DUMP_DEST가 어디에 설정되어있는지 확인(RAC등을 사용하여 이중화 된 경우 DB1,2 다를 수 있음)

2. TRACEFILE_IDENTIFIER='ZZZ' 를 설정하여 뒤에 생성할 TRACE 파일명 뒤에 _ZZZ를 붙임(구분하기 위함)

3. SQL_TRACE를 활성화

4. 쿼리 실행

5. SQL_TRACE 비활성화

6. TRACEFILE_IDENTIFIER='' 를 설정하여 기존대로 복구

7. UX등에서 USER_DUMP_DEST 경로로 이동 후 "TKPROF 현재파일명 변환할파일명" 입력

8. 변환된 파일을 메모장, 에디터플러스, 노트패드++ 등을 이용하여 확인

*/

SHOW PARAMETER USER_DUMP_DEST

ALTER SESSION SET TRACEFILE_IDENTIFIER='ZZZ';

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(1) FROM DUAL;

ALTER SESSION SET SQL_TRACE = FALSE;

ALTER SESSION SET TRACEFILE_IDENTIFIER='';

/*UX 등에서 실행*/

cd /oracle/product/.../trace

cd /oracle/product/.../trace

tkprof ..._ora_8112_ZZZ.trc ..._ora_8112_ZZZ.txt

==> 아래와 같이 나올 시 성공

TKPROF: Release 11.2.0.2.0 - Development on Tue Jul 28 17:06:59 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

/*끝*/

1. 시스템 작업이 있어 FK 비활성화 후 다시 활성화 하려고 보니, 아이고... 너무 많은 데이터가 들어오는 나머지 FK에 해당하지 않는, 고아 데이터들이 있는지, FK 활성화가 안된다. 오류난다. 으아니챠

2. 데이터 양이 많아 선결시되는 작업이, 미들웨어, web/was STOP 후 데이터를 검색하는 방법뿐이다. 데이터가 너무 많아 검색조차 힘들다. 하위 테이블이 5억건정도 되는듯...

3. 아아... 현 상황은, A테이블에 3개 컬럼이 PK로 걸려있고, 해당 3개 컬럼이 B 테이블의 FK로 걸려있다. A 테이블은 500만건정도 되고, B 테이블은 5억건정도 존재한다.

4. where (AA, BB, CC) not in (select AA, BB, CC from B) 으로 검색 시도. 미쳤다 이건. 안나온다. 인덱스 타는것도 아니고, FULL 스캔중인데... 안나온다.

5. 하다하다 도무지 힘들어서 구글링좀 하고있는데.... 맙소사. 해결법이 FK를 삭제하란다. 이런 1차원적인 사고방식이라니... 운영중인 시스템의 FK를 삭제하라고? 이런 말도안되는 사항은 보류.

--------------------------------------------------------------------------------------------------------------------

1. 포기다. 방법 없다. 그냥 검색하고 기다려본다. 닥치고 기다린다... ㅠㅠ

검색쿼리

select /*+full(b)*/* from b
where not exists (select /*+full(a)*/1 from a where a.AA = b.AA and a.BB=b.BB and a.CC = b.CC)

--------------------------------------------------------------------------------------------------------------------

보니까 대충 20분정도 걸린다.

조회건은 3만건.

그런데... 지금껏 아무도 몰랐던 오류 데이터가 상당하다. 현장에서 이 사항을 먼저 고쳐야 할듯.

후...... 이런 밑바닥에 있는 오류 데이터라니;

--------------------------------------------------------------------------------------------------------------------

현장 조치는 대부분 끝냈다. 

시간이 지연되서 32,000건으로 늘었다만... 초기에나 발생한 데이터가 많고, 많이 조치되서 증가율은 현저히 떨어졌다. 특히 최근에는. 마지막 데이터만 고치고, 트리거를 이용해 해당 데이터를 다른 테이블에 따로 쌓도록 진행했다.

데이터가 추가로 들어오면 정상 진행이 힘들기 때문에 미들웨어 선 종료 후 데이터 삭제

FK 활성화 진행

1시간정도 후에 확인해보니 FK가 활성화되어있다.

이것때문에 몇주를 고생한거지;;;;

오늘 일하는 중 단순 조회에서 막혀버렸다.

'ORA-00913: 값의 수가 너무 많습니다'

원래 내가 알고있는게 맞다면 위의 오류는 INSERT 할때 입력값의 개수랑 입력받는 컬럼 개수가 다를 때 나는 쿼리인데

SELECT 할 때 나타나서 벙찌고 있었다.

사용한 쿼리는 아래와 같다.

SELECT 
  B.CITY,
  APT,
  EQIP_NM,
  MEMO
FROM 
  APT_CD A,
  (
    SELECT 
      CITY,
      APT_CD,
      EQIP_CD,
      MEMO
    FROM 
      EQIP
    WHERE CITY IN ('23','24','25','26')
    AND EQIP_CD NOT IN
      (
        SELECT 
          CITY,
          EQIP_CD
        FROM DSCHQTY
        WHERE CITY  IN ('23','24','25','26')
        AND DTTM >= TO_DATE(20140205,'YYYYMMDD')
        GROUP BY CITY, EQIP_CD
      )
    AND DEL='N'
  ) B
WHERE A.CITY = B.CITY
AND A.APT_CD(+) = B.APT_CD
ORDER BY CITY, EQIP_CD;

▲ 이 쿼리는 실제 사용한 쿼리와 조금 다릅니다. 컬럼명이라던가 컬럼명이라던가 컬럼명이...

10분정도 뒤져봐도 뭐가 잘못됬는지 확인이 안되다가

차 한잔 마시고 진정하고 다시 확인해보니 어머! 서브쿼리가 문제네!?

해결한 쿼리는 아래와 같다.

SELECT 
  B.CITY,
  APT,
  EQIP_NM,
  MEMO
FROM 
  APT_CD A,
  (
    SELECT 
      CITY,
      APT_CD,
      EQIP_CD,
      MEMO
    FROM 
      EQIP
    WHERE CITY IN ('23','24','25','26')
    AND EQIP_CD NOT IN
      (
        SELECT 
          EQIP_CD
        FROM DSCHQTY
        WHERE CITY  IN ('23','24','25','26')
        AND DTTM >= TO_DATE(20140205,'YYYYMMDD')
        GROUP BY CITY, EQIP_CD
      )
    AND DEL='N'
  ) B
WHERE A.CITY = B.CITY
AND A.APT_CD(+) = B.APT_CD
ORDER BY CITY, EQIP_CD;

수정한 부분은 조건절의 

    AND EQIP_CD NOT IN
      (
        SELECT 
          CITY_ARCD,
          EQIP_CD
        FROM DSCHQTY
        WHERE CITY  IN ('23','24','25','26')
        AND DTTM >= TO_DATE(20140205,'YYYYMMDD')
        GROUP BY CITY, EQIP_CD
      )

바로 이 부분.

조건을 비교하는건 EQIP_CD 하나인데, 서브쿼리에서 두개를 불러와서 생긴 오류였다.

진짜 간단한건데... 이런에데서 실수하니 그저 벙찔뿐....

이런 실수는 또 찾기도 어렵다 찐발...