首页 / ORACLE / Oracle 自动化备份脚本
Oracle 自动化备份脚本
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 自动化备份脚本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4866字，纯文字阅读大概需要7分钟。
内容图文
Oracle 日常RMAN备份脚本，很基础。但是对于多个需要备份的Oracle数据库，可以很简单实施，并利于后期批量状态的查询。
-
备份脚本，基于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.115 export instance_name=liang export username=liang export password=liang export syspsw=oracle export backup_home=/home/oracle/bk export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1 export PATH=$PATH:$ORACLE_HOME/db_1/bin:/sbin:/usr/sbin ##################################################################################### export curTime=$(date "+%Y%m%d") mkdir $backup_home/ftp/$curTime echo "-----------------------------RMAN start-----------------------------";date #backup start cd $backup_home $ORACLE_HOME/bin/rman target sys/$syspsw@$host_ip:1521/$instance_name cmdfile=‘rman.sql‘ echo "------------------------------RMAN End------------------------------";date sleep 10 echo "------------------------------SQL Start------------------------------";date $ORACLE_HOME/bin/sqlplus $username/$password@$host_ip:1521/$instance_name @status.sql echo "------------------------------END-----------------------------";date -
RMAN备份sql脚本
[oracle@test bk]$ more rman.sql run { 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; } - 查询备份状态的脚本,并将查询信息打印到日志，方便查询。
$ more status.sql ----每日归档产生量,可以判断数据库是否繁忙 spool $backup_home/redo_switch.log; set echo off set feedback off set colsep ‘,‘ set pagesize 2000 set term off set heading off set line 400 col Count for 9999 col GB for 99999 select d.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 d group by trunc(completion_time),d.dbid; spool off; ---查询表空间使用率 spool $backup_home/tablepace_usage.log; set echo off set feedback off set colsep ‘,‘ set pagesize 2000 set term off set heading off set line 400 col startup_time for a20 col status for a6 col tablespace_name for a20 col total_mb for 99999999 col used_mb for 99999999 col used_pct for a10 select d.dbid, to_char(b.STARTUP_TIME,‘yyyy-mm-dd-hh24-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_Pct from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total, v$instance b, v$database d where free.tablespace_name = total.tablespace_name; spool off; --- 查看近7天备份情况 spool /home/oracle/bk/log/rman.log; set echo off set feedback off set colsep ‘,‘ set pagesize 2000 set term off set heading off set line 202000 col DBID for 9999999999 col status for a25 col type for a12 col start_time for a22 col Finish_time for a22 col in_sec for a12 col out_sec for a12 col command for a8 col INPUT_M for 99999 col OUTPUT_M for 99999 col obj_type for a15 select d.DBID as DBID, s.status as status, b.INPUT_TYPE as type, to_char(b.START_TIME,‘yyyy-mm-dd hh24:mi:ss‘) as start_time, to_char(b.end_time, ‘yyyy-mm-dd hh24: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_type from v$rman_status s,v$rman_backup_job_details b, v$database d where to_char(s.START_TIME, ‘yyyy-mm-dd hh24:mi:ss‘) < to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) and to_char(s.END_TIME, ‘yyyy-mm-dd hh24:mi:ss‘) > to_char(sysdate-7,‘yyyy-mm-dd hh24:mi:ss‘) and s.COMMAND_ID=b.COMMAND_ID order by s.START_TIME desc ; spool off; exit;
Oracle 自动化备份脚本
标签：targe input lte rac trunc feedback windows disk spl
本文系统来源：http://blog.51cto.com/hsbxxl/2322690
内容总结
以上是互联网集市为您收集整理的Oracle 自动化备份脚本全部内容，希望文章能够帮你解决Oracle 自动化备份脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】