[Oracle] Oracle Data Pump 유틸리티를 사용하여 스키마를 이전하는 절차

by 딸둘아비 · 2024년 05월 23일

Oracle Data Pump 유틸리티를 사용하여 스키마 데이터를 이전하는 절차는 다음과 같습니다. 여기서는 데이터 내보내기(expdp)와 데이터 가져오기(impdp)를 단계별로 설명하고, 이 과정에서 필요한 점검 및 설정 항목을 함께 안내하겠습니다. 만일 데이터베이스 전체를 이전하고자 하시면, “RMAN DUPLICATE 명령어를 사용하여 운영DB를 개발DB로 이관하기” 글을 참고하세요.

사전 점검 및 설정
데이터 내보내기 (Export)
데이터 가져오기 (Import)
사후 점검 및 설정

1. 사전 점검 및 설정(소스, 타겟 동일)

1.1. 데이터베이스 접속 정보 및 이전할 스키마 등의 정보 확인

사용하는 테이블 스페이스, 권한, Role, 디렉토리 객체 등에 동일해야 합니다.
데이터베이스 인스턴스 이름, 사용자 이름, 비밀번호, 이전할 스키마를 확인합니다.
– SID : export ORACLE_SID = orcl
– 사용자 이름(스키마): scott
– 비밀번호: tiger
– 실제 Datapump 디렉터리 : /path/to/datapump
– Oracle 디렉터리 이름: DATA_DUMP

1.2. 디렉터리 객체 생성 및 권한 부여

Oracle Data Pump 유틸리티로 데이터를 import/export할 때 사용할 실제 디렉터리를 생성(mkdir -p /path/to/datapump)합니다.
디렉터리 객체를 생성 및 삭제할 수 있는 권한을 사용자에게 부여합니다.
데이터 파일을 저장할 파일 시스템 경로에 대한 디렉터리 객체를 생성하고 학인합니다.

생성한 디렉터리에 대한 읽기 및 쓰기 권한을 사용자에게 부여합니다.

SQL> GRANT CREATE ANY DIRECTORY TO scott;
SQL> GRANT DROP ANY DIRECTORY TO scott;

SQL> CREATE OR REPLACE DIRECTORY DATA_DUMP AS '/path/to/datapump';
SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_DUMP';

SQL> GRANT READ, WRITE ON DIRECTORY DATA_DUMP TO scott;

1.3. 디스크 공간 확인

DBA_SEGMENTS 뷰를 통해 내보내기될 데이터의 크기를 확인합니다.

SELECT TABLESPACE_NAME, SUM(bytes/1024/1024/1024)
FROM DBA_SEGMENTS 
WHERE OWNER = 'scott' 
GROUP BY TABLESPACE_NAME;

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024/1024 AS SIZE_IN_GB, AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'TS_SCOTT';

내보내기 및 가져오기 작업에 충분한 디스크 공간이 있는지 확인합니다.(소스, 타겟 동일)
```
$ du -s /path/to/datapump
```

1.4. 네트워크 연결 상태 확인

소스와 타겟 데이터베이스 간의 네트워크 연결 상태를 점검합니다.(타겟 호스트 예: 198.168.56.101)

2. 데이터 내보내기 (Export)

2.1. expdp 명령어를 사용하여 전체 또는 특정 스키마 데이터를 내보냅니다.

# "full=Y"와 "include" 옵션을 동시에 사용하면 충돌이 발생하거나 의도와 다르게 작동할 수 있습니다.
# -->(include=user include=role include=grant 등)
# 특정 사용자(Schema)들만 골라서 백업하려면 "full=y" 대신 "schemas=사용자1,사용자2"를 사용하면 됩니다.
# "exclude=statistics" 옵션을 사용하여 전체 백업 데이터 중 통계 데이터를 제외할 수 있습니다.
# "parallel=4" 옵션을 사용하여 4개 프로세스로 백업 작업을 병렬로 처리합니다. 반드시 dumpfile명에 "%U" 옵션을 추가해야 합니다.
# "parallel=1" 옵션을 사용하는 경우에는 "%U"을 사용하지 않습니다.
# "status=3" 옵션은 3초마다 진행 상황을 화면으로 출력합니다.
# "compression=all" 옵션을 사용하면 백업 파일 용량을 획기적으로 줄여줍니다.(Advanced Compression 파라미터 값이 TRUE여야 사용 가능)
#  -->(SELECT * FROM v$option WHERE parameter = 'Advanced Compression';)
# "cluster=n" 옵션을 사용하면 RAC환경에서 명령어를 실행한 노드에서만 작업이 집중되도록 합니다.
$ expdp \"/ as sysdba \" \
  full=Y \
  directory=DATA_DUMP \
  dumpfile=full_$(date +%Y%m%d)_%U.dmp \
  logfile=full_$(date +%Y%m%d)_expdp.log \
  exclude=statistics \
  parallel=4 \
  status=3 \
  compression=all \
  cluster=n

