数据库导入导出脚本

数据泵导入(导入到指定表空间)
impdp czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=CZCK201511.DMP REMAP_SCHEMA=fxgl:fxgl REMAP_TABLESPACE='(zhzs_portal:czck,fxgl:czck)' 
<< REMAP_TABLESPACE='(USERS1:FXGL,user2:fxgl)' >>


impdp a6/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=a60115.DMP REMAP_SCHEMA=a6:a6 REMAP_TABLESPACE=a6:czck transform=oid:n


impdp czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=czck0115.DMP REMAP_SCHEMA=czck:czck REMAP_TABLESPACE=czck:czck 


impdp jzb_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0225.DMP REMAP_SCHEMA=ts_fxgl:jzb_fxgl REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck (基准版)


impdp jzb_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=TS_CZCK0225.DMP REMAP_SCHEMA=ts_czck:jzb_czck REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck (基准版)
 
impdp jz_gs/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_2015-08-26.DMP REMAP_SCHEMA=a6:jz_gs,a6_jz:jz_gs,fxgl:jz_gs REMAP_TABLESPACE=USERS:JZ_GS,FXGL:JZ_GS(186焦作国税)


impdp jz_ds/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20150826.DMP REMAP_SCHEMA=fxgl:jz_ds REMAP_TABLESPACE=USERS:JZ_DS,FXGL:JZ_DS(186焦作地税)


impdp jz_a6/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP REMAP_SCHEMA=a6:jz_a6,a6_jz:jz_a6 REMAP_TABLESPACE=USERS:JZ_CZCK,CZCK:JZ_CZCK,FXGL:JZ_CZCK(186焦作财政)


impdp jz_cz/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=czck0206.DMP REMAP_SCHEMA=czck:jz_cz,czck_jz:jz_czck REMAP_TABLESPACE=CZCK:JZ_CZCK,FXGL:JZ_CZCK,DSCK:JZ_CZCK(186焦作财政)


impdp jz_cspt/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=CSPT1029.DMP REMAP_SCHEMA=cspt:jz_cspt REMAP_TABLESPACE=USERS:JZ_CZCK,CZCK:JZ_CZCK,FXGL:JZ_CZCK,DSCK:JZ_CZCK,SP:JZ_CZCK(焦作财政)


impdp fxgl_product/1@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0226.DMP REMAP_SCHEMA=fxgl_product:fxgl_product REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck


impdp ts_fxgl_16/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20160227.DMP REMAP_SCHEMA=fxgl:ts_fxgl_16,czck:ts_fxgl_16 REMAP_TABLESPACE=czck:ts_fxgl_16,fxgl:ts_fxgl_16(16年唐山)


impdp ts_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_CZCK0225.DMP REMAP_TABLESPACE=ts_czck:jz_gs,ts_fxgl:jz_gs transform=oid:n(原基准版)


impdp ts_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0225.DMP REMAP_TABLESPACE=ts_czck:czck,ts_fxgl:czck transform=oid:n(原基准版)


impdp da_a6/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=a60115.DMP REMAP_SCHEMA=a6:da_a6 REMAP_TABLESPACE=a6:jz_gs,czck:jz_gs transform=oid:n(德安环境)


impdp da_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=czck0115.DMP REMAP_SCHEMA=czck:da_czck REMAP_TABLESPACE=czck:jz_gs transform=oid:n(德安环境)


IMPDP cy_a6/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE=a6_20160305.dmp REMAP_SCHEMA=a6:cy_a6 transform=oid:n (51朝阳环境3.10)
IMPDP  cy_czck/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= czck_20160305.dmp REMAP_SCHEMA=czck:cy_czck REMAP_TABLESPACE=dsck:czck,TBS_YY_IDX:czck transform=oid:n(51朝阳环境3.10)
IMPDP a6_cy/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= a6_cy_20160305.dmp transform=oid:n(51朝阳环境3.10)
IMPDP db_dw/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= db_dw_20160308.dmp transform=oid:n(51朝阳环境3.10)


impdp eeds_fxgl02/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20160311.DMP REMAP_SCHEMA=eeds_fxgl01:eeds_fxgl02 REMAP_TABLESPACE=EEDS_FXGL01:eeds_fxgl02 transform=oid:n




impdp bq82/1@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=BQ82_20160316.DMP REMAP_SCHEMA=bq82_erds:bq82,bq82_eeds:bq82  transform=oid:n


impdp jz_a6/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n(186焦作财政)(表空间jz_czck_a6)
impdp jz_cz/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n(jz_czck)
impdp jz_cspt/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n(jz_cspt)


impdp jj_a6/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=A60309.DMP REMAP_SCHEMA=a6:jj_a6 REMAP_TABLESPACE=a6:jj_a6,czck:jj_a6 transform=oid:n
impdp jj_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=CZCK0309.DMP REMAP_SCHEMA=czck:jj_czck REMAP_TABLESPACE=a6:jj_czck,czck:jj_czck transform=oid:n


impdp dan_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=jzb_fxgl20160321.DMP REMAP_SCHEMA=jzb_fxgl:dan_fxgl REMAP_TABLESPACE=jzb_czck:dan (德安新版)


