따봉 구걸남's 블로그

DBMS_SCHEDULER 를 활용하면 서버에서 cron job 을 쓰지 않고, 정기적인 작업을 실행할수 있다. 

이것의 장점은 .. 

  - 굳이 서버에 접속하지 않고도 오렌지 같은 툴로 DB에만 접속해서 결과 확인이 가능. 

  - Active-stand by 구성된 경우 failover 되어 master 가 변경이 되는것에 신경 쓸 필요가 없다.

관리의 포인트가 DB 로 넘어온다는 것이 부담스럽긴 하지만 잘 애플리케이션 측면에서 활용도가 높으니 

알아두는 것이 좋다. 

1. 스케쥴 등록

BEGIN DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME => ＇TEST.TEST_JOB＇,  --잡이름
  JOB_TYPE => ＇PLSQL_BLOCK＇,   --PL/SQL로 만들어진 오브젝트 실행시 PLSQL_BLOCK를 지정
  JOB_ACTION => ＇BEGIN TEST.PC_JOBTEST; END;＇,  --실행 할 오브젝트명 등록
  START_DATE => TO_DATE(＇2022/03/03 10:10:00＇,＇YYYY/MM/DD HH24:MI:SS＇),  --실행을 시작 할 일시 지정
  REPEAT_INTERVAL => ＇FREQ=MINUTELY; INTERVAL=10＇, -- 반복 실행 주기 설정
  COMMENTS => ＇여기에 주석 남기기!!＇

);   

  DBMS_SCHEDULER.SET_ATTRIBUTE('TEST.JB_TEST_JOB','INSTANCE_ID',2);  --수행할 노드지정(RAC환경에서) 

** 인터벌 example

REPEAT_INTERVAL => 'FREQ=HOURLY; interval=1'          <- 1시간 간격 수행 
REPEAT_INTERVAL => 'FREQ=MINUTELY; interval=30'       <- 30분 간격 수행 
REPEAT_INTERVAL => 'FREQ=SECONDLY; interval=5'        <- 5초 간격 수행    
REPEAT_INTERVAL => 'FREQ=WEEKLY; interval=2'          <- 2주 간격 수행

REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09;BYMINUTE=0;' <- 매일 09시 
REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09,12,15,18;' <- 매일 특정시간 
REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY= MON;' <- 격주차 월요일 
REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=1  INTERVAL=1'   <- 매달 1일 

 

2. 스케쥴

EXEC DBMS_SCHEDULER.DISABLE ('스케줄명') ;   -- 비활성화
EXEC DBMS_SCHEDULER.ENABLE ('스케줄명') ;    -- 활성화
EXEC DBMS_SCHEDULER.RUN_JOB('스케줄명') ;   -- 실행
EXEC DBMS_SCHEDULER.DROP_JOB('스케줄명') ;   -- 삭제

JOB 을 등록했으면 ENABLE 까지 해주어야 JOB 이 스케쥴링대로 돌아간다.

3. 등록현황 조회

SELECT P.OWNER,
       P.JOB_NAME,
       P.STATE,
       P.ENABLED,
       P.COMMENTS,
       CAST(P.NEXT_RUN_DATE AS DATE) NEXT_RUN_DATE,
       P.SCHEDULE_TYPE,
       P.REPEAT_INTERVAL,
       P.JOB_ACTION
FROM   DBA_SCHEDULER_JOBS P
WHERE  P.OWNER = 'AAA'

4. 실행로그 확인

SELECT LOG_ID,LOG_DATE,OWNER,JOB_NAME,JOB_SUBNAME,JOB_CLASS,OPERATION,STATUS
  FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME = '스케줄명 '
 ORDER BY LOG_DATE DESC;
 
5. 실행 상세로그 확인

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = '스케줄명'
ORDER BY LOG_DATE DESC;

ASM 환경에서 테이블스페이스를 관리하는 것은 파일시스템에서 하는것과 조금은 다르다.
컨셉은 사용자(DBA) 가 데이터파일에 대한 관리(데이터파일명, 디스크 IO 등) 에 대해서 신경쓰지 말고 ASM 에게 위임을 하라는 것이다.
1. 테이블스페이스 생성
# F/S
CREATE TABLESPACE TS_TEST_MN01
DATAFILE '/oradata/ORCL/ts_test_mn01_001.dbf' SIZE 30G AUTOEXTEND OFF;
,'/oradata/ORCL/ts_test_mn01_002.dbf' SIZE 30G AUTOEXTEND OFF;

