[BPEL] stale된 instance log 확인하기

Oracle SOA Suite 에서 BPEL 을 재 deploy 하게 되면, 기존의 cube_instance 의 state 가 stale 상태가 된다. (CUBE_INSTANCE Table 의 STATE 컬럼의 값이 9 가 됨)

이 상태에서는 BPEL Console 로 instance log 를 확인할 수가 없으나, 방법은 있다. CUBE_INSTANCE 테이블에서만 state 가 9로 변경되었을 뿐 SYNC_TRAIL, AUDIT_DETAILS 에는 여전히 남아있으므로 이 2개의 테이블을 이용하면 된다.

SYNC_TRAIL, AUDIT_DETAILS 의 BIN 컬럼에 log trail data 가 들어있으나, 이 컬럼의 data type 은 BLOB 이므로 확인하기가 쉽지는 않다.

따라서, 일단 이 BLOB 를 우리가 사용하기 편리한 XMLTYPE 으로 변경하자.

BLOB 데이터를 XMLTYPE 으로 변환하기

확인하고자 하는 instance 의 id (cikey) 를 BPEL Console 을 통해 확인한다. 아래 그림에서 보면 회색으로 표시된 instance 가 stale 상태이며, 여기서 앞의 빨간 네모 안의 34466122 등이 cikey 이다.

cikey 를 확인후, 다음 query 를 실행하면 XMLTYPE 형태의 instance log 를 확인할 수 있다. (blob_to_xmltype 은 이 포스트 를 참고한다.)

 

SELECT blob_to_xmltype (tt.bin, 'audit-trail')
  FROM orabpel.sync_trail tt
 WHERE tt.cikey = 34466122

 

[Oracle] BLOB type 데이터를 XMLTYPE 으로 변환하기

Oracle SOA Suite 의 Repository DB 의 테이블들을 보면 Log Trail 데이터들은 크기가 커서 BLOB type 으로 저장되어 있다. (SYNC_TRAIL, AUDIT_DETAILS, SUITCASE_BIN 등의 BIN 컬럼)

BPEL Console 로 확인이 힘든 (해당 instance를 찾기 힘든) 경우, 직접 Repository DB 에 접속하여 query 를 통해 확인하기도 하는데, 이때 BLOB Type 은 읽어낼 수가 없어 XMLTYPE 으로 변환하는 Functio 을 만들어보았다. (어차피 내용은 xml 이니, XMLTYPE 으로 변환하면 활용도가 크다.)

과정은 간단하지 않다. XMLTYPE 을 만들기 위해선 XMLTYPE type package 의 CREATEXML 을 이용해야 하는데, 지원하는 type 이 BLOB 는 없고, VARCHAR2 등 외에 CLOB 만 지원한다. 따라서 일단 BLOB 를 CLOB 로 변환후, CLOB 를 XMLTYPE 으로 변환하는 과정을 거친다.

1단계 : BLOB 를 CLOB 로 변환하기

DBMS_LOB package 의 CONVERTOCLOB 를 이용한다.

   PROCEDURE convert_to_clob (
      v_clob           IN OUT   CLOB,
      v_blob           IN OUT   BLOB,
      v_amount         IN OUT   NUMBER,
      v_clob_offset    IN OUT   NUMBER,
      v_blob_offset    IN OUT   NUMBER,
      v_blob_csid      IN       NUMBER,
      v_lang_context   IN OUT   NUMBER,
      v_warning        OUT      NUMBER
   )
   AS
   BEGIN
      DBMS_LOB.OPEN (v_blob, DBMS_LOB.lob_readonly);
      DBMS_LOB.OPEN (v_clob, DBMS_LOB.lob_readwrite);
      DBMS_LOB.converttoclob (v_clob,
                              v_blob,
                              v_amount,
                              v_clob_offset,
                              v_blob_offset,
                              v_blob_csid,
                              v_lang_context,
                              v_warning
                             );
      DBMS_LOB.CLOSE (v_blob);
      DBMS_LOB.CLOSE (v_clob);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM);
         DBMS_LOB.CLOSE (v_blob);
         DBMS_LOB.CLOSE (v_clob);
   END;

2단계 : BLOB 를 CLOB 변환후, XMLTYPE 으로 변환하기

알다시피 LOB 는 compress 된 형태이므로, BLOB data 를 uncompress 한 후, 1단계에서 만든 convert_to_clob 를 이용하여 CLOB 로 변환하고 이것을 XMLTYPE.CREATEXML 을 통해 XMLTYPE 으로 변환한다.

   FUNCTION blob_to_xmltype (
      p_blob          IN BLOB,
      p_root_tag_name IN VARCHAR2
   )
      RETURN XMLTYPE
   IS
      v_blob_locator         BLOB;
      v_clob_locator         CLOB        := EMPTY_CLOB;
      v_blob_offset          NUMBER;
      v_clob_offset          NUMBER;
      v_blob_csid            NUMBER      := 873;
      v_lang_context         NUMBER      := DBMS_LOB.default_lang_ctx;
      v_warning              NUMBER;
      v_amount               PLS_INTEGER;
      v_append_string        VARCHAR2(100);
      v_append_string_len    NUMBER      := 0; 
      v_xmltype              XMLTYPE;  
   BEGIN
      v_blob_locator := UTL_COMPRESS.lz_uncompress(p_blob);
      v_amount := DBMS_LOB.getlength (v_blob_locator);
  
      DBMS_LOB.createtemporary (v_clob_locator, TRUE);
      v_append_string := '<' || NVL(p_root_tag_name, 'xmltype-root') || '>';
      v_append_string_len := LENGTH(v_append_string);
  
      DBMS_LOB.writeappend (v_clob_locator, v_append_string_len, v_append_string);
  
      v_clob_offset := v_append_string_len + 1;
      v_blob_offset := 1;
  
      convert_to_clob (v_clob_locator,
                       v_blob_locator,
                       v_amount,
                       v_clob_offset,
                       v_blob_offset,
                       v_blob_csid,
                       v_lang_context,
                       v_warning );
                      
      IF v_warning = DBMS_LOB.warn_inconvertible_char
      THEN
         RAISE_APPLICATION_ERROR(-20000,'Inconvertible Char Error');
      END IF;
     
      v_append_string := '</' || NVL(p_root_tag_name, 'xmltype-root') || '>';
      v_append_string_len := LENGTH(v_append_string);
     
      DBMS_LOB.writeappend (v_clob_locator, v_append_string_len, v_append_string);     
     
      v_xmltype := XMLTYPE.createxml (v_clob_locator);
     
      DBMS_LOB.FREETEMPORARY (v_clob_locator);
      RETURN v_xmltype;     
     
      EXCEPTION WHEN others
      THEN
         v_xmltype := NULL;
         DBMS_OUTPUT.PUT_LINE(SQLERRM);
         --DBMS_LOB.FREETEMPORARY (v_clob_locator);
         RETURN v_xmltype;
     
   END;

[DOS] 어제 날짜의 로그파일 FTP로 일괄 가져오기

내가 하는 일 중에 3대의 웹서버로 들어온 apache access log 를 이용해서 전날의 transaction 을 분석하는 게 있다. 지금 하는 업무에 맞게 몇가지 category 로 나누고 그 숫자를 파악하는 건데, access log 를 매일 새벽 shell script 를 돌려 csv 파일로 만들어둔다.

 

그리고 나선 csv 파일을 보기 좋게 Excel Pivot 으로 정리하려고 하는데 그러려면 일단 csv 파일들을 로컬로 FTP 다운로드 받아야한다. 문제는 이게 꽤나 귀찮다는 거...

 

3대의 서버에 FTP 로 접속해 경로 찾아들어가 파일찾아서 내 로컬PC 경로로 다운로드 받으려면 아주 귀찮다.

 

사람이 일을 하다보면 자꾸 잔머리만 늘어가는 듯...

내 로컬PC 를 FTP 서버로 만들어 웹서버에서 매일 쏴주면 좋겠지만, 개인 노트북이 무슨 24시간 운영중도 아니고 그래서 DOS Batch 파일을 하나 만들었다. 매일 그냥 한번 실행만 시켜주면 3군데 서버에서 알아서 전날 로그 csv 파일을 가져다 로컬 PC 에 넣어준다. 기특한 놈.. ㅋㅋ

 

문제는 전날의 날짜 구하기. 오늘 날짜야 date /t 하면 그냥 이쁘게 나온다만, 하루전은? 아주 난감하다. 그렇다고 무턱대고 오늘날짜 빼기 1 을 할 수도 없는 노릇이고...

 

구글링을 해보니 나와 같은 고민을 한 사람이 역시나 있다.

 

Date arithmetic in dos scripting - Stack Overflow

stackoverflow.com

http://www.robvanderwoude.com/datetiment.html#Yesterday ... Browse other questions tagged dos scripting date or ask your own question. ...

 

위의 예제는 온갖 format 의 date 형식에 상관없이 적용되도록 좀 복잡한데, 우리야 그저 yyyy-mm-dd 로 나온다고 가정하고 심플하게 가자. 그리고 예제에선 "며칠전" 을 parameter 로 입력받으나, 우린 날짜는 그냥 하루전이면 족하다.

 

