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 | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기

+ Recent posts