오라클 Dump 및 복원 처리하기

http://fordev.tistory.com/23

TEST : 스키마를 Backup 한다면

expdp TEST/TESTPWD dumpfile=test.dmp schemas=TEST;

위와 같이 안될때는 expdp sys/manage dumpfile=test.dmp schemas=TEST;

impdp TEST/TESTPWD dumpfile=test.dmp schemas=TEST;

위와 같이 안될때는 impdp sys/mgr dumpfile=test.dmp schemas=TEST;

* parfile 이용시

# expdp parfile=test_exp.par;

test_exp.par 내용 :

USERID=TEST/TESTPWD
DIRECTORY=data_pump
DUMPFILE=TEST_%U.DMP
LOGFILE=TEST.LOG
SCHEMAS=TEST
CONTENT=DATA_ONLY
TABLES=TBL_MEMBER,TBL_ITEMS,TBL_ITEMSET,TBL_ITEMS_EXP,TBL_BIZDAY,TBL_HISTORY,TBL_RSV,TBL_RSVITEMS,TBL_RSVITEMS_TEMP,TBL_INDATA,TBL_INADDR,TBL_BANK
QUERY=TBL_MEMBER:"WHERE MEM_ID = 'B0000008'"
QUERY=TBL_ITEMS:"WHERE OWNER_ID = 'B0000008'"
QUERY=TBL_ITEMS_EXP:"WHERE MEM_ID = 'B0000008'"
QUERY=TBL_BIZDAY:"WHERE TO_NUMBER(TO_CHAR(TDATE,'YYYYMMDD')) >= 20140101"
QUERY=TBL_HISTORY:"WHERE TO_NUMBER(SUBSTR(RSV_ID,0,4)) > 1403"
QUERY=TBL_RSV:"WHERE MEM_ID = 'B0000008' AND TO_NUMBER(SUBSTR(RSV_ID,0,4)) > 1403"
QUERY=TBL_RSVITEMS:"WHERE TO_NUMBER(SUBSTR(RSV_ID,0,4)) > 1403"
QUERY=TBL_RSVITEMS_TEMP:"WHERE TO_NUMBER(SUBSTR(RSV_ID,0,4)) > 1403"
QUERY=TBL_INDATA:"WHERE TO_NUMBER(SUBSTR(RSV_ID,0,4)) > 1403"

# impdp TEST/TESTPWD parfile=test_imp.par;

test_imp.par 내용 :

DIRECTORY=data_pump
DUMPFILE=test_01.dmp
TABLES= TBL_RSVITEMS,TBL_RSVITEMS_TEMP,TBL_INDATA,TBL_INADDR,TBL_BANK
REMAP_SCHEMA=TEST:RETEST
TABLE_EXISTS_ACTION=APPEND
CONTENT=DATA_ONLY
LOGFILE=test.log