Oralce备份/恢复脚本

非归档/归档模式–冷备
脚本定时运行:
为了能每天定时备份，我们可以做个定时任务,同时输出Log.
[oracle@DBServer ~]$ crontab -e
01 04 * * * /orabak/bakscript/col_backup.sh >> /home/oracle/backup_$(date +%Y_%m_%d-%H:%M:%S).log

OS备份脚本
/orabak/bakscript/col_backup.sh
——————————————————
#!/bin/bash
# oracle auto backup
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
export ORACLE_SID=orcl
export LD_LIBRARY=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin

sqlplus sys/oracledb11g as sysdba<<EOF
@/orabak/bakscript/col_backup1.sql;
quit;
EOF
—————————————————————

数据库备份脚本
/orabak/bakscript/col_backup1.sql
—————————————————————
set heading off;
set feedback off;
set pagesize 0;
set trimspool on;
spool /orabak/bakscript/col_backup2.sql;
select ‘shutdown immediate’ from dual
union all
select ‘host cp -v ‘||name||’ /orabak/backup/’ from v$datafile
union all
select ‘host cp -v ‘||name||’ /orabak/backup/’ from v$controlfile
union all
select ‘host cp -v ‘||member||’ /orabak/backup/’ from v$logfile
union all
select ‘host cp -v ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora /orabak/backup/’ from dual
union all
select ‘host cp -v ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} /orabak/backup/’ from dual
union all
select ‘startup’ from dual;
spool off;
@/orabak/bakscript/col_backup2.sql
—————————————————————

恢复脚本
/orabak/bakscript/col_restore1.sql
—————————————————————
set heading off;
set feedback off;
set pagesize 0;
set trimspool on;
spool /orabak/bakscript/col_restore2.sql;
select ‘host cp -v /orabak/backup’||substr(name,instr(name,’/’,-1))||’ ‘||name from v$controlfile
union all
select ‘host cp -v /orabak/backup’||substr(name,instr(name,’/’,-1))||’ ‘||name from v$datafile
union all
select ‘host cp -v /orabak/backup’||substr(member,instr(member,’/’,-1))||’ ‘||member from v$logfile
union all
select ‘host cp -v /orabak/backup/spfile${ORACLE_SID}.ora ${ORACLE_HOME}/dbs’ from dual
union all
select ‘host cp -v /orabak/backup/orapw${ORACLE_SID} ${ORACLE_HOME}/dbs’ from dual;
spool off;
—————————————————————

归档模式下热备
/orabak/bakscript/hot_backup1.sql 热备脚本–整库
—————————————————————
set heading off;
set feedback off;
set pagesize 0;
set trimspool on;
spool /orabak/bakscript/hot_backup2.sql;
select ‘alter database begin backup’ from dual
union all
select ‘host cp -v ‘||name||’ /orabak/backup/’ from v$datafile
union all
select ‘alter database backup controlfile to /orabak/backup/ctl.bak’ from dual
union all
select ‘alter database end backup’ from dual;
spool off;
—————————————————————

hot_backup3.sql –热备脚本,逐个表空间进行,不备份临时表空间
—————————————————————
set serveroutput on;
set heading off;
set feedback off;
set pagesize 0;
set trimspool on;
spool /orabak/bakscript/hot_backup3.sql;
declare
cursor ts_cursor is select tablespace_name from dba_tablespaces where CONTENTS<>’TEMPORARY’;
cursor dbf_cursor(ts_name varchar2) is select file_name from dba_data_files where tablespace_name=ts_name;
command_text varchar2(4000);
begin
for t in ts_cursor loop
dbms_output.put_line(‘alter system switch logfile;’);
dbms_output.put_line(‘alter tablespace ‘||t.tablespace_name||’ begin backup;’);
for f in dbf_cursor(t.tablespace_name) loop
dbms_output.put_line(‘host cp -v ‘||f.file_name||’ /orabak/backup/’);
end loop;
dbms_output.put_line(‘alter tablespace ‘||t.tablespace_name||’ end backup;’);
dbms_output.put_line(chr(13));
end loop;
dbms_output.put_line(‘alter database backup controlfile to ”/orabak/backup/ctl.bak”;’);
dbms_output.put_line(‘alter database backup controlfile to trace as ”/orabak/backup/ctl.sql”;’);
end;
/
spool off;
@/orabak/bakscript/hot_backup3.sql;
—————————————————————