JayK

DataPump

● DataPump 특징

1. 수행 속도가 빠르다

- parallel 4 이상해야 수행 속도가 export보다 빠르다

2. 일시 중단 후 다시 이어서 작업을 할 수 있다

3. 작업 시간을 예상할 수 있다

4. OS 파일에 직접 접근할 수 없어 보안이 우수하다

 

● expdp 실행 모드

(1) Full 모드

- Full 이란 파라미터를 사용

- 데이터베이스 전체를 export 받을 수 있다

- DBA 권한을 가지고 있거나 export_full_database권한을 가지고 있어야 수행할 수 있다

(2) Schema 모드

- Schemas 파라미터를 사용

- 특정 스키마 전체를 export 받을 수 있다

(3) Tablespace 모드

- Tablespaces 파라미터를 사용

- 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있다

- 만약 transport_tablespace 파라미터를 사용한다면 테이블 스페이스 정보가 다르더라도 다른 DB로 동일하게 impdp할 수 있음

* block 크기가 맞아야함

- 단 transport_tablespace를 사용하려면 양쪽 데이터베이스의 OS, block size와 characterset이 같아야 하는 단점이 있다

- 별도 옵션 사용안할시 유저가 없으면 생성(테이블오너) 해줘야함

(4) Table 모드

- Tables 파라미터를 사용

- 여러 개의 테이블을 export 받으려면 콤마로 구분

 

● expdp 파라미터 정리

1) directory : 디렉토리 오브젝트를 지정해서 덤프파일 및 로그 파일의 위치를 지정

directory=directory_object_name형식으로 지정하여 사용

 

2) dumpfile : 파일 시스템에 저장될 덤프파일의 이름을 지정해주는 파라미터

Dumpfile=dumpfile_%U와 같은 형식

%U는 파일이 여러 개로 나누어질 때 사용

01-99까지 자동증가

사용자가 덤프파일을 지정하지 않으면 expdat.dmp라는 파일으로 자동으로 지정

 

3) logfile / nologfile : 작업내용을 저장할 로그파일명 지정

Logfile = logfile 형식으로 로그 파일명 지정

파라미터를 지정하지 않으면 expdp를 저장하는 곳에 export.log라는 파일명으로 생성

로그파일을 남기고 싶지 않으면 Nologfile=Y을 사용

 

4) compression : export 파일을 압축하여 저장

ex) expdp scott/tiger dumpfile = scott00.dmp directory = datapump compression=all

* 이름 겹칠 시 : 이름포맷 바꾸기, 기존파일 삭제, reuse_dumpfiles 옵션 사용, mv

5) network_link : 원격지에 있는 데이터베이스에 접근하여 데이터를 expdp 작업을 할 경우에 사용

DB Link 기능을 이용하여 작동

원격지 DB로 DB Link가 생성되어 있어야 가능

ex) $?~~scott10.dmp network_link=emp@db_link_name

 

6) parallel : parallel 작업 수행 시 프로세스 개수

ex) $~~scott10.dmp …….. parallel=4

 

7) reuse_dumpfiles : 저장하고자 하는 덤프파일명이 있을 경우 해당 파일을 덮어쓰도록 하는 옵션

reuse_dumpfiles=Y 설정 시 덮어쓰고 설정 안 하면 N가 기본

 

 

● impdp 관련 파라미터

1) Table_exists_action : 테이블이 이미 있을 경우 action

skip : 같은 테이블을 만나면 건너뜀

append : 기존 내용에 데이터를 추가

truncate : 기존 테이블을 truncate 하고 새로 impdp

drop : 기존 테이블을 drop 하고 새로 만들어서 impdp

2) Remap_schema : 다른 유저로 impdp 할 경우 사용

remap_schema=scott:hr

export/import에서 from scott to hr 와 같음

3) Remap_datafile : 기존 서버의 데이터파일 정보를 이전 후 서버의 데이터 파일로 매핑

ex) remap_datafile='/data1/users01.dbf':'/data2/users01.dbf','/data1/test.dbf':'/data2/test.dbf'

4) Remap_tablespace : 테이블 스페이스 정보가 다를경우 명시

ex) remap_tablespace='users':'example'

