[오라클]효율적인 백업 및 복구

효율적인 백업 및 복구

http://dinggur.tistory.com/category/Study

Export / Import
1. 특정테이블만 백업받고 싶다.
2. 특정 테이블만 옮기고 싶을때.
3. 구DB(윈도8i) → 신규DB(리눅스11g) 로 DATA 옮기고 싶을때

Migration(마이그레이션) : 데이터 옮기기
방법 1 : exp/imp
방법 2 : data pump


※ 원리는 select와 같다 = DB open상태에서 실행한다.


Export 원리
: 서버에 있는 데이터를 빼올때.
: 시작시점의 데이터만 들어간다.
: sys계정은 exp 안된다 → system 사용자
temporary tablespace 공간 사용(관리법 잘알기)

① Conventional Path export
- 여러사람이 같이 쓸때
- evaluation buffer 사용

② Direct Path export
- 혼자 쓸때
- 단점 : 원본 테이블에 여러 process 가 동시에 접근을 해서 사용할 경우 속도 저하가 심해진다.


▶ DB는 여러 사람이 동시에 사용하는 것이기 때문에, 기본적으로 conventional path 모드로 작동하게 되어있다.
conventional path 모드의 기본적인 속도는 과정이 다소 복잡하기 때문에 direct path에 비해 속도가 저하된다.

EXP 단점 : 속도가 느리다.
※ 주의 : 편집하면 파일깨짐. EXP 처음부터 다시해야함.


Export 옵션

옵 션 명	Default value	의 미
userid	없음	export 를 수행하는 사용자의 계정과 암호
buffer	os에 따라 다름	evaluation buffer 크기를 바이트 단위로 지정
file	expdat.dmp	export 결과르 저장할 파일명
grants	yes	해당 스키마에 설정된 권한을 export 받을 것인가 유무
indexes	yes	인덱스를 export 받을 것인가 유무
rows	yes	데이터를 받을 것인가 유무
constraints	yes	제약 조건을 받을 것인가 유무
compress	yes	export 받을 때 데이터를 하나의 셋으로 압축 할지 유무
full	no	전체 데이터베이스를 export 받을 것인가 유무
owner	current user	export 받을 사용자 이름을 지정
tables	없음	export 받을 테이블 이름 지정
tablespaces	없음	export 받을 테이블 스페이스 이름 지정
recordlength	os 에 따라 다름	파일레코드의 바이트 단위 길이. evaluation buffer에서 데이터 파일로 저장할 때 운반바이트 단위 지정
inctype	없음	증분 export 의 유형설정, complete, cumulative, incremental
record	yes	증분 export 내용을 기록할지 지정, .sys.incvid, sys.incexp
parfile	없음	export 파라미터 파일을 지정

 

=칠판실습=

$ exp scott/tiger tables=emp file=emp.dmp

$ vi emp.dmp
바이너리파일
IMP에게 수행할 명령 쭉~ 적혀있음..
(수행할 SQL명령어들이 순서대로 들어있다)
- 내부순서 : create table → insert → create index → add constraints

※ EXP/IMP 계정이 동일해야 한다 : IMP때 아이디/암호는 EXP할때 아이디/암호 와같아야 한다.
- 예외 : system 계정으로 작업시 상관없다.

ex) 다른사람이 받아놓은 exp를 imp받아야 하는 경우. (계정을 모를때)
→ VI로 dmp파일 열어보면 2째줄 U뒤에 아이디 적혀있음

 

Export
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 시작
exp 실습 1: conventional Path로 full export 받기

$ time exp system/oracle full=y log=full_log.log file=/home/oracle/full_test01.dmp


exp 실습 2: direct Path로 full export 받기

$ time exp system/oracle full=y log=full_log.log file=/home/oracle/full_test01.dmp direct=y


exp 실습 3: exp 를 저장하는 백업파일 분할해서 받기

