Oracle 自动化备份脚本

Oracle 日常RMAN备份脚本，很基础。但是对于多个需要备份的Oracle数据库，可以很简单实施，并利于后期批量状态的查询。

1. 备份脚本，基于linux，windows环境需要适当修改。
   主脚本，会调用2,3步骤的rman.sql&status.sql

   $ more main.sh#set env#########################Change the below parameter for the different server##################export host_ip=172.16.32.115export instance_name=liangexport username=liangexport password=liangexport syspsw=oracleexport backup_home=/home/oracle/bkexport ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1export PATH=$PATH:$ORACLE_HOME/db_1/bin:/sbin:/usr/sbin#####################################################################################export curTime=$(date "+%Y%m%d")mkdir $backup_home/ftp/$curTimeecho "-----------------------------RMAN start-----------------------------";date#backup startcd $backup_home$ORACLE_HOME/bin/rman target sys/$syspsw@$host_ip:1521/$instance_name cmdfile='rman.sql'echo "------------------------------RMAN End------------------------------";datesleep 10echo "------------------------------SQL Start------------------------------";date$ORACLE_HOME/bin/sqlplus $username/$password@$host_ip:1521/$instance_name @status.sqlecho "------------------------------END-----------------------------";date

2. RMAN备份sql脚本

   [oracle@test bk]$ more rman.sqlrun{ALLOCATE CHANNEL node_c1 DEVICE TYPE DISK MAXPIECESIZE=5G;ALLOCATE CHANNEL node_c2 DEVICE TYPE DISK MAXPIECESIZE=5G;backup as compressed backupset database format 'C:\ftp\uploadfile\db_%U.bak_%T';sql 'alter system switch logfile';CROSSCHECK ARCHIVELOG ALL;backup as compressed backupset archivelog all format 'C:\ftp\uploadfile\archivelog_%d_%s_%p_%T' not backed up 2 times;backup spfile format 'C:\ftp\uploadfile\spfile_%U_%T';backup current controlfile format 'C:\ftp\uploadfile\controlfile_%d_%s_%p_%I_%u_%T';sql 'alter system switch logfile';CROSSCHECK BACKUP;CROSSCHECK COPY;delete noprompt archivelog all completed before 'sysdate-7';release channel node_c1;release channel node_c2;}

3. 查询备份状态的脚本,并将查询信息打印到日志，方便查询。

   $ more status.sql----每日归档产生量,可以判断数据库是否繁忙spool $backup_home/redo_switch.log;set echo offset feedback offset colsep ','set pagesize 2000set term offset heading offset line 400col Count for 9999col GB for 99999selectd.dbid,to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",substr((sum(blocks*block_size))/1024/1024/1024,0,4) as "GB"from v$archived_log,v$database dgroup by trunc(completion_time),d.dbid;spool off;---查询表空间使用率spool $backup_home/tablepace_usage.log;set echo offset feedback offset colsep ','set pagesize 2000set term offset heading offset line 400col startup_time for a20col status for a6col tablespace_name for a20col total_mb for 99999999col used_mb for 99999999col used_pct for a10selectd.dbid,to_char(b.STARTUP_TIME,'yyyy-mm-dd-hh34-mi-ss') as startup_time,b.status,total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pctfrom (select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name) total, v$instance b, v$database dwhere free.tablespace_name = total.tablespace_name;spool off;--- 查看近7天备份情况spool /home/oracle/bk/log/rman.log;set echo offset feedback offset colsep ','set pagesize 2000set term offset heading offset line 202000col DBID for 9999999999col status for a25col type for a12col  start_time for a22col  Finish_time for a22col in_sec for a12col out_sec for a12col command for a8col  INPUT_M for 99999col  OUTPUT_M for 99999col obj_type for a15select d.DBID as DBID,s.status as status,b.INPUT_TYPE as type,to_char(b.START_TIME,'yyyy-mm-dd hh34:mi:ss') as start_time,to_char(b.end_time, 'yyyy-mm-dd hh34:mi:ss') as  Finish_time,b.INPUT_BYTES_PER_SEC_DISPLAY in_sec,b.OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,s.OPERATION as command,trunc(s.INPUT_BYTES/1024/1024,2) as INPUT_M,trunc(s.OUTPUT_BYTES/1024/1024,2) as OUTPUT_M,s.OBJECT_TYPE as obj_typefrom v$rman_status s,v$rman_backup_job_details b, v$database dwhere to_char(s.START_TIME, 'yyyy-mm-dd hh34:mi:ss') < to_char(sysdate,'yyyy-mm-dd hh34:mi:ss')and to_char(s.END_TIME, 'yyyy-mm-dd hh34:mi:ss') > to_char(sysdate-7,'yyyy-mm-dd hh34:mi:ss')and s.COMMAND_ID=b.COMMAND_IDorder by s.START_TIME desc ;spool off;exit;