5) Remap_table : 기존 테이블 이름을 impdp 하면서 변경할 경우 사용

ex) remap_table=emp:emp100

6) Network_link : 원격지에 impdp

7) Partition_options : 파티션되어 있던 테이블을 단일 테이블로 impdp할 경우

ex) partition_options=merge

8) Data_options=skip_constrait_errors : 제약조건 위배 시 에러 무시하고 진행

 

------------------------------------------------------------------

--------------------------------------------------------------------------

--step1. 테스트용 계정과 테스트용 테이블을 생성

create user tuser identified by abc123

default tablespace users

temporary tablespace temp2;

 

select systimestamp from dual; --2015/12/16 22:44:52 +09:00

 

grant connect, resource to tuser;

 

create table tuser.test1 (no number);

create table tuser.test2 (no number);

 

insert into tuser.test1 values(1);

insert into tuser.test1 values(2);

insert into tuser.test2 values(3);

insert into tuser.test2 values(4);

 

commit;

 

select * from dba_temp_files;

 

--step2. 테스트용 계정 tuser를 삭제한 후 데이터를 확인

drop user tuser cascade;

 

select * from tuser.test2;

--ERROR at line 1:

--ORA-00942 : table or view does not exist

 

--step3. Flashback 명령어로 복구

flashback database to timestamp(to_timestamp('2015/12/16 22:45:00','YYYY/MM/DD HH24:MI:SS'));

--ERROR at line 1:

--ORA-38757 : Database must be mounted and not open to FLASHBACK

 

SYS> shutdown immediate

SYS> startup mount;

SYS> flashback database to timestamp(to_timestamp('2015/12/16 22:45:00','YYYY/MM/DD HH24:MI:SS'));

 

SYS> alter database open;

--alter database open

--ERROR at line 1:

--ORA-01589 : must use RESETLOGS or NORESETLOGS option for database open

 

SYS> alter database open resetlogs;

 

select * from tuser.test1;

 

-------------------------------------------------------------------------------

create public database LINK my_orcl

CONNECT TO system IDENTIFIED BY "imsi00"

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)

(HOST=172.16.1.52)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL2)))';

 

select * from dba_db_links;

 

select instance_name,host_name from v$instance@my_orcl;

 

--datapump 사전 준비

SQL > !mkdir -p /oracle11/datapump;

 

create or replace directory datapump as '/oracle11/datapump';

grant read,write on directory datapump to scott;

 

select * from dba_directories;

 

 

--empdp 수행(특정테이블)

SQL > !expdp system/imsi00 tables=flashback_test1 directory=datapump dumpfile=flashback_test1.dmp;

SQL > !expdp scott/imsi00 schemas=scott directory=datapump dumpfile=scott;

SQL > !expdp systemp/imsi00 full=y directory=datapump dumpfile=full01.dmp;

 

SQL > !expdp system/imsi00 full=y directory=datapump parallel=4 dumpfile=full1%U.dat logfile=full1.log

-- top -c

select * from v$parameter

where name like '%pool%';

 

alter system set shared_pool_size=50m;

alter system set sga_target=400m scope=spfile;

alter system set sga_max_size=512m scope=spfile;

 

 

test1) expdp 받은 dump파일을 이용한 impdp

-- step1) table 삭제

drop table flashback_test1;

select * from flashback_test1;

 

-- step2) impdp 수행

SQL > !impdp system/imsi00 directory=datapump dumpfile=flashback_test1.dmp nologfile=y;

 

select * from flashback_test1;

 

 

test2) db_link를 통한 impdp 수행

--step1) db링크 걸기 위에서했음

--step2) test table 생성(ORCL)

create table scott.impdp_test1(a number);

 

insert into scott.impdp_test1 values(1);

insert into scott.impdp_test1 values(2);

 

commit;

 

select * from scott.impdp_test1;

 

--step3) impdp 수행(TESTDB)

SQL > !impdp scott/imsi00 tables=scott.impdp_test1 network_link=my_orcl nologfile=y;

-- network_link에 설정된 DB에서 expdp 받고 local에 impdp 수행

-- network_link를 이용하면 굳이 export를 수행할 필요없이 import가능

 

