oracle脚本热备,oracle数据库手工冷热备脚本

虽然现在的大部分生产库都是在用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