impdp dan_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=jzb_czck20160321.DMP REMAP_SCHEMA=jzb_czck:dan_czck REMAP_TABLESPACE=jzb_czck:dan (德安新版)
 


impdp jzb_czck/oracle@orcl1  DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160328).DMP REMAP_SCHEMA=jzb_czck:jzb_czck REMAP_TABLESPACE=jzb_czck:jzb_czck,jz_gs:jzb_czck transform=oid:n


impdp jzb_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160405).DMP  REMAP_TABLESPACE=jzb_czck:czck transform=oid:n(九江县)
impdp jzb_fxgl/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_FXGL20160323.DMP  REMAP_TABLESPACE=jzb_czck:czck transform=oid:n(九江县)


impdp fxgl/oracle@gqczhzs  DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL20160407.DMP transform=oid:n(共青)




impdp jzb_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_FXGL20160323.DMP REMAP_SCHEMA=a6:jzb_fxgl,fxgl:jzb_fxgl REMAP_TABLESPACE=jzb_fxgl:czck,jzb_czck:czck 


impdp jzb_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160405).DMP  REMAP_TABLESPACE=jzb_czck:czck 
impdp jzb_czck/oracle@orcl  DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK0328.DMP REMAP_SCHEMA=jzb_czck:jzb_czck REMAP_TABLESPACE=jzb_czck:czck version=11.2.0.1.0






数据导入:
imp fxgl/oracle@orcl file=D:\app\zhudong\admin\orcl\dpdump\fxgl.DMP full=y log=D:\app\zhudong\admin\orcl\dpdump\import.log


imp jt_a6/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\a6.dmp fromuser=a6 touser=jt_a6 (51九台环境3.10)
imp jt_czck/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\czck.dmp fromuser=czck touser=jt_czck(51九台环境3.10)
imp jt_sjcj/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\sjcj.dmp fromuser=sjcj touser=jt_sjcj(51九台环境3.10)




imp bq82/1@orcl file=D:bq820160316.DMP full=y






select * from sys_usermanage where user_id='sa' for update 




数据泵导出
Expdp system/manager@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=Y ' logfile=日志名字.log


Expdp fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=Y ' logfile=日志名字.log
(去掉full=y是备份指定用户)


Expdp ts_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=ts_fxgl0225.dmp
Expdp ts_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=ts_czck0225.dmp


Expdp jz_cz/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_cz0316.dmp
Expdp jz_cspt/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_cspt0316.dmp
Expdp jz_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_a60316.dmp


Expdp fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=fxgl20160407.dmp(共青)


1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
  exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
  exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
  exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) 


  exp fxgl/oracle@gqczhzs file=d:\table.dmp table=uq_blob
  imp a6/oracle@gqczhzs file=d:\table.dmp fromuser=fxgl touser=a6







1、提供了oracle数据库备份脚本导出dmp文件 2、定时任务由操作系统提供的支持,在操作系统中设置定时任务,由操作系统定期执行脚本 3、补充。压缩包脚本有点问题,不能删除旧的备份文件。需要自动删除历史备份文件请用最新脚本: @echo off @echo ================================================ @echo windows环境下Oracle数据库的自动备份脚本 @echo 说明:启动备份时,需要配置以下变量 @echo 1、BACKUP_DIR 指定要备份到哪个目录 @echo 2、ORACLE_USERNAME 指定备份所用的Oracle用户名 @echo 3、ORACLE_PASSWORD 指定备份所用的Oracle密码 @echo 4、ORACLE_DB 指定备份所用的Oracle连接名 @echo 5、BACK_OPTION 备份选项,可以为空,可以为full=y,可以为owner=a用户,b用户 等等.... @echo 6、RAR_CMD 指定RAR命令行压缩工具所在目录 @echo ================================================ rem 以下变量需要根据实际情况配置 set BACKUP_DIR=D:\tools\oracle_bak\backup230 set ORACLE_USERNAME=PLATADMIN set ORACLE_PASSWORD=123456 set ORACLE_DB=ORCL set BACK_OPTION=owner=PLATADMIN set RAR_CMD="D:/Program Files/WinRAR/Rar.exe" rem for /f "tokens=1,2" %%a in ('date/t') do set TODAY=%%a rem set BACK_NAME=%ORACLE_DB%_%TODAY%(%time:~0,2%时%time:~3,2%分)_ set BACK_NAME=%Date:~0,4%%Date:~5,2%%Date:~8,2%%Time:~0,2%%Time:~3,2%%Time:~6,2% set BACK_FULL_NAME=%BACKUP_DIR%/%BACK_NAME% rem 开始备份 exp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@%ORACLE_DB% %BACK_OPTION% file="%BACK_FULL_NAME%.dmp" log="%BACK_FULL_NAME%exp.log" rem 压缩并删除原有文件 %RAR_CMD% a -df "%BACK_FULL_NAME%back.rar" "%BACK_FULL_NAME%.dmp" "%BACK_FULL_NAME%exp.log" rem 删除15天前的备份文件 forfiles /p %BACKUP_DIR% /s /m *.rar /d -15 /c "cmd /c del @file"
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值