Oracle数据库RMAN的自动备份脚本简介

Oracle数据库RMAN的自动备份脚本简介

各位同学知道Oracle数据库RMAN如何自动备份脚本嘛?下面我为大家整理了关于Oracle数据库RMAN的自动备份脚本文章，希望能为你提供帮助：

1、数据库设置为归档方式

2、数据库的备份脚本

db_full_backup.sh ：数据库全备脚本

db_l0_backup.sh ：数据库0级备份脚本

db_l1_backup.sh ：数据库1级备份脚本

ftp.sh ：数据FTP上传脚本

ftp_del.sh ：数据FTP清理脚本

rman_bak.sh ：数据备份主程序

3、备份原理

每周1、3、6进行0级备份

每周日、2、4、5进行1级备份

备份文件上传到FTP服务器

FTP服务器每周清理一次，但是清理后将周六和周日的备份进行保留(6.bak和0.bak)

所有工作防暑crontab中自动执行备份

4、备份目录含义

arc ：数据库归档目录

rmanbak ：数据库备份文件的保存目录

rmanscripts ：数据库脚本存放路径

5、FTP目录

ftp上必须手动建立目录

L0：

---1

---3

---6

---6.bak

L1：

---2

---4

---5

---0

---0.bak

rman_bak.sh脚本主程序

#!/bin/bash

#--------------------------------------------

# Oracle auto backup using rman

#

# author:songrh

# week:1,3,6 Level 0 backup

# 2,4,5,0 Level 1 backup

# Copyright by ChenLong Tec

#--------------------------------------------

#

#

export ORACLE_BASE=/u02/oracle

export ORACLE_HOME=/u02/oracle/product/9.2.4

export ORACLE_SID=PROD

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib

export NLS_LANG=american_america.ZHS16GBK

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORA_NL33=$ORACLE_HOME/ocommon/nls/admin/data

export PATH=/bin:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:$PATH

export PATH=$PATH:/opt/local/bin

#

SH_PATH=/u02/rmanscripts

ARC_PATH=/u02/arch

RMAN_BAK_PATH=/u02/rmanbak

#

#FULL_PATH=$RMAN_BAK_PATH/full

L0_PATH=$RMAN_BAK_PATH/L0

L1_PATH=$RMAN_BAK_PATH/L1

#

#DAY_TAG=`date "%Y-%m-%d"`

LOG_TAG=`date "%Y-%m-%d"`

#FIRST_DAY=`date %e`

WEEK=`date %w`

#WEEK=1

#

# FTP configure

IP="122.120.150.155"

FTPUSER="ftpbak"

FTPPASS="******"

FTPROOT0="L0"

FTPROOT1="L1"

#

DISK_USE=`df -k |sed -n '/u02/'p | awk '{print $5}' |sed 's/%//'`

####check path function

############

if [[ $DISK_USE -ge 90 ]]then

rm -rf $L0_PATH/*

rm -rf $L1_PATH/*

fi

if [ "$WEEK" = "6" -o "$WEEK" = "3" -o "$WEEK" = "1" ]then

if [ ! -d $L0_PATH ]then

mkdir $L0_PATH

fi

if [ "$WEEK" = "1" ]then

rm -rf $L0_PATH/*

rm -rf $L1_PATH/*

mkdir $L0_PATH/$WEEK

$SH_PATH/db_l0_backup.sh $L0_PATH/$WEEK

cd $L0_PATH/$WEEK

$SH_PATH/ftp_del.sh $IP $FTPUSER $FTPPASS $FTPROOT0 $WEEK $WEEK_$LOG_TAG.log

else

if [ ! -d $L0_PATH/$WEEK ]then

mkdir $L0_PATH/$WEEK

$SH_PATH/db_l0_backup.sh $L0_PATH/$WEEK

cd $L0_PATH/$WEEK

$SH_PATH/ftp.sh $IP $FTPUSER $FTPPASS $FTPROOT0 $WEEK $WEEK_$LOG_TAG.log

else

rman的简单使用: catalog的建立 全备份 保留备份策略 删除过期备份查看当前数据库的数据文件 SQL>select name from V$datafileNAME /oracle/oracle i/oradata/testmysiteok/system dbf/oracle/oracle i/oradata/testmysiteok/undotbs dbf/oracle/oracle i/oradata/testmysiteok/cwmlite dbf/oracle/oracle i/oradata/testmysiteok/drsys dbf/oracle/oracle i/oradata/testmysiteok/example dbf/oracle/oracle i/oradata/testmysiteok/indx dbf/oracle/oracle i/oradata/testmysiteok/odm dbf/oracle/oracle i/oradata/testmysiteok/tools dbf/oracle/oracle i/oradata/testmysiteok/users dbf/oracle/oracle i/oradata/testmysiteok/xdb dbf/oracle/oradata/webdb_file dbfNAME /oracle/oradata/webdb_file dbf/oracle/oradata/webdb_file dbf/oracle/oradata/webdb_file dbf rows selected建立catalog表空间为back_up SQL>create tablespace back_up datafile /oracle/oradata/catalog dbf size mTablespace created建立rman用户密码为rman rman默认的表空间为back_up 使用临时表空间temp SQL>create user rman identified by rman default tablespace back_up temmporary tablespace tempcreate user rman identified by rman default tablespace back_up temmporary tablespace temp　 *ERROR at line :ORA : missing or invalid optionSQL>create user rman identified by rman default tablespace back_up temporary tablespace tempUser created给rman用户授权 SQL>grant connect resource recovery_catalog_owner to rmanGrant succeeded SQL>quitDisconnected from Oracle i Enterprise Edition Release bit ProductionWith the Partitioning OLAP and Oracle Data Mining optionsJServer Release Production 连接或者创建catalog到表空间 back_up bash $ rmanRecovery Manager: Release bit ProductionCopyright (c) Oracle Corporation 　All rights reserved RMAN>connect catalog rman/rmanconnected to recovery catalog databaserecovery catalog is not installedRMAN>create catalog tablespace backupRMAN : ===========================================================RMAN : =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN : ===========================================================RMAN : error encountered while parsing input mandsRMAN : syntax error: found backup : expecting one of: double quoted string identifier single quoted string RMAN : at line column file: standard inputRMAN : ===========================================================RMAN : =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN : ===========================================================RMAN : error encountered while parsing input mandsRMAN : syntax error: found : expecting one of: allocate alter backup beginline blockrecover catalog change connect copy create crosscheck configure duplicate debug delete drop exit endinline host { library list mount open print quit recover register release replace report renormalize reset restore resync rman run rpctest set setlimit sql spool startup shutdown send show test upgrade validate RMAN : at line column file: standard inputRMAN>create catalog tablespace back_uprecovery catalog createdRMAN>quitRecovery Manager plete连接到sid 为testmysiteok的 catalog上 并注册testmysiteok数据库 bash $ rman　target testmysiteok catalog rman/rmanRecovery Manager: Release bit ProductionCopyright (c) Oracle Corporation 　All rights reserved target database Password:connected to target database: testmysiteOK (DBID= )connected to recovery catalog databaseRMAN>register databasedatabase registered in recovery catalogstarting full resync of recovery catalogfull resync plete 全备份testmysiteok数据库 RMAN>backup full tag testmysiteok format /data/ora_backup/testmysiteok_%u_%s_%p databaseStarting backup at AUG allocated channel: ORA_DISK_ channel ORA_DISK_ : sid= devtype=DISKchannel ORA_DISK_ : starting full datafile backupsetchannel ORA_DISK_ : specifying datafile(s) in backupsetincluding current SPFILE in backupsetincluding current controlfile in backupsetinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/system dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/undotbs dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/example dbfinput datafile fno= name=/oracle/oradata/webdb_file dbfinput datafile fno= name=/oracle/oradata/webdb_file dbfinput datafile fno= name=/oracle/oradata/webdb_file dbfinput datafile fno= name=/oracle/oradata/webdb_file dbfinput datafile fno= name=/oracle/oradata/catalog dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/xdb dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/indx dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/users dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/cwmlite dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/drsys dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/odm dbfinput datafile fno= name=/oracle/oracle i/oradata/testmysiteok/tools dbfchannel ORA_DISK_ : starting piece at AUG channel ORA_DISK_ : finished piece at AUG piece handle=/data/ora_backup/testmysiteok_ grgroc_ _ ment=NONEchannel ORA_DISK_ : backup set plete elapsed time: : : Finished backup at AUG设置保留最近的 次备份 RMAN>configure retention policy to redundancy old RMAN configuration parameters:CONFIGURE RETENTION POLICY TO REDUNDANCY new RMAN configuration parameters:CONFIGURE RETENTION POLICY TO REDUNDANCY new RMAN configuration parameters are successfully storedstarting full resync of recovery catalogfull resync plete 查看过期的备份 RMAN>report obsoleteRMAN retention policy will be applied to the mandRMAN retention policy is set to redundancy Report of obsolete backups and copiesType KeyCompletion TimeFilename/Handle Backup Set　 OCT 　Backup Piece　 OCT 　/oracle/testmysiteok dbfArchive Log　 SEP 　/oracle/OraHome/dbs/arch _ dbf 查看所有备份 RMAN>list backup 删除过期备份 RMAN>delete obsoleteRMAN retention policy will be applied to the mandRMAN retention policy is set to redundancy using channel ORA_DISK_ Deleting the following obsolete backups and copies:Type KeyCompletion TimeFilename/Handle Backup Set　 OCT 　Backup Piece　 OCT 　/oracle/testmysiteok dbfArchive Log　 SEP 　/oracle/OraHome/dbs/arch _ dbfDo you really want to delete the above objects (enter YES or NO)? ydeleted backup piecebackup piece handle=/oracle/testmysiteok dbf recid= stamp= Deleted objects lishixinzhi/Article/program/Oracle/201311/18428

---