아무거나 시도해보는 개발일지

리오그에 대해 설명하기전에, 필요성에 대해 언급하고 넘어가자.

특정 Tablespace가 Full이 차서, 테이블 데이터 일부를 지웠다고 가정해보자.

과연 아래경우 TBS 용량은 얼마가 될까?

1) 60%

2) 100%

※ HWM ( High Water Mark ) : 지금까지 점유되었던 공간의 최고점

1) 60%

2) 100%

100% 이다. 기존의 할당된 영역은 그대로고, 사이에 데이터만 지워졌기 때문이다.

이런 경우 실제 데이터 양과는 상관없이,

TBS와 HWM이 동일하기 때문에 아래 문제가 예상된다.

Truncate 하면 ( 테이블 구조만 남기고, 데이터는 모두 삭제 )

TBS 확보 및 HWM 위치를 조절할 수 있으나

일부 데이터만 삭제하려는 위의 케이스에는 맞지않다.

해당 케이스에는 비워진 공간만큼 재구성하는 리오그 ( Reorganization ) 작업이 필요한 것이다.

Reorg 방법

1) ALTER TABLE MOVE

- 속도가 빠르다

- 데이터 용량만큼의 TBS 공간 추가로 필요

- TBS 오프라인 필요

1) 임시 테이블 스페이스 생성
CREATE TABLESPACE '임시TBS명' DATAFILE 'DB파일_PATH' SIZE 1G;

2) 속도를 위해 해당 테이블 Nologging 모드
ALTER TABLE 테이블이름 NOLOGGING;

3) 데이터 삭제후, 대상 테이블 임시 테이블스페이스로 이동
ALTER TABLE '테이블명' MOVE TABLESPACE '임시TBS명';

4) 기존 테이블스페이스로 원복
ALTER TABLE '테이블명' MOVE TABLESPACE '기존TBS명';

5) 인덱스 상태 확인
SELECT TABLE_NAME, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = '테이블명';
-> 지워진 인덱스의 경우, 실제 delete가 아닌 Unusable 마킹만 되어있다.
    계속 두면 segment가 실 데이터양보다 커질 수 있으니, rebulid 명령어로 정리해주자. 

ALTER INDEX (INDEX_NAME) REBUILD;

[참조]

https://positivemh.tistory.com/350

2) Shrink

- 온라인작업

- 오라클 10g 이상

- Undo TBS 확보 必

- 이동하는 Row에 대해 순간 Lock ( Enqueue ) 가 걸릴 수 있음

1) 각 row들 빈공간으로 이동 가능하게 설정
alter table 'Table명' enable row movement;

2) Row 이동
alter table 'Table명' shrink space cascade compact;
=> 실 데이터만 이동하고 HWM은 유지됌

3) HWM 이동 및 TBS 반환
alter table 'Table명' shrink space cascade ;

4) row 이동 X
alter table GSBN.TCOMCR_PRODUCT_VARIANT disable row movement;

이번에는 DB Link로 데이터베이스에 직접 붙어서, 마이그레이션 하는 방법을 알아보자

그 전에, (1) data pump를 통한 데이터 이관과의 차이를 알아보려한다.

2021.07.06 - [DB/Oracle] - 마이그레이션 (1) expdp/impdp

1) F/S  VS  Network

먼저, data pump 방식은 File System을 통해 이관하기떄문에

데이터 크기에 준하는 F/S 용량이 필요하다. ( .dmp 파일만큼 크기 필요 )

export 하는 시스템은, 원본데이터 + .dmp 만큼의 F/S 크기가 필요한 셈이다.

이는 import 쪽에도 해당되는 얘기로, 데이터의 크기가 크다면 임시 스토리지 할당등의 추가적인 작업이 필요하며,

큰 용량의 .dmp 파일을 import 하는쪽으로 옮기기도 쉽지가 않다.

반면에 DB Link를 통한 마이그레이션은 네트워크를 통하기 떄문에

연결만 가능하다면, export 하는 시스템에 추가로 필요한 사항이 없다.

또한 import 하는쪽도 데이터 크기의 용량만큼만 필요하다.

2) 속도

DB Link를 사용하는 쪽이 훨씬 빠르다.

로그를 남기지 않고, 직접적으로 데이트를 쓰기 때문이다.

Data pump와 DB Link 속도 비교를 한 블로그가 있는데

data pump로 90분이 걸리던 것을, DB Link로는 20분만에 끝낼만큼 성능차이가 심하다

데이터 환경
서비스 DBMS 버전 : Oracle 9i
전체 데이터 파일 사이즈 : 120G (인덱스 포함)
타겟 테이블 데이터 사이즈 : 26G (인덱스 제외)
네트워크 속도 : 100Mbps (max: 12.5MB/s)

https://gywn.net/2012/04/migration_with_dblink_exp_imp/

※ DB Link 란?

네트워크를 통해 다른 데이터베이스 접속 설정을 정의하는 오라클 객체

조건

1) Host Name이나 Oracle SID 가 다를것

 -> 동일 Host에서 Oracle SID마저 같으면 TNS Error 발생

2) NLS_CHARACTER_SET 통일

 -> 데이터 이관 시 Character Set 이 다르면 깨질 가능성이 있다.

