ITPub博客

首页 > 数据库 > Oracle > oracle数据库手工冷热备脚本

oracle数据库手工冷热备脚本

原创 Oracle 作者:Toooooooo晓旭 时间:2015-07-17 23:43:24 0 删除 编辑

虽然现在的大部分生产库都是在用RMAN或者第三方软件来进行备份。但是练习手工冷热备让你明白RMAN或者第三方软件具体都做了一些什么。


手工冷备脚本

set echo off trimspool  off heading off  feedback off verify off time off
set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/cold_bak';                        //自己的备份路径                                                
define bakscp='/home/oracle/prod_bak/cold_cmd.sql';               //他会产生一个CMD脚本
spool &bakscp                                                                                                                      
select 'host cp '||name||' &bakdir' from v$datafile;
select 'host cp '||name||' &bakdir' from v$controlfile;                                                                            
spool off
shutdown immediate
@&bakscp
startup


手工热备脚本

必须在归档日志的前提下,否则报错

set pagesize 0 linesize 200
define bakdir='/home/oracle/prod_bak/hot_bak';          //设置备份路径
define bakscp='/home/oracle/prod_bak/hot_cmd.sql';   //会产生一个CMD文件
set serveroutput on
spool &bakscp                                                                                                                       
prompt alter system switch logfile;;
declare                                                                                                                             
 cursor cu_tablespace is
   select tablespace_name from dba_tablespaces where contents not like '%TEMP%' and status='ONLINE';                                
 cursor cu_datafile(name varchar2) is                                                                                               
   select file_name from dba_data_files where tablespace_name=name;
begin                                                                                                                              
for i in cu_tablespace loop                                                                                                         
  dbms_output.put_line('alter tablespace '||i.tablespace_name||' begin backup;');
   for j in cu_datafile(i.tablespace_name) loop                                                                                     
    dbms_output.put_line('host cp '||j.file_name||' &bakdir');
   end loop;
  dbms_output.put_line('alter tablespace '||i.tablespace_name||' end backup;');                                                    
end loop;
 dbms_output.put_line('alter database backup controlfile to trace;');
 dbms_output.put_line('alter database backup controlfile to ''&bakdir/control01.ctl'' reuse;');
end;
/
prompt alter system switch logfile;;
spool off
@&bakscp

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30134188/viewspace-1736776/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-02-09

  • 博文量
    5
  • 访问量
    11790