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

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

Oracle Data Pump 유틸리티를 사용하여 스키마 데이터를 이전하는 절차는 다음과 같습니다. 여기서는 데이터 내보내기(expdp)와 데이터 가져오기(impdp)를 단계별로 설명하고, 이 과정에서 필요한 점검 및 설정 항목을 함께 안내하겠습니다.

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

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

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

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

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

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

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

$ mkdir /path/to/datapump

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

SQL> CREATE DIRECTORY DATA_DUMP AS '/path/to/datapump';
SQL> SELECT * FROM DBA_DIRECTORIES;

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

1.3. 디스크 공간 확인

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

SELECT TABLESPACE_NAME, SUM(bytes/1024/1024/1024)
FROM DBA_SEGMENTS 
WHERE OWNER = 'HR_schema' 
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 = 'HR_tablespace_name';

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

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

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

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

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

$ expdp \"/ as sysdba \" [full=y 또는 schemas=HR_schema] directory=DATA_DUMP dumpfile=full_yymmdd.dmp logfile=full_yymmdd.log [include=user include=role include=grant exlude=statistics status=3 parallel=4]

\”/ as sysdba \”: 소스 데이터베이스 접속 정보입니다.
full=y: 모두 내보내기를 하면 타겟 데이터베이스트에서 권한이나 Role 등을 따로 설정할 필요가 없습니다.(권장)
schemas=HR_schema: 내보낼 스키마만 지정할 수도 있습니다.
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

또는 해당 디렉토리의 사용량이 증가하는지 같이 확인하면 좋습니다.
$ while
du -sh .
do
sleep 1
done;

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 HR_schema CASCADE;

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

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

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

SQL> CREATE TABLESPACE <HR_tablespace_name>
DATAFILE '/path/to/acnt2pjt_data01.dbf' SIZE 200M 
AUTOEXTEND ON NEXT 10M MAXSIZE 32G;
```
DBA_DATA_FILES 뷰의 AUTOEXTENSIBLE 컬럼 설정값이 “NO”라면 대량의 데이터를 가져오기할 때 공간부족으로 가져오기 작업이 실패할 수 있으므로, 1.3단계에서 확인한 DATA FILE의 갯수 및 크기와 비교하여 DATA FILE을 수작업으로 추가해 줍니다.
```
SQL> ALTER TABLESPACE <HR_tablespace_name> 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.4. 사전 스키마 생성 및 Import 필요 권한 부여를 위한 DDL 확인 및 실행

3.3단계에서 생성한 DDL 파일(ddl_yymmdd.sql)로부터 스키마 생성 및 권한 부여 SQL 확인

$ grep -A2 "CREATE USER \"HR_schema\" IDENTIFIED BY" ./ddl_yymmdd.sql
CREATE USER "HR_schema" IDENTIFIED BY VALUES 'S:CD349DF0A9378B1648FCCDA6978DA98567DA0EFF4F255657DF85F1C8658F;A1C7FF4EDC56CB5C'
      DEFAULT TABLESPACE "HR_tablespace_name"
      TEMPORARY TABLESPACE "TEMP"

스키마 생성 및 Import 필요 권한 부여

CREATE USER "HR_schema" IDENTIFIED BY VALUES 'S:CD349DF0A9378B1648FCCDA6978DA98567DA0EFF4F255657DF85F1C8658F;A1C7FF4EDC56CB5C'
DEFAULT TABLESPACE "HR_tablespace_name"
TEMPORARY TABLESPACE "TEMP";

GRANT UNLIMITED TABLESPACE TO "HR_schema";
GRANT "CONNECT" TO "HR_schema";
GRANT "RESOURCE" TO "HR_schema";

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

$ impdp \"/ as sysdba \" schemas=HR_schema directory=DATA_DUMP dumpfile=full_yymmdd.dmp logfile=hr_import_yymmdd.log

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

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

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

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

의존성 문제, Import 순서 문제 등으로 Invalid된(비활성) PL/SQL 오브젝트(프로시저, 함수, 패키지, 트리거 등)를 다시 컴파일(Recompile)합니다.
```
SQL> @?/rdbms/admin/utlrp.sql
```

4. 사후 점검 및 설정

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

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

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

DEFINE V_GRANTEE = 'HR_schema';
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('HR_schema');
```

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]audit 패키지로 시스템의 감사(audit)와 감사 이벤트 Logging

[Oracle] 사용자 세션을 수동으로 종료시키는 방법

[Oracle] 분석함수와 OVER( ) 문 사용법

답글 남기기 응답 취소

최신 글

최신 댓글