iSelfSchooling.com - Community of Sharing

Click here for Online Oracle 8i, 9i, 10g, 11g and 12c Training

from www.iSelfSchooling.com

November 18, 2017, 12:27:46 PM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 
   Home   Help Search Login Register  
Pages: [1] |   Go Down
  Send this topic  |  Print  
Author Topic: Get a query of your Oracle RMAN backup size for an specific day for many databas  (Read 38816 times)
AllanDBA
AllanDBA
Newbie
Newbie
*
Posts: 0


« on: June 25, 2015, 05:35:38 AM »

Get a query of your Oracle RMAN backup size for an specific day for many databases.

Use the following query.
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
   from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize
            from v$backup_set bs, v$backup_piece bp
            where bs.set_stamp = bp.set_stamp
               and to_char(bp.completion_time,'DD-MON-YY')='24-JUN-15'
               and bs.set_count  = bp.set_count
               and bp.status = 'A'
               group by trunc(bp.completion_time), backup_type)
               order by 1, 2 ;


Have all the SIDs listed at the file4sid.txt file.
For example:
# more file4sid.txt
sid1
sid2
sid3
...

Then write the following scipt and execute it.

#!/bin/bash

# read file4sid.txt file
# ----------------------
while read -r sid;
do
{
      echo "SID name = ${sid}";
      echo '-------------------------'
      ORACLE_SID=${sid}; export ORACLE_SID;
      val=`sqlplus  -s " / as sysdba"  <<EOF
      set pagesize 0 feedback off verify off heading off echo off;

      select name from v\\$database;
      select database_role from v\\$database;
      select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
         from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize
            from v\\$backup_set bs, v\\$backup_piece bp
            where bs.set_stamp = bp.set_stamp
               and to_char(bp.completion_time,'DD-MON-YY')='21-JUN-15'
               and bs.set_count  = bp.set_count
               and bp.status = 'A'
               group by trunc(bp.completion_time), backup_type)
               order by 1, 2 ;
      exit;
EOF`
      echo "$val"
      echo '================================================================'
      echo ''
}
done <./file4sid.txt

--admin
Logged
Pages: [1] |   Go Up
  Send this topic  |  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!