$ time exp system/oracle full=y \
file=(/data/backup/export/test03_1.dmp,\
/data/backup/export/test03_2.dmp,\
/data/backup/export/test03_3.dmp,\
/data/backup/export/test03_4.dmp)\
filesize=20M direct=y


exp 실습 4: 특정 테이블스페이스만 받기

$ exp system/oracle file=/data/backup/export/ex_un.dmp \
tablespaces=(example, undotbs1)


exp 실습 5: 여러 사용자를 동시에 백업 받기

$ exp system/oracle file=/data/backup/export/scott_hr.dmp \
owner=(scott,hr)


exp 실습 6: evaluation Buffer 값을 조정하면서 export 수행

-----------
실습용 테이블 생성
SQL> create table scott.test01
2 (no number, name varchar2(20), address varchar2(50)) tablespace example;

테이블 스페이스 용량 충분히 늘려주고 대량의 데이터 입력시키기
SQL> begin
2 for i in 1..1000000 loop
3 insert into scott.test01
4 values (i, dbms_random.string('A',19),
5 dbms_random.string('Q',40) );
6 end loop;
7 commit;
8 end;
9 /

생성후 확인
SQL> select sum(bytes)/1024/1024 MB
2 from dba_segments
3 where owner='SCOTT'
4 and segment_name='TEST01';
--------------

① evaluation Buffer 값을 설정하지 않고 export 수행

$ time exp scott/tiger file=/data/backup/export/test02.dmp tables=test01

real 0m12.340s


② evaluation Buffer 값을 1M 로 설정후 export 수행

$ time exp scott/tiger file=/data/backup/export/test03.dmp buffer=1024000 tables=test01

real 0m6.156s


③ evaluation Buffer 값을 10M 로 설정후 export 수행

$ time exp scott/tiger file=/data/backup/export/test04.dmp buffer=10240000 tables=test01

real 0m2.919s


④ evaluation Buffer 값을 20M 로 설정후 export 수행

$ time exp scott/tiger file=/data/backup/export/test05.dmp buffer=20480000 tables=test01

real 0m3.267s


⑤ direct Path 로 export 수행

$ time exp scott/tiger file=/data/backup/export/test06.dmp direct=y tables=test01

real 0m7.150s

※ 정리 : evaluation Buffer의 크기 / temporary tablespc의 크기 = exp/imp 속도에 영향을 준다.

exp 실습 7: 일반사용자(여기서는 scott)로 full export 수행

SYS> select * from dba_role_privs where grantee='SCOTT';
SYS> grant exp_full_database to scott;
설명 : 원래 일반 사용자는 DB전체백업을 받지 못한다. 그래서 DBA권한을 주거나 exp_full_database 라는 role을 주던지 해야 함.

$ time exp scott/tiger full=y file=/data/backup/export/test7.dmp buffer=10240000



exp 실습 8: parameter file 을 이용한 export 수행

$ vi par_full.dat
file=/data/backup/export/test09.dmp
full=y
direct=y
:wq!

$ exp system/oracle parfile=par_full.dat



exp 실습 9: 특정 조건만 export 받기 - query 옵션 사용(8i부터 가능)
※ OS에서 사용하는 특수문자 ' " < > 등을 쿼리로 사용할 경우 앞에 \(escape문자)를 붙여서 구분해 주어야 한다.

- emp 테이블에서 이름 첫 글자가 F인 사람만 export 받기
$ exp scott/tiger query=\"where ename like \'F%\'\" tables=emp file=/data/backup/export/emp.dmp

- emp 테이블에서 job이 CLERK 이고 급여가 1000인 사람만 export 받기
$ exp scott/tiger query=\"where job=\'CLERK\' and sal \> 1000 \" file=/data/backup/export/scott2.dmp tables=emp

- parameter file 에서 query 옵션 사용하기 - escape 문자(\) 안써도 됨
$ vi par_q.dat
tables=emp query="where job='CLERK' and sal>1000"
:wq!

$ exp scott/tiger parfile=par_q.dat



exp 실습 10: schema 별로 자동 export 백업 받는 스크립트

