一、以exp命令备份
1.首先做个批处理脚本:backup.bat 脚本内容如下
@echo off
set curdate=%date:~0,4%%date:~5,2%%date:~8,2%
set backuppath=D:\backup
md %backuppath%
exp scott/tiger@127.0.0.1/orcl file=%backuppath%\%curdate%.dmp owner(scott) log=%backuppath%\%curdate%.log
forfiles /p "D:\backup" /s /m *.dmp /d -30 /c "cmd /c del @file"
set curdate=%date:~0,4%%date:~5,2%%date:~8,2%
set backuppath=D:\backup
md %backuppath%
exp scott/tiger@127.0.0.1/orcl file=%backuppath%\%curdate%.dmp owner(scott) log=%backuppath%\%curdate%.log
forfiles /p "D:\backup" /s /m *.dmp /d -30 /c "cmd /c del @file"
说明:在备份之前先在D盘建个backup文件夹.
2.以Windows中的计划任务程序来触发这个备份脚本(以Win7为例)
(1)
完成配置,这个自动备份脚本会在每天10:43的时候执行,将数据备份到D:\backup文件夹下 格式为:20151212.dmp
2.导入dmp文件的命令
spool log.txt;
drop user scott cascade;
drop tablespace SCOTT including contents and datafiles;
create tablespace SCOTT logging
datafile 'D:\orcl\SCOTT .dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create user scott identified by tiger default tablespace SCOTT;
grant connect,resource to scott;
grant dba to scott;
spool off;
exit;
drop user scott cascade;
drop tablespace SCOTT including contents and datafiles;
create tablespace SCOTT logging
datafile 'D:\orcl\SCOTT .dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create user scott identified by tiger default tablespace SCOTT;
grant connect,resource to scott;
grant dba to scott;
spool off;
exit;
在dos窗口输入命名导入数据文件
imp scott/tiger@ORCL file=20151212.dmp full=Y
二、以expdp命名备份脚本如下
create directory mydmp as 'D:\backup' --directory指定导出到本地目录
--授权给操作用户这个mydmp目录的权限
grant read, write on directory mydmp to scott--需要超级管理员的账号 sysdba
--查看一下是否存在该目录
select * from dba_directories;
--授权给操作用户这个mydmp目录的权限
grant read, write on directory mydmp to scott--需要超级管理员的账号 sysdba
--查看一下是否存在该目录
select * from dba_directories;
执行脚本如下
@echo off
rem set backupfile=%date:~0,4%%date:~5,2%%date:~8,2%.dmp
rem set logfile=%date:~0,4%%date:~5,2%%date:~8,2%.log
rem delete 30days filesforfiles /p "D:\backup" /d -30 /c "cmd /c
echo deleting @file ... && del /f @path"cd D:\backup
rem backup schemas
set backupfile=%date:~0,4%%date:~5,2%%date:~8,2%.dmp
set logfile=%date:~0,4%%date:~5,2%%date:~8,2%.log
expdp scott/tiger directory=mydmp dumpfile=%backupfile% logfile=%logfile% schemas=scott parallel=4
用expdp导出的必须要用impdp命令导入
rem set backupfile=%date:~0,4%%date:~5,2%%date:~8,2%.dmp
rem set logfile=%date:~0,4%%date:~5,2%%date:~8,2%.log
rem delete 30days filesforfiles /p "D:\backup" /d -30 /c "cmd /c
echo deleting @file ... && del /f @path"cd D:\backup
rem backup schemas
set backupfile=%date:~0,4%%date:~5,2%%date:~8,2%.dmp
set logfile=%date:~0,4%%date:~5,2%%date:~8,2%.log
expdp scott/tiger directory=mydmp dumpfile=%backupfile% logfile=%logfile% schemas=scott parallel=4
用expdp导出的必须要用impdp命令导入
impdp scott/tiger DIRECTORY=mydmp DUMPFILE=20151212.DMP SCHEMAS=SCOTT
后记:
用expdp也会把空表也导出,非常好用。