#ASM
CREATE TABLESPACE TS_TEST_MN01
DATAFILE '+DATA1' SIZE 30G AUTOEXTEND OFF;

2. 데이터파일 추가
* UNDO
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;
* TEMP
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;
* DATA
ALTER TABLESPACE TS_TEST_MN01 ADD DATAFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;

3. RESIZE

ALTER DATABASE DATAFILE '+DATA/PIMG/DATAFILE/ts_xvarm_mn01.268.1093358285' RESIZE 10G;

4. 삭제 (F/S 과 동일)

DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES;

5. 온라인무브

ALTER DATABASE MOVE DATAFILE '###' TO '***';

원인 :
아카이브 프로세스가 트랜잭션이 들어옴에 따라 online redo log 파일을 스위치하며 archive 파일로
써내려가는 중에, 해당 destination 에 더 이상 공간이 없어서 발생하는 에러.
보통 운영 중인 디비는 처음 세팅할 때 아카이브 공간을 여유있게 잡아놓는데 그럼에도 불구하고
full 이 발생하는 경우는 대량의 DML 작업을 하는 경우이다.
조치 :
당장은 쌓인 아카이브를 (필요하다면) 백업을 받고 아카이브를 삭제.
대량작업으로 인한 로그발생이면 테이블을 no logging 으로 변경하고 재작업하자.
초기 용량 산정이 잘못되어 공간이 부족한 것이라면 디스크를 추가로 할당하자.

-- 디스크 사용량 확인

SELECT GROUP_NUMBER, NAME,
     TOTAL_MB/1024 AS TOTAL_GB,
     ROUND((TOTAL_MB - FREE_MB)/1024,2) AS USED_GB,
    ROUND(FREE_MB/1024,2) AS FREE_GB,
   ROUND((TOTAL_MB - FREE_MB) / TOTAL_MB * 100,2) AS PERCENT
  FROM V$ASM_DISKGROUP
;

- RMAN 아카이브 삭제

rman target=/
report obsolete;
crosscheck copy of archivelog all;
delete expired copy of archivelog all;
delete obsolete;
delete archivelog all;

• MCI/ MCA(Multi-Channel Intergration/Architecture) : 

멀티채널 통합/아키텍쳐. 

 MCA 는 멀티채널로 된 아키텍처, MCI 는 그것의 통합(서비스연계) 를 지칭. 

실무에선 둘을 구분하지 않고 혼용해서 사용. 

MCI 를 적용하지 않으면 업무가 추가, 변경함에 따라 개별 채널마다 추가 개발이 발생할 수 있다. 

• EAI (Enterprise Application Integration) : 

   기업 어플리케이션 통합. 

   쉽게 얘기하면 A 시스템에서 B 시스템의 데이터를 조회하고 싶을때 다이렉트로 붙어서(디비링크 등) 조회하지 말고

   EAI 를 거쳐서 조회를 하라는 것.  

   이렇게 함으로써 인터페이스가 통합/표준/단순화 되는 효과를 얻을수 있다. 

   반면 EAI 개념 없는 아키텍처에서는 시스템간 의존성이 시간이 지날수록 파악하기 힘들어질수 있다. 

• FEP (front-end processor) : 

FEP는 메인프레임의 통신제어를 위해 설계된 전용 컴퓨터이다. 대개 FEP의 한쪽은 통신회선에 그리고 다른 한쪽은 메인프레임에 연결되어, 메시지의 전송이나 수신, 패킷의 조립 및 해체, 에러의 감지 및 교정 등의 역무를 수행한다. 그러므로, FEP가 때로 통신제어기라는 말과 동의어로 사용되는 경우도 있지만, 통신제어기라는 용어가 다소 유연성이 떨어지는 용어이다.

또한, FEP는 클라이언트/서버 구조에서는 백엔드, 즉 서버에 서비스를 요청하기 위한 하나의 노드 또는 소프트웨어 프로그램을 의미하기도 한다. FEP는 일반적으로, 입력되는 데이터를 어느 정도까지 사전에 처리함으로써, 주 소프트웨어가 일을 좀 더 쉽고 빠르게 처리할 수 있도록 하는 프로그램을 가리키는 경우도 있다.

http://www.terms.co.kr/FEP.htm 원문 출처

