Oracle 日常RMAN備份腳本,很基礎。但是對于多個需要備份的Oracle數(shù)據(jù)庫,可以很簡單實施,并利于后期批量狀態(tài)的查詢。
臨翔ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景,ssl證書未來市場廣闊!成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道,可以享受市場價格4-6折優(yōu)惠!如果有意向歡迎電話聯(lián)系或者加微信:18982081108(備注:SSL證書合作)期待與您的合作!
備份腳本,基于linux,windows環(huán)境需要適當修改。
主腳本,會調用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
----每日歸檔產生量,可以判斷數(shù)據(jù)庫是否繁忙
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-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_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 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_type
from v$rman_status s,v$rman_backup_job_details b, v$database d
where 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_ID
order by s.START_TIME desc ;
spool off;
exit;
新聞標題:Oracle自動化備份腳本
網(wǎng)頁地址:http://bm7419.com/article6/geisig.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián),為您提供Google、定制開發(fā)、虛擬主機、網(wǎng)站制作、軟件開發(fā)、網(wǎng)站收錄
聲明:本網(wǎng)站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)