◆ impdp 관련 파라미터

1) Table_exists_action : 테이블이 이미 있을 경우 action

skip : 같은 테이블을 만나면 건너뜀

append : 기존 내용에 데이터를 추가

truncate : 기존 테이블을 truncate 하고 새로 impdp

drop : 기존 테이블을 drop 하고 새로 만들어서 impdp

 

2) Remap_schema : 다른 유저로 impdp 할 경우 사용

remap_schema=scott:hr

export/import에서 from scott to hr 와 같음

 

3) Remap_datafile : 기존 서버의 데이터파일 정보를 이전 후 서버의 데이터 파일로 매핑

   ex) remap_datafile='/data1/users01.dbf':'/data2/users01.dbf','/data1/test.dbf':'/data2/test.dbf'

 

4) Remap_tablespace : 테이블 스페이스 정보가 다를경우 명시

   ex) remap_tablespace='users':'example'

 

5) Remap_table : 기존 테이블 이름을 impdp 하면서 변경할 경우 사용

   ex) remap_table=emp:emp100

 

6) Network_link : 원격지에 impdp

 

7) Partition_options : 파티션되어 있던 테이블을 단일 테이블로 impdp할 경우

   ex) partition_options=merge

 

8) Data_options=skip_constrait_errors : 제약조건 위배 시 에러 무시하고 진행

 

Flashback

1. 논리적 오류를 복구 할 수 있다. (물리적 오류는 복구 불가)

2. Row Level / Table Level / Database Level 로 나뉜다.

3. 별도의 백업 파일 복원이 없기 때문에 전통적 복구에 비해 아주 빠르다.

4. 휴지통(recyclebin) / Undo data / Flashback log 등의 기술을 사용한다.

5. restore 없이 복구하므로 빠름. (물리적인 오류 복구는 restore[dbf+archive file])

 

◆ Row Level Flashback

- Flashback Query : 특정 시점의 변경 내역만 알 수 있는 쿼리

- Flashback Version Query : 해당 데이터의 과거 변경이력을 전부 찾아주는 쿼리

- Flashback Transaction Query : 변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리

 반드시 supplemental_logging을 min level 이상으로 설정해야 함

 supplemental_logging을 min level 이상으로 설정하면 Undo와 Redo log, archive log 사용량 증가(상세히 기록)

● Row Level Flashback

- 특정 테이블의 특정 Row만 Flashback 해주는 기능

- commit된 데이터만 복구 가능

- Undo data의 정보를 이용하여 복구

- Undo segment가 재사용된 경우 ORA-01555 snapshot too old 에러 발생

- 한 두건은 Row level을 쓰지만 많은 데이터 복구는 테이블 단위로 함

 

----------------------------------row level-------------------------------------

-- 실습 test table 및 data 만들기

create table flashback_test1

( name varchar2(10),

addr varchar2(10),

tel varchar2(10)

);

 

insert into flashback_test1 values('홍은혜','용산','111');

insert into flashback_test1 values('김예진','제천','222');

insert into flashback_test1 values('양순정','부산','333');

 

commit;

 

select * from flashback_test1;

 

update flashback_test1 set name = '홍길동' where tel = '111';

commit;

update flashback_test1 set name = '김범수' where tel = '333';

commit;

 

select * from flashback_test1;

 

-- 변경 이력을 찾는 Flashback Version Query 수행

select versions_startscn st_scn,

versions_endscn endscn,

versions_xid txid,

versions_operation opt,

name

from flashback_test1 versions between scn minvalue and maxvalue

where tel = '111';

 

 

 

 

 

-- 동시에 여러 번의 수정이 일어났을 경우 변경 history 찾는 방법

update flashback_test1 set name = '전준민' where tel = '111';

commit;

 

update flashback_test1 set name = '홍민택' where tel = '111';

commit;

 

-- 변경 이력을 찾는 Flashback Version Query 수행

select versions_startscn st_scn,

versions_endscn endscn,

versions_xid txid,

versions_operation opt,

name

from flashback_test1 versions between scn minvalue and maxvalue

where tel = '111';

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

-- 변경된 DATA별로 변경 시점 조회

select scn_to_timestamp(4260670) from dual;

 

