Oracle查看RMAN备份大小

楚枫默寒

已于 2024-05-14 11:44:58 修改

阅读量955

点赞数 9

文章标签： oracle 数据库

于 2024-04-15 17:19:05 首次发布

本文链接：https://blog.csdn.net/ffoooxx/article/details/137787356

版权

查看RMAN历史备份情况及大小

select to_char(start_time, 'yyyy-mm-dd') start_time,
       INCREMENTAL_LEVEL,
       BACKUP_TYPE,
       device_type,
       to_char(start_time, 'day') day,
       round(sum(OUTPUT_BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
  from v$backup_set_details
 group by to_char(start_time, 'yyyy-mm-dd'),
          to_char(start_time, 'day'),
          INCREMENTAL_LEVEL,
          BACKUP_TYPE,device_type
 order by start_time desc;

最后一次全备时间及大小

incremental_level = 0

select *
  from (select to_char(start_time, 'yyyy-mm-dd') start_time,
               INCREMENTAL_LEVEL,
               BACKUP_TYPE,
               device_type,
               to_char(start_time, 'day') day,
               round(sum(OUTPUT_BYTES) / 1024 / 1024 / 1024, 2) SIZE_GB
          from v$backup_set_details
         where incremental_level = 0
         group by to_char(start_time, 'yyyy-mm-dd'),
                  INCREMENTAL_LEVEL,
                  BACKUP_TYPE,
                  device_type,
                  to_char(start_time, 'day')
         order by start_time desc) c
 where rownum = 1;

v$backup_set_details displays information about backup sets from the control file. A backup set record is inserted after the backup set is successfully completed.

Column	Datatype	Description
RECID	NUMBER	Backup set record ID
STAMP	NUMBER	Backup set record stamp
SET_STAMP	NUMBER	Backup set stamp. The backup set stamp and count uniquely identify the backup set. Primary key for theV$BACKUP_SET table, and the foreign key for the following tables: V$BACKUP_PIECE,V$BACKUP_DATAFILE, V$BACKUP_REDOLOG,V$BACKUP_CORRUPTION. 备份集标记。备份集标记和计数唯一地标识了备份集。
SET_COUNT	NUMBER	Backup set count. The backup set count is incremented by one every time a new backup set is started (if the backup set is never completed the number is "lost"). If the control file is re-created then the count is reset to 1. Therefore the count must be used with the stamp to uniquely identify a backup set. Primary key for theV$BACKUP_SET table, and the foreign key for the following tables: V$BACKUP_PIECE,V$BACKUP_DATAFILE, V$BACKUP_REDOLOG,V$BACKUP_CORRUPTION 备份集计数。每次一个新的备份集开始备份集计数会增加1。（如果备份集从来没有完成则编号“丢失”）。如果控制文件被重新创建则计数重置为1，因此计数必须和标记一起使用来唯一地标识一个备份集。
BACKUP_TYPE	VARCHAR2(1)	Type of files that are in this backup. If the backup contains archived redo logs, the value is L. If this is a datafile full backup, the value is D. If this is an incremental backup, the value is I.
CONTROLFILE_INCLUDED	VARCHAR2(3)	Set to YES if there is a control file included in this backup set, otherwise set to NO
INCREMENTAL_LEVEL	NUMBER	Location where this backup set fits into the database's backup strategy. Set to NULL for full datafile, archivelog, controlfile, and spfile backups, set to 0 for incremental level 0 datafile backups, and set to 1 for incremental level 1 datafile backups.
PIECES	NUMBER	Number of distinct backup pieces in the backup set
START_TIME	DATE	Starting time
COMPLETION_TIME	DATE	Time that this backup set completed
ELAPSED_SECONDS	NUMBER	The number of elapsed seconds
BLOCK_SIZE	NUMBER	Block size of the backup set
INPUT_FILE_SCAN_ONLY	VARCHAR2(3)	YES indicates no actual backup is performed, but the datafiles are read. NO indicates a normal backup is performed.
KEEP	VARCHAR2(3)	(YES/NO) Indicates whether or not this backup set has a retention policy that is different than the value for the configure retention policy
KEEP_UNTIL	DATE	If KEEP_UNTIL_TIME is specified, this is the date after which the backup becomes obsolete. If this column is null, then the backup never expires.
KEEP_OPTIONS	VARCHAR2(10)	Lists additional retention options for this backup set. Possible values are: LOGS - The logs need to recover this backup are kept NOLOGS - The logs needed to recover this backup will not be kept BACKUP_LOGS - An archive log backup exists to support this backup set
MULTI_SECTION	VARCHAR2(3)	Indicates whether or not this backup set is a multi-section backup. Valid values are YES andNO. A multi-section backup is a backup in which multiple backup pieces are produced independently in parallel by multiple channels.

该视图查看的信息与RMAN中命令LIST BACKUP类似，只不过表示形式不同。其中BACKUP_TYPE列标记该备份集中包含的文件类型，有下列几个值：

L：表示包含归档重做日志文件；

D：表示数据文件完全备份；

I：表示增量备份。

附：rmanbackup.sh

#!/bin/bash
 
#####################################################
# Author:        楚枫默寒
# Create_time:   2022/7/11
# Modify_time:   2024/5/14
# function：     零级备份、归档备份
# notes：
# Script logic： 1、有备份进程，则退出程序
#                2、无备份进程且无备份目录，则进行零级备份
#                3、无备份进程且有备份目录，则进行归档备份
#                4、full\arc 指定备份类型
#
# 命令格式
# rmanbackup.sh      零级备份（默认方式）
# rmanbackup.sh full 指定零级备份
# rmanbackup.sh arc  指定归档备份
# &&并关系  ||或关系
#####################################################

umask 022
export ORACLE_SID=xxxx
export ORACLE_BASE=xxxx
export ORACLE_HOME=xxxx
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PS1='$ORACLE_SID:$PWD>'

BPATH='/rman/'                           #备份主路径
BACKPATH=$BPATH`date +%Y%m%d`_rman       #备份路径
DBTIME=`date +%Y%m%d`                    #备份时间
ALTIME=`date +%Y%m%d_%H%M%S`_`hostname`  #备份时间
COMM=`which rman`                        #备份命令

RMANID=`ps x |grep $BACKPATH|grep -v grep|awk '{ print $1 }'`

#无进程且无输入 或 无进程且输入为full
if [[ -z $RMANID ]] && ([[ -z $1 ]] || [[ $1 = full ]]);then
    if [[ ! -d $BACKPATH ]] || [[ $1 = full ]];then
        mkdir $BACKPATH;
        echo `date +"%Y-%m-%d %H:%M:%S"`"开始零级备份" >> /$BACKPATH/rmanbackup_$DBTIME.log
        $COMM target / msglog $BACKPATH/rmanDB_$DBTIME.log  <<EOF
        run
        {
        allocate channel ch11 type disk;
        allocate channel ch12 type disk;
        allocate channel ch13 type disk;
        allocate channel ch14 type disk;
        allocate channel ch15 type disk;
        allocate channel ch16 type disk;
        CONFIGURE CONTROLFILE AUTOBACKUP ON;
        CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKPATH/%F';
        crosscheck backup;
        crosscheck archivelog all;
        backup filesperset 20 as backupset incremental level=0 database skip inaccessible format '$BACKPATH/DB_%n_%I_%T_%U' plus archivelog delete all input format '$BACKPATH/AL_%n_%I_%T_%U';
        report obsolete;
        delete noprompt expired archivelog all;
        delete noprompt expired backup;
        delete noprompt obsolete;
        release channel ch11;
        release channel ch12;
        release channel ch13;
        release channel ch14;
        release channel ch15;
        release channel ch16;
        }
EOF

        sqlplus -s / as sysdba <<EOF
        create pfile='$BACKPATH/pfile_$ALTIME.ora' from spfile;
        set line 900
        SET newpage none
        SET heading off
        SET feedback off
        SET pagesize 0
        SET echo on
        SET newp none
        SET trimout on
        SET trimspool on
        SET SERVEROUTPUT ON size 1000000
        col name for a20
        col value for a50
        col display_value for a50
        col CMD for a200
        col instance_name for a10 
        col host_name  for a20 
        col online_status for a10 
        col TABLESPACE_NAME  for a40 
        col status for a10
        col Extent for a10 
        spool $BACKPATH/DG_$ALTIME.ini
        select name,value,display_value from v\$parameter where name='log_archive_config' or (name like 'log_archive_dest_%' and name not like 'log_archive_dest_state_%') and value is not null order by 1;
        col parameter for a30
        spool $BACKPATH/CHARACTERSET_$ALTIME.conf
        select * from nls_database_parameters where parameter like '%CHARACTERSET%' order by 1;
        spool $BACKPATH/DB_username_$ALTIME.sql
        select 'CREATE USER "'||a.username||'" IDENTIFIED BY VALUES '''||b.spare4||';'||b.password||''' DEFAULT TABLESPACE "'||a.default_tablespace||'" TEMPORARY TABLESPACE "'||a.temporary_tablespace||'";' CMD from dba_users a,sys.user$ b where a.username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN' and a.username= b.name ;
        spool $BACKPATH/Privs_table_$ALTIME.sql
        select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) 
        union 
        select 'grant '||PRIVILEGE||' on '||grantor||'.'||TABLE_NAME||' to '||GRANTEE||';' CMD from dba_tab_privs where ( owner in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN'));
        spool $BACKPATH/Privs_user_$ALTIME.sql
        DECLARE
          l_privs CLOB;
        BEGIN
          FOR rec IN (SELECT PRIVILEGE, ADMIN_OPTION, GRANTEE FROM DBA_SYS_PRIVS where grantee in (select username from dba_users where username not in ('ANONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','OWBSYS','OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','ZABBIX') and account_status='OPEN')) LOOP
            l_privs := 'GRANT ' || rec.PRIVILEGE || ' TO ' || rec.GRANTEE ||
                       CASE WHEN rec.ADMIN_OPTION = 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';';
            DBMS_OUTPUT.PUT_LINE(l_privs);
          END LOOP;
        END;
        /
        SET heading on
        SET pagesize 50
        spool $BACKPATH/Tablespace_$ALTIME.txt
        SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) 总计_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) 已使用_GB
        FROM (SELECT status,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_DATA_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
        (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
        V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
        union
        SELECT I.instance_name,I.host_name,A.status,A.autoextensible Extent,A.TABLESPACE_NAME,ROUND(A.TOTAL_SPACE/1024/1024/1024,0) 总计_GB,ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE,0))/1024/1024/1024,0) 已使用_GB
        FROM (SELECT STATUS,TABLESPACE_NAME,SUM(BYTES) BYTES_ALLOC,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) TOTAL_SPACE,autoextensible FROM DBA_temp_FILES GROUP BY STATUS, TABLESPACE_NAME, autoextensible) A,
         (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES_FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
        V\$INSTANCE I WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5;
        SET heading off
        SET pagesize 0
        SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name))||';' SQL FROM DBA_TABLESPACES TS;
EOF
        echo `date +"%Y-%m-%d %H:%M:%S"`"零级备份结束" >> /$BACKPATH/rmanbackup_$DBTIME.log
    else 
        echo `date +"%Y-%m-%d %H:%M:%S"`"开始归档备份" >> /$BACKPATH/rmanbackup_$DBTIME.log
        $COMM target / msglog $BACKPATH/rman_AL_$ALTIME.log  <<EOF
        run
        {
        allocate channel ch21 type disk;
        allocate channel ch22 type disk;
        allocate channel ch23 type disk;
        CONFIGURE CONTROLFILE AUTOBACKUP ON;
        CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKPATH/%F';
        crosscheck backup;
        crosscheck archivelog all;
        sql 'alter system archive log current';
        backup as compressed backupset archivelog all delete input format '$BACKPATH/AL_%n_%I_%T_%U';
        report obsolete;
        delete noprompt expired archivelog all;
        delete noprompt expired backup;
        delete noprompt obsolete;
        release channel ch21;
        release channel ch22;
        release channel ch23;
        }
EOF
        echo `date +"%Y-%m-%d %H:%M:%S"`"归档备份结束" >> /$BACKPATH/rmanbackup_$DBTIME.log
    fi

#无进程且输入为arc
elif [[ -z $RMANID ]] && [[ $1 = arc ]];then
    if [[ ! -d $BACKPATH ]];then mkdir $BACKPATH;  fi
    echo `date +"%Y-%m-%d %H:%M:%S"`"开始归档备份" >> /$BACKPATH/rmanbackup_$DBTIME.log
    $COMM target / msglog $BACKPATH/rman_AL_$ALTIME.log  <<EOF
    run
    {
    allocate channel ch31 type disk;
    allocate channel ch32 type disk;
    allocate channel ch33 type disk;
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKPATH/%F';
    crosscheck backup;
    crosscheck archivelog all;
    sql 'alter system archive log current';
    backup as compressed backupset archivelog all delete input format '$BACKPATH/AL_%n_%I_%T_%U';
    report obsolete;
    delete noprompt expired archivelog all;
    delete noprompt expired backup;
    delete noprompt obsolete;
    release channel ch31;
    release channel ch32;
    release channel ch33;
    }
EOF
    echo `date +"%Y-%m-%d %H:%M:%S"`"归档备份结束" >> /$BACKPATH/rmanbackup_$DBTIME.log

#有备份进程则退出
elif [[ ! -z $RMANID ]] ;then
    echo "有备份进行中";
fi

#清理历史备份目录（默认清理10天前的目录）
REDUNDANCY=10
rmdir_no=`find $BPATH -maxdepth 1 -type d -name "*_rman"|sort|tail -n $REDUNDANCY|wc -l`
if [ $rmdir_no -le 10 ];then
    rmdir=`find $BPATH -maxdepth 1 -type d -name "*_rman"|sort|tail -n $REDUNDANCY|head -n 1`;
    if [ $rmdir ];then
       rm -rf $rmdir;
    fi
fi

楚枫默寒

关注

9
点赞
踩
19

收藏

觉得还不错? 一键收藏
打赏
1
评论
Oracle查看RMAN备份大小

每次一个新的备份集开始备份集计数会增加1。（如果备份集从来没有完成则编号“丢失”）。如果控制文件被重新创建则计数重置为1，因此计数必须和标记一起使用来唯一地标识一个备份集。该视图查看的信息与RMAN中命令LIST BACKUP类似，只不过表示形式不同。备份集标记和计数唯一地标识了备份集。查看RMAN历史备份情况及大小。L：表示包含归档重做日志文件；D：表示数据文件完全备份；最后一次全备时间及大小。
复制链接

扫一扫