\”/ as sysdba \”: 소스 데이터베이스 접속 정보입니다.
full=Y: 모두 내보내기를 하면 타겟 데이터베이스트에서 권한이나 Role 등을 따로 설정할 필요가 없습니다.(권장)
schemas=scott: “,” 로 구분하여 내보낼 스키마만 지정할 수도 있습니다.
tables=스키마.테이블: “,”로 구분하여 내보낼 테이블만 지정할 수도 있습니다.
directory=DATA_DUMP: 데이터 파일을 저장할 디렉터리입니다.
dumpfile=full_yymmdd.dmp: 생성할 덤프 파일 이름입니다.
logfile=full_yymmdd.log: 생성할 로그 파일 이름입니다.
include: 작업에 포함시킬 오브젝트를 설정합니다.
exclude: 작업에 제외할 오브젝트를 설정합니다.
status=3: 작업 중 상태 정보를 3초 간격으로 출력합니다.
parallel=4: 최대 4개의 작업 프로세스(Worker Process)가 동시에 병렬 처리되어 백업(Export) 및 복구(Import) 속도를 향상

2.2. 데이터 내보내기 결과 확인

2.1단계 실행 후 다른 Session에서 로그 파일(full_yymmdd.log)을 실시간으로 검토하여 오류가 없는지 확인합니다.

$ tail -f full_yymmdd.log

또는 10초 간격으로 Dump 파일이 저장되는 디렉토리의 사용량이 증가하는지 같이 확인하면 좋습니다.
$  watch -n 10 du -sh /path/to/datapump/

2.3. full_yymmdd.dmp 파일을 타겟 데이터베이스의 서버로 전송합니다.

scp 명령어를 사용하여 전송합니다.

$ scp /path/to/datapump/full_yymmdd.dmp username@target_host:/path/to/datapump/datapump

3. 타겟 서버에서 데이터 가져오기 (Import)

3.1. 필요한 경우 타겟 데이터베이스에서 기존 스키마를 삭제

기존 스키마가 존재할 경우, 해당 스키마를 삭제합니다. 삭제하기 전에 연결된 session이 없어야 합니다.
```
SQL> DROP USER scott CASCADE;

※ CASCADE 옵션: 해당 사용자가 소유한 모든 객체(Schema Objects)를 함께 삭제
```

3.2. 테이블스페이스 생성 또는 여유 공간 확보

타겟 데이터베이스에서 스키마에게 할당된 테이블스페이스가 없다면 생성해야 합니다.
오라클 환경 설정(db_create_file_dest)에 기본 경로가 지정되어 있다면, 경로와 파일명을 생략할 수 있습니다.(AUTOEXTEND ON, MAXSIZE UNLIMITED가 기본값)
```
SQL> SHOW PARAMETER db_create_file_dest;

SQL> CREATE TABLESPACE TS_SCOTT
DATAFILE '/path/to/datafile_name.dbf' SIZE 200M 
AUTOEXTEND ON NEXT 10M MAXSIZE 32G;
```
DBA_DATA_FILES 뷰의 AUTOEXTENSIBLE 컬럼 설정값이 “NO”라면 대량의 데이터를 가져오기할 때 공간부족으로 가져오기 작업이 실패할 수 있으므로, 1.3단계에서 확인한 DATA FILE의 갯수 및 크기와 비교하여 DATA FILE을 수작업으로 추가해 줍니다.
```
SQL> ALTER TABLESPACE TS_SCOTT ADD DATAFILE SIZE 20G;
```

3.3. 덤프 파일로 부터 DDL파일 생성

스키마 생성 및 Import에 필요한 권한을 확인하기 위해 DDL sql 파일 생성

$ impdp \"/ as sysdba \" directory=DATA_DUMP dumpfile=full_yymmdd.dmp logfile=impdp_ddl_yymmdd.log sqlfile=ddl_yymmdd.sql

※ sqlfile=ddl_yymmdd.sql : Dump파일로 부터 추출된 DDL(Data Definition Language) 저장

3.5. impdp 명령어를 사용하여 스키마 데이터를 가져옵니다.

# "schemas=scott" 옵션을 사용해서 특정 스키마의 데이터만 가져올 수 있습니다. ","로 구분하여 여러 개의 스키마를 지정합니다.
# "schemas" 옵션 대신에 "full=Y" 옵션을 사용하면 모든 스키마 데이터를 가져옵니다.
# "remap_schema=scott:scott_test" 옵션는 백업받은 스키마(Source)와 넣을 스키마(Target)의 이름이 다를 때 사용합니다.
# "remap_tablespace" 옵션을 사용하면 다른 네임스페이스에 데이터를 부을 수 있습니다.
# "parallel=n"(n>1) 옵션을 사용해서 export을 했다면 "dumpfile" 옵션에 반드시 "%U"를 포함해야 하고, import 시 "parallel=n" 옵션도 동일해야 합니다.
# "table_exists_action=truncate" 옵션을 사용해서 이미 있는 테이블의 데이터를 모두 삭제 후 입력.(상황에 따라 skip, append, replace로 변경 가능)
# 스키마를 삭제한 후 impdp 명령어를 사용한다면 "table_exists_action" 옵션은 필요없습니다.
# "exclude=statistics" 옵션을 사용하면 데이터만 빠르게 부어넣고 통계 정보는 나중에 따로 생성할 수 있습니다.
$ impdp \"/ as sysdba \" \
  directory=DATA_DUMP \
  dumpfile=full_yyyymmdd_%U.dmp \
  logfile=full_yyyymmdd_impdp.log \
  schemas=scott \
  parallel=4 \
  table_exists_action=truncate \
  status=3

