'오라클' 태그의 글 목록

'오라클'에 해당되는 글 2건

2017.09.08 Pipe를 통해 export, load 하기
2017.08.02 Undo tablespace full로 인한 서비스 실패를 막기 위한 시나리오

Pipe를 통해 export, load 하기

Oracle 2017. 9. 8. 16:41

데이터를 export 하여 가령 다른 서버로 전송한 후 sqlloader로 import할 때

특히 네트워크 속도가 좋지 않은 경우

덤프파일을 압축하면 전체적으로 시간을 절감할 수 있다.

데이터를 export할 공간이 넉넉하지 않은 경우에도 도움이 될 수 있다.

데이터 export 수행할 쿼리 파일을 생성한다.

-- in export.sql
SET term on
SET echo off
SET UNDERLINE off
SET HEADSEP off
SET TRIMSPOOL on
SET TRIMOUT on
SET COLSEP ","
SET SQLPROMPT ''
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 999999999
SET TERMOUT OFF
SET FEEDBACK OFF

SPOOL export.pipe;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT

col1

||'@@'|| col2

||'@@'|| col3

||'@@'|| col4

||'@@'|| col5
FROM TBL WHERE 1=1;
SPOOL OFF;
quit;
/

맨 마지막에 quit; / 부분이 없으면, 쿼리를 수행한 후 sqlplus가 종료되지 않고 멍하니 멈춰있게 되므로 유의한다.

sqplus로 데이터 export → pipe 파일 → gzip 으로 direct하여 export와 동시에 압축되도록 한다.

rm -f ./export.pipe

mkfifo ./export.pipe
chmod 770 ./export.pipe
cat ./export.pipe | gzip > ./export.csv.gz &
sqlplus -s ${DBUSER}/${password} @export.sql
rm -f ./export.pipe

반대로, 이 파일을 sqlload할 때는 다음과 같이 수행한다.

-- in import.ctl

LOAD DATA
INFILE import.pipe
BADFILE import.bad
APPEND INTO TABLE TBL
FIELDS TERMINATED BY "@@"
TRAILING NULLCOLS
(

col1

,col2

,col3

,col4 CHAR -- load하는 타겟 테이블의 칼럼이 VARCHAR2 칼럼일 경우 굳이 명시하자면

,col5 INTEGER -- load하는 타겟 테이블의 칼럼이 NUMBER일 경우

)

rm -f ./import.pipe

mkfifo ./import.pipe
chmod 770 ./import.pipe
gunzip < ./export.csv.gz > ./import.pipe &

sqlldr userid=${DBUSER}/${password} control=import.ctl errors=100 rows=5000

rm -f ./import.pipe

INFILE을 지정할 때 현재 디렉토리임을 명시하려고 ./import.pipe로 쓰면 오류가 발생하므로

그냥 파일이름만 써줘야 한다.

마찬가지로 sqlldr 커맨드에서 control 파일을 control=./import.ctl 로 지정하면 오류 발생한다.

컨트롤 파일을 통해 여러 옵션을 조정할 필요가 없다면

간단히 sqlldr userid=${DBUSER}/${password} data=import.pipe 로 수행할 수도 있다.

5000건 단위로 commit을 위해 rows=5000을 지정했지만, 실제 수행될 때는 5천 건 단위로 커밋을 찍지 않는데, 이런 경우에는 bindsize 옵션을 추가해볼 수 있다.

저작자표시 비영리 변경금지

Posted by in0de

Undo tablespace full로 인한 서비스 실패를 막기 위한 시나리오

Oracle 2017. 8. 2. 23:38

Undo segment를 과도하게 사용하여

예를 들어, 개발한 배치, 개발자의 임의 작업 등이 잘못 진행되고 있다가 뒤늦게 발견하여 cancel했을 경우라던가 할 때

이를 rollback을 하는 중이지만 UNDO tablespace의 unexpire 영역이 반환되지 않아,

정규 배치 등의 서비스가 실패할 가능성이 있는 시나리오에 대응하려면

다음 쿼리들을 통해 문제를 해결하도록 한다.

