简单的用户管理模式下冷备&热备脚本

cold_backup.cmd：

rem /Ss删除 /Q不提示
RMDIR /S /Q D:\backup_dir\
rem 创建一个备份的目录
MKDIR D:\backup_dir\
rem 设置oracle_si环境变量
set oracle_sid=ymh
rem 关闭显示
echo off
rem 用sys用户登陆并执行SQL脚本，@cold_backup，默认是.sql格式的
sqlplus "sys/ymh as sysdba" @cold_backup

cold_backup.sql

--去头
set heading off
--去尾 
set feedback off
--设置每行显示
set linesize 121
--不显示命令的执行结果
set termout off 
--生成备份的sql，并保存到d:\01.log
spool d:\01.log
--备份所有的数据文件
select '--'||ts.name tsname,'host copy '||df.name||' D:\backup_dir\'
   ||substr(df.name,instr(df.name,'\',-1) + 1)
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
--备份所有的控制文件
select 'host copy '||name||' D:\backup_dir\' from v$controlfile;
spool off
--生成恢复的sql，并保存到d:\02.log
spool d:\02.log
--生成恢复数据文件的脚本
select '--'||ts.name tsname,'host copy D:\backup_dir\'||substr(df.name,instr(df.name,'\',-1) + 1)||' '||df.name
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
--生成恢复控制文件的脚本
select 'host copy D:\backup_dir\'||substr(name,instr(name,'\',-1) + 1)||' '||name from v$controlfile;
spool off
--执行自动冷备份
--一致性关闭
shutdown immediate
--执行物理拷贝的脚本
@d:\01.log
--一打开
startup
--退出
exit

--------------------------------------------------------------------------------

hot_backup.cmd：

rem /Ss删除 /Q不提示
RMDIR /S /Q D:\backup_dir\
rem 创建一个备份的目录
MKDIR D:\backup_dir\
rem 设置oracle_si环境变量
set oracle_sid=ymh
rem 关闭显示
echo off
rem 用sys用户登陆并执行SQL脚本，@hot_backup，默认是.sql格式的
sqlplus "sys/ymh as sysdba" @hot_backup

hot_backup.sql：

--去头
set heading off 
--去尾
set feedback on
--设置每行显示
set linesize 121
--不显示命令的执行结果
set termout on
--打开输出
set serveroutput on
--让所有表空间进入begin backup：alter tablespace XXX begin backup
begin                                                                                    
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )   
loop                                                                                     
        dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' begin backup');  
        execute immediate 'alter tablespace ' || x.tablespace_name || ' begin backup';     
end loop;       
end;
/
set heading off 
set feedback off
set linesize 121
set termout off 
--生成备份的sql，并保存到d:\01.log
spool d:\01.log
--备份所有的数据文件
select '--'||ts.name tsname,'host copy '||df.name||' D:\backup_dir\'
   ||substr(df.name,instr(df.name,'\',-1) + 1)
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
--备份所有的控制文件
select 'host copy '||name||' D:\backup_dir\' from v$controlfile;
spool off
--生成恢复的sql，并保存到d:\02.log
spool d:\02.log
--生成恢复数据文件的脚本
select '--'||ts.name tsname,'host copy D:\backup_dir\'||substr(df.name,instr(df.name,'\',-1) + 1)||' '||df.name
from v$datafile df,v$tablespace ts
where ts.ts# not in (select distinct ts# from v$tempfile)
  and df.ts# = ts.ts#
  order by 1;
--生成恢复控制文件的脚本
select 'host copy D:\backup_dir\'||substr(name,instr(name,'\',-1) + 1)||' '||name from v$controlfile;
spool off
--执行自动热备份
@d:\01.log
set termout on
begin                                                                                   
for x in ( select tablespace_name from dba_tablespaces where contents <> 'TEMPORARY' )  
loop                                                                                    
        dbms_output.put_line( 'alter tablespace ' || x.tablespace_name || ' end backup');
        execute immediate 'alter tablespace ' || x.tablespace_name || ' end backup';    
end loop;                                                                               
end;
/
alter system archive log current;