oracle DB 이관 expdp, impdp, tablespace

오라클 DB이관작업. 

expdp로 system 계정전체 pump후 계정별로 impdp 가능.

SHUTDOWN IMMEDEATE

---------------1. 디비 테이블 스페이스 생성

create tablespace KBTEST_ERP_TS datafile 'D:/oradata/TableSpace/KBTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_02.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_03.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_04.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_05.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_06.dbf' SIZE 10G;

create TEMPORARY tablespace KBTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KBTEST_ERP_TEMP_TS_01.dbf' size 5000M;

create tablespace TEL_TEST_LOGIS_TS datafile 'D:/oradata/TableSpace/TEL_TEST_LOGIS_TS_01.dbf' size 1G autoextend on next 100M MAXSIZE UNLIMITED;

create TEMPORARY tablespace TEL_TEST_LOGIS_TEMP_TS tempfile 'D:/oradata/TableSpace/TEL_TEST_LOGIS_TEMP_TS.dbf' size 500M;

create tablespace KFTEST_ERP_TS datafile 'D:/oradata/TableSpace/KFTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KFTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KFTEST_ERP_TS_02.dbf' SIZE 10G;

create TEMPORARY tablespace KFTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KFTEST_ERP_TEMP_TS_01.dbf' size 500M;

create tablespace KNTEST_ERP_TS datafile 'D:/oradata/TableSpace/KNTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KNTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KNTEST_ERP_TS_02.dbf' SIZE 10G;

create TEMPORARY tablespace KNTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KNTEST_ERP_TEMP_TS_01.dbf' size 500M;

---------------1. 디비 테이블 스페이스 생성

---------------- 템프러리 테이블 스페이스 -----------------

3.----------------- 유저 생성, 권한 주기, 

create user KBTEST_ERP identified by KBTEST_ERP default tablespace KBTEST_ERP_TS temporary tablespace KBTEST_ERP_TEMP_TS;

create user KFTEST_ERP identified by KFTEST_ERP default tablespace KFTEST_ERP_TS temporary tablespace KFTEST_ERP_TEMP_TS;

create user KNTEST_ERP identified by KNTEST_ERP default tablespace KNTEST_ERP_TS temporary tablespace KNTEST_ERP_TEMP_TS;

create user KNTEST_ERPUSER identified by KNTEST_ERP default tablespace KNTEST_ERP_TS temporary tablespace KNTEST_ERP_TEMP_TS;

create user ASP_GET identified by ASP_GET default tablespace ASP_GET_TS temporary tablespace ASP_GET_TEMP_TS;

create user ASP_NIX identified by ASP_NIX default tablespace ASP_NIX_TS temporary tablespace ASP_NIX_TEMP_TS;

create user USER_DAS identified by USER_DAS default tablespace ASP_GET_TS temporary tablespace ASP_GET_TEMP_TS;

create user TEL_TEST_LOGIS identified by TEL_TEST_LOGIS default tablespace TEL_TEST_LOGIST_TS temporary tablespace TEL_TEST_LOGIS_TEMP_TS;

grant connect, resource, DBA to KBTEST_ERP;

grant create view to KBTEST_ERP;

grant create SYNONYM to KBTEST_ERP;

--------------------------------------------------------------

---- 디렉토리 생성-------------------------------------

CREATE DIRECTORY IMPORT as 'd:/IMPORT';

CREATE DIRECTORY OraPump as 'd:/OraPump';

grant write,read on directory OraPump to KBTEST_ERP ;

----------------디렉토리 생성-------------------------------

----------------유저, 테이블스페이스 삭제-----------

drop user KBTEST_ERP cascade;

drop tablespace KBTEST_ERP_TS including contents;

drop tablespace KBTEST_ERP_TEMP_TS including contents;

----------------유저, 테이블스페이스 삭제-----------

-- 데이터펌프 

------- 시스템 펌프.

expdp system/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.dmp full=y logfile=KB_SYSTEM_EXPDP_LOG.log

expdp system/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.dmp full=y logfile=KB_SYSTEM_EXPDP_LOG2.log

------- 유저 펌프.

expdp KBTEST_ERP/KBTEST_ERP directory=OraPump dumpfile=KBTEST_ERP_EXPDP.dmp full=y logfile=KBTEST_ERP_EXPDP_LOG.log;

imp KBTEST_ERP/KBTEST_ERP file='D:\import\KBTEST_ERPDB.dmp' logfile=IMPLOG2.log

-- 시스템 임프 

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KB_SYSTEM_IMPDP.log TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KB_SYSTEM_IMPDP.log REMAP_SCHEMA=KFTEST_ERP:KFTEST_ERP TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=TELLOGIS_IMPDP.log SCHEMAS=TEL_TEST_LOGIS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=GETUSER_DAS.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=REPLACE

--2차시도 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP3.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE

-- 마지막테스트 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=GETUSER_DAS2.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP4.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE

--실제 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.DMP logfile=GETUSER_DAS5.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.DMP logfile=KBTEST_ERP_IMPDP7.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE