使用impdp时需要注意的几点

拼搏现实的明天。 2022-12-07 00:40 225阅读 0赞

### impdp 250G data to another schema:tablespace ###

#### 1 使用DBA权限用户建立tablespace ####

CREATE TABLESPACE sparkdata DATAFILE '/oracle/oradata/cqj/sparkdataspace1.dbf' SIZE 10240M AUTOEXTEND ON NEXT 1024M MAXSIZE 30720M;
    ALTER TABLESPACE sparkdata ADD DATAFILE '/oracle/oradata/cqj/sparkdataspace2~9.dbf' SIZE 10240M AUTOEXTEND ON NEXT 1024M MAXSIZE 30720M;

– 后来发现前90G满了后，表空间以1G的大小增长，感觉应该以10G的大小增加 autoextend on next 10000M

#### 2 建立用户sparkdata ####

create user sparkdata identified by sparkdata default tablespace sparkdata;
    GRANT CREATE SESSION,CREATE TABLE,CREATE VIEW,CREATE SEQUENCE,UNLIMITED TABLESPACE TO sparkdata; 
    GRANT CONNECT TO sparkdata; 
    GRANT RESOURCE TO sparkdata; 
    GRANT DBA TO sparkdata;

#### 3 在oracle用户的路径下建立dbbak文件夹 ####

**mkdir /u01/oracle/dbbak**

create directory dbbak as '/u01/oracle/dbbak';
    grant read,write on directory dbbak to public;

#### 4 使用root用户把XXXX.dmp文件拷贝到 dbbak ,并 chown oracle:oinstall xxxx.dmp ####

#### 5 使用oracle用户跑impdp ####

export ORACLE_HOME=/u01/oracle/app/productxxxx/;
    impdp sparkdata/sparkdata@odsdata  directory=dbbak dumpfile=xxxxx.dmp logfile=xxxx.log remap_schema=odsbizdata:sparkdata remap_tablespace=odsbizdata:sparkdata

#### 6 用impdp载入指定的表的备份 ####

impdp sparkdata/sparkdata@odsdata  directory=dbbak dumpfile=xxxxx.dmp table_exists_action=replace tables=odsdata.T0204_COMMON_ZHENGZHAOZL,odsdata.T0508_FUD_SUNLINEDAYBAL,odsdata.T0617_KTAB_CFTYHQ,odsdata.T0307_DPS_ZHMINX,odsdata.T0321_KDPA_ZHLLDY,odsdata.T0322_KDPB_XDCKDJ,odsdata.T0308_KLNA_DKZHZB,odsdata.T0503_KDPA_YHZHWH,odsdata.T0510_KDPB_TBZHJB,odsdata.T0505_KFAP_YHKMDZ,odsdata.T0502_KDPB_YHZHLS,odsdata.T0511_KFAP_YBKMDZ,odsdata.T0512_KFAP_NBJGDZ,odsdata.T0514_KGLB_ZONGZH,odsdata.T0312_KCFB_CFTYZJ,odsdata.T0313_KDPA_ZHDUIZ,odsdata.T0314_KDPA_ZHJXDY,odsdata.T0315_KBRP_JGGXII,odsdata.T0316_KCFB_CFDGZJ,odsdata.T0317_KCFB_CFTYFB,odsdata.T0318_KDPL_ZHLXMX,odsdata.T0319_KDPL_DONJMX,odsdata.T0320_KDPB_DNGJDJ,odsdata.T0625_KFAA_NBFHZH,odsdata.T0222_KCFB_CFDGFB,odsdata.T0223_KCFB_CFDGJC,odsdata.T0224_KCFB_CFTYJC,odsdata.T0225_KDPA_ZHXINX,odsdata.T0226_KDPA_TUOZXX,odsdata.T0227_KDPB_ZHRZYE,odsdata.T0626_KDPA_FKDJBU,odsdata.T0620_KAPB_ZHXXHZ,odsdata.T0633_KTAB_CFTYDQ,odsdata.T0309_KLNL_DKJXMX,odsdata.T0515_KFAA_NBFHZH,odsdata.T0634_KDPA_YUEXXI,odsdata.T0635_KDPA_FPQYXY,odsdata.T0636_KDPA_FPJYMX,odsdata.T0637_KDPA_FPJBXX,odsdata.T0603_KBRP_JYCSHU,odsdata.T0639_KDPA_FPJBXXJY,odsdata.T0641_KTAB_HQRZYE,odsdata.T0648_KTAB_DQRZYE,odsdata.T0644_KTAA_NBRZYE,odsdata.T0643_KTAA_NBZHMX,odsdata.T0640_KTAB_CFHQMX,odsdata.T0647_KTAB_ZHRZYE,odsdata.T0667_KTAB_CFDQMX,odsdata.T0642_KTAA_NBFHZH,odsdata.T0645_KTAB_DXZDJB,odsdata.T0646_KTAB_DXZMXD,odsdata.T0323_KDPF_CHPSHX,odsdata.T638_KDPB_LHANGH,odsdata.T0307_ZHMINX_zijinlay,odsdata.T0601_CB_TRANFLOW,odsdata.T0621_CB_CST_INF,odsdata.T0622_CB_USER_INF,odsdata.T0627_CB_ITEM,odsdata.T0628_CB_CST_BSN,odsdata.T0629_IM_BRANCH,odsdata.T0630_CB_DRAWDEPOSIT,odsdata.T0631_CB_INFORM_DEPOSIT_OPEN,odsdata.T0632_CB_GROUP_TRANFLOW,odsdata.T0428_LOAN_ISSUE_WARRANTY,odsdata.T0440_BUSI_GUARANTY,odsdata.T0401_CRD_JIEDAIHETONG,odsdata.T0402_CRD_JIEJU,odsdata.T0410_BUSI_TYPE,odsdata.T0421_CUS_CON_FOUNINFO,odsdata.T0422_CUS_TRA_FOUNINFO,odsdata.T0423_CUS_CON_BASEINFO,odsdata.T0424_APPBACK_PRO_CONINFO,odsdata.T0431_Accou_Pub_Info,odsdata.T0432_crecap_interbase_Info,odsdata.T0434_CRECAP_ACCO_INFO,odsdata.T0436_GUAR_ACCOUNTBASE_INFO,odsdata.T0439_BACK_LOSEEFF_CONINFO,odsdata.T0441_CREACC_INTERBASE_INFO,odsdata.T0442_ACCPRO_CONINFO,odsdata.T0443_ACCEVE_MANA_CONINFO,odsdata.T0444_LOAN_PROMISE_BOOK,odsdata.T0445_MORTPLE_INTERBASE_INFO,odsdata.T0446_APPGUAR_CONTCOND_INFO,odsdata.T0447_MORTPLE_ESTIVAL_CONDINFO,odsdata.T0448_BUSI_GUARANTY_CONDINFO,odsdata.T0449_GUARAMT_INTERBASE_INFO,odsdata.T0450_SYN_LOAN_INFO,odsdata.T0425_GUARAMT_INTERBASE_INFO,odsdata.T0426_GUARCUS_BASEINFO,odsdata.T0427_GUAR_CONDINFO,odsdata.T0429_DAILY_BALANCE,odsdata.T0498_KLNL_DKZHMX,odsdata.T0488_LOAN_PROMISE_BOOK,odsdata.T0496_BUSI_APPLY_CORP,odsdata.T0476_BUSI_MORTGAGE,odsdata.T0208_MULTILEVELDIC,odsdata.T0498_PORTAL_ORG,odsdata.T0430_DUEBILL_ENDDATECHANGE,odsdata.T0497_DAYBAT_TASK_INFO,odsdata.T0407_FBUS_CTCTM,odsdata.T0409_FBUS_SUBCM,odsdata.T0411_FINT_LOANM,odsdata.T0412_FEFP_CTCTM,odsdata.T0413_FEFP_SUBCM,odsdata.T0414_FEFP_LOANM,odsdata.T0415_FINT_GLFDM,odsdata.T0416_FEFP_LOANH,odsdata.T0417_FBUS_SUBCH,odsdata.T0418_FEFP_SUBCH,odsdata.T0419_FEFP_CTCTH,odsdata.T0420_FBUS_CTCTMH,odsdata.T0480_FINT_LOANH,odsdata.T0481_FINT_RECEH,odsdata.T0482_FINT_RECEM,odsdata.T0483_FEFP_RECEH,odsdata.T0484_FEFP_RECEM,odsdata.T0485_S_BCH,odsdata.T0486_FBUS_SBRTM,odsdata.T0487_CUS_CORP,odsdata.T0435_APPR_RES_ENTER,odsdata.T0490_OPR_OPRINFORMATION,odsdata.T0495_FIN_YSBILL_ACCOUNT,odsdata.T0472_fin_yfbillfk_account,odsdata.T0473_BILL_ZS_BEG,odsdata.T0474_FIN_YSBILLSK_ACCOUNT,odsdata.T0491_BILL_TX_BEG,odsdata.T0492_BILL_FK_BEG,odsdata.T0493_CA_ENTITY,odsdata.T0494_FIN_YSBILLTX_ACCOUNT,odsdata.T0438_BILL_ECDS_INMSG,odsdata.T0571_ACC_ACCCASHPAYMODE,odsdata.T0572_OPR_TEMPLATE,odsdata.T0437_BILL_XDCONTBILLCMP,odsdata.T0475_BILL_NEWCOREBILLCMP,odsdata.T0433_CA_ENTITYACCOUNT,odsdata.T0480_SHCPE_ACC_DEAL_BILL,odsdata.T0481_SHCPE_ACC_ZTX_DEAL,odsdata.T0482_SHCPE_LOCALBRANCH,odsdata.T0483_SHCPE_BILL_BASE,odsdata.T0484_SHCPE_ACC_BILL,odsdata.T0485_SHCPE_CAS_STLINF,odsdata.T0486_SHCPE_ACC_ZYTX_DEAL,odsdata.T0487_SHCPE_MEM_BRANCH,odsdata.T0403_R_CONT_MAIN,odsdata.T0404_LM_LOAN,odsdata.T0405_LM_PM_SHD,odsdata.T0405_RENTSHD,odsdata.T0406_S_ORG,odsdata.T0408_R_PUT_START,odsdata.T0611_TB_NB_ACC_DAYDTL,odsdata.T0612_STM_ENT_DLZZ,odsdata.T0616_STM_ENT_XBHZ,odsdata.T0614_STM_ENT_JBZZ,odsdata.T0618_STM_PERS_JBZZ,odsdata.RI_VIEW_ASSET_RESULT remap_schema=odsdata:sparkdata remap_tablespace=odsdata:sparkdata