원래 용어의 의미와 다르게 현재는 (주로 금융권에서)  대외연계 를 지칭

sql loader 로 작업시 실제 데이터 길이보다 컬럼의 길이가 작아서 안들어가는 경우

alter 구문으로 길이를 늘려주고 다시 loader 작업을 해주면 된다.

로그 파일에 필요한 정보(테이블명, 컬럼명, 원본길이) 가 다 있으니 로그를 읽어서

자동으로 alter 스크립트를 생성할수 있다.  

SQL LOADER 테스트
create user test identified by test;
alter user quota unlimite on users;
grant resource, connect to test;

create table test(a char(3), b varchar2(3),c char(2));
insert into test values('aaa','bbb','ccc');
commit;


-- test.dat ---
fff,100000000000000,Y
ggg,100,N
iss,chchchchcc,N
aaaaaa,ch,C
aaaaaaaa,bbbbbbbbbbbb,ccccccccc
aa,bb,dd
-- test.dat ---
-- test.ctl ---
LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
a
,b
,c
)
-- test.ctl ---

-- ldr.sh --
sqlldr userid=test/test control=test.ctl
sqlldr userid=test/test control=tesa.ctl
...
-- ldr.sh --

sh ./ldr.sh > ldr.log

1. loader 결과에서 ora에러 추출
: ORA-12899: value too large for column "TEST"."TEST"."B" (actual: 15, maximum: 3)
=> cat ldr.log | grep ORA > ldr_ext.log
2. 에러 메세지에서 sql 에 쓸부분 (오너,테이블/컬럼명, 길이) 만 추출
: "TEST"."TEST"."B" 3)
=> awk '{print $7, $11}' ldr_ext.log > ldr_ext.v2.log
3. 불필요 문자 제거 ( ".", ")" )
: "TEST" "TEST" "B" 3
=> awk -F '.' '{ print $1, $2, $3 }' ldr_ext.v2.log | awk -F ')' '{ print $1 }' > ldr_ext.v3.log
4. alter 구문 생성 (원본 길이 1.5배 & 반올림 )
: alter table "TEST" ."TEST" modify "B" varchar2(5);
=> awk '{ print "alter table " $1,"." $2, "modify " $3, "varchar2(" int($4*1.5 +0.5) ");" }' ldr_ext.v3.log
## 실패한 sqlldr 다시 실행
5. bad 파일에서 리스트 추출
=> ls -l *.bad | awk '{print $9}' | awk -F '.bad' '{print $1}' > rerun.log

6. 추출 결과로 스크립트 생성
: sqlldr userid=test/test control=tesa.ctl
=> awk '{ print "sqlldr userid=test/test control=" $1 ".ctl" }' rerun.log > ldr.v2.sh

DBMS_XMLGEN.GETXMLTYPE 는 동적쿼리 처럼 쓸수 있어서 활용도가 높다.
lob 타입의 경우 가공이 불가한 제약이 있는데 이 함수를 써서 타입을 변경 할 수 있다.

