1

We restore backups to our test systems from production via a point in time RMAN Duplicate with an online full backup and hourly archivelog. Every blue moon something goes wrong (working to resolve that separately) and I end up not having an archive log. It spends hours restoring the datafiles, gets to the archivelogs and then bombs.

How can I verify that RMAN has all the correct archivelogs and datafiles to restore before it starts pushing the data around significantly?

2

Use RESTORE ... PREVIEW. I intentionally deleted some archivelogs, so I do not have a backup of them.

Now I want to check if I can restore the database to a specified time:

run
{
  set until time "to_date('2018-11-12 19:58:00', 'YYYY-MM-DD HH24:MI:SS')";
  restore database preview;
}

Output:

executing command: SET until clause

Starting restore at 12-NOV-18
using channel ORA_DISK_1


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.11G      DISK        00:00:03     11-NOV-18
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20181111T221533
        Piece Name: /oracle_backup/DOMINO_O77/04thvv85_1_1
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/system.257.991918055
  3       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/sysaux.258.991918107
  4       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/undotbs1.259.991918131
  7       Full 2397081    11-NOV-18              NO    +DATA/DOMINO_O77/DATAFILE/users.260.991918133

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore

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


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       151.71M    DISK        00:00:01     12-NOV-18
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195537
        Piece Name: /oracle_backup/DOMINO_O77/09ti2bdp_1_1

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    43      2396293    11-NOV-18 2397794    11-NOV-18
  1    44      2397794    11-NOV-18 2398565    11-NOV-18
  1    45      2398565    11-NOV-18 2398568    11-NOV-18
  1    46      2398568    11-NOV-18 2398574    11-NOV-18
  1    47      2398574    11-NOV-18 2398581    11-NOV-18
  1    48      2398581    11-NOV-18 2398588    11-NOV-18
  1    49      2398588    11-NOV-18 2398606    11-NOV-18
  1    50      2398606    11-NOV-18 2398912    11-NOV-18
  1    51      2398912    11-NOV-18 2498959    11-NOV-18
  1    52      2498959    11-NOV-18 2498962    11-NOV-18
  1    53      2498962    11-NOV-18 2499076    11-NOV-18
  1    54      2499076    11-NOV-18 2499560    11-NOV-18
  1    55      2499560    11-NOV-18 2499580    11-NOV-18
  1    56      2499580    11-NOV-18 2601155    11-NOV-18
  1    57      2601155    11-NOV-18 2601158    11-NOV-18
  1    58      2601158    11-NOV-18 2601263    11-NOV-18
  1    59      2601263    11-NOV-18 2601701    11-NOV-18
  1    60      2601701    11-NOV-18 2601705    11-NOV-18
  1    61      2601705    11-NOV-18 2601709    11-NOV-18
  1    62      2601709    11-NOV-18 2784642    12-NOV-18

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       13.50K     DISK        00:00:00     12-NOV-18
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20181112T195610
        Piece Name: /oracle_backup/DOMINO_O77/0bti2beq_1_1

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    65      2784741    12-NOV-18 2784798    12-NOV-18
  1    66      2784798    12-NOV-18 2784812    12-NOV-18
  1    67      2784812    12-NOV-18 2784830    12-NOV-18
recovery will be done up to SCN 2784849
Media recovery start SCN is 2397081
Recovery must be done beyond SCN 2397081 to clear datafile fuzziness
Finished restore at 12-NOV-18

The complete recovery is not possible, and RMAN reported the missing archivelog backups:

no backup of archived log for thread 1 with sequence 63 and starting SCN of 2784642 found to restore
no backup of archived log for thread 1 with sequence 64 and starting SCN of 2784723 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 2784830 found to restore
  • Awesome, I'll test it out and accept if works for me, thanks! – rfusca Nov 12 '18 at 19:19
  • When I run this, my output doesn't look anything like that. It does an implicity crosscheck, lists the Cataloged_files and then fails with "RMAN-03002: failure of restore command at 11/12/2018 14:31:34 RMAN-06085: must use SET NEWNAME command to restore datafile 1" . Presumably because the name is different because its a test system. DUPLICATE normally changes the name as part of its process. – rfusca Nov 12 '18 at 19:38
  • @rfusca So try using set newname. In my database the db_create_file_dest parameter is set and that takes care of automatically naming files to be created, so I did not need it. If you are on at least version 11.2, you do not need to set each files seperately, you can use set newname for database to '/whatever/path'; in the above run block. – Balazs Papp Nov 12 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.