그러나 무조건 하루전이면 되나? 월요일에 출근해서 돌리면 전날 일요일 꺼를 갖고올텐데 그럼 금요일하고 토요일은? 그래 날짜를 입력하면 그 날짜 로그를 가져오고, 그냥 실행만 시키면 default 로 전날짜 로그를 가져오도록 슥삭슥삭...

 

하루 전 날짜는 이제 구했으니 FTP 로 파일을 가져오는 걸 해봐야 할텐데 이 역시 다른 분의 소스를 참조했다.

 

나만의 사이버 카페 :: [Windows/DOS Batch] 특정 폴더의 파일을 날짜 ...

by 부평지킴이 | 2009/04/13 22:25

[Windows/DOS Batch] 특정 폴더의 파일을 날짜가 포함된 파일로 압축하여 FTP로 전송하기. 2009/04/13 22:25 in 운영 체제. 특정 폴더에 생성된 파일들을 DOS Batch(배치파일) 스크립트를 작성한 스크립트를 이용하여 다른 서버로 FTP를 이용하여 전송 ...

 

그렇게 해서 정리하면 아래와 같이 된다. 로그파일은 stat_XX.yyyymmdd.csv 라고 가정한다.

(ip, user, password, path 는 감춰주는 센스.. ㅡ.,ㅡ)

 

@echo off

set logDate=%1
if not "%logDate%"=="" goto FTPGET

for /F "tokens=1" %%a in ('date /t') do set DATE_TODAY=%%a

REM [Date Format = yyyy-mm-dd]
set yyyy=%DATE_TODAY:~0,4%
set mm=%DATE_TODAY:~5,2%
set dd=%DATE_TODAY:~8,2%

set dayCnt=1

REM Substract your days here
set /A dd=1%dd% - 100 - %dayCnt%
set /A mm=1%mm% - 100

 

:CHKDAY
if /I %dd% GTR 0 goto DONE
set /A mm=%mm% - 1
if /I %mm% GTR 0 goto ADJUSTDAY
set /A mm=12
set /A yyyy=%yyyy% - 1

 

:ADJUSTDAY
if %mm%==1 goto SET31
if %mm%==2 goto LEAPCHK
if %mm%==3 goto SET31
if %mm%==4 goto SET30
if %mm%==5 goto SET31
if %mm%==6 goto SET30
if %mm%==7 goto SET31
if %mm%==8 goto SET31
if %mm%==9 goto SET30
if %mm%==10 goto SET31
if %mm%==11 goto SET30

 

REM ** Month 12 falls through

 

:SET31
set /A dd=31 + %dd%
goto CHKDAY

 

:SET30
set /A dd=30 + %dd%
goto CHKDAY

 

:LEAPCHK
set /A tt=%yyyy% %% 4
if not %tt%==0 goto SET28
set /A tt=%yyyy% %% 100
if not %tt%==0 goto SET29
set /A tt=%yyyy% %% 400
if %tt%==0 goto SET29

 

:SET28
set /A dd=28 + %dd%
goto CHKDAY

 

:SET29
set /A dd=29 + %dd%
goto CHKDAY

 

:DONE
if /I %mm% LSS 10 set mm=0%mm%
if /I %dd% LSS 10 set dd=0%dd%
set logDate=%yyyy%%mm%%dd%

 

:FTPGET
setlocal

 

echo open {host1}> ftp_get.sh
echo user {user1} {password1}>> ftp_get.sh
echo prompt off>> ftp_get.sh
echo cd {path1}>> ftp_get.sh
echo get stat_1Q.%logDate%.csv>> ftp_get.sh
echo close>> ftp_get.sh

 

echo open {host2}>> ftp_get.sh
echo user {user2} {password2}>> ftp_get.sh
echo prompt off>> ftp_get.sh
echo cd {path2}>> ftp_get.sh
echo get stat_2Q.%logDate%.csv>> ftp_get.sh
echo close>> ftp_get.sh

 

echo open {host3}>> ftp_get.sh
echo user {user3} {password3}>> ftp_get.sh
echo prompt off>> ftp_get.sh
echo cd {path3}>> ftp_get.sh
echo get stat_3Q.%logDate%.csv>> ftp_get.sh
echo quit>> ftp_get.sh

 

c:\windows\system32\ftp -v -n -s:ftp_get.sh

del ftp_get.sh

 

rename stat_1Q.%logDate%.csv stat_1Q_%logDate%.csv
rename stat_2Q.%logDate%.csv stat_2Q_%logDate%.csv
rename stat_3Q.%logDate%.csv stat_3Q_%logDate%.csv