Infrastructure at your Service

Oracle Team

List all RMAN backups that are needed to recover

By Franck Pachot

.
This blog post is something I had in draft and Laurent Schneider blog post reminds me to publish it. With the right RMAN configuration you should not have to managed backup files yourself. The RMAN catalog knows them and RMAN should be able to access them. If you want to keep a backup for a long time, you just tell RMAN to keep it.
But sometimes, RMAN is not connected to your tape backup software, or the backups are not shared on all sites, and you have to restore or copy the set of files that is needed for a restore database or a duplicate database.

A customer was in that case, identifying the required files from their names because they are all timestamped with the beginning of the backup job. It’s our DMK default. In order to rely on that, the ‘backup database plus archivelog’ was run. And in order to be sure to have all archived logs in those backup sets, any concurrent RMAN job are blocked during that database backup. Because if a concurrent job is doing some archivelog backups, they will be timestamped differently.

RPO and availability

I don’t like that. I don’t want that anything can block the backup of archived logs.
They are critical for two reasons:

  • The Recovery Point Objective is not fulfilled if some archivelog backups are delayed
  • The frequency of archivelog backup is also defined to prevent a full FRA

But if we allow concurrent backup of archived logs, we need something else to be able to identify the whole set of files that are needed to restore the database at that point in time. then my suggestion was to generate the list of those files after each database backup, and keep that list. When we need to restore that backup, then we can send the list to the backup team ans ask them to restore them.

The script

Here is my script, I’ll explain later:

echo "restore controlfile preview; restore database preview;" | NLS_DATE_FORMAT='yyyymmddhh24miss' rman target / | awk '
/Finished restore at /{timestamp=$4}
/Recovery must be done beyond SCN /{if ($7>scn) scn=$7 }
/^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,"") ; files[$0]=1 }
END{ for (i in files) print i > "files-"timestamp"-SCN-"scn".txt" }
' 

this script generate the following file:

files-20150519013910-SCN-47682382860.txt

which list the files needed to do a RESTORE/RECOVER UNTIL SCN 47682382860

the content of the file is:

oracle@dbzhorap01:/home/oracle/ [DB01PP1] sort files-20150519019910-SCN-47682382860.txt
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168278_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168279_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168280_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168281_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168282_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168283_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168284_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169462_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169463_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169464_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169465_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169466_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169467_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169468_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169469_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169470_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169471_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169472_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169481_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169482_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169473_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169474_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169475_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169476_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169477_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169478_p1.bck
/u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169479_p1.bck

and lists the backup pieces for the incremental 0, incremental 1 and archivelogs needed to recover to a consistent state that can be opened. The script lists only backup sets so we are supposed have have backed up the latest archived logs (with backup database plus archivelog for example).

You can put an ‘until scn’^but my primary goal was to run it just after a backup database in order to know which files have to be restored to get that backup (restore or duplicate).

Restore preview

The idea is to rely on RMAN to find the files that are needed to restore and recover rather than doing it ourselves from the recovery catalog. RMAN provides the PREVIEW restore for that:

RMAN> restore database preview
Starting restore at 20150501390436
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
166388  Incr 0  10.53G     DISK        00:52:56     20150516031010
        BP Key: 166388   Status: AVAILABLE  Compressed: YES  Tag: WEEKLY
        Piece Name: /u00/app/oracle/admin/DB01PP/backup/20150516_023003_inc0_DB01PP_961537327_s168278_p1.bck
  List of Datafiles in backup set 166388
  File LV Type Ckp SCN    Ckp Time       Name
  ---- -- ---- ---------- -------------- ----
  1    0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/system.329.835812499
  2    0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/undotbs1.525.835803187
  10   0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/cpy.676.835815153
  17   0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/cpy.347.835815677
  23   0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/cpy.277.835814327
  25   0  Incr 47581173986 20150516023945 +U01/DB01Pp/datafile/cpy.342.835811161
...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
167586  Incr 1  216.09M    DISK        00:01:34     20150519012830
        BP Key: 167586   Status: AVAILABLE  Compressed: YES  Tag: DAYLY
        Piece Name: /u00/app/oracle/admin/DB01PP/backup/20150519_010013_inc1_DB01PP_961537327_s169479_p1.bck
  List of Datafiles in backup set 167586
  File LV Type Ckp SCN    Ckp Time       Name
  ---- -- ---- ---------- -------------- ----
  43   1  Incr 47681921440 20150519012700 +U01/DB01Pp/datafile/cpy_idx.346.835815097

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
167594  105.34M    DISK        00:00:23     20150519015400
        BP Key: 167594   Status: AVAILABLE  Compressed: YES  Tag: DAYLY
        Piece Name: /u00/app/oracle/admin/DB01PP/backup/20150519_010013_arc_DB01PP_961537327_s169481_p1.bck

  List of Archived Logs in backup set 167594
  Thrd Seq     Low SCN    Low Time       Next SCN   Next Time
  ---- ------- ---------- -------------- ---------- ---------
  3    59406   47681333097 20150519010239 47682617820 20150519014652
  4    46800   47681333143 20150519010240 47682617836 20150519014652
  1    76382   47681333188 20150519010240 47682618254 20150519014655
  2    60967   47681333315 20150519010242 47682385651 20150519013711
...

Media recovery start SCN is 47681637369
Recovery must be done beyond SCN 47682382860 to clear datafile fuzziness
Finished restore at 20150501390440

You see the list of datafiles backupsets and archivelog backupsets and at the end you have information about SCN. Let me explain what are those SCNs.

Recovery SCN

Because it is online backup the datafiles are fuzzy. We need to apply redo generaed during backup.
The ‘media recovery start SCN’ is the begining of the archivelog to be applied:

SQL> select scn_to_timestamp(47681637369) from dual;

SCN_TO_TIMESTAMP(47681637369)
---------------------------------------------------------------------------
19-MAY-15 01.10.38.000000000 AM

The ‘recovery must be done beyond SCN’ is the last redo that must be applied to have datafiles consistent:

SQL> select scn_to_timestamp(47682382860) from dual;

SCN_TO_TIMESTAMP(47682382860)
---------------------------------------------------------------------------
19-MAY-15 01.35.58.000000000 AM

In my example, the backup (incremental level 1 + archivelog) started at 01:00:00 and was completed at 01:35:00

Conclusion

And I have a file with the list of backups that are needed to restore or duplicate the database at that point in time. Why do I need that when RMAN is supposed to be able to retrieve them itself? Because sometimes we backup to disk and the disk is backed up to tape without RMAN knowing it. Of course RMAN can connect directly to the tape backup software but that is not for free. Or we want to duplicate to another site where backups are not shared. We need to know which files we have to bring there. And that sometimes requires a request to another team so it’s better to have the list of all files we need.

As usual, don’t hesitate to comment if you see something to improve in my small script.

 