3) 연결 하려는 데이터베이스의 리스너가 켜져있을 것

문법

CREATE (Public) DATABASE LINK [Link 명] CONNECT TO [접속유저] IDENTIFIED BY [유저 패스워드]
USING [네트워크 서비스명-tnsname.ora 內];

- Public 옵션을 통해 모든 유저가 링크 사용가능

- tnsname.ora 파일에는 연결하려는 DB 정보가 하기와 같이 있어야한다.

====== tnsnames.ora =====
testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oracle)
    )
  )
=========================

사용

생성

SQL>CREATE DATABASE LINK testlink CONNECT TO scott IDENTIFIED BY tiger
USING testdb

조회

SQL>SELECT ename FROM table1@testlink;

SYNONYM 생성 및 조회

SQL> CREATE SYNONYM synolink FOR table1@testlink;

SQL> SELECT ename FROM synolink;

DB 링크 삭제

SQL> DROP DATABASE LINK test_server;

as select를 사용한 마이그레이션 ( 테이블구조까지 복사 )

SQL> DROP TABLE new_table ( 기존 테이블 삭제 )

SQL> CREATE TABLE new_table AS SELECT * FROM table1@testlink;

※ Large Object인 LOB타입 데이터가 있는경우는 dblink로 불가능.

   원격 DB간의 LOB 조회,삽입이 안되기 떄문.

-> 중간에 임시 DB를 하나 거쳐서 수행할수는 있음 ( https://ooz.co.kr/280 )

출처 : [구루비] http://www.gurubee.net/lecture/1560

Oracle DB 데이터 마이그레이션이 필요할때, 크게 두가지 방법으로 나뉜다.

1) expdp/impdp 명령어를 통해, 파일 기반으로 마이그레이션 수행
2) dblink로 다른 데이터베이스에 직접 붙어, 데이터를 가져오는 방식

그중에 먼저, exp/imp 방식을 알아보자

먼저, 오라클 10g 이전에 exp/imp 방식과 expdp/impdp ( data pump ) 방식으로 나뉜다.

두 방식은 서로 호환되지 않으며,

차이점으로는 1. 속도 및 작업방식  2. 병렬수행  3. Job Control  4. 소요시간 예측 등이 있다.

- 속도

가장 큰 차이는 속도로, 기존 exp/imp의 single stream data unload 방식보다

direct path method 방식을 사용하여, 15~45배 정도 빠르다.

direct path method 
- direct I/O를 통하여 OS 메모리를 쓰지않고, 데이터파일에 직접 작성하는 방식

※ [ Direct-path 가 되지 않는 경우 ]
- 클러스터 테이블인 경우
- 테이블에 활성화된 트리거가 존재할 경우
- 글로벌 인덱스를 가진 테이블이 하나의 파티션에 존재 할 경우
- LOB컬럼에 있는 도메인 인덱스
- insert 모드에서 fine_grained access control 이 enable인 경우
- BFILE을 가진 테이블인 경우

- 작업방식 ( 병렬수행 가능 )

또한, data pump는 Oracle Directory를 통해 OS 물리경로로 접근하기 때문에

Oracle Directory를 매핑해주어야한다.

exprot ( 기존 ) : exp 계정/패스워드 file=덤프파일위치

export ( datapump ) 
 - OS 물리경로 확인 : mkdir /data/pump
 - Oracle 디렉토리 지정 : reate directory pump_dir  AS '/data/pump';
 - DBMS 계정에 디렉토리 권한 부여 : grant read,write on directory pump_dir to scott;
 - export 명령 수행 : expdp 계정/PWD direcotry=pump_dir dumpfile={데이터파일명} logfile={로그파일명}
                           full/schema/table/tablespace 중에 모드 선택하여 대상 데이터 선택
   추가옵션 : filesize : 한 파일당 최대크기 / exclude: 특정 오브젝트 제외 /  query : 조건에 맞는 오브젝트만 포함
                 job_name : job name 설정 / parallel : 병렬수행 프로세스 개수 ( 동일한 데이터파일 개수 필요 )

- Job Control

하기 명령어들을 통해 수행중인 Job의 중단, 재수행, 모니터링등의 Control 가능

 add_file: 덤프파일 추가
 exit: job 작업에서 빠져나감
 parallel: 현재 작업중인 프로세스 개수 조정
 status: 현재 작업 상태를 모니터링 하는 갱신 시간 지정
 kill_job: 해당 작업 완전히 삭제, 이게 작동 안하면 job_name의 테이블을 drop 해야함
 start_job: 중단된 작업 다시 시작
 stop_job: 현재 작업 일시 중단

- 데이터 예상시간 조회

SELECT sid, serial#, sofar, totalwork 
FROM v$session_longops
WHERE opname='job_name대문자'
AND sofar != totalwork ;

※ Import 시 주의점

1) 버전이 다를경우 import 하는 DB의 버전으로 expdp 시에 version 옵션 넣어줄 것2) 스키마, Table space, table에 대하여 exp/imp 환경이 다르다면 remap_스키마/TBS/table 옵션을 사용하여 명시

출처 :

https://artdap.tistory.com/entry/datapump란 [이제 나의 인생에는 Ctrl+Z 란 없다.]

https://hayleyfish.tistory.com/99 [DB네 생선가게]