windows环境oracle备份和还原脚本

windows环境oracle备份和还原脚本

1.命令行备份还原

1.1 登录

  管理员登录 非本机：

    sqlplus system/OracleManager1@10.1.16.97:1521/orcl

  管理员登录 本机：

    sqlplus system/OracleManager1@orcl

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/OracleManager1@orcl directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN

  示例：expdp system/OracleManager1@orcl 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/OracleManager1@orcl directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN

  示例：impdp system/OracleManager1@10.1.16.97:1521/orcl directory=dpdata1 dumpfile=test.dmp logfile=test.log schemas=TEST_ADMIN

2 脚本备份还原

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