ORACLE Migration Using Datadump
#version : Oracle 10g or higher is supported (expdp / impdp)
A: Source DB -> B: Destination DB
A Source DB : CHECK Number of tables , IDX, VEIW, SYN, PK, etc
Compare the numbers with the data that was transferred later to see if the transfer was successful.
step 1 : create datadump directory
[root@A]# mkdir -p /oracle/datapump
step 2 : grant read, write to schema user
SYS:SQL> create or replace directory datapump as '/oracle/datapump';
SYS:SQL> grant read, write on directory datapump to hr;
hr <= user account in oracle db and schema owner
Ex) schema user : TAS
step 3 : export datadump
case #1) Table export
# expdp hr/oracle tables=employees, departments directory=datapump job_name=hr_export dumpfile=hr_emp_dept
Case #2) Shema export
# expdp hr/oracle schemas = hr directory=datapump dumpfile=hr_schema.dmp
Case #3) DB export
# expdp system/oracle full=y directory=datapump dumpfile=full01.dmp job_name=fullDB1
Ex) TAS shema export (case #2)
DESTINATION DB
Source DB(Datadump file) – copy -> destination DB
step 4 : create datafile on demand
step 5 : create db account on demand
step 6 : import datadump
impdp TAS/TAS directory=datapump dumpfile=TAS.dmp logfile=/oracle/tas_imp.log
step 7 : compare source db and destination db ( number of tables ..etc)
[Query to check the number of objects]
SQL> set line 200
COL OWNER FORMAT A25
COL TAB FORMAT 99999
COL IDX FORMAT 99999
COL VEW FORMAT 99999
COL SYN FORMAT 99999
COL SEQ FORMAT 99999
COL TRG FORMAT 99999
COL PRO FORMAT 99999
COL FUN FORMAT 99999
COL CLU FORMAT 99999
COL PKG FORMAT 99999
COL PKB FORMAT 99999
COL DBL FORMAT 99999
COL PK# FORMAT 99999
COL FK# FORMAT 99999
SELECT A.OWNER, NVL(A.TAB,0) AS TAB, NVL(A.IDX,0) AS IDX, NVL(A.VEW,0) AS VEW,
NVL(A.SYN,0) AS SYN, NVL(A.SEQ,0) AS SEQ, NVL(A.TRG,0) AS TRG,
NVL(B.PK,0) AS PK#, NVL(B.FK,0) AS FK#, NVL(A.DBL,0) AS DBL,
NVL(A.PRO,0) AS PRO, NVL(A.FUN,0) AS FUN, NVL(A.PKG,0) AS PKG,
NVL(A.PKB,0) AS PKB, NVL(A.CLU,0) AS CLU
FROM
(SELECT OWNER,
COUNT(DECODE(OBJECT_TYPE,'TABLE',1)) AS TAB,
COUNT(DECODE(OBJECT_TYPE,'INDEX',1)) AS IDX,
COUNT(DECODE(OBJECT_TYPE,'VIEW',1)) AS VEW,
COUNT(DECODE(OBJECT_TYPE,'SYNONYM',1)) AS SYN,
COUNT(DECODE(OBJECT_TYPE,'SEQUENCE',1)) AS SEQ,
COUNT(DECODE(OBJECT_TYPE,'TRIGGER',1)) AS TRG,
COUNT(DECODE(OBJECT_TYPE,'DATABASE LINK',1)) AS DBL,
COUNT(DECODE(OBJECT_TYPE,'PROCEDURE',1)) AS PRO,
COUNT(DECODE(OBJECT_TYPE,'FUNCTION',1)) AS FUN,
COUNT(DECODE(OBJECT_TYPE,'PACKAGE',1)) AS PKG,
COUNT(DECODE(OBJECT_TYPE,'PACKAGE BODY',1)) AS PKB,
COUNT(DECODE(OBJECT_TYPE,'CLUSTER',1)) AS CLU
FROM DBA_OBJECTS
GROUP BY OWNER ) A,
(SELECT OWNER,
SUM(DECODE(CONSTRAINT_TYPE,'P',1)) AS PK,
SUM(DECODE(CONSTRAINT_TYPE,'R',1)) AS FK
FROM DBA_CONSTRAINTS
GROUP BY OWNER ) B
WHERE A.OWNER = B.OWNER(+)
and a.owner not in ('SYS','SYSTEM','SYSMAN','SYSAUX','XDB','HR','ODM','OUTLN','OE','SH','PM','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')
/
사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기
'DB - ORACLE > Oracle Up,Mig' 카테고리의 다른 글
| ORACLE DATAPUMP 정리 (0) | 2015.11.04 |
|---|---|
| 윈도우 아카이브 삭제 스크립트 (0) | 2015.08.24 |
| Oracle TTS 마이그레이션 작업 가이드 (0) | 2015.02.26 |
| 오라클 패치 테스트 for Windows (0) | 2015.01.19 |