\”/ as sysdba \”: 타겟 데이터베이스 접속 정보입니다.
schemas=scott: 가져올 스키마 이름입니다.
directory=DATA_DUMP: 데이터 파일이 저장된 디렉터리입니다.
dumpfile=full_yymmdd.dmp: 가져올 덤프 파일 이름입니다.
logfile=full_yyyymmdd_impdp.log: 생성할 로그 파일 이름입니다.
table_exists_action=truncate: 기존 스키마(Schema)를 DROP하지 않고 가져오기할 때 사용합니다. 즉, 기존 테이블이 존재하는 경우 해당 테이블의 데이터를 삭제 후 입력하는 방식으로 작동합니다.

3.6. 데이터 가져오기 결과 확인

로그 파일(full_yyyymmdd_impdp.log)을 검토하여 오류가 없는지 확인합니다.
```
$ tail -f full_yyyymmdd_impdp.log
```

3.7. $ORACLE_HOME/rdbms/admin/utlrp.sql 유틸리티를 실행

의존성 문제, Import 순서 문제 등으로 Invalid된(비활성) PL/SQL 오브젝트(프로시저, 함수, 패키지, 트리거 등)를 다시 컴파일(Recompile)합니다.
```
-- 오라클 환경에서 "?"는 ORACLE_HOME 경로를 대신하는 약어
SQL> @?/rdbms/admin/utlrp.sql
```

4. 사후 점검 및 설정

4.1. 데이터 무결성 및 일관성 점검

데이터가 올바르게 이전되었는지, 데이터 무결성과 일관성을 점검합니다.

expdp 명령어를 사용할 때 FULL 옵션과 INCLUDE 옵션을 사용해도 권한 설정이 이전되지 않으면 아래 스크립트로 권한 설정을 수작업으로 수행합니다.

DEFINE V_GRANTEE = 'scott';
SET HEADING OFF;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;

SPOOL grant_privileges.sql;

SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE ||';'
FROM   DBA_ROLE_PRIVS
WHERE  GRANTEE = '&V_GRANTEE';

SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || 
       CASE WHEN GRANTABLE = 'YES' THEN ' WITH GRANT OPTION;' ELSE ';' END
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE = '&V_GRANTEE';

SELECT 'GRANT '|| PRIVILEGE || ' TO ' || GRANTEE ||';'
FROM DBA_SYS_PRIVS
WHERE  GRANTEE = '&V_GRANTEE';

SPOOL OFF;

DBA_OBJECTS 뷰는 Oracle 데이터베이스에서 모든 데이터베이스 객체(테이블, 인덱스, 뷰, 프로시저, 패키지 등)를 관리하고 조회하는 데 사용됩니다. 원본 스키마의 객체 수량과 import된 스키마의 객체 수량을 비교해 봅니다.
```
COL OWNER FOR A15
COL OBJECT_TYPE FOR A20
COL STATUS FOR A15
SET HEAD OFF
SET PAGESIZE 0
SELECT OWNER, OBJECT_TYPE, STATUS , COUNT(*) FROM DBA_OBJECTS
WHERE OWNER IN ('TGMADM')
GROUP BY OWNER, OBJECT_TYPE, STATUS
ORDER BY 1,2,3;
```

4.2. 데이터베이스 통계 수집

성능 최적화를 위해 데이터베이스 통계를 수집합니다.
```
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('scott');
```

4.3. 애플리케이션 연결 테스트

애플리케이션이 정상적으로 타겟 데이터베이스에 연결되고 기능이 올바르게 작동하는지 테스트합니다.

4.4. 백업 수행

데이터 이전 작업이 완료되고 정상적으로 확인된 후, 타겟 데이터베이스의 백업을 수행합니다.

[Oracle] Oracle Data Pump 유틸리티를 사용하여 스키마를 이전하는 절차

1. 사전 점검 및 설정(소스, 타겟 동일)

2. 데이터 내보내기 (Export)

3. 타겟 서버에서 데이터 가져오기 (Import)

4. 사후 점검 및 설정

You may also like...

답글 남기기 응답 취소

최신 글

최신 댓글

[Oracle] Oracle Data Pump 유틸리티를 사용하여 스키마를 이전하는 절차

1. 사전 점검 및 설정(소스, 타겟 동일)

2. 데이터 내보내기 (Export)

3. 타겟 서버에서 데이터 가져오기 (Import)

4. 사후 점검 및 설정

You may also like...

[Linux] Unix/Linux 계열 비밀번호 사용기간 정책 설정 파일

[Oracle] 데이터베이스 모니터링 스크립트(Weekly)

Proxmox VE: 미러링으로 설치하기(ZFS 파일시스템 사용)

답글 남기기 응답 취소

최신 글

최신 댓글