开发、测试和演示的数据库可以使用逻辑备份。
首先，得到所有非系统用户,然后进行逻辑备份，最后删除过期的数据。
配合Windows任务调度，可以实现定期备份。

backup.bat

rem 设置备份路径

set backupdir=c:\tmp\

rem 获取所有用户的列表

@echo off

@echo set echo off         >getUsers.sql

@echo set feedback off    >>getUsers.sql

@echo set newpage none    >>getUsers.sql

@echo set pagesize 5000   >>getUsers.sql

@echo set linesize 500    >>getUsers.sql

@echo set verify off      >>getUsers.sql

@echo set pagesize 0      >>getUsers.sql

@echo set term off        >>getUsers.sql

@echo set trims on        >>getUsers.sql

@echo set linesize 600    >>getUsers.sql

@echo set heading off >>getUsers.sql

@echo set timing off   >>getUsers.sql

@echo set verify off   >>getUsers.sql

@echo set numwidth 38   >>getUsers.sql

@echo spool users.txt   >>getUsers.sql

@echo select username from dba_users where account_status='OPEN' and username not in('SYSMAN','DBSNMP','SYSTEM','SYS','MGMT_VIEW'); >>getUsers.sql

@echo spool off           >>getUsers.sql

@echo exit                >>getUsers.sql

sqlplus / as sysdba @getUsers.sql

rem 备份

for /f %%i in (users.txt) do (

    exp userid='/ as sysdba' owner=%%i file=%backupdir%%%i_%date:~0,4%%date:~5,2%%date:~8,2%.dmp log=%backupdir%%%i_%date:~0,4%%date:~5,2%%date:~8,2%.log

)

rem 删除7天之前的备份

forfiles /P %backupdir% /D -7 /S /M * /C "cmd /c if @isdir==FALSE echo deleting @file>>%backupdir%log.txt && del /q @file"

rem 清理

del getUsers.sql

del users.txt

@echo on

ITPub博客

使用Windows脚本进行逻辑备份

最新文章