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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23185328/viewspace-705580/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23185328/viewspace-705580/