目录
1. 创建复制文件脚本
获取控制文件/数据文件/日志文件/参数文件/口令文件脚本
保存为getbackupsql.sql
set feedback off;
set heading off;
set pagesize 0;
set trimspool on;
spool /app/oracle/orclbackup/colbak.sql;
select 'shutdown immediate' from dual
union all
select 'host cp '||name||' /usr/oabak/temp/' from v$controlfile
union all
select 'host cp '||name||' /usr/oabak/temp/' from v$datafile
union all
select 'host cp '||file_name||' /usr/oabak/temp/' from dba_temp_files
union all
select 'host cp '||member||' /usr/oabak/temp/' from v$logfile
union all
select 'host cp $ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora /usr/oabak/temp/'
from dual
union all
select 'host cp $ORACLE_HOME/dbs/orapw${ORACLE_SID} /usr/oabak/temp/'
from dual
union all
select 'startup' from dual;
spool off;
@/app/oracle/orclbackup/colbak.sql;
quit
2. 创建自动运行备份脚本
vi autobackup.sh
#!/bin/bash
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/11g
export ORACLE_SID=test
export LANG=en
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
sqlplus "/as sysdba" @/app/oracle/orclbackup/getbackupsql.sql
3. 创建备份定时任务
crontab -e
56 11 * * * /usr/data_back/autobackup.sh
4. 创建自动压缩和删除备份文件
vi TarDelBackup.sh
#!/bin/bash
date=`date +"%Y%m%d"`
tar -czPvf /usr/bak/orcl/orcl_$date.tar.gz -C /usr/bak/ temp
rm -rf /usr/bak/temp/*
#find /usr/data_backup/temp/ -mtime +3 -name "*.*" -exec rm {} \;
压缩脚本注意事项
tar -czPvf /usr/oabak/metenoa/metenoa_$date.tar.gz -C /usr/oabak/ temp
参数z , 压缩脚本可以用unzip解压
-c 表示创建压缩文件
-C 表示只取后边文件夹
-C /usr/oabak/ temp 后边备份文件夹要空格隔开
-P 参数表示使用绝对路径
5. 创建定时压缩任务
crontab -e
56 11 * * * /usr/data_backup/TarDelBackup.sh
6. 创建恢复脚本
set feedback off;
set heading off;
set pagesize 0;
set trimspool on;
spool /home/oracle/restoreSQL.sql;
select 'host cp /u04/colbak/backup/'||substr(name,(instr(name,'/',-1)+1))||
' '||name from v$controlfile
union all
select 'host cp /u04/colbak/backup/'||substr(name,(instr(name,'/',-1)+1))||
' '||name from v$datafile
union all
select 'host cp /u04/colbak/backup/'||substr(member,(instr(member,'/',-1)+1))
||' '||member from v$logfile
union all
select 'host cp /u04/colbak/backup/spfile${ORACLE_SID}.ora $ORACLE_HOME/dbs/'
from dual
union all
select 'host cp /u04/colbak/backup/orapw${ORACLE_SID} $ORACLE_HOME/dbs/'
spool off;