oracle RMAN备份及备份信息查询

最新推荐文章于 2024-09-09 14:18:52 发布

川xc

最新推荐文章于 2024-09-09 14:18:52 发布

阅读量1.6k

点赞数

分类专栏： oracle 文章标签： oracle备份

oracle 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

RMAN备份

RMAN备份分为全备和增量备份两部分，而增量备份:分为0 1 2级

备份脚本（db_bak_rman.sh）

##======================================================================
##  db_bak_rman.sh
##  ##  2014/06/10
##  usage: db_bak_rman.sh <$ORACLE_SID> <$BACKUP_LEVEL>
##         BACKUP_LEVEL:
##                    F: full backup
##                    0: level 0
##                    1: level 1
##======================================================================
#!/bin/bash
# User specific environment and startup programs

if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi

ORACLE_SID=${1};                           export ORACLE_SID
RMAN_LEVEL=${2};                           export RMAN_LEVEL
TIMESTAMP=`date +%Y%m%d%H%M`;              export TIMESTAMP
DATE=`date +%Y%m%d`;                       export DATE
#RMAN_DIR=/backup_db_rman/rman;            export RMAN_DIR  #marked by elaine 160603
RMAN_DIR=/mnt/bak/${1};                         export RMAN_DIR
RMAN_DATA=${RMAN_DIR}/${DATE};             export RMAN_DATA
#RMAN_LOG=/backup_db_rman/rman/log;        export RMAN_LOG  #marked by elaine 160603
RMAN_LOG=${RMAN_DIR}/${DATE};                     export RMAN_LOG

# check rman level
#=======================================================================

if [ "$RMAN_LEVEL" == "F" ];
then
  unset INCR_LVL
  BACKUP_TYPE=full
else
  INCR_LVL="INCREMENTAL LEVEL ${RMAN_LEVEL}"
  BACKUP_TYPE=lev${RMAN_LEVEL}
fi

RMAN_FILE=${RMAN_DATA}/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP};    export RMAN_FILE
SSH_LOG=${RMAN_LOG}/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}.log;   export SSH_LOG

MAXPIECESIZE=2G;                                                     export MAXPIECESIZE

#check RMAN Backup Path
#=======================================================================

if ! test -d ${RMAN_DATA}
then
  mkdir -p ${RMAN_DATA}
fi

echo "------------------------------------" >>${SSH_LOG}
echo "      " >>${SSH_LOG}
echo "Rman Begin to Working .............." >>${SSH_LOG}
echo "Begin time at:" `date` -- `date +%Y%m%d%H%M` >>${SSH_LOG}

#Startup rman to backup
#=======================================================================
$ORACLE_HOME/bin/rman log=${RMAN_FILE}.log <<EOF

connect target /
run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${RMAN_FILE}_%F';
ALLOCATE CHANNEL ch1 TYPE DISK;
ALLOCATE CHANNEL ch2 TYPE DISK;
#set limit channel ch1 readrate=10240;
#set limit channel ch1 kbytes=4096000;
#set limit channel ch2 readrate=10240;
#set limit channel ch2 kbytes=4096000;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
BACKUP ${INCR_LVL} DATABASE FORMAT '${RMAN_FILE}_%U' TAG '${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '${RMAN_FILE}_arc_%U' TAG '${ORACLE_SID}_arc_${TIMESTAMP}'
DELETE INPUT;
#DELETE NOPROMPT OBSOLETE;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7'; 
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
}

sql "alter database backup controlfile to ''${RMAN_DATA}/cntl_${BACKUP_TYPE}.bak''";

exit;
EOF
RC=$?

cat ${RMAN_FILE}.log >>${SSH_LOG}

echo "Rman Stop working @ time:"`date` `date +%Y%m%d%H%M` >>${SSH_LOG}

echo >>${SSH_LOG}
echo "----------------------------------------" >>${SSH_LOG}
echo "------------------ Disk Space ----------" >>${SSH_LOG}
df -h >>${SSH_LOG}

echo >>${SSH_LOG}

if [ $RC -ne "0" ]; then
  echo "-------------------- error --------------------" >>${SSH_LOG}
