[Oracle] SQL*Loader (대량 데이터 import 시 사용)

대량의 데이터를 집어넣는 방법중 SQL*Loader 라는게 있어 정리.
(CSV 형식으로 되어있는 데이터를 import 한다.)


batch.sh (수행 파일)
1004lucifer

#!/bin/sh
sqlldr DB_ID/DB_PW@HOST:PORT/SERVICE_NAME CONTROL=control.ctl   skip=1

control.ctl (컨트롤 파일)

load data                        <= 기본 들어감
infile 'data.csv' "str '\n'"      <= '파일명' "레코드 구분자"
append                          <= append형식
into table {_TABLE_NAME_}    <= 데이터가 들어갈 Table명
fields terminated by ','          <= 데이터 구분자
OPTIONALLY ENCLOSED BY '"' AND '"'  <= 데이터마다 콤마(")로 감싸져있음 - 옵션
trailing nullcols
( 
{_FIELD_NAME1_} CHAR(4000),  <= 첫번째 데이터가 들어갈 컬럼
{_FIELD_NAME2_} CHAR(4000),  <= 두번째 데이터가 들어갈 컬럼
{_FIELD_NAME3_} "{_FIELD_NAME3_}.NEXTVAL" <= 시퀀스를 이용해 자동입력
)

data.csv (데이터 파일)

B0001393,2
B0001316,2
B0001371,2
B0001344,2
B0001362,4
B0001335,2
B0001313,2
B0001316,4
B0001321,2
B0001326,3
... (생략)

결과 후 로그
1004lucifer

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Dec 9 11:59:01 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Control File:   control.ctl
Data File:      data.csv
  File processing option string: "str '
'"
  Bad File:     data.bad
  Discard File:  none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional
Table {_TABLE_NAME_}, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
{_FIELD_NAME1_}                           FIRST  4000   ,  O(") CHARACTER
                                                      O(")
{_FIELD_NAME2_}                                  NEXT  4000   ,  O(") CHARACTER
                                                      O(")
{_FIELD_NAME3_}                                  NEXT     *   ,  O(") CHARACTER
                                                      O(")
    SQL string for column : "{_FIELD_NAME3_}.NEXTVAL"
value used for ROWS parameter changed from 64 to 30
Table {_TABLE_NAME_}:
  1375721 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                 247860 bytes(30 rows)
Read   buffer bytes: 1048576
Total logical records skipped:          1
Total logical records read:       1375721
Total logical records rejected:         0
Total logical records discarded:        0
Run began on Fri Dec 09 11:59:01 2016
Run ended on Fri Dec 09 12:01:57 2016
Elapsed time was:     00:02:56.37
CPU time was:         00:00:07.11

PS.
137만건의 데이터를 입력하는데 3분정도 걸렸다.
확실히 수행속도는 괜찮은듯..



SQL*Loader 설명 잘 되어 있는 곳 (링크)
- DB별 CSV 파일 로딩 방법

[Oracle] DB에서 수행되는 SQL을 로그파일로 생성하기

특정 시간, 또는 특정 주기마다 Oracle 에 어떤 문제가 발생을 하는데
프로그램상으로는 별다른 쿼리를 날리지 않는데 DB서버에 부하가 많이 증가하는 경우

Oracle 내부적으로 다른 쿼리를 수행하는지 확인하기 위해 아래와 같이 10분 간격으로 수행되는 쿼리를 로그파일로 저장을 시켰다.

 과거 수행된 내역의 SQL을 추출하는 쿼리가 있기는 하지만..
 시간(구간)을 지정해야 하고 한눈에 보기에 편하지가 않아 별다른 솔루션이 없다면 이와 같은 방법으로 구축을 해 놓으면 한눈에 보기 편하게 된다.
 단점으로는 10분간격(변경가능)으로 수행중인 쿼리이다보니 그 사이에 수행된 쿼리는 로그에 쌓이지 않는다.

1. DB서버에 쉘을 만들어 Cron 등록

[id@SERVER_NAME ~]$ crontab -l
# logging SQL Query
*/10 * * * * /home/id/sqlStatic.sh
10 0 * * * /home/id/sqlStatic_log.sh
[id@SERVER_NAME ~]$

2. sqlStatic_log.sh 파일 내용
 (전날의 로그파일에 log 라는 확장자를 붙여준다. - 매일 0시 10분에 수행)

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic_log.sh
#!/bin/sh
mv /home/id/sqlStat/sqlStatic_$(date -d '1day ago' +%y%m%d) /home/id/sqlStat/sqlStatic_$(date -d '1day ago' +%y%m%d).log
[id@SERVER_NAME ~]$

3. sqlStatic.sh 파일 내용
  (현재 수행되는 SQL Query 의 Logging을 남긴다. - 매 10분마다 수행)
1004lucifer

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic.sh
#!/bin/sh

# 환경설정
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
ORACLE_SID=orcl
PATH=$PATH:/oracle/app/11.2.0/grid/bin
export ORACLE_HOME
export ORACLE_SID

# 용량을 위해 log 확장자가 붙은 파일을 압축한다.
gzip /home/id/sqlStat/*.log 2> /dev/null

# sqlStatic.sql 파일을 수행한다. (/home/id/sqlStat/spool.lst 파일에 수행중인 query가 저장된다. )
sqlplus dbId/dbPw@host/orcl @/home/id/sqlStatic.sql > /dev/null

# 로그파일에 현재 수행된 시간을 추가한다.
echo $(date +%y\.%m\.%d\ %H\:%M\:%S) >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)

# 현재 수행되는 query 를 로그파일에 추가한다.
cat /home/id/sqlStat/spool.lst >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)

# 날짜 가독성을 위해 빈줄을 로그파일에 추가한다.
echo -e \\n\\n\\n >> /home/id/sqlStat/sqlStatic_$(date +%y%m%d)
[id@SERVER_NAME ~]$

4. sqlStatic.sql 파일 내용
  (현재 수행중인 query를 뽑아내기 위한 쿼리 - /home/appadmin/sqlStat/spool 파일에 query 내용이 저장된다.)

[id@SERVER_NAME ~]$ cat /home/id/sqlStatic.sql
-- set define off;
set null @
set pages 0
set colsep '^'
set trimspool on
set lines 30000
set termout off
set feed off
spool /home/id/sqlStat/spool
SELECT a.osuser,
       a.SID,
       a.serial#,
       a.status,
       b.sql_text
  FROM v$session a,
       v$sqlarea b
 WHERE a.sql_address = b.address
       AND status = 'ACTIVE'
;
spool off
exit
[id@SERVER_NAME ~]$

5. 파일 생성 결과
  생성된 임시파일(spool.lst)과 로그파일(sqlStatic_160913)
1004lucifer

[id@SERVER_NAME ~]$ ls -l /home/id/sqlStat/
합계 52
-rw-r--r-- 1 id id  4500  9월 13 17:00 spool.lst
-rw-r--r-- 1 id id 42899  9월 13 17:00 sqlStatic_160913
[id@SERVER_NAME ~]$

확인

 sqlStatic.sql 파일의 내용을 보면 각각의 컬럼에 대해서 구분자로 '^' 문자가 들어가 있음을 볼 수 있다.
 파일에 cvs 확장자를 붙여서 엑셀로 띄운 뒤 '^' 문자로 구분자를 변경하여 열면 한눈에 보기가 좋게 표시가 된다.

[Linux] 주기적으로 리눅스 디스크 용량을 로그파일로 기록(생성)하는 방법

Oracle이 설치된 Unix 서버에서 undo와 같이 트랜잭션이 일어날 수 있는 쿼리가 많이 수행이 되면 /archive 용량이 늘어나는데 해당 용량이 100%가 되어버리면 Oracle에 문제가 생긴다고 해서 /archive 의 용량이 1분마다 어떻게 변화하는지 파일로 로그로 기록을 했다.

#1분마다 해당 쉘을 수행

[id@SERVER_NAME diskStat]$ crontab -l
* * * * * /home/id/diskStat/archiveDiskLog.sh
[id@SERVER_NAME diskStat]$

#쉘이 수행된 시간을 포함하여 /archive 용량을 로그로 기록
[id@SERVER_NAME diskStat]$ cat archiveDiskLog.sh
#!/bin/sh
df -hP | grep archive | xargs echo -e "$(date +%m\.%d\ %H\:%M\:%S) - " >> /home/id/diskStat/archive.log
[id@SERVER_NAME diskStat]$

1004lucifer
위와같이 /archive 용량을 로그형식의 파일로 기록 후 언제 /archive 용량이 늘어났는지 분석하는데 조금은 도움이 되었다.

[Oracle] imp 수행 시 발생하는 'max # extents (32765) reached for rollback' 에러

환경: Oracle 11g Enterprise Edition Release 11.2.0.3.0


개발서버에서 imp 명령어를 이용해 데이터를 import 할 때 아래와 같은 오류가 발생했다.
1004lucifer

[id@SERVER_NAME tmp]$ imp {DB_ID}/\"{DB_PW}" tables={TABLE_NAME} file=/tmp/{DATA_FILE_NAME}.dmp ignore=y
Import: Release 11.2.0.3.0 - Production on Fri Jul 22 20:03:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing {DB_ID}'s objects into {DB_ID}
. importing {DB_ID}'s objects into {DB_ID}
. . importing table                "{TABLE_NAME}" 
date
top
IMP-00058: ORACLE error 1628 encountered
ORA-01628: max # extents (32765) reached for rollback {SEGMENT_NAME}
IMP-00027: failed to rollback partial import of previous table
IMP-00003: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
IMP-00000: Import terminated unsuccessfully
[id@SERVER_NAME tmp]$

원인을 찾아보니 해당 테이블에 인덱스가 3개가 잡혀있는데..
한번에 생성할 수 있는 인덱스 갯수를 초과해서 문제가 발생한 것으로 보인다.
(근거자료 링크는 잊어버림..ㅠ)
1004lucifer
암튼 인덱스 관련해서 수정해 주면 된다는데 그것도 관리자 권한이 있어야만 가능한 것 같아 결국 못함..


결국 import 할 데이터가 많아 발생한 에러이며, 데이터가 많지 않은 경우에는 에러가 발생하지 않았다.


PS.
개발서버에서는 Undo Tablespace 가 부족하여 위와같은 에러가 아닌 아래와 같은 에러가 발생했다.

[Oracle] imp 수행 시 발생하는 ORA-30036 에러

[Oracle] imp 수행 시 발생하는 ORA-30036 에러

환경: Oracle 11g Enterprise Edition Release 11.2.0.3.0


개발서버에서 imp 명령어를 이용해 데이터를 import 할 때 아래와 같은 오류가 발생했다.
1004lucifer

[id@SERVER_NAME ~]$ imp {DB_ID}/\"{DB_PW}" tables={TABLE_NAME} file=./{DATA_FILE_NAME}.dmp ignore=y
Import: Release 11.2.0.3.0 - Production on Fri Jul 22 17:47:11 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing {DB_ID}'s objects into {DB_ID}
. importing {DB_ID}'s objects into {DB_ID}
. . importing table                "{TABLE_NAME}"date
top
IMP-00058: ORACLE error 30036 encountered
ORA-30036: unable to extend segment by 8 in undo tablespace '{TABLE_SPACE_NAME}'
IMP-00028: partial import of previous table rolled back: 7963774 rows rolled back
Import terminated successfully with warnings.
[id@SERVER_NAME ~]$

1004lucifer
인터넷을 찾아보니 Undo Tablespace 가 부족해서 발생한다는 의견이 있고..
Undo Tablespace 를 늘리는 방법은 직접 해보지 않아서 어떤게 맞는지 잘 모르겠다.

결국 import 할 데이터가 많아 발생한 에러이며, 데이터가 많지 않은 경우에는 에러가 발생하지 않았다.

PS.
운영서버는 Undo Tablespace 가 부족하지 않았는지 위와같은 에러가 발생하지 않았고 아래와 같이 다른 에러가 발생을 했다.

[Oracle] imp 수행 시 발생하는 'max # extents (32765) reached for rollback' 에러