数据导出脚本
expdata.bat
set ORA_CONN=exp Oracle_UserNameA/Oracle_UserAPwd@OracleTnsLinkName full=y
set NOW_DATE=%date:~4,10%
set NOW_DAY=%date:~12,10%
rd %NOW_DATE%_complete/q/s
md %NOW_DATE%_complete
cd %NOW_DATE%_complete
%ORA_CONN% file=(%NOW_DATE%01.dmp,%NOW_DATE%02.dmp,%NOW_DATE%03.dmp,%NOW_DATE%04.dmp,%NOW_DATE%05.dmp,%NOW_DATE%06.dmp,%NOW_DATE%07.dmp,%NOW_DATE%08.dmp,%NOW_DATE%09.dmp,%NOW_DATE%10.dmp,%NOW_DATE%11.dmp,%NOW_DATE%12.dmp,%NOW_DATE%13.dmp,%NOW_DATE%14.dmp,%NOW_DATE%15.dmp,%NOW_DATE%16.dmp,%NOW_DATE%17.dmp,%NOW_DATE%18.dmp,%NOW_DATE%19.dmp,%NOW_DATE%20.dmp,%NOW_DATE%21.dmp,%NOW_DATE%22.dmp,%NOW_DATE%23.dmp,%NOW_DATE%24.dmp,%NOW_DATE%25.dmp,%NOW_DATE%26.dmp,%NOW_DATE%27.dmp,%NOW_DATE%28.dmp,%NOW_DATE%29.dmp,%NOW_DATE%30.dmp,%NOW_DATE%31.dmp,%NOW_DATE%32.dmp,%NOW_DATE%33.dmp,%NOW_DATE%34.dmp,%NOW_DATE%35.dmp,%NOW_DATE%36.dmp,%NOW_DATE%37.dmp,%NOW_DATE%38.dmp,%NOW_DATE%39.dmp,%NOW_DATE%40.dmp,%NOW_DATE%41.dmp,%NOW_DATE%42.dmp,%NOW_DATE%43.dmp,%NOW_DATE%44.dmp,%NOW_DATE%45.dmp,%NOW_DATE%46.dmp,%NOW_DATE%47.dmp,%NOW_DATE%48.dmp,%NOW_DATE%49.dmp) log=exp%NOW_DATE%.log FILESIZE=600M
cd ..
数据备份脚本
impdata.bat
imp system/SytemPwdl@OracleTnsLinkName fromuser=Oracle_UserNameA touser=Oracle_UserNameB file=(2005-10-2101.dmp,2005-10-2102.dmp,2005-10-2103.dmp,2005-10-2104.dmp,2005-10-2105.dmp,2005-10-2106.dmp,2005-10-2107.dmp,2005-10-2108.dmp,2005-10-2109.dmp,2005-10-2110.dmp) ignore=y log=imp200510.log compile=y
冷备份与恢复代码生成脚本
使用connect system/systempassword@OracleTnsLinkName as sysdba
set feedback off heading off verify off timespool off set pagesize 0 linesize 200
define dir = 'e:\temp\'
define bak = 'e:\temp\closed_backup_commands.sql'
define rcv = 'e:\temp\closed_recover_commands.bat'
define rcvnoredolog = 'e:\temp\closed_recover_commands_noredolog.bat'
prompt *** spooling to &bak
spool &bak
select 'host copy '||name||' &dir' from v$datafile order by 1;
select 'host copy '||member||' &dir' from v$logfile order by 1;
select 'host copy '||name||' &dir' from v$controlfile order by 1;
select 'host copy '||name||' &dir' from v$tempfile order by 1;
spool off;
spool &rcv
select 'copy &dir'||substr(name,instr(name,'\',-1) + 1)||' '||substr(name,1,instr(name,'\',-1)) from v$datafile order by 1;
select 'copy &dir'||substr(member,instr(member,'\',-1) + 1)||' '||substr(member,1,instr(member,'\',-1)) from v$logfile order by 1;
select 'copy &dir'||substr(name,instr(name,'\',-1) + 1)||' '||substr(name,1,instr(name,'\',-1)) from v$controlfile order by 1;
select 'copy &dir'||substr(name,instr(name,'\',-1) + 1)||' '||substr(name,1,instr(name,'\',-1)) from v$tempfile order by 1;
spool off;
spool &rcvnoredolog
select 'copy &dir'||substr(name,instr(name,'\',-1) + 1)||' '||substr(name,1,instr(name,'\',-1)) from v$datafile order by 1;
select 'copy &dir'||substr(name,instr(name,'\',-1) + 1)||' '||substr(name,1,instr(name,'\',-1)) from v$tempfile order by 1;
spool off;
使用connect sys/syspassword@OracleTnsLinkName as sysoper
shutdown immediate;
@e:\temp\closed_backup_commands.sql;
startup;
生成的文件如下
一下前提都是先用sys账号shutdown immdiate数据库，执行完成后使用startup启动数据库
closed_backup_commands.sql 冷备份数据文件、重做日志文件、控制文件的脚本
closed_recover_commands.bat 连数据文件、重做日志文件、控制文件一起恢复的脚本
closed_recover_commands_noredolog.bat 仅恢复数据文件的脚本