else
  echo "------ no error found during RMAN backup period --------" >>${SSH_LOG}
  rm -rf ${RMAN_FILE}.log
fi

#Remove old backup than 5 days
#===========================================================================================
RMDIR=${RMAN_DIR}/`/bin/date +%Y%m%d -d "7 days ago"`;        export RMDIR
echo >>${SSH_LOG}
echo -e "---------------Remove old backup than 7 days -------------\n" >>${SSH_LOG}

if test -d ${RMDIR}
then
  rm -rf ${RMDIR}
  RC=$?
fi

echo >>${SSH_LOG}

if [ $RC -ne "0" ]; then
  echo -e "----------- Remove old backup exception-------------- \n" >>${SSH_LOG}
else
  echo -e "----------- no error found during remove old backup set period------ \n" >>${SSH_LOG}
fi

exit

归档日志清理（delete_archivelog.sh）

#!/bin/bash

export  ORACLE_SID=数据库sid

$ORACLE_HOME/bin/rman target sys/pwd@test<<EOF #pwd@test是随意输入的

crosscheck archivelog all;

delete NOPROMPT expired archivelog all;

delete noprompt archivelog all completed before 'sysdate -7';

exit;
EOF

定时

使用crontab做定时

crontab格式：

往 cron 中添加一个作业：

[root@localhost ~]# crontab [选项]
选项:
    -e:    编辑crontab定时任务
    -l:    查询crontab任务
    -r:    删除当前用户所有的crontab任务
[root@localhost ~]# crontab -e
#进入crontab编辑界面。会打开vim编辑你的工作

#输入数据，基本格式为 :
*　　*　　*　　*　　*　　command

查看定时任务

RMAN备份信息查询

--查看rman备份的记录（周日做全量备份，其他时间做增量备份）
SELECT start_time,end_time ,row_level,output_bytes/1024/1024/1024  文件大小G  FROM V$RMAN_STATUS a 
where operation = 'RMAN'
order by a.end_time desc  
--查看所有备份集详细信息：
SELECT A.RECID "BACKUP SET",
       A.SET_STAMP,
        DECODE (B.INCREMENTAL_LEVEL,
                '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
                1, 'Incr-1级',
                0, 'Incr-0级',
                B.INCREMENTAL_LEVEL)
           "Type LV",
        B.CONTROLFILE_INCLUDED "包含CTL",
        DECODE (A.STATUS,
                'A', 'AVAILABLE',
                'D', 'DELETED',
                'X', 'EXPIRED',
                'ERROR')
           "STATUS",
        A.DEVICE_TYPE "Device Type",

        A.START_TIME "Start Time",
        A.COMPLETION_TIME "Completion Time",
        A.ELAPSED_SECONDS "Elapsed Seconds",
        A.BYTES/1024/1024/1024 "Size(G)",
        A.COMPRESSED,
        A.TAG "Tag",
        A.HANDLE "Path"
   FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
  WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;

--查找某个备份集中包含数据文件
SELECT DISTINCT c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
WHERE A.SET_STAMP = C.SET_STAMP
    AND D.FILE# = C.FILE#
    AND A.DELETED='NO'
    --AND c.set_stamp=&set_stamp
 ORDER BY C.FILE#;
 --查询某个备份集中控制文件
 SELECT DISTINCT A.SET_STAMP,
                D.NAME,
                C.CHECKPOINT_CHANGE#,
                C.CHECKPOINT_TIME
 FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
 WHERE A.SET_STAMP = C.SET_STAMP
   AND C.FILE# = 0
   AND A.DELETED = 'NO'
   --AND C.SET_STAMP = &SET_STAMP;
   
   --查看某个备份集中归档日志:
   SELECT DISTINCT B.SET_STAMP,
                B.THREAD#,
                B.SEQUENCE#,
               B.FIRST_TIME,
                B.FIRST_CHANGE#,
                B.NEXT_TIME,
                B.NEXT_CHANGE#
 FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   --AND B.SET_STAMP = &SET_STAMP
 ORDER BY THREAD#, SEQUENCE#;
 
 --查看某个备份集SPFILE
 SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   --AND B.SET_STAMP = &SET_STAMP;
   
   --查看RMAN的配置信息
   SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;