$ vi exp_script.sh
export LANG=C
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb
sqlplus /nolog << EOF3
conn / as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /home/oracle/exp.tmp
select 'mkdir -p /data/backup/exp/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') from dual;
select distinct 'exp system/oracle'||' owner='||lower(owner)||' file=/data/backup/exp/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS')||'/'||lower(owner)||'.dmp'||' filesize=100m direct=y' from dba_tables where owner not in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB','ORDSYS',
'OUTLN','TSMSYS','DMSYS');
spool off

!cat /home/oracle/exp.tmp | egrep -v SQL | egrep -v SYS > /home/oracle/exp.sh
!sh /home/oracle/exp.sh
exit

EOF3




=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 끝

Import
: export 를 수행해서 만든 파일을 다시 데이터베이스로 넣는 작업을 수행
: 대량의 DDL과 DML을 수행하는 것이므로, redo log와 undo segment를 사용 → 대량의 데이터 import시 큰 undo tablespace를 준비
만약 용량이 부족할 경우 마지막에 에러 나면서 전부 rollback 될수도 있다.
이런 위험 줄이려면 import할때 commit=y로 변경하면 된다. 기본값은 테이블전체가 입력완료된후 커밋되는데
이 옵션을 사용하면 array단위로 커밋수행.
: 만약 DBA로(system) export 받은 파일의 경우, DBA로(system) import 해야 한다.
일반 사용자(Ex. scott)로 import 시도하면 에러발생.


IMP 특징
- imp할때마다 자료는 추가됨.
- 중간에 에러나서 자료 입력이 다 안되었을때, 다시 imp하면 에러전까지 입력된 자료 남겨두고, 새로 또 입력됨.
에러나서 다시 imp해야 할 경우 에러 전까지 입력된 데이터 truncate나 drop으로 날리고 다시작업해야 데이터 중복없음.

Import 주요옵션

ignore : Import 도중 에러나도 무시하고 계속 진행함.
fromuser : export 할 당시 원래 주인 user
touser : import 할때 새로 지정해줄 주인 user

 

옵 션 명	Default value	설 명
userid	없음	import 를 수행하는 username / password
buffer	os 에 따라 다름	evaluation buffe 의 크기, export와 동일함
file	expdat.dmp	import 할 export 파일 명
show	no	데이터를 import 하지 않고 내용만 확인함
ignore	yes	import 도중 에러나도 무시하고 계속 진행함
grants	yes	권한도 import 할 것인지 설정
rows	yes	데이터를 import 할 것인지 설정
indexes	yes	index를 import 할 것인지 설정
full	no	전체 파일을 import 할 것인지 설정
fromuser	없음	export 할 당시 오브젝트의 소유자를 지정함
touser	없음	import 할 오브젝트의 새 owner 이름
tables	없음	import 할 테이블 목록
recordhength	os 별로 다름	한번에 import 할 record의 길이를 지정
inctype	없음	증분 import의 유형 지정, system 과 restore 가 적당함
commit	no	각 array의 입력 후 commit 할것인가 결정, 디폴트는 array가 아니라 테이블 전체가 입력 완료 된 후 커밋을 함. Array는 한번 입력되는 단위를 의미함
parfile	없음	import 의 파라미터를 적어둔 파라미터 파일을 지정함

Import
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 시작
사전작업
$ exp system/oracle file=/data/backup/export/full01.dmp full=y dirct=y



imp 실습 1: DBA로 전체 데이터 import 수행
: DB전체를 이동할 때 사용. A서버의 데이터를 B서버로 import로 이동시킬때 B서버에 같은 테이블이나 데이터가 존재한다면 추가를 하게 된다.
그래서 만약 B서버에 제약조건이나 index가 존재한다면(unique index 나 primary key 등이 존재할 경우) 데이터가 추가되지 않고 에러가 발생한다.

$ imp system/oracle file=/data/backup/export/full01.dmp ignore=y full=y