30 Comments

  • Gascard T says:

    Hello Franck, very interesting as usual.
    For a duplicate can I use the restore preview with the clone database ?
    Thank you

     
  • Hi Gascard,
    Thanks for your feedbacl.
    You need to be connected to the target database, so the answer is no.
    There is no ‘duplicate preview’ but I expect that a ‘restore preview’ on the target database lists all the files required for a duplicate at the same SCN.
    Regards,
    Franck.

     
  • Rishi says:

    Hi frank, when I run your script I get the following error:

    awk: cmd. line:5: (FILENAME=- FNR=369) fatal: can’t redirect to `files-31/03/2016-SCN-112305523.txt’ (No such file or directory)
    I am running in bash shell

    any help, thanks Rish

     
  • Hi Rishi,
    Yes you’re right. If your NLS_DATA_FORMAT contains ‘/’ it cannot build a file name with timestamp.
    I updated the script in the blog post to add: NLS_DATE_FORMAT=’yyyymmddhh24miss’ in front on the RMAN command. Should be better.
    Thanks a lot for your comment.
    Regards,
    Franck.

     
  • phani says:

    Hi frank,

    Nice post and very useful.

    i am beginner can you explain the script used to extract the list of the files needed .

    Regards
    phani

     
  • Idris says:

    Can you please provide the full script for below

    List all RMAN backups that are needed to recover

    echo “restore controlfile preview; restore database preview;” | NLS_DATE_FORMAT=’yyyymmddhh24miss’ rman target / | awk ‘
    /Finished restore at /{timestamp=$4}
    /Recovery must be done beyond SCN /{if ($7>scn) scn=$7 }
    /^ *(Piece )Name: / { sub(/^ *(Piece )Name: /,””) ; files[$0]=1 }
    END{ for (i in files) print i > “files-“timestamp”-SCN-“scn”.txt” }

     
  • Idris says:

    Suppose if we want to do pointing time recovery till last 2 days.
    How to use your script for getting the required backup pieces.

     
  • pianz says:

    try run the script and get these error message

    :/home/oracle:>86948:(pid 1831198): Failed to initialize ports using nsrexecd on : Service not available.

    86939:(pid 1831198): Unable to get port range from local nsrexecd: Failed to initialize ports using nsrexecd on Service not available.

    :/home/oracle:>86948:(pid 2195466): Failed to initialize ports using nsrexecd on Service not available.

    86939:(pid 2195466): Unable to get port range from local nsrexecd: Failed to initialize ports using nsrexecd on : Service not available.

    ^Xcc

     
  • na says:

    restore database preview

    I wonder how many people actually had their perfectly functioning databases restored thanks to this command by forgetting to include the word “preview”. Couldn’t oracle find a better commands to do the same?

     
  • Hi,
    The risk on an open database is only getting:

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 10/20/2017 06:42:27
    ORA-19870: error while restoring backup piece /u01/fast_recovery_area/CDB1A/backupset/2017_10_20/o1_ mf_nnndf_TAG20171020T062744_dylyt0vb_.bkp
    ORA-19573: cannot obtain exclusive enqueue for datafile 1

    RMAN do not restore opened files.
    Regards,
    franck.

     
  • na says:

    Hi Franck,

    Please ignore my answer to your earlier comment.

    Is there a way to restore a hot backup (the daily size of archived log files are somewhere around 30-40gb and the total size of the db is about 770gb) without applying any archived redo logs (in other no recovery)? The reason I am asking this, if there are corrupted archived log files, the db won’t open when the recovery happens ( I am aware of an undocumented feature to force open the db having this issue, however I am trying to avoid altogether applying logs to the db and only restore it to the last backup).

    Best
    NA.

     
  • na says:

    Hi Franck,

    If I add little more to the earlier post..

    The script I am using for the backup of the production basically takes a hot backup. Then I clone that on another identical server. My next question is, what should I include in the original backup script that when the output only restores on the target without the requirement of a recovery. You think this is possible under a hotbackup scenario? In the past we seems to have done this without archivedlogs, but not anymore and the command restore database preview on the target tells me to run a recovery too to avoid a database inconsistency.

    Thank you for your answer.
    Best,
    NA.

     
    • Hi,
      You cannot open a database restored from hot backup without recovery. The files are ‘fuzzy’ because they were read from a different point in time, with writes ongoing. The redo stream generated between the start and the end of the backup must be applied to them (i.e recovery) to bring them consistent to the same point in time. This is the reason why you cannot do a hot backup when the database is not in archivelog mode.
      This has always been the case.
      Regards,
      Franck.

       
      • na says:

        Hi Franck,

        This is probably the last question I have on this one.

        How would you handle missing or corrupted archivelog file which is part of a hot backup and needed for a restore?
        Eg: as in the below link (my appologies if links are not allowed here), the workaround is to use the undocumented oracle feature “_allow_read_only_corruption = true” to open the database.
        https://dbpost.wordpress.com/2012/12/22/recover-an-oracle-database-with-missing-archived-logs/

        I have done this with a development env. and managed to open the db even though there were many issues that I have to resolve along the way to get rid of various Ora 000600 and other errors. Also it’s interesting to know what the official oracle narrative on this issue too. I haven’t come across any official solution on this eventhough the issue is so common among the users. I am using oracle 11g by the way.

        https://dbpost.wordpress.com/2012/12/22/recover-an-oracle-database-with-missing-archived-logs/

        Thank you and regards,
        NA

         
        • Hi,
          By setting this parameter you accept to open an inconsistent database. The goal is to get data out (export), import it into another database, and validate the data.
          Better have a standby database which is continuously recovering and you avoid this problem…
          Regards,
          Franck.

           
          • na says:

            Hi Franck,

            Thank you once again.

            I am coming from an informix back ground and fairly new to oracle. Informix allows restores without a need for the archived logs in a hotbackup scenario (regardless whether you have archived logs or not and it’s happy if you apply them or not, and the db can be opened without archived logs and will be consistent and will show no errors whatsoever in the server logs or within the db ).
            In one of your previous answers, you mentioned that Oracle needs the archived logs to recover the db in a hotbackup scenario. In other words Oracle doesn’t support what informix does in a hotbackup scenario described above. Based on the above assesment I have one final question.
            Is there an Oracle rman backup option including the Oracle Enterprise Versions(11g, 12c) that tells it to take a backup(in a hot backup scenario with 30-40gb of archived logs) without the need for archived logs when restoring the same backup on another server/or the same?

            Best Regards,
            NA

             
          • na says:

            Hi Franck,

            In other words, I am pretty much asking for a NOLOGS type backup but in a hobackup scenario. Would that be a possibility?

            Best Regards,
            NA

             
  • na says:

    Thank you Franck.

    Very much appreciated the quick reply

     
    • Hi,
      That’s interesting. By curiosity do you have a link to this Informix feature?
      Basically, I don’t understand how you can backup online without the redo that protects the changes made to memory and not yet in files. Except if you block all changes, but that cannot be called ‘online’. So, In Oracle no you cannot hot backup without the redo. And in other databases, I’m very curious to see how it can be done.
      Regards,
      Franck.

       
  • na says:

    Hi Franck,

    Here is a similar scenario discussed under IBM-Informix env. From memory you need Informix Dynamic Server atleast the standard edition version 7.2x(released in 2001) and above for this feature to work.

    Please see below link.
    http://www-01.ibm.com/support/docview.wss?uid=swg21575948

    In this document you will find the line where you run the Informix restore utility which is called ontape.

    To start the restore of db (assumiing you have an informix level zero backup and in logical log mode is enabled – informix level 0 is very much an oracle level 0 and the logical log mode is similar to oracle’s ARCHIVELOG mode ) plus the logical logs, you run the command
    .
    $ ontape -r
    After the restore of the level 0 backup the ontape utility prompts the user with the below question.
    (assuming you don’t have a level 1 backup of the db and have only the logical log backup)

    $ Do you want to restore log tapes? (y/n)n

    All you have to do is enter “n”, like saying to the utility that there are no logical logs available to restore that holds all the changes since the last level 0 backup. And the ontape utility will move the database to fast recovery mode once the answer is confirmed. After may be about 30 mintues the database will be fully recovered in to a single user mode. Then you can bring the db to multi user mode by running the command.

    $ onmode -e

    That’s it and the db is online in no time. Consistent as a rock.

    I personally believe Informix should be where Oracle currently is :). It’s no secret that IBM marketing destroyed it.

    Best Regards,
    NA.

     
  • SanSan says:

    How can we match the result but the queries give. Suppose I want to get the same output using query won’t the below query will help. Because the SCRIPT you have shared gives me 271 files [March 04 (FULL) till March 13(Incremental)]. But my query gives me 725 files. Where is the GAP.

    select tag,round(BYTES/1024/1024/1024,3) size_gb,PIECE#,handle,to_char(start_time, ‘yyyy-mm-dd hh24:mi:ss’) start_time,to_char(completion_time, ‘yyyy-mm-dd hh24:mi:ss’) completion_time
    from v$backup_piece
    where to_char(start_time,’YYYYMMDD HH24:MI:SS’) between ‘20180304 22:00:00′ and ‘20180313 22:47:20′
    –where tag in (‘TAG20170620T220532′,’TAG20170619T220526′,’TAG20170618T220452′,’TAG20170620T224008′,’TAG20170620T220159′,’TAG20170619T223955′,’TAG20170619T220316′,’TAG20170618T235329′,’TAG20170618T220142′)
    order by COMPLETION_TIME;

    I have a requirement where the Query has to be used to retrieve the file names.

    Thanks in Advance.

     
  • Liana says:

    Great script! I’m having trouble adding the until time to it. Where would that go? Before the restore controlfile?

     

Leave a Reply

Oracle Team
Oracle Team