This discussion is archived
10 Replies Latest reply: Dec 7, 2012 2:09 PM by Daryl E. RSS

Possible to show datafiles that require level 0 backup?

Daryl E. Explorer
Currently Being Moderated
We have some large databases with many datafiles. Is there a command to list / report which datafiles dont have a level 0 backup?
From what I am seeing report need backup - ignores reporting datafiles that may just have a level 1 backup - even if there is no level 0 to base it from.
Is this correct?

Finding this out the hard way when creating a duplicate and finding out that the duplicate fails as it cant find the level 0 for some datafiles

(Linux 11gr1 100tb db Exadata)

Thanks for helping me make sense of this..

Daryl
  • 1. Re: Possible to show datafiles that require level 0 backup?
    mseberg Guru
    Currently Being Moderated
    Hello;

    RMAN> REPORT NEED BACKUP;

    Your retention policy will come into play when using this.

    Best Regards

    mseberg
  • 2. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    I tried that .. it doesnt list any issues but I know there is..
    RMAN> report need backup
    2> ;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 89 days
    Report of files that must be backed up to satisfy 89 days recovery window
    File Days  Name
    ---- ----- -----------------------------------------------------
    
    Nothing..
    but look at this.. there is no level 0 backup .. it fell off .. ie over 89 days old.
    
    RMAN> list backup of datafile 71;
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    93363024 Incr 1  793.18G    SBT_TAPE    10:39:55     14-OCT-12
            BP Key: 93363042   Status: AVAILABLE  Compressed: NO  Tag: TAG20121013T210225
            Handle: bk_PCINF_69501_1_796597627   Media: JW0536
      List of Datafiles in backup set 93363024
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    93551429 Incr 1  10.60G     SBT_TAPE    00:04:28     20-OCT-12
            BP Key: 93551451   Status: AVAILABLE  Compressed: NO  Tag: TAG20121020T210951
            Handle: bk_PCINF_72018_1_797202927   Media: JW5859
      List of Datafiles in backup set 93551429
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    93978586 Incr 1  10.60G     SBT_TAPE    00:07:03     27-OCT-12
            BP Key: 93978613   Status: AVAILABLE  Compressed: NO  Tag: TAG20121027T211433
            Handle: bk_PCINF_74209_1_797807878   Media: JW4007
      List of Datafiles in backup set 93978586
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    94415892 Incr 1  10.60G     SBT_TAPE    00:05:55     03-NOV-12
            BP Key: 94415905   Status: AVAILABLE  Compressed: NO  Tag: TAG20121103T211332
            Handle: bk_PCINF_76973_1_798412667   Media: JW5871
      List of Datafiles in backup set 94415892
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    94725007 Incr 1  10.60G     SBT_TAPE    00:06:13     11-NOV-12
            BP Key: 94725015   Status: AVAILABLE  Compressed: NO  Tag: TAG20121111T000027
            Handle: bk_PCINF_80432_1_799027731   Media: JF0177
      List of Datafiles in backup set 94725007
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    95026253 Incr 1  973.96G    SBT_TAPE    06:48:17     18-NOV-12
            BP Key: 95026315   Status: AVAILABLE  Compressed: NO  Tag: TAG20121117T220628
            Handle: bk_PCINF_83850_1_799626027   Media: JW4286
      List of Datafiles in backup set 95026253
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 984352498996 09-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    95819737 Incr 1  448.65G    SBT_TAPE    07:56:00     02-DEC-12
            BP Key: 95819747   Status: AVAILABLE  Compressed: NO  Tag: TAG20121201T221733
            Handle: bk_PCINF_92173_1_800922570   Media: JW3707
      List of Datafiles in backup set 95819737
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      71   1  Incr 1016584342670 01-DEC-12
    
    RMAN>
  • 3. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    I quickly threw this together .. not 100% certain its valid. So at this point i expect my "duplicate" to fail with missing backup for datafile 71.
     (89 days retention)
    
    select * from
    (select file#, max(completion_time) t
    from rc_backup_datafile s
    where db_name = 'PCINF'
    and incremental_level = 0
    and (exists (select * from rc_backup_datafile d where d.db_name = s.db_name and D.FILE# = s.file# and d.completion_time > s.completion_time and d.completion_time > sysdate -89 and d.incremental_level != 0)
    or completion_time > sysdate - 89)
    group by file#
    order by 1
    )
    where t < sysdate-89
    
    
         FILE# T        
    ---------- ---------
            71 12-JUL-11
    1 row selected.
  • 4. Re: Possible to show datafiles that require level 0 backup?
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Not sure it this helps, but you can use the command like this too :

    RMAN> REPORT NEED BACKUP REDUNDANCY n;

    RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS;

    RMAN> REPORT NEED BACKUP DAYS = n;

    Did you search the exedata forum ?

    Exadata

    Later

    OK, I just did. Not much there on your question. You are probably in the right place.

    Best Regards

    mseberg

    Edited by: mseberg on Dec 7, 2012 3:09 PM
  • 5. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    I did try the various forms of the commands you listed .. it highlighted that some of my backups were a bit "stale" but it missed completely that I dont have a valid level 0 backup (for some ffor atleast 1 datafile.

    This isnt specific to Exadata - as we have other databases that we are facing this same challenge. With multi-tb databases we cant be certain that we get all datafiles covered off due to media failures etc. A backup incremental 0 starts at datafile 1 it seems such that the later datafiles may never get there if the backups fail after a day or two of writing. I do like the idea that a level 1 will force a level 0 if it doesnt exist but that makes me wonder how that really works as it apparently missed the above case. I guess if you dont deligently obosolete/expire backups from the catalog you can run into this issue.

    Interesting..I may have others. I guess the real test will be when DUPLICATE tries to restore these datafiles. Then I will know for sure I dont have a level 0 (valid restore) of these datafiles.
    At that point, the DUPLICATE will fail, no harm, I will backup at source and then retry the DUPLICATE.
    Learning more about holes in our bacskups than I want to know about :-(

    We are working on standby databases for our Exadatas and other large environments just to be 100% certain we have our b*tts covered.
    RMAN> report need backup redundancy  2;
    
    Report of files with less than 2 redundant backups
    File #bkps Name
    ---- ----- -----------------------------------------------------
    11   0
    72   1
    73   1
    
    
    RMAN> list backup of datafile 72;
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    93551430 Incr 1  34.22G     SBT_TAPE    00:25:19     20-OCT-12
            BP Key: 93551452   Status: AVAILABLE  Compressed: NO  Tag: TAG20121020T210951
            Handle: bk_PCINF_72009_1_797202771   Media: JW5859
      List of Datafiles in backup set 93551430
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      72   1  Incr 991382884735 20-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    94055574 Incr 1  4.40T      SBT_TAPE    29:44:37     29-OCT-12
            BP Key: 94055580   Status: AVAILABLE  Compressed: NO  Tag: TAG20121027T211433
            Handle: bk_PCINF_74200_1_797807701   Media: JW1663
      List of Datafiles in backup set 94055574
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      72   1  Incr 995769181801 27-OCT-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    95026255 Incr 1  232.77G    SBT_TAPE    07:13:15     18-NOV-12
            BP Key: 95026318   Status: AVAILABLE  Compressed: NO  Tag: TAG20121117T220628
            Handle: bk_PCINF_83841_1_799625210   Media: JW3310
      List of Datafiles in backup set 95026255
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      72   1  Incr 1008575022489 17-NOV-12
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    95819741 Incr 1  1.79T      SBT_TAPE    12:20:29     02-DEC-12
            BP Key: 95819753   Status: AVAILABLE  Compressed: NO  Tag: TAG20121201T221733
            Handle: bk_PCINF_92170_1_800921950   Media: JW0445
      List of Datafiles in backup set 95819741
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      72   1  Incr 1016582010090 01-DEC-12
    And this one ..
    RMAN> list backup of datafile 11;
    
    specification does not match any backup in the repository
  • 6. Re: Possible to show datafiles that require level 0 backup?
    mseberg Guru
    Currently Being Moderated
    Hmmm

    Can you not do duplicate from active database?

    With exedata hardware this should be very fast.

    I check RMAN everyday, Saturday, Sunday, my days off. Everyday. I'm waiting for management to come around the corner with a restore issue and I'm hoping to answer "yes, i just checked that a few hours ago".


    Best Regards

    mseberg

    Edited by: mseberg on Dec 7, 2012 3:33 PM
  • 7. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    The particulare Exadata we are duplicating is a V2 - it only has a 1gig port. I am not an RMAN expert, but it appeared thru quick testing that it would only hit one node so max 1-1gig port.
    I am restoring from our backups and that I can do thru multiple 10gig ports. So backups took weeks .. and never completed in 1 single run .. The backups atleast used all 8 nodes to write out. 8x1gig (theoreticallly) so it was "quicker" to write to tape then to duplicate out only one node.

    So rather then fuss with making the duplicate use more of the V2 IP channels I thought, what better opportunity to test our backups ;-)

    BTW I am duplicating the db to another Exadata X2-2 we have available to us (for the short term) but also duplicating it to a non-exadata with nfs/netapp storage to start using netapp snaps to provide backup images hourly/daily.

    Daryl
  • 8. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    re: "I check RMAN everyday, Saturday, Sunday, my days off. Everyday. I'm waiting for management to come around the corner with a restore issue and I'm hoping to answer "yes, i just checked that a few hours ago".


    ~180 databases with 3 dbas .. not really possible to check every output every day. We count on the return status of the rman jobs but with failures nearly everyday you cant always followup. With an Exadata incremental 0, if it fails, what can you do other then kick it off again and wait another ~week.

    Full Rack Exadata and any multiple tb database is a challenging environment no doubt...

    Too bad rman backup database doesnt check what has been backed up recently vs not recently before it kicks off the threads. Perhaps it does .. but it sure doesnt look like it.

    And bigfile -- multi +15tb datafiles going out thru 1 or 10gig pipes still takes too much time. IB is the answer but powers above me say no to that.. "we cant run IB to every exadata" etc etc..

    Been meaning to see if I can come up with a smarter backup script .. to fire off backup datafile commands rather than simplye backup database... one of many things on the todo list..

    Edited by: Daryl E. on Dec 7, 2012 1:47 PM
  • 9. Re: Possible to show datafiles that require level 0 backup?
    mseberg Guru
    Currently Being Moderated
    Hello again;

    I hope I didn't beat the RMAN checking drum too hard.

    In any event I was looking at this, but have not tried it yet.

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

    http://arup.blogspot.com/2010/11/tool-to-present-consolidated-view-of.html

    This might help tame the beast some.

    Best Regards

    mseberg
  • 10. Re: Possible to show datafiles that require level 0 backup?
    Daryl E. Explorer
    Currently Being Moderated
    Thanks I'll take a look ..

    re: "I hope I didn't beat the RMAN checking drum too hard."
    No worries .. we wish we could do lots more than we could .. unfortunately we do what we can and hope to catch the problems via tools. I have a couple of User defined metrics that we use to check for last valid full and last valid rman backups. It flags them but then the onus is on us to fix them ;-)

    Appreciate the feedback..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points