imp 실습 2: 특정 사용자의 데이터만 import 수행
: 전체 export받은 full01.dmp 파일에서 scott 사용자의 test01테이블만 import하기

$ imp system/oracle file=/data/backup/export/full01.dmp fromuser=scott tables=test01 ignore=y




imp 실습 3: scott 사용자의 test02 테이블을 hr 사용자 소유로 변경하기
--------------------------------
테스트 테이블 생성

SCOTT> create table test02 (no number,addr varchar2(10));

SCOTT> begin
2 for i in 1..1000 loop
3 insert into test02 values (i, dbms_random.string('A',10));
4 end loop;
5 commit;
6 end;
7 /

SCOTT> select count(*) from test02;
COUNT(*)
--------
1000

$ exp scott/tiger file=/data/backup/export/test02.dmp tables=test02
--------------------------------

$ imp system/oracle file=/data/backup/export/test02.dmp fromuser=scott touser=hr ignore=y



imp 실습 4: 실제 데이터는 import 하지 않고 DDL 문장만 추출하기

$ imp system/oracle file=/data/backup/export/test02.dmp full=y show=y log=test02.log
$ vi test02.log



imp 실습 5: import 할 때 테이블과 index를 분리해내기

테이블과 인덱스는 다른 테이블스페이스에 저장해야 성능이 좋아진다.
exp/imp할때 같은 테이블스페이스에 있던 테이블과 인덱스를 다른 테이블 스페이스로 분리하려고 할때.

① 인덱스 스크립트가 있다면.
import 할때 indexes=n 옵션주고 import 한후 나중에 인덱스 스크립트에서 테이블스페이스만 변경하고 실행

② 인덱스 스크립트가 없다면.
import 할때 indexfile=파일이름 옵션주고 새로 생성되는 스크립트 수정(테이블스페이스)해서 실행

SCOTT> create index idx_test02_addr on test02(addr); ← 테이블스페이스 따로 지정 안해줘서 테이블과 같은 장소에 생성됨

$ exp scott/tiger file=/data/backup/export/test02_index.dmp tables=test02

$ imp system/oracle file=/data/backup/export/test02_index.dmp indexfile=test02_index.sql full=y

$ vi test02_index.sql
열어서 tablespace 부분 수정해주고 스크립트 실행


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 끝

※ 추가 사항

1. export 시
: buffer = evaluation buffer 크기 결정하는 파라미터
: recordlength = bufer의 내용을 OS파일로 내려쓸 때 사용하는 레코드의 크기를 결정하는 파라미터
→ 두 파라미터의 크기는 OS 블록의배수로 설정하기
: Array fetch → DB Buffer cache의 내용을 evaluation buffer 로 가져올때 한건씩 가져오는 것이 아니라 여러건의 데이터를 한꺼번에 가지고 옴

2. feedback=정수값
: 큰 데이터 exp/imp 할때 진행과정 보여주는 옵션

3. import 시 core dump/segmentation fault 에러 발생
: export 받은 DB와 import 하는 DB의 케릭터 셋이 다를 경우 발생
: 케릭터 셋을 일치시킨후 export 받는것이 정석
→ 이미 받아버린 덤프파일이라면 convert 프로그램을 이용하여 import 하는 곳의 케릭터 셋과 변경후 import 수행

※ 현재 캐릭터 셋 확인 SQL
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';


4. Array Fetch : export시 DB Buffer cache의 내용을 한문장씩 evaluation buffer 로 가져오면 I/O 하는 회수가 많아져 느려진다.
evaluation buffer에 일정량의 데이터가 쌓이게 되면 한꺼번에 파일에 내려써 I/O 하는 회수를 줄여 속도를 높이는 방법.
Array insert : import시 1row씩 insert 하게되면 비효율적. 여러 건의 데이터를 한꺼번에 insert 하여 효율을 높인다.


5. import 시 "ABNORMAL END OF FILE" 에러 : export 받은 파일에 문제가있다. 다시 export 받아야 함.

6. sys 계정은 dictionary 객체들을 소유한다. 새로운 데이터베이스는 이미 고유의 dictionary를 가지고 있다.
sys 계정의 객체를 export/import 하는것은 아주 큰 위험 부담이 있고 부하도 많이 걸리는 작업이라
▶ sys 계정은 export 를 수행할 수 없도록 되어 있다.

7. A소유의 테이블에 B가 인덱스 만들었다면, DBA권한으로 exp받을때 B의 인덱스도 함께 export 받을 수 있다.

8. Exp는 select와 동일한 원리 이므로 offline 이나 shutdown 상태에서는 사용할 수 없다.

9. exp시 tablespaces 옵션을 주었다 하더라도,
imp시 tablespace를 생성하고 table 을 만드는 것이 아니다.
imp 하길 원하는 서버에 tablespace 를 먼저 만들어 두고 imp를 해야 한다.

10. DB1 - scott 의 디폴트테이블스페이스 USERS
DB2 - scott 의 디폴트테이블스페이스 EXAMPLE. USERS 존재.
인상황에서 DB1 exp 받아서 DB2 imp 한다면 해당테이블은 어느 테이블 스페이스로 들어갈까?
▶ DB1에서 지정되어있던 USERS 테이블스페이스로 들어간다.

11. DB1 - scott 의 디폴트테이블스페이스 TS_B
DB2 - scott 의 디폴트테이블스페이스 EXAMPLE. TS_B는 존재하지 않음.
인상황에서 DB1 exp 받아서 DB2 imp 한다면 해당테이블은 어느 테이블 스페이스로 들어갈까?
▶ imp는 정상적으로 된다. 하지만 테이블스페이스는 DB2의 디폴트테이블스페이스인 EXAMPLE에 들어간다.

 

마이그레이션 기본방법
AS-IS서버(현재서버)와 TO-BE서버(이전할서버)의
① 사용자 계정과 각 사용자의 default tablespace를 동일하게 설정
② Privilege와 role을 동일하게 설정
③ schema 별로 exp 를 수행해서 imp를 수행
→ AS-IS 서버에 schema가 많을 경우 수동으로 하나하나 조사해서 받기 어렵기 때문에,
위의 실습 10번 스크립트를 활용하여 schema 별로 자동으로 export 받을 수 있는 스크립트 활용

 

CLOB의 경우

char → varchar2 → CLOB
2000 4000 2기가이상데이터

LOB : 하나의 컬럼에 크기가 큰 데이터
L arge
OB ject

 

CLOB : 글자로 구성
Character
L arge
OB ject

BLOB
Binary
L arge
OB ject



ex)
대본 tabale → users 테이블스페이스에 존재
--------------------
code 드라마명 대본
--------------------
....

대본 컬럼이 너무 커서 CLOB의 경우 컬럼하나만 별도의 테이블스페이스 지정해준다.
▶ export 후 import 할경우 해당 테이블스페이스가 없으면 에러남 → 미리 생성해주기


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 시작
1. CLOB 컬럼을 포함한 테스트 테이블 생성
SQL> create tablespace ts_lob
2 datafile '~/ts_lob01.dbf' size 1M

SQL> create table scott.clobtest
2 (no number, name varchar2(10), contents clob)
3 lob(contents) store as (tablespace ts_log);

SQL> insert into scott.clobtest
2 values (1,'AAA','BBBBB');


2. 현재 LOB 목록 확인
col owner for a10
col table_name for a10
col column_name for a10
col segment_name for a30
col tablespace_name for a10
SQL> select owner, table_name, column_name, segment_name, tablespace_name
2 from dba_lobs
3 where table_name='CLOBTEST'


3. Export
$ exp scott/tiger table=cobtest file=clobtest.dmp

4. Import
해당 테이블스페이스 없으면 에러발생함
해당 테이블스페이스 생성후 import 해주기

SQL> create tablespace ts_lob
2 datafile '~/ts_lob01.dbf' size 1M

$ imp scott/tiger file=clobtest.dmp fromuser=scott ignore=y

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습 끝