Windows系统下Oracle数据库冷备

浏览数：25 / 时间：2015年06月12日

一、背景：

　　具体的场景是数据库不是普通的OLTP系统，更像是OLAP系统，数据的更新频率很低，在noarchivelog 模式下运行，实时性要求低，但是数据只有一份不能弄丢，需要应付磁盘损坏等情况。这应该是冷备的一个比较好的应用场景。

二、规划：

　　将数据库服务器多挂载一块磁盘。查出Oracle datafile,controlfile,spfile,redologfile,tempfile的具体位置，拼凑出bat命令，在sqlplus上执行。通过windows的任务计划定时调度。

三、详细步骤：

　　1.创建sql脚本：

set feedback off
set heading off
set verify off
set trimspool off
set pagesize 0
set linesize 200
define dir = ‘G:\oraclebackup\InstanceNames\LUOYI\coolback‘
define script = ‘G:\oraclebackup\InstanceNames\LUOYI\coolback\coolbak.sql‘
spool &script
select ‘ho copy ‘ || name || ‘ &dir‘ from v$controlfile
union all
select ‘ho copy ‘ || name || ‘ &dir‘ from v$datafile
union all
select ‘ho copy ‘ || member || ‘ &dir‘  from v$logfile
union all
select ‘ho copy ‘ || name || ‘ &dir‘ from v$tempfile
/
create pfile = ‘&dir/initLUOYI.ora‘ from spfile;
spool off
shutdown immediate
start &script
startup
exit
/

　　2.创建批处理脚本：

@echo *********************** >>G:\oraclebackup\InstanceNames\cool_back.log
@echo LUOYI backup is start  >>G:\oraclebackup\InstanceNames\cool_back.log
@echo %date%  >>G:\oraclebackup\InstanceNames\cool_back.log
@echo %time%  >>G:\oraclebackup\InstanceNames\cool_back.log
@echo *********************** >>G:\oraclebackup\InstanceNames\cool_back.log

set ORACLE_SID=LUOYI
del /S /Q G:\oraclebackup\InstanceNames\LUOYI\coolback
sqlplus "/as sysdba" @G:\oraclebackup\InstanceNames\LUOYI\LUOYI_cool_backup.sql >>G:\oraclebackup\InstanceNames\cool_back.log

@echo *********************** >>G:\oraclebackup\InstanceNames\cool_back.log
@echo LUOYI backup is finished >>G:\oraclebackup\InstanceNames\cool_back.log
@echo %date%  >>G:\oraclebackup\InstanceNames\cool_back.log
@echo %time%  >>G:\oraclebackup\InstanceNames\cool_back.log
@echo *********************** >>G:\oraclebackup\InstanceNames\cool_back.log

@echo ------------------------------------------------------------------------