-- 테이블 정의서 gen
SELECT rownum as "순번"
, A.COMMENTS AS "항목명"
, A.COLUMN_NAME AS "항목ID"
, ( SELECT 'PK'
FROM DBA_IND_COLUMNS IC
WHERE IC.TABLE_OWNER = :OWNER
AND IC.TABLE_NAME = A.TABLE_NAME
AND IC.COLUMN_NAME = A.COLUMN_NAME
AND IC.INDEX_NAME LIKE 'PK#_%' ESCAPE '#'
) AS "KEY"
, A.DATA_TYPE AS "TYPE"
, A.DATA_LENGTH AS "길이"
, ' ' AS "항목설명"
,(SELECT '시퀀스 '||'SQ_'||Q.TAB_NM||'_01'
FROM SEQ_INFO Q
WHERE Q.DBNAME = A.OWNER
AND Q.TAB_NM = A.TABLE_NAME
AND Q.COL_NM = A.COLUMN_NAME
) AS "COLUMN RULE"
, ' ' AS "개인신용정보구분"
, DECODE(A.NULLABLE,'N', 'NOT NULL','')||' '||DATA_DEFAULT AS "NULL (DEFAULT)"
FROM (
SELECT T.OWNER,
T.TABLE_NAME,
M.COMMENTS,
C.COLUMN_NAME,
C.DATA_TYPE,
C.DATA_LENGTH,
C.NULLABLE,
CASE WHEN DATA_DEFAULT IS NOT NULL THEN
'('|| REPLACE(DBMS_XMLGEN.GETXMLTYPE('SELECT DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='
||''''||C.TABLE_NAME||''''||' AND COLUMN_NAME='||''''||C.COLUMN_NAME||'''').EXTRACT('//text()')
,'&'||'apos;','''') || ')' END DATA_DEFAULT,
C.COLUMN_ID
FROM DBA_TABLES T
,DBA_TAB_COLUMNS C
,DBA_COL_COMMENTS M
WHERE 1=1
AND T.TABLE_NAME = :TAB_NM
AND T.TABLE_NAME = C.TABLE_NAME
AND C.TABLE_NAME = M.TABLE_NAME(+)
AND C.COLUMN_NAME = M.COLUMN_NAME(+)
ORDER BY COLUMN_ID
) A



-- 전체 테이블 rows 조회
SELECT OWNER,
TABLE_NAME,
DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) CNT FROM '
||T.OWNER||'.'||TABLE_NAME ).EXTRACT('//text()') CNT
FROM DBA_TABLES T
WHERE TABLE_NAME LIKE 'T%'
AND OWNER ='AA'

1. 엔터티 정의

 - 엔터티 후보 수집 -> 엔터티 후보 선정 -> 엔터티 확정

2. 식별자 정의

 - 식별자 부여 -> 식별자 확정

3. 관계 정의

 - 관계 형태 설정 -> 식별/비식별 관계설정 -> M:N 관계 해소

4. 속성 정의

 - 속성후보수집 -> 속성후보선정 -> 속성확정 -> 속성검증

5. 정규화/이력관리

- 정규화 -> 이력관리 결정

6. 데이터 모델 검증

 - 사례데이터 작성 -> 데이터 모델 보완 

오라클 디비는 아래의 단계를 거쳐서 기동이 되며, 정상 open 이 되어야 client 에서 사용가능한 상태가 된다. 

shutdown -> nomount -> mount -> open 

Step 1) shutdown (closed)

  오라클 process 가 완전히 종료된 상태

   ps -ef | grep ora_  명령어를 치면 떠있는 프로세스가 없다.  

Step 2) nomount

  오라클 프로세스가 SGA, 버퍼캐쉬 등 자신이 사용할 메모리영역을 os 로 부터 할당 받는 단계.

  오라클 초기화 파라미터 파일(spfile,pfile) 에 명시된 레시피를 참조하여 인스턴스를 생성한다. 

  참조할 파라미터 파일은 아래처럼 직접 명시할수도 있다 

STARTUP NOMOUNT pfile=C:\oracle\database\initora.ora 

Step 3) mount 

  datafile 등이 instance 에 붙는(mounted) 단계

  no mount 단계에서 인스턴스가 생성이 되면 초기화 파라미터에 명시된 컨트롤 파일 경로를 참고하여

 우리가 데이터베이스라고 부르는 데이터 파일들에 마운트를 시도한다. 

[initSID.ora]  

*.control_files='/home/oracle/control01.ctl' ,

Step 4) open 

컨트롤 파일과 데이터 파일간의 정합성을 체크하고 이상이 없다면 데이터베이스는 open 상태가 된다.   

만약 일부 데이터 파일이 문제가 있다면 컨트롤 파일을 수정해서 일부만 open 할수 있고, 

불완전복구나 백업된 컨트롤파일이 아닌 컨트롤 파일로 오픈할 경우엔는 resetlogs 를 써야 open 이 가능하다. 

1. 파라미터 파일이란 : 

   shutdown 상태에서 no mount 가 되면 SGA 등이 포함된 instance 를 메모리에 올리게 되는데 이때

 파라미터 파일을 참고해서 instance 를 올린다. 

 오라클 서버 프로세스가 사용할 인스턴스를 어떤 스펙으로 올려라 같은 레시피 같은 것.

2. 어떤것을 규정하는가 

- 인스턴스에 대한 내용(SGA, PGA, 버퍼캐쉬)

- 컨트롤 파일의 위치

- 기타 성능 및 버그 관련 work around

3. 최적화 

파라미터는 사이트마다 최적이 다르다. 

- 최적을 알고 싶다면 oracle acs 파라미터 진단 서비스  => 유료 

- 널리 쓰이는 공통적인 것은 적용

- 좋은 기능이지만 리스크가 있을만한것은 놔두고  

- 그때그때 필요한것을 추가 

- 버그 문제는 히든파라미터를 써가며 work around

- 현실 : 오라클 버전 업, 패치 적용이후 파라미터들을 최신, 최적화하여 관리해야 하지만, 한번 설정한 파라미터는 시간이 갈수록 side effect 에 대해서 책임지기 싫어서 놔둠.

- 결론 : 한방에 최적을 알고 싶지만 경험을 통해 넣고 빼고 해야함 

사이베이스 datetime -> 오라클로 이관 하기
날짜 타입의 경우 형식이 달라 바로 이관이 되지 않는다.
sql loader 로 들어갈수 있게 형식을 맞추고 집어넣어야 한다.

1. sybase 에서 view 만들기

-- 오라클 date 타입 으로 변경 

create view aaa
as
select aa, bb,
convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 8),':',null) dt
from souce_tab
go
=> yyyymmddhh24miss 형식으로 datetime 을 바꿔준다.

-- 오라클 timestamp 타입(milsec 까지) 으로 변경 

convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 20),':',null) dt

=> yyyymmddhh24missff 형식으로 datetime 을 바꿔준다.

2. bcp out 으로 덤프 내리기

bcp owner..table_name out ./dat/TAB_NM.dat -c -t'|~#' -r'!@\r\n' -U계정 -P비번
-t : field terminator => '|' 한글자는 데이터에 포함될 가능성이 있고, '||' 두글자는 널 컬럼이 많을 경우 제대로 인식 못할 가능성. 안전하게 특수문자 2,3 개
-r : row terminator => 필드 하나의 값에 여러줄 데이터가 있을 경우 loader 가 row 를 잘 인식못한다.
'특수문자 + 개행' 조합 으로 한줄의 끝을 정하자.

SELECT 'BCP '||OWNER||'..'||TABLE_NAME||' OUT ./dat/'||table_name||'.dat -c -t'||''''||'|~#'||''''||' -r'||''''||'!@\r\n'||''''||' -Uxx -Pp'
FROM DBA_TABLES
WHERE OWNER='XXX'
ORDER BY TABLE_NAME
;

3. control 파일 만들기
이관 대상 테이블이 여러개일 경우, 한방 쿼리로 ctl 파일을 만들자.

========= mk_ctl.sql ===========
SET PAGESIZE 0
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF

spool ./ctl/&&..ctl

SELECT 'LOAD DATA'||CHAR(10)
||'INFILE '||''''||'./dat/'||t.table_name||'.dat'||''''||' "str '||''''||'!@\r\n'||''''||'"'||CHR(10)
||'TRUNCATE '||CHR(10)
||'INTO TABLE '||OWNER||'.'||TABLE_NAME||CHR(10)
||'FIELDS TERMINATED BY '||''''||','||''''||CHR(10)
||'TRAILING NULLCOLS'||CHR(10)
||'('
FROM DBA_TABLES T
WHERE T.OWNER='AA'
AND T.TABLE_NAME LIKE '&&1'
UNION ALL
SELECT DECODE(COLUMN_ID,1,'',',')||COLUMN_NAME||
DECODE(DATA_TYPE,'VARCHAR2',' CHAR('||DATA_LENGTH||')',
'CHAR',' CHAR('||DATA_LENGTH||')',
'CLOB',' CHAR('||DATA_LENGTH||')',
'DATE',' DATE '||''''||'YYYYMMDDHH24MISS'||''''
)
FROM (
SELECT T.OWNER, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,
C.DATA_LENGTH, C.COLUMN_ID
FROM DBA_TABLES T, DBA_TAB_COLS C
WHERE T.OWNER='AA'
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_NAME LIKE '&&1'
ORDER BY COLUMN_ID
)
UNION ALL
SELECT ')' FROM DUAL
;

spool off
exit;

** data_type = timestamp(6) => .ctl  timestamp missff

입력값 (테이블 이름) 을 받고 ctl 파일 만들기
========= mk_ctl.sh ===========

sqlplus -s lee/pwd @mk_ctl $1

========= run_mkctl.sh ===========

./mk_ctl.sh TAB_NM
./mk_ctl.sh TAB_NM

4. sql loader 실행
loader 스크립트 만들기
sqlldr userid=xxx/aaa control='./ctl/aaa.ctl' log='./log/aaa.log' bad='./bad/aaa.bad'

select 'sqlldr ....'
from dba_tables t
where t.owner='XXX'
order by table_name