windows环境oracle备份和还原脚本
windows环境oracle备份和还原脚本
1.1 登录
??管理员登录 非本机:
????sqlplus system/[email protected]:1521/orcl
??管理员登录 本机:
????sqlplus system/[email protected]
1.2 创建一个dmp的目录
??create directory dpdata1 as ‘E:\temp\dmp’;
??查询是否有此目录:
????select * from dba_directories;
?&emsp同时在E盘下新建E:\temp\dmp目录
1.3 赋于要导出数据表的所属用户权限
??grant read,write on directory dpdata1 to 用户名;
??示例:grant read,write on directory dpdata1 to TEST_ADMIN;
1.4 数据备份
??expdp system/密码@orcl directory=dpdata1 dumpfile=文件名.dmp logfile=文件名.log schemas=用户名
??示例:expdp system/[email protected] directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN
??示例:expdp system/[email protected] directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN
??查看下目录E:\temp\dmp下面可以看到备份文件,备份完成:
1.5 导入还原数据
??导入还原数据,输入 impdp system/密码@orcl directory=dpdata1 dumpfile=dmp文件名 logfile=log文件名 schemas=用户名;
??示例:impdp system/[email protected] directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN
??示例:impdp system/[email protected]:1521/orcl directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN
2.1 备份脚本
@echo off
set backupfile=f_database_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp
set logfile=f_database_%date:~0,4%-%date:~5,2%-%date:~8,2%.log
delete 1days files
forfiles /p "E:\temp\dmp" /d -1 /c "cmd /c echo deleting @file ... && del /f @path"
cd F:\temp\dmp
backup schemas
set backupfile=TEST_ADMIN_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp
set logfile=TEST_ADMIN_%date:~0,4%-%date:~5,2%-%date:~8,2%.log
expdp TEST_ADMIN/TEST_ADMIN@orcl directory=dpdata1 dumpfile=%backupfile% logfile=%logfile% schemas=TEST_ADMIN parallel=4
2.2 还原脚本
@echo off
set backupfile=TEST_ADMIN_2020-09-01.dmp
set logfile=TEST_ADMIN_2020-09-01.log
impdp TEST_ADMIN/TEST_ADMIN@orcl directory=dpdata1 dumpfile=%backupfile% logfile=%logfile% schemas=TEST_ADMIN parallel=4
2.3 定时备份脚本
@ECHO OFF
schtasks /create /tn "TEST_ADMIN定时任务" /sc daily /st 10:29:00 /tr "E:\temp\bf.bat"
PAUSE
EXIT
windows环境oracle备份和还原脚本相关教程