Checking Failed Backups In Rman

Today I like post a simple query to check the backup which have problem and there are “not usable” for a possible restore. It is very important check your backups (full, archives, CF, spfile) in regular way to evite surprises.
The next select prompt the backup with problems, only completed, if you want the running you need add into the in clause the tags RUNNING WITH WARNINGS and RUNNING WITH ERRORS;

 
SET LINES 1000
SET PAGES 1000
column "Time Elapsed" format a12

select 
  session_key,
  input_type,
  status,
  to_char(start_time,'yyyy-mm-dd hh24:mi') AS  "Backup Date",
  time_taken_display  AS "Time Elapsed"
from 
  v$rman_backup_job_details
where 
  status in ('COMPLETED WITH WARNINGS','COMPLETED WITH ERRORS','FAILED')
order by session_key desc
/

In my case it return multi-rows, I have a problem with this backup;

 
SESSION_KEY INPUT_TYPE    STATUS                  Backup Date      Time Elaps
___________ _____________ _______________________ ________________ __________
      30574 ARCHIVELOG    FAILED                  2016-05-08 04:03 00:50:25
      30552 ARCHIVELOG    FAILED                  2016-05-07 04:32 00:19:22
      30505 DB FULL       FAILED                  2016-05-04 21:04 04:57:18
      30484 DB FULL       FAILED                  2016-05-03 21:03 05:05:16
      30278 ARCHIVELOG    FAILED                  2016-04-24 02:31 01:46:10
      30206 DB FULL       FAILED                  2016-04-20 21:02 04:59:07
      30083 ARCHIVELOG    FAILED                  2016-04-15 02:31 02:55:41
      29963 DB FULL       FAILED                  2016-04-09 20:46 04:55:28
      29808 DB FULL       FAILED                  2016-04-03 02:12 00:00:44
      29804 ARCHIVELOG    FAILED                  2016-04-03 01:03 01:04:29
      29802 DB FULL       FAILED                  2016-04-02 20:47 05:01:33
      29536 ARCHIVELOG    FAILED                  2016-03-21 12:05 00:09:54
      29507 ARCHIVELOG    FAILED                  2016-03-20 03:31 ø
      29483 ARCHIVELOG    FAILED                  2016-03-19 03:35 00:00:40
      29479 ARCHIVELOG    FAILED                  2016-03-19 03:02 00:31:59
      29203 DB FULL       COMPLETED WITH ERRORS   2016-03-06 00:54 14:20:00
      29201 DB FULL       FAILED                  2016-03-05 22:03 02:33:58
      29183 ARCHIVELOG    FAILED                  2016-03-05 03:31 01:29:18
      29072 ARCHIVELOG    FAILED                  2016-02-29 01:17 00:32:03
      29046 DB FULL       FAILED                  2016-02-27 21:58 04:35:56
      28986 ARCHIVELOG    FAILED                  2016-02-25 03:30 ø

21 filas seleccionadas.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s