Rollback 진행 중인 undo segment를 조회하는 쿼리

parallel rollback 일 경우

select * from v$fast_start_transactions where state='RECOVERING';

serial rollback 일 경우 (x$KTUXE(Kernel Transaction Undo Transaction Entry))

select ktuxesiz from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;

Undo tablespace의 사용량은 다음과 같이 확인한다.

col msg format a80
WITH TB AS (
    SELECT TABLESPACE_NAME
          ,(SELECT ROUND(SUM(BLOCKS)*8/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=A.TABLESPACE_NAME) TOT_MB
          ,SUM(DECODE(A.STATUS,'ACTIVE'   ,SIZE_MB,0)) ACTIVE_MB
          ,SUM(DECODE(A.STATUS,'UNEXPIRED',SIZE_MB,0)) UNEXPI_MB
          ,SUM(DECODE(A.STATUS,'EXPIRED' ,SIZE_MB,0)) EXPIRE_MB
    FROM (
           SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES)/1024/1024) SIZE_MB
           FROM   DBA_UNDO_EXTENTS
         --WHERE tablespace_name in ( select value from v$parameter where name='undo_tablespace')
           GROUP BY TABLESPACE_NAME, STATUS
          ) A
    GROUP BY TABLESPACE_NAME
)
SELECT to_char(sysdate,'HH24:MI:SS')||' '||TABLESPACE_NAME||' : '||round((active_mb+unexpi_mb+expire_mb)/tot_mb*100)||
       ' % -> Act('||active_mb||') Unexp('||unexpi_mb||') Exp('||expire_mb||') TOT('||TOT_MB||')' MSG,
       round(100*(ACTIVE_MB)/TOT_MB,2),
       round(100*(ACTIVE_MB+UNEXPI_MB)/TOT_MB,2)
FROM TB;

현재 진행되고 있는 rollback의 잔여시간은 다음 쿼리로 대충 추산할 수 있다.

set serveroutput on
declare
    l_start number ;
    l_end   number ;
    r_min   number ;
begin
    select ktuxesiz into l_start from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    dbms_lock.sleep(60);
    select ktuxesiz into l_end   from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    r_min := round(l_end / (l_start - l_end) ) ;
    dbms_output.put_line('['||to_char(sysdate,'HH24:MI:SS')||'] Remain Time : '|| r_min ||'분 '|| round(r_min/60,2)||'시간 / '||(l_start-l_end)||' blocks done / '|| l_end ||' Total blocks remains' );
end ;
/

운영DB에 임시 UNDO Tablespace를 생성한 후, 향후의 트랜잭션들은 이 UNDO tbs를 사용하도록 조치한다.

create undo tablespace undotbs2 datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_001';

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_002';

...

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_NNN';

alter system set undo_tablespace=undotbs2 ;

이때 UNDO tablespace를 변경하는 것 자체는 금방 수행되므로 긴장하지 않아도 된다.

다만, alert log에 'active transactions found in undo tablespace NN - moved to pending switch-out state 라는 로그가 트랜잭션 당 1 라인씩 계속 발생할 수 있는데, 어떤 사이트의 경우에는 이 메시지가 다량 발생하기도 하였다고 하므로, alert log가 존재하는 path의 가용량을 모니터링하면서 작업 수행하자.

이러한 세션들은 세션에서 commit을 찍고 알아서 종료되길 기다리거나, 아니면 kill하여 처리하면 된다.

UNDO tablespace를 switch 한 후, 이전의 UNDO tbs를 사용하는 채로 commit 되지 않고 남아있는 세션을 찾아본다

select a.usn, a.name, b.status, c.tablespace_name, d.addr, e.sid, e.serial#, e.username, e.program, e.machine, e.osuser
from   v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e
where a.usn=b.usn
and    a.name = c.segment_name
and a.usn = d.xidusn
and    d.addr = e.taddr
and b.status = 'PENDING OFFLINE';

rollback이 종료되고 상황이 정상화 되면,

기존 UNDO tablespace를 사용하도록 파라미터를 재설정해주고,

추가했었던 UNDO tablespace를 drop하면 된다.