-- 변경 전 data로 직접 UPDATE(row level flashback)

update flashback_test1 set name = '홍길동' where tel = '111';

commit;

 

-- 변경 이력을 찾아 복구하는 Flashback Transaction Query 수행

select undo_sql

from flashback_transaction_query

where table_name = 'FLASHBACK_TEST1'

and commit_scn between 4260443 and 4268760

order by start_timestamp desc;

=================> supplemental_logging min을 안해서 안나옴

-- min level supplemental logging 조회 및 변경

select supplemental_log_data_min from v$database;

alter database add supplemental log data; --> MIN을 YES로 변경

select supplemental_log_data_min from v$database;

-- 데이터 변경

update flashback_test1 set name = '조우성' where tel = '111';

commit;

update flashback_test1 set name = '조규현' where tel = '111';

commit;

update flashback_test1 set name = '김일주' where tel = '111';

commit;

-- 변경 이력을 찾아 복구하는 Flashback Transaction Query 수행

select undo_sql

from flashback_transaction_query

where table_name = 'FLASHBACK_TEST1'

and commit_scn between 4268746 and 4278716

order by start_timestamp desc;

=================> logging 이후 변경된 데이터만 조회됨 (dml 쿼리)

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '조우성' where ROWID = 'AAASTtAABAAAeI5AAA';

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '조규현' where ROWID = 'AAASTtAABAAAeI5AAA';

update "SYSTEM"."FLASHBACK_TEST1" set "NAME" = '홍민택' where ROWID = 'AAASTtAABAAAeI5AAA';

◆ Table Level Flashback

- 특정 테이블 전체 내용을 장애 발생 전 상태로 flashback하는 방법

- Row level과는 달리 테이블 전체의 내용이 변경된다.

- 복구 방법

 DML 에러에 대한 복구(undo data 이용)

 특정 테이블이 Drop 되었을 때 사용하는 방법 (recyclebin 이용)

 

-----------------------★Table Level Flashback★------------------

select * from flashback_test1;

 

select systimestamp from dual; -- 2015/12/15 22:40:10 +09:00

 

delete from flashback_test1;

select * from flashback_test1;

 

commit;

 

-- 복구 방법 1 ( 확인하고 select insert 하거나 임시 테이블 만들어서 제공 )

create table flashback_test1_imsi

as

select *

from flashback_test1

as of timestamp to_timestamp('20151215 2240','yyyymmdd hh24mi');

 

-- 복구 방법 2 --기존데이터가 뭐든 간에 바로 복구(=데이터유실)(그래서 잘 안씀)

1) flashback table flashback_test1

to timestamp(systimestamp - interval '30' minute);

--ERROR at line 1:

--ORA-08189 : cannot flashback the table because row movement is not enabled

--flashback table 사용 시 row movement enable설정해야 가능

--바로 복구를 원할 경우, 시간이 명확하고 중간 트랜잭션이 없을 경우 더욱 빠름

 

alter table flashback_test1 enable row movement;

 

2) flashback table flashback_test1

to timestamp(to_timestamp('20151215 2240','yyyymmdd hh24mi'));

 

select * from flashback_test1;

 

--시간을 너무 이전 시점으로 설정할 경우 에러 발생

flashback table flashback_test1

to timestamp(systimestamp - interval '0' second);

--ERROR at line 1:

--ORA-01466: unable to read data - table definition has changed

 

 

-------------drop 테이블 recyclebin에서 복구(purge로 날리면 recyclebin으로 저장 안되므로 복구 불가)------------------------

--삭제하고 누군가 동일 테이블을 만들어 동일 테이블이 있을 때 복구가 안됨

select * from scott.emp;

drop table scott.emp;

 

select *

from dba_recyclebin

where original_name like 'EMP%';

flashback table scott."BIN$JvGK2YFr+9LgUAB/AQAMsg==$0" to before drop;

--drop된 테이블이 여러 개 일수도 있어서 original_name사용안함 scott.emp(object_name 사용 권고)

 

select * from scott.emp;

 

 

--동일한 이름의 table이 있는 경우

create table scott.dept2

as select * from scott.dept

where 1=2;

 

drop table scott.DEPT;

 

