Tuesday, November 09, 2010

Tool to Present a Consolidated View of the Status of RMAN Backups

A very important area of database administration is backups which are essential to the availability of the databases. If you use RMAN to backup databases (as you should be doing), how will you ensure that all the database backups have successfully completed? One way of making sure that occurs is by going into each server and checking the backup log – a task not just tedious but often impractical considering the large number of databases we have. Is there an easy way – via a consolidated output of all databases?

The Tool

Yes, there is an easy way – by checking the catalog, assuming of course that you are using a catalog, as you should be. I developed this tool to extract the information from the catalog for each database. Since the repository may be different for each database being backed up, I used a view to union all the RC_* views from all the repositories on a common schema which I named RMAN_COMMON. Then I wrote a PL/SQL program to pull the report presenting the information on all databases together. Since my objective was to have a consolidated view of all the backups, whether they succeeded or not, I selected from multiple views to provide an indented output in case of failures to identify specifically which component failed. I will illustrate with an output shown below. The tool can be customized for number of days it reports. Here I have chosen for the last 2 days.

SQL> conn rman_common/rman_common@catalogdb
Connected.
SQL> @status

********  RMAN Database Backup Report *********

DB Name  Start Time         End Time           Status   Time Tak Output S Type
-------- ------------------ ------------------ -------- -------- -------- -------
DEMO1    2010-11-08 00:25:1 2010-11-08 00:50:4 COMPLETE 00:25:27    17.99 DISK
DEMO1    2010-11-09 00:25:1 2010-11-09 00:50:3 COMPLETE 00:25:16    17.99 DISK
ECR      2010-11-07 23:12:0 2010-11-07 23:14:2 COMPLETE 00:02:13     5.55 DISK
ECR      2010-11-08 23:12:0 2010-11-08 23:14:3 COMPLETE 00:02:21     5.58 DISK
EMREP    2010-11-08 01:00:2 2010-11-08 01:02:5 COMPLETE 00:02:35   326.07 DISK
EMREP    2010-11-09 01:00:2 2010-11-09 01:02:5 COMPLETE 00:02:28   353.71 DISK
PROPRD   2010-11-07 23:00:1 2010-11-08 00:04:0 COMPLETE 01:03:49    50.48 DISK
PROPRD   2010-11-08 00:04:2 2010-11-08 11:47:1 COMPLETE 11:42:42    27.59
PROPRD   2010-11-08 10:35:3 2010-11-08 11:20:4 COMPLETE 00:45:12    30.00 DISK
PROPRD   2010-11-08 11:28:5 2010-11-08 12:21:2 COMPLETE 00:52:33    30.00 DISK
PROPRD   2010-11-08 12:23:5 2010-11-08 12:38:5 COMPLETE 00:15:00    10.00 DISK
PROPRD   2010-11-08 12:43:3 2010-11-08 12:43:4 COMPLETE 00:00:07   192.00 DISK
PROPRD   2010-11-08 12:46:1 2010-11-08 12:46:2 COMPLETE 00:00:07   224.00 DISK
PROPRD   2010-11-08 12:48:1 2010-11-08 13:14:0 COMPLETE 00:25:50    20.00 DISK
PROPRD   2010-11-08 13:37:3 2010-11-08 13:58:4 COMPLETE 00:21:11    15.00 DISK
PROPRD   2010-11-08 14:00:2 2010-11-08 14:13:5 COMPLETE 00:13:30    10.00 DISK
PROPRD   2010-11-08 14:29:0 2010-11-08 14:29:0 FAILED   00:00:01     0.00
. Operation            Input                Status
. -------------------- -------------------- --------------------
. .                    .                    FAILED
.  Level  Status   Operation            Object Type
.  ------ -------- -------------------- --------------------
.  >      COMPLETE RMAN                 .
.  ->     FAILED   BACKUP               DATAFILE INCR
PROPRD   2010-11-08 23:00:2 2010-11-09 00:21:0 COMPLETE 01:20:43    66.75 DISK
PROPRD   2010-11-09 00:21:2 2010-11-09 14:07:3 COMPLETE 13:46:09    40.25
LIGPRD1  2010-11-08 03:00:0 2010-11-08 03:00:4 COMPLETE 00:00:37     1.74 DISK
LIGPRD1  2010-11-08 03:00:5 2010-11-08 03:04:1 COMPLETE 00:03:12     1.49
LIGPRD1  2010-11-09 03:00:0 2010-11-09 03:00:5 COMPLETE 00:00:45     2.59 DISK
LIGPRD1  2010-11-09 03:01:0 2010-11-09 03:05:1 COMPLETE 00:04:05     1.37
LIGPRD2  2010-11-08 02:00:0 2010-11-08 02:00:3 COMPLETE 00:00:29     1.04 DISK
LIGPRD2  2010-11-08 02:00:4 2010-11-08 02:02:3 COMPLETE 00:01:52     1.34
LIGPRD2  2010-11-09 02:00:0 2010-11-09 02:00:4 COMPLETE 00:00:35     1.67 DISK
LIGPRD2  2010-11-09 02:00:5 2010-11-09 02:03:2 COMPLETE 00:02:26     1.38
SW1      2010-11-08 00:05:0 2010-11-08 00:06:1 COMPLETE 00:01:06   519.17 DISK
SW1      2010-11-09 00:05:0 2010-11-09 00:08:0 COMPLETE 00:03:04     2.01 DISK