impdp sparkdata/sparkdata@odsdata  directory=dbbak dumpfile=xxxxx.dmp table_exists_action=replace tables=odsdata.T0610_KFAB_LSZZCP remap_schema=odsdata:sparkdata remap_tablespace=odsdata:sparkdata

### 导入新的sasac数据-20200914更新 ###

#### 1 查看当前的 data directory: ####

select * from dba_directories;
    owner | directory_name | directory_path 
    SYS	    DATADUMP	     C:\Data\sas\Datadump_ORACLE

PS：可以使用drop命令来删除这些路径

sql> drop directory datadump；

然后把文件 cqj\_0821.dmp 拷贝到该目录下

#### 2 使用导入命令，listener 出错 ####

ORA-12514:TNS:监听程序当前无法识别连接描述符中请求的服务

找到listener.ora监听文件，具体位置：D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN\\listener.ora  
  
修改listener.ora , 并重新启动 listener

SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = CLRExtProc)
          (ORACLE_HOME = C:\app\joniu\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (ENVS = "EXTPROC_DLLS=ONLY:C:\app\joniu\product\11.2.0\dbhome_1\bin\oraclr11.dll")
        )
    	(SID_DESC =
          (SID_NAME = orcl)
          (ORACLE_HOME = C:\app\joniu\product\11.2.0\dbhome_1)
        )
      )

lsnrctl stop / start / status

#### 3 使用导入命令，schema出错 ####

#### 4 调整导入命令 ####

查看表空间名称

select  username,default_tablespace from user_users;
    
    CQJ	DATA_SPACE_CQJ

修改导入命令：

impdp cqj/cqj@localhost:1521/orcl directory=datadump dumpfile=cqj_0821.dmp remap_schema=TESTPROENV:CQJ remap_tablespace=TESTPROENV:DATA_SPACE_CQJ transform=segment_attributes:n table_exists_action=replace logfile=cqj0821.log

localhost 需要修改为主机名 xxxxx-cn