alter table scott.dept2 rename to dept;

 

select * from scott.dept;

 

select * from dba_recyclebin

where original_name like 'DEPT%';

 

--이미 동일 테이블명이 존재해서 ORA-38312 : original name is used by an existing object

flashback table scott."BIN$JvzOjsrODFjgUAB/AQAP2Q==$0"

to before drop;

 

 

 

--rename 옵션 이용하여 복구

flashback table SCOTT."BIN$JvzOjsrODFjgUAB/AQAP2Q==$0"

to before drop rename to dept_imsi;

 

select * from scott.dept_imsi;

 

insert into scott.dept

select * from scott.dept_imsi;

 

commit;

 

select * from dba_tables

where table_name like '%DEPT%';

◆ Database Level Flashback

- 전통적인 복구 방식 : 장애 발생 시 백업된 데이터 파일을 복원해서 Redo log와 Archive log를 적용시켜 복구

- Flashback database 복구 방식 : 장애가 발생한 데이터 파일에 Flashback log로 복구

- 물리적인 백업 파일을 복원할 필요가 없으므로 빠른 복구 가능

- Redo log와 Archive log를 사용하기도 함

 

●Flashback database 복구 방식

 

 

●Database Level Flashback 사용 준비

- db_recovery_file_dest 설정 필요

: flashback log 쌓이는 장소

- db_recovery_file_dest_size 설정 필요

: flashback log 쌓이는 최대 size(default : 4G)

* 해당 공간이 full이면 DB Hang

- db_flashback_retention_target 분 단위 설정 필요(default : 1440분/24시간)

* 2일 전으로는 못 돌림, size도 키워야 함

- archive log mode

- flashback database mode

 

----------------------------------------

-- 아카이브 모드 확인

SQL> archive log list;

 

-- flashback 모드 확인

select flashback_on from v$database;

 

-- 관련 파라미터 확인

select *

from v$parameter

where name in ('db_recovery_file_dest', -- flashback log 위치

'db_recovery_file_dest_size', -- flashback log 위치 크기

'db_flashback_retention_target', -- 분(설정시간까지만)

'log_archive_dest_1' ,-- 아카이브 로그 위치

'log_archive_format');

-- 아카이브 모드 변경

SQL> startup mount;

SQL> alter database archivelog; <-> alter database noarchivelog;

SQL> archive log list;

SQL> alter database flashback on; <-> alter database flashback off;

SQL> alter database open;

 

-- flashback log 확인

SQL> alter system switch logfile; -- 물리적으로 로그 발생 확인

 

select * from v$flash_recovery_area_usage;

 

-- 아카이브 위치 변경

alter system set log_archive_dest_1='location=/oracle11/arch';

--flashback log와 archive log 위치를 분리 --> full 차는 거 예방

--------------------------------------recyclebin---------------------------

purge recyclebin; -- 리사이클빈 지우기

purge dba_recyclebin; -- sys 계정에서 수행

 

select * from recyclebin;

select * from dba_recyclebin;

 

drop table PCT_TEST2; -- scott 계정 수행

drop table job_test02; -- system 계정 수행

--********system tablespace의 테이블을 drop 하면 recyclebin에 들어가지 않음*******

 

purge table scott."";

purge table scott.SAL_HISTORY;

 

--recyclebin 끄기

alter session set recyclebin=off; -- 세션 별

 

create table honghong3

(a number)

tablespace users;

 

drop table honghong3;

 

select * from dba_recyclebin;

 

--system tablespace의 table은 drop해도 recyclebin에 가지 않음

--------------------------------------------------------------------------

select * from flashback_test1;

 

select systimestamp from dual;--2015/12/16 21:22:28 +09:00

 

truncate table flashback_test1;

 

--set1)

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

flashaback table FLASHBACK_TEST1

to timestamp('2015/12/16 21:21:00');

 

--set2)

flashback table flashback_test1

to timestamp(to_timestamp('2015/12/16 21:21:00','YYYY/MM/DD HH24:MI:SS'));

--ORA-01466 : unable to read data - table definition has changed

--DDL발생(truncate) 구조적 변경 됐으므로 위에 에러 발생

 

select * from scott.flashback_test1;

 

--flashback database 복구

--mount 단계에서 수행, DBA권한 필요

SQL > shutdown immediate;

SQL > startup mount;

 

SQL> flashback database to timestamp (to_timestamp('2015/12/16 21:21:00','YYYY/MM/DD HH24:MI:SS'));

 

SQL > alter database open;

--ERROR at line 1:

--ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--과거로 돌렸기 때문에 SCN이 맞지 않아서 에러가 남

SQL > alter database open resetlogs; --> scn 정보를 최상으로 맞춤

 

select * from flashback_test1;

 

DBMS_JOB

●DBMS_JOB 패키지

- SNP 백그라운드 프로세스에 의해 등록된 JOB이 설정한 INTERVAL 간격으로 수행됨

- 초 단위 수행가능이 장점 but, 초단위 작업이 별로 없어서 crontab 이용

- job_queue_processes와 job_queue_interval 이 설정되어 있어야 동작 가능

 job_queue_processes : SNP 프로세스 개수

 _job_queue_interval : SNP 프로세스가 sleep 상태에서 깨어나는 간격을 초로 지정

 

●DBMS_JOB 패키지 구성

- submit : 새로운 작업을 job queue 목록에 등록

- remove : job queue에 등록된 job 제거

- change : job queue에 등록된 job 변경

- next_date : job queue에 등록된 job의 작동 시간 변경

- interval : job queue에 등록된 job의 수행 주기 변경

- what : 수행할 procedure or package 변경

- run : 등록되어 있는 특정 job을 수동으로 수행

 

●job 관리하기

- 기본 문법

DBMS_JOB.submit( job out binary_interger, what in varchar2, next_date in date default sysdate, interval in varchar2 default 'null', no_parse in boolean default false )

- job : job 번호로 다른 프로시저에서 호출될 수도 있다

- what : 수행할 pl/sql or procedure or package 이름을 지정

이곳에 직접 수행하기를 원하는 sql문장을 써도됨

- next_date : 다음에 수행될 시간을 지정

- interval : 수행되는 주기를 지정하며 초 단위까지 지정 가능

- no_parse : Parse 여부를 지정. (기본 값 false로 parse 수행)

 

- -job 프로그램 등록

create sequence seq_job_seq1; 

create table job_test01 (no number, name varchar2(5));

 

create or replace procedure insert_job_test01

is begin

insert

into job_test01

values(seq_job_seq1.nextval,

dbms_random.string('a', 3));

end;

/

 

-- job 프로그램 등록

!vi job1.sql

 

begin dbms_job.submit(:jno,

'insert_job_test01;',

sysdate,

'sysdate + 1/24/60', -- 1분에 한 번 수행

false);

end;

/

 

SQL > variable jno number;

SQL > @job1.sql

SQL > print jno; -- random으로 할당되는 job번호 확인

SQL > commit;

-- 이 시간부터 job이 수행되며 commit 되지 않으면 수행되지 않음

 

 

-- rac 환경이라면 아래와 같이 수행

exec dbms_job.instance(job=>25, instance=>1);

 

-- 현재 수행되고 있는 job 확인

select *

from dba_jobs

where 1=1

and what='insert_job_test01;' -- 이름에 ; 들어간 거 주의!

-- and job=3

;

 

-- 테이블 확인

select * from job_test01;

 

-- job 수정

exec dbms_job.change(

3,

'insert_job_test01;',

sysdate,

'sysdate + 5/24/60'

);

commit;

 

-- job 삭제

exec dbms_job.remove(3);

commit;

 

 DBMS_SCHEDULER

- 오라클에서 생성된 프로시저나 함수 외에 OS에서 생성된 각종 프로그램 실행 가능

- 데이터베이스 내부 이벤트까지 추적가능하기 때문에 OS나 dbms_job 보다 훨씬 다양한 작업을 체크하고 수행할 수 있음

 

●DBMS_SCHEDULER 등록

양식

begin

dbms_scheduler.create_job(

job_name=>'',

job_type=>'',

job_action=>'',

start_date => ,

repeat_interval =>'');

end;

/

 

 

* 사용편이성 때문에 UNIX Crontab 많이 이용함!