oracle自动化,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/[email protected]$host_ip:1521/$instance_name cmdfile=‘rman.sql‘

echo "------------------------------RMAN End------------------------------";date

sleep 10

echo "------------------------------SQL Start------------------------------";date

$ORACLE_HOME/bin/sqlplus $username/[email protected]$host_ip:1521/$instance_name @status.sql

echo "------------------------------END-----------------------------";date

RMAN备份sql脚本 [[email protected] 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:ftpuploadfiledb_%U.bak_%T‘;

sql ‘alter system switch logfile‘;

CROSSCHECK ARCHIVELOG ALL;

backup as compressed backupset archivelog all format ‘C:ftpuploadfilearchivelog_%d_%s_%p_%T‘ not backed up 2 times;

backup spfile format ‘C:ftpuploadfilespfile_%U_%T‘;

backup current controlfile format ‘C:ftpuploadfilecontrolfile_%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;