Oracle冷熱備份恢複的方法

資料匯出指令碼
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 僅恢複資料檔案的指令碼