PL/SQL procedure successfully completed.

Here you can see the databases in the catalog – PROPRD, LIGPRD1, LIGPRD2 and SW1. The columns – “Start Time”, “End Time” and “Time Taken” – are self-explanatory. The “Output Size” shows the size of the backupset produced. The “Status” column shows the status of the job – the key to this report. If it shows “COMPLETE”, then all was well in the job. If it shows “FAILED” then lines below show what actually failed. For instance you can see on 8th Nov, incremental backup of one datafile of PROPRD failed. That one definitely needs investigating.  You got all that important information in just one report. As you add all the databases into the same catalog, your reports will be more complete and expansive.

Construction

Now that you saw the result of the tool, let’s see the code behind it. First I created a user – RMAN_COMMON:

SQL> grant create session, unlimited tablespace, create view to rman_common identified by rman_common;

We need just three views from the repositories; so this user needs to be granted select privileges on those only. As SYS user, grant these:

grant select on rman_PROPRD.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_job_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_job_details to rman_common;
grant select on rman_11g.rc_rman_backup_job_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD11.rc_rman_backup_subjob_details to rman_common;
grant select on rman_LIGPRD21.rc_rman_backup_subjob_details to rman_common;
grant select on rman_11g.rc_rman_backup_subjob_details to rman_common;
--
grant select on rman_PROPRD.rc_rman_status to rman_common;
grant select on rman_LIGPRD11.rc_rman_status to rman_common;
grant select on rman_LIGPRD21.rc_rman_status to rman_common;
grant select on rman_11g.rc_rman_status to rman_common;

Of course, you will need to customize the above script to add more repositories as you add them.

Next, you will create the views by selecting from the corresponding views from individual repositories. Instead of creating a view with all the columns, I chose only a few columns. This will help us in combining the views from 10g and 11g where the columns could be different.


conn rman_common/rman_common
--
create view rc_rman_backup_job_details
as
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_PROPRD.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_job_details
union all
select
        db_name,
        start_time,
        end_time,
        status,
        time_taken_display,
        output_bytes_display,
        output_device_type,
        session_key,
        session_recid,
        session_stamp
from rman_11g.rc_rman_backup_job_details
/
create view rc_rman_backup_subjob_details
as
select
        operation,
        input_type,
        status,
        session_stamp
from rman_PROPRD.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_LIGPRD11.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        input_type,
        status,
        session_stamp
from rman_LIGPRD21.rc_rman_backup_subjob_details
union all
select
        operation,
        input_type,
        status,
        session_stamp
from rman_11g.rc_rman_backup_subjob_details
/
create view rc_rman_status
as
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_PROPRD.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_LIGPRD11.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
        session_key,
        session_recid
from rman_LIGPRD21.rc_rman_status
union all
select
        row_level,
        operation,
        object_type,
        status,
        session_key,
        session_recid
from rman_11g.rc_rman_status
/

Now that the views are in place, you can create the actual tool. Here is the PL/SQL code:

/*---------------------------------------------------------------------------
Name            : RMAN Job Summary Report
Version         : 2.0
Released        : Nov 9th, 2010
Author          : Arup Nanda.
Description     : Creates a report of all backup jobs in a specified number
                  of days. If anything failed, it also shows the sub-job details.
                  Tested on 11g; but will work on 10gR2 as well.
                  Connect to RMAN repository and execute.
                  For number of days, change the l_days constant.
---------------------------------------------------------------------------*/
prompt
prompt ********  RMAN Database Backup Report *********
prompt
set serveroutput on size unlimited
declare
        l_days                  constant number := 2;
        l_place                 varchar2(400);
        l_db_name               rc_rman_backup_job_details.db_name%type;
        l_start_time            varchar2(40);
        l_end_time              varchar2(40);
        l_status                rc_rman_backup_job_details.status%type;
        l_time_taken_display    rc_rman_backup_job_details.time_taken_display%type;
        l_output_bytes_display  rc_rman_backup_job_details.output_bytes_display%type;
        l_output_device_type    rc_rman_backup_job_details.output_device_type%type;
        l_session_key           rc_rman_backup_job_details.session_key%type;
        l_session_recid         rc_rman_backup_job_details.session_recid%type;
        l_session_stamp         rc_rman_backup_job_details.session_stamp%type;

        l_operation             rc_rman_backup_subjob_details.operation%type;
        l_input_type            rc_rman_backup_subjob_details.input_type%type;

        l_command_level         varchar2(9);
        l_object_type           rc_rman_status.object_type%type;
        cursor bjd_cur
        (
                p_days in number
        )
        is
        select
                bjd.db_name,
                to_char(bjd.start_time, 'yyyy-mm-dd hh24:mi:ss'),
                to_char(bjd.end_time, 'yyyy-mm-dd hh24:mi:ss'),
                bjd.status,
                bjd.time_taken_display,
                bjd.output_bytes_display,
                bjd.output_device_type,
                bjd.session_key,
                bjd.session_recid,
                bjd.session_stamp
        from rc_rman_backup_job_details bjd
        where end_time > sysdate - p_days
        order by  bjd.db_name, bjd.start_time;

begin
        l_place := 'Place 100';
        dbms_output.put_line(
                rpad('DB Name',8)
                ||' '||
                rpad('Start Time',18)
                ||' '||
                rpad('End Time',18)
                ||' '||
                rpad('Status',8)
                ||' '||
                rpad('Time Taken',8)
                ||' '||
                rpad('Output Size',8)
                ||' '||
                rpad('Type',8)
        );
        dbms_output.put_line(
                rpad('-',8,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',18,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
                ||' '||
                rpad('-',8,'-')
        );
        open bjd_cur (l_days);
        loop
                fetch bjd_cur
                into
                        l_db_name,
                        l_start_time,
                        l_end_time,
                        l_status,
                        l_time_taken_display,
                        l_output_bytes_display,
                        l_output_device_type,
                        l_session_key,
                        l_session_recid,
                        l_session_stamp
                ;
                exit when bjd_cur%notfound;
                dbms_output.put_line(
                        rpad(l_db_name ,8)
                        ||' '||
                        rpad(l_start_time ,18)
                        ||' '||
                        rpad(l_end_time ,18)
                        ||' '||
                        rpad(l_status ,8)
                        ||' '||
                        rpad(l_time_taken_display ,8)
                        ||' '||
                        rpad(l_output_bytes_display ,8)
                        ||' '||
                        rpad(l_output_device_type,8)
                );
                --
                --
                l_place := 'Place 300';
                if (l_status != 'COMPLETED') then
                        for bsjd_rec in (
                        select
                                operation,
                                input_type,
                                status
                        from rc_rman_backup_subjob_details
                        where session_stamp = l_session_stamp
                        ) loop
                        l_place := 'Place 400';
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('Operation',20)
                                ||' '||
                                rpad('Input',20)
                                ||' '||
                                rpad('Status',20)
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                                ||' '||
                                rpad('-',20,'-')
                        );
                        dbms_output.put_line(
                                '.'
                                ||' '||
                                rpad(nvl(l_operation,'.') ,20)
                                ||' '||
                                rpad(nvl(l_input_type,'.') ,20)
                                ||' '||
                                rpad(nvl(l_status,'.') ,20)
                        );
                        end loop;
                        --
                        l_place := 'Place 500';
                        dbms_output.put_line(
                                '.  '||
                                rpad('Level' ,6)
                                ||' '||
                                rpad('Status' ,8)
                                ||' '||
                                rpad('Operation' ,20)
                                ||' '||
                                rpad('Object Type' ,20)
                        );
                        dbms_output.put_line(
                                '.  '||
                                rpad('-' ,6,'-')
                                ||' '||
                                rpad('-' ,8,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                                ||' '||
                                rpad('-' ,20,'-')
                        );
                        for status_rec in (
                                select
                                        rpad('-', row_level, '-')||'>' command_level,
                                        operation,
                                        object_type,
                                        status
                                from rc_rman_status
                                where session_key = l_session_key
                                order by row_level, session_recid
                        ) loop
                                l_place := 'Place 600';
                                dbms_output.put_line(
                                        '.  '||
                                        rpad(nvl(status_rec.command_level,'.') ,6)
                                        ||' '||
                                        rpad(nvl(status_rec.status,'.') ,8)
                                        ||' '||
                                        rpad(nvl(status_rec.operation,'.') ,20)
                                        ||' '||
                                        rpad(nvl(status_rec.object_type,'.') ,20)
                                );
                        end loop;
                end if;
                end loop;
exception
        when OTHERS then
                dbms_output.put_line(l_place);
                raise;
end;
/

This is the PL/SQL code in the file status.sql.  You can adjust the value of the constant l_days to generate the report for as many days as you want. When you run the script it shows the status of the RMAN jobs in last 2 days.

Hope you find this useful. As always, any feedback will be highly appreciated.

52 comments:

Alberto Frosi said...

Great job Arup, i've implemented today in my RMAN catalog for view a solid report for all RMAN backup.Unfortunately i have 3 instance on 9i, and my DB catalog is on 10g, for these 3 instances i cannot view anything with your report.
Alberto

Arup said...

@Alberto - thanks. Sorry, I don't have any 9i databases wot test it on. The catalog views may be different in 9i. Anyway, as I mentioned in the post, it's only for 10g and 11g; not others.

Alberto Frosi said...

what a pity! Would be nice to implement in your report even if the backup is a full or incremental, or only for the archivelogs.
Alberto

Jared said...

Very nice Arup, I will be implementing this.

Vishal said...

Arup,

We take backups by using BCVs, and bringing the database on backup server on mount stage and taking backups on Tape. When I query RC_RMAN_BACKUP_JOB_DETAILS it gives status of full backups as "RUNNING WITH ERRORS" even though the logfile states it completed successfully.

The WARNINGS are fine, since the control file on BCV that is used to mount, rman complains about it not being current!

Any thoughts on this!

Thanks,
Vishal

Vishal said...

I am trying to include this in a custom GRID Control report and am using the following querry:

select
input_type "Backup Object",
output_device_type "Target Device",
status "Status",
output_bytes_display "Backup Size",
output_bytes_per_sec_display "Backup Speed per Sec",
time_taken_display "Time Taken",
start_time "Backup Begin",
end_time "Backup End"
from rmanadmin.rc_rman_backup_job_details@to_catalog
where db_name = 'PROD'
and start_time > sysdate - 7
and input_type in ('DB FULL','ARCHIVELOG','BACKUPSET')
order by session_recid desc

Vishal

Vishal said...

I meant to rectify the STATUS mentioned above, it should be "RUNNING WITH WARNINGS" instead of "RUNNING WITH ERRORS" on my first comment!

Arup said...

@Vishal - if I understand correctly, this is what you are doing:

(1) bring the database to backup mode
(2) break the bcv
(3) mount bcv copy on a different server
(4) bring it up all the way
(5) backup with RMAN incremental

In this case, there is no difference to RMAN and it shouldn't complain about backup controlfile.

Vishal said...

Arup thats exactly the sequence of events except, database only comes up on mount state on BCV side and a full backup is taken.

In rman log the following warnings comes:

1) Starting backup at 11-11-2010 00:35:21
RMAN-06554: WARNING: file 1 is in backup mode
-- Since BCVs were split after primary was in hot backup mode

2) RMAN-06497: WARNING: control file is not current, control file AUTOBACKUP skipped

Even with these warnings the backup completes fine. We have also restored using these backups on Physical Standby as well!.

It also states it finished successfully in rman log and when I go to check in RC_RMAN_BACKUP_JOB_DETAILS it shows "RUNNING WITH WARNINGs" instead of "COMPLETED WITH WARNINGs" for last few runs.

I checked 2 weeks prior and STATUS for that timeframe shows up correct as "COMPLETED WITH WARNINGS" but not for recent 4-5 runs. Everyday rman log is clean and backup completes successfully to Tapes as seen from netwroker console as well!

Thanks,
Vishal

Alberto said...

Hi Arup, when i run status.sql for view my backup status i see:

DB Name Start Time End Time Status Time Tak Output S Type BackupType
-------- ------------------ ------------------ -------- -------- -------- -------- ----------
CTY1 2010-11-21 13:00:0 2010-11-21 13:00:3 COMPLETE 00:00:28 146.00 DISK ARCHIVELOG
CTY1 2010-11-21 16:00:0 2010-11-21 16:30:5 COMPLETE 00:30:48 48.79 DISK DB FULL
CTY1 2010-11-21 17:00:1 2010-11-21 17:00:2 COMPLETE 00:00:10 32.14 DISK ARCHIVELOG
CTY1 2010-11-21 21:00:0 2010-11-21 21:00:2 COMPLETE 00:00:19 143.35 DISK ARCHIVELOG

I run a archive log backup today the first time at 9 AM and the second one at 10.45 AM but in output result this backup set don't appear.
I try run command list backupset from RMAN console and here there are.
I try to see in the view on RMAN catalog but this backup set dont'exist.The last backupsets are on 11/21/2010.
What happens?
Why the difference?
Alberto

Alberto said...

Ciao Arup,
I found the trick, for this RMAN backup i used control file instead of recovery catalog and for this reason that i did not find the last backupset.
I changed my RMAN script and all work fine... your tool too....
thanks a lot
Alberto

PT said...
This comment has been removed by the author.
PT said...

Arup,

Seems to me that you have installed multiple RMAN repositories; looks like one repository per database?

I wonder why not just keep all your RMAN information, for all DBs, in a single repository, then you only have to query one set of tables, rather than having to create new views, etc?

Cheers,
Pete

Kiran said...

Arup,

I am writing a report to find out the successful level0 and level1 backups in last 1 week. I there a way to distinguish level0 and level1 backup's from your report.

Suresh.G said...

The report is very nice arup..
I have created similar ways using only sql statements and your approach helped me a lot with pl/sql.

can you suggest how to get hostnames in the report.

1) Full backup runs on DR site and
2) ARC backup runs on Prod site.

I want to add column of hostname to get prod & dr host name in the consolidated backup report.

I tried rc views and by v$instance but i am not successful to get the source hostname of FULL & ARC backup types.

Can you please help me on this..

Regards,
Suresh.G

Anonymous said...

Hi, This is a great post. I tried modifying the code so that I get, only DBNAME,DBID, Start time, End time and the size of each database backup, but I ended up writing incorrect querie. Can you help modify the code

Alberto said...

Hi,
post your code and just let me take a look...

Arup said...

@Anonymous - thank you for your compliments.

Regarding what you asked for, you can change the lines:

dbms_output.put_line(
rpad(l_db_name ,8)
||' '||
rpad(l_start_time ,18)
||' '||
rpad(l_end_time ,18)
||' '||
rpad(l_status ,8)
||' '||
rpad(l_time_taken_display ,8)
||' '||
rpad(l_output_bytes_display ,8)

to

dbms_output.put_line(
rpad(l_db_name ,8)
||' '||
rpad(l_start_time ,18)
||' '||
rpad(l_end_time ,18)
||' '||
rpad(l_output_bytes_display ,8)


This will also need a change in the header. Change:

dbms_output.put_line(
rpad('DB Name',8)
||' '||
rpad('Start Time',18)
||' '||
rpad('End Time',18)
||' '||
rpad('Status',8)
||' '||
rpad('Time Taken',8)
||' '||
rpad('Output Size',8)
||' '||
rpad('Type',8)
);
dbms_output.put_line(
rpad('-',8,'-')
||' '||
rpad('-',18,'-')
||' '||
rpad('-',18,'-')
||' '||
rpad('-',8,'-')
||' '||
rpad('-',8,'-')
||' '||
rpad('-',8,'-')
||' '||
rpad('-',8,'-')
);

to:

dbms_output.put_line(
rpad('DB Name',8)
||' '||
rpad('Start Time',18)
||' '||
rpad('End Time',18)
||' '||
rpad('Size',8)
);
dbms_output.put_line(
rpad('-',8,'-')
||' '||
rpad('-',18,'-')
||' '||
rpad('-',18,'-')
||' '||
rpad('-',8,'-')
||' '||
rpad('-',8,'-')
);

Hope this helps.

Anonymous said...

Alberto,

Firstly, I dont find the rmaan_ views as you said:

grant select on rman_PROPRD.rc_rman_backup_job_details to rman_common;

I get error table or view does not exists(i believe PROPRD is the name of database. So i substituted it with the db_name I have).

I tried confirming by running this query in the catalog db:

SQL> select owner,object_name from dba_objects where object_name like 'RMAN%';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
RMAN
RMAN_SEQ

RMAN2
RMAN_SEQ

Alberto said...

Hi,
rman_PROPRD is owner (and username) to connect of your catalog db.
rc_rman_backup_job_details view is in schema name of your catalog db.
you grant only user rman_common to select this existent view.
Can you post your string connection to catalog db?
HTH
Alberto

Anonymous said...

Alberto,

Once I connect to catalog database. I just use

connect rman/password;

Anonymous said...

if rman_PROPRD is the user you are connecting, then can you tell me why you are using rman_LIGPRD11 rman_LIGPRD21 and rman_11g. I dont have any of these users

Alberto said...

Hi,
there are main 2 methods to connect to catalog:
1) rman, connect target /
2) rman target user_name_db/passwd_db catalog user_name_catalog/passwd_catalog@ora_server_catalog.

This procedure suppose you create a Oracle catalog database in another server, and all this users, rman_LIGPRD11 rman_LIGPRD21 and rman_11g are different users of your catalog.
e.g. rman_LIGPRD11 is for you finane DB, rman_LIGPRD21 is for your chemical db and so on.
In this examples in the second point should be: rman target user_name_db/passwd_db catalog rman_LIGPRD11/passwd_catalog@ora_server_catalog.
If you don't use catalog DB in the sys schema of your DB there is the same views called V_$RMAN_BACKUP_SUBJOB_DETAILS and so on to select for this tool, and this is procedure is described in point 1.
HTH

Alberto

P.S.:would be nice if you sign with your real name, Anonymous is not really that great..:)

Roshan Jose said...

Sure Alberto,

The query is good. Can you tell me how this query can be modified to get info about full backups only and not incremental or archive.

Alberto said...

Hi Roshan Jose,
sure, there are 2 methods:
1)modifying
RC_RMAN_BACKUP_JOB_DETAILS views, adding a where clause with input_type='DB FULL'
RC_RMAN_BACKUP_SUBJOB_DETAILS views, adding a where clause with input_type='DB FULL'
RC_RMAN_STATUS views, adding a where clause with object_type='DB FULL'

with this method you modify the views in permanent mode, so when you run the query, you should see always only a DB FULL backup

2)modifying the script adding in these sections
.....
declare
l_days constant number := 2;
l_place varchar2(400);
l_db_name rc_rman_backup_job_details.db_name%type;
l_start_time varchar2(40);
l_end_time varchar2(40);
........
bjd.session_stamp,
bjd.input_type
from rc_rman_backup_job_details bjd
where end_time > sysdate - p_days
and input_type = 'DB FULL'
order by bjd.db_name, bjd.start_time;
begin
l_place := 'Place 100';
dbms_output.put_line(
....
operation,
input_type,
status
from rc_rman_backup_subjob_details
where session_stamp = l_session_stamp
and input_type = 'DB FULL'
) loop
select
rpad('-', row_level, '-')||'>' command_level,
operation,
object_type,
status
from rc_rman_status
where session_key = l_session_key
and object_type = 'DB FULL'
order by row_level, session_recid
end if;
end loop;
.......
exception
end;

/

HTH
Ciao
Alberto

Anonymous said...

hai arup,

In the script which you have written to check the backup status ,it shows sucess or failure message.but what if any backups are missing .

how do we get that status .

Regards,
Keerthi.

Arup said...

@Keerthi - I a m not sure what you mean by backups missing. The script checks from recovery catalog. Even if backups are missing, the catalog will still have a history of that and the script will report it.

If you want to make sure the catalog removes the reference to backups that are missing, simply run a "crosscheck" command to accomplish that. The script then will not show the missing ones.

Anonymous said...

I used your query. But when I tried to modify it, I messed it up.

I was trying to generate 2 reports from a single PL/SQL

1st report should contain the list of backups completed in the last 12 hours

2nd report should contain the list of databases missed for backups.


Both the reports should have the same columns:

DB_NAME, Backup_type, START_TIME END_TIME, Size of backup

Can you help me with this.

Regards,
Anita

Anonymous said...

forgot to add that the 2nd report contains list of databases that missed backup in the last week

Thanks,
Anita

Alberto said...

@Anita Can you post your 2nd script?I don't know what you mean for missing backup, do you have read Arup's answer for Keerthi's question? Could be usefull to understand.
Ciao

Anonymous said...

@Alberto:

I'll explain.

In our environment, there are 783 databases in 115 servers. Full backup is scheduled for the databases on different days of the week. The full backup of a database runs once a week. It so happens that backup could get missed for a particular database. It might be difficult to know that, hence we decided that we might need 2 reports:
1) backups that ran yesterday
2) backups that were missed for a week

I didnt get the script yet. I just tried modifying Arup's script. But couldn't get the desired output.

-Anita

Alberto said...

@Anita - Ok these scripts check the recovery catalog backup status, could be completed or with errors, but the backup was always started .Backup missed could be a backup which doesn't run for other errors, but always scheduled.
In this case this script is not usefull at all, I think, because the reasons are not in RMAN, for example crontab in you're in Unix or Linux enviroment or schedule task if you're in Windows.
Which schedule are you using to schedule backups ?If you are using Oracle Scheduler could be usefull check other views to verify these operations.

Alberto said...

@Anita - check your policy retention in RMAN also and verify crosscheck command also.

Anita said...

@Alberto: The backups are scheduled from TSM.

But I think it doesn't matter.

Let me try to put it in another way

1st script contains a list of database backups that ran in the past 24 hours

2nd list contains the list of database backups that didnt run for the past 7 days. This second list looks more like 1st list. But only thing is the difference between the time since last backup and sysdate should be more than 7

Alberto said...

@Anita - I now understand the issue, doesn't matter the schedule or something else.
Very strange...can you post script 1 or 2 or both for small example ?could be help.

Anita said...

@Alberto:

I used the same script given in this blog. Tried modifying it. But ended up in getting it messed. Can you help with this

Alberto said...

@Anita - I use the same script, it's egual or more similiar and reproduce this output in my case:

Last 1 day:

DB Name Start Time End Time Status Time Tak Output Size Type BackupType
------------ ------------------ ------------------ -------- -------- -------- -------- ----------
AAA 17-06-2012 16:00 17-06-2012 16:36 COMPLETE 00:36:32 XXXXXXX DISK DB FULL
AAA 17-06-2012 18:00 17-06-2012 18:00 COMPLETE 00:00:21 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 22:00 17-06-2012 22:00 COMPLETE 00:00:30 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 02:00 18-06-2012 02:01 COMPLETE 00:00:38 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 06:00 18-06-2012 06:00 COMPLETE 00:00:33 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 14:00 18-06-2012 14:02 COMPLETE 00:01:35 XXXXXXX DISK ARCHIVELOG

....

Alberto said...

Last 3 days:

DB Name Start Time End Time Status Time Tak Output Size Type BackupType
------------ ------------------ ------------------ -------- -------- -------- -------- ----------
AAA 15-06-2012 16:00 15-06-2012 16:39 COMPLETE 00:39:26 XXXXXXX DISK DB FULL
AAA 15-06-2012 18:00 15-06-2012 18:01 COMPLETE 00:00:37 XXXXXXX DISK ARCHIVELOG
AAA 15-06-2012 22:00 15-06-2012 22:01 COMPLETE 00:01:13 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 02:00 16-06-2012 02:01 COMPLETE 00:00:52 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 06:00 16-06-2012 06:01 COMPLETE 00:00:56 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 06:00 16-06-2012 06:39 COMPLETE 00:38:37 XXXXXXX DISK DB FULL
AAA 16-06-2012 10:00 16-06-2012 10:01 COMPLETE 00:00:39 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 14:00 16-06-2012 14:01 COMPLETE 00:00:37 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 16:00 16-06-2012 16:38 COMPLETE 00:38:24 XXXXXXX DISK DB FULL
AAA 16-06-2012 18:00 16-06-2012 18:00 COMPLETE 00:00:24 XXXXXXX DISK ARCHIVELOG
AAA 16-06-2012 22:00 16-06-2012 22:01 COMPLETE 00:00:42 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 02:00 17-06-2012 02:01 COMPLETE 00:00:47 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 06:00 17-06-2012 06:00 COMPLETE 00:00:23 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 10:00 17-06-2012 10:00 COMPLETE 00:00:31 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 14:00 17-06-2012 14:01 COMPLETE 00:00:26 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 16:00 17-06-2012 16:36 COMPLETE 00:36:32 XXXXXXX DISK DB FULL
AAA 17-06-2012 18:00 17-06-2012 18:00 COMPLETE 00:00:21 XXXXXXX DISK ARCHIVELOG
AAA 17-06-2012 22:00 17-06-2012 22:00 COMPLETE 00:00:30 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 02:00 18-06-2012 02:01 COMPLETE 00:00:38 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 06:00 18-06-2012 06:00 COMPLETE 00:00:33 XXXXXXX DISK ARCHIVELOG
AAA 18-06-2012 14:00 18-06-2012 14:02 COMPLETE 00:01:35 XXXXXXX DISK ARCHIVELOG

I have the same output in the last 3 days in these 2 scripts , how do you get the 2nd list of databases didn't run?
Which script you tried to change?
the views or the final?

Anita said...

Below are the queries and their respective output

Report for the backup that ran in the past 24 hours

I used the same PL/SQL query which Arup had posted in the blog

DB Name Start Time End Time Status Time Tak Output S Type
-------- ------------------ ------------------ -------- -------- -------- --------
AAAAAA 2012-06-17 13:06:1 2012-06-17 13:06:3 COMPLETE 00:00:13 656.62 DISK
AAAAAA 2012-06-17 22:02:5 2012-06-17 22:09:4 COMPLETE 00:06:56 15.24 DISK

2nd Report for full backups that didnt run for past 7 days

In the same query I just changed

end_time > sysdate - p_days

to
end_time - sysdate=7

I am not sure if these are right. Also is there any way I can get whether they are Full,incremental or archive backups?

Alberto said...

@Anita - sure, you're using the right settings, you have modified from 1 day to 7 days this parameter, that's right, is the output of 2nd script blank?
Sure, it's possible have the type of backup, I've modified a little bit the Arup's script.

Anita said...

Glad to hear.

Yes the output of second script is blank

Can you give that modified script?

Alberto said...

@Anita - How many days ago you 2nd script works?
yes, if you want, can you send me a mail so i send the scripts modified?
Do you have a linkedin account or twitter?

Anita said...

I didnt understand about the question about scripts.

I have only a gmail account. I searched for your email in profile, but I didnt get.

Can you share your email id

Alberto said...

@Anita - now I've shared my mail account in gmail.
My question is how many days back your second script works?
If you change 7 to 6 or 5 days work?

Anita said...

Its not working when I change it to 3,4 or 5.. I guess something's wrong

Alberto said...

@Anita - Ok, send me a mail so I can send you the scripts.

Anonymous said...

Arup

I see some mismatch between the data in V$rman views in the database and the data in rman catalog table/views.What would be the reason for this behaviour.

Regards
sangeeth

Alberto said...

Hi sangeeth,
can you post some examples, please?
Ciao
Alberto

Manish Shah said...

If you are using OEM to manage the backups follo. query will provide the similar output as Arunp's report does.

select DATABASE_NAME, host, status, to_char(start_time,'dd-MON-yyyy hh24:mi') start_time,
to_char(end_time,'dd-MON-yyyy hh24:mi') end_time, input_type, output_device_type
from mgmt$ha_backup
where END_TIME between trunc(SYSDATE) -1 and SYSDATE
order by host, database_name

Alberto said...

Hi Manish,
sure, it's similar but in this case I have some limitations:
1) Can you see only the last backup full or archive but not all the history.
2) You don't have a repository where you can see all your backups for servers.
thanks.
Ciao

Anonymous said...

I had two combinations , full and incremental backups , can you pls help in modifying the script

Baskar said...

Manish,

From mgmt$ha_backup or from other repository views , are you able to get the incremental level say 0 or 1 of the backup taken?

baskar.l

Translate