This discussion is archived
4 Replies Latest reply: Aug 29, 2013 5:50 AM by EdStevens RSS

RMAN-05556: not all datafiles have backups that can be recovered to SCN

EdStevens Guru
Currently Being Moderated

Oracle 11.2.0.2 SE-One

Oracle Linux 5.6 x86-64

 

Weekly refresh of a test db from prod, using rman DUPLICATE DATABASE, failed with “RMAN-05556: not all datafiles have backups that can be recovered to SCN”

 

Background Summary:

Weekly inc 0 backup of production starts on Sunday at 0100, normally completes around 1050.  Includes backups of archivelogs

Another backup of just archivelogs runs on Sunday at 1200, normally completes NLT 1201.

On the test server, the refresh job starts on Sunday at 1325.  In the past this script used a set until time \"to_date('`date +%Y-%m-%d` 11:55:00','YYYY-MM-DD hh24:mi:ss')\"; -- hard-coded for ‘today at 11:55’.

 

For a variety of reasons I decided to replace this semi-hard coding of the UNTIL with a value determined by querying the rman catalog, getting the completion time of the inc 0 backup.  This tested out just fine in my vbox lab, even when I deliberately drove some updates and log switches during the period the backup was running.  But the first time to go live I got the above reported error.

 

Details:

The key part of the inc 0 backup is this (run from a shell script)

 

export BACKUP_LOC=/u01/backup/dbprod

$ORACLE_HOME/bin/rman target=/ catalog rman/***@rmcat<<EOF

configure backup optimization on;

configure default device type to disk;

configure retention policy to recovery window of 2 days;

crosscheck backup;

crosscheck archivelog all;

delete noprompt force obsolete;

delete noprompt force expired backup;

delete noprompt force expired archivelog all;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '$BACKUP_LOC/%d_%F_ctl.backup';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '$BACKUP_LOC/%U.rman' MAXPIECESIZE 4096 M;

sql "alter system archive log current";

show all;

backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

backup as compressed backupset incremental level 0 database tag tag_dbprod;

sql "alter system archive log current";

backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

list recoverable backup;

EOF


The archivelog-only backup (runs at noon) looks like this:

 

export BACKUP_LOC=/u01/backup/dbprod

$ORACLE_HOME/bin/rman target=/ catalog rman/***@rmcat<<EOF

configure backup optimization on;

configure default device type to disk;

configure retention policy to recovery window of 2 days;

crosscheck backup;

crosscheck archivelog all;

delete noprompt force obsolete;

delete noprompt force expired backup;

delete noprompt force expired archivelog all;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '$BACKUP_LOC/%d_%F_ctl.backup';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '$BACKUP_LOC/%U.rman' MAXPIECESIZE 4096 M;

sql "alter system archive log current";

show all;

backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

list recoverable backup;

EOF

 

 

And the original refresh looked like this:

 

>> a step to ftp the backups from the prod server to the test server, and some other housekeeping  <<, then

 

cd /backup/dbtest

 

echo "connect catalog rman/***@rmcat" >  /backup/dbtest/dbtest_refresh.rman

echo "connect target sys/*******@dbprod" >> /backup/dbtest/dbtest_refresh.rman

echo "connect auxiliary /"             >> /backup/dbtest/dbtest_refresh.rman

echo "run {"                           >> /backup/dbtest/dbtest_refresh.rman

echo "set until time \"to_date('`date +%Y-%m-%d` 11:55:00','YYYY-MM-DD hh24:mi:ss')\";"  >> /backup/dbtest/dbtest_refresh.rman

echo "duplicate target database to DBTEST;"  >> /backup/dbtest/dbtest_refresh.rman

echo "}" >> /backup/dbtest/dbtest_refresh.rman

 

So, my mod to the refresh was

 

bkup_point=`sqlplus -s rman/***@rmcat <<EOF1

set echo off verify off feedback off head off pages 0 trimsp on

select to_char(max(completion_time),'yyyy-mm-dd hh24:mi:ss')

from rc_backup_set_details

where db_name='DBPROD'

and backup_type='D'

and incremental_level=0

;

exit

EOF1`

cd /backup/dbtest

 

echo "connect catalog rman/***@rmcat"     > /backup/dbtest/dbtest_refresh.rman

echo "connect target sys/*******@dbprod"    >> /backup/dbtest/dbtest_refresh.rman

echo "connect auxiliary /"                >> /backup/dbtest/dbtest_refresh.rman

echo "run {"                              >> /backup/dbtest/dbtest_refresh.rman

echo "set until time \"to_date('${bkup_point}','YYYY-MM-DD hh24:mi:ss')\";"  >> /backup/dbtest/dbtest_refresh.rman

echo "duplicate target database to DBTEST;" >> /backup/dbtest/dbtest_refresh.rman

echo "}"                                  >> /backup/dbtest/dbtest_refresh.rman

 

 

Now the fun begins.

First, an echo in the refresh script confirmed the ‘bkup_point’:

 

=======================================================

We will restore to 2013-08-25 10:41:38

=======================================================

 

Internally, rman reset the ‘until’ as follows:

 

executing command: SET until clause

 

Starting Duplicate Db at 25-Aug-2013 15:35:44

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=162 device type=DISK

 

contents of Memory Script:

{

   set until scn  45633141350;

 

Examining the result of LIST BACKUP (the last step of all of my rman scripts) the full backup shows this:

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    

------- ---- -- ---------- ----------- ------------ --------------------

5506664 Full 61.89M     DISK        00:00:03     25-Aug-2013 02:11:32

        BP Key: 5506678   Status: AVAILABLE  Compressed: NO  Tag: TAG20130825T021129

Piece Name: /u01/backup/dbprod/DBPROD_c-3960114099-20130825-00_ctl.backup

  SPFILE Included: Modification time: 24-Aug-2013 22:33:08

  SPFILE db_unique_name: DBPROD

  Control File Included: Ckp SCN: 45628880455   Ckp time: 25-Aug-2013 02:11:29

 

BS Key Type LV Size       Device Type Elapsed Time Completion Time    

------- ---- -- ---------- ----------- ------------ --------------------

5507388 Incr 0 206.03G    DISK        08:30:00     25-Aug-2013 10:41:30

  List of Datafiles in backup set 5507388

  File LV Type Ckp SCN    Ckp Time             Name

  ---- -- ---- ---------- -------------------- ----

  1    0 Incr 45628880495 25-Aug-2013 02:11:38 +SMALL/dbprod/datafile/system.258.713574775

 

  >>>>>>>>> snip lengthy list <<<<<<<<<

 

  74   0 Incr 45628880495 25-Aug-2013 02:11:38 +SMALL/dbprod/event_i2.dbf

 

  Backup Set Copy #1 of backup set 5507388

  Device Type Elapsed Time Completion Time      Compressed Tag

----------- ------------ -------------------- ---------- ---

  DISK        08:30:00     25-Aug-2013 10:41:36 YES        TAG_DBPROD

 

    List of Backup Pieces for backup set 5507388 Copy #1

    BP Key  Pc# Status      Piece Name

    ------- --- ----------- ----------

    5507391 1   AVAILABLE   /u01/backup/dbprod/eeoi55iq_1_1.rman

 

    >>>>>>>>>>>>> snip lengthy list <<<<<<<<<<<

 

    5507442 52  AVAILABLE   /u01/backup/dbprod/eeoi55iq_52_1.rman

 

 

Notice the slight difference in time between what is reported in the LIST BACKUP and what was reported by my query to the catalog.

 

Continuing with the backup list, the second archivelog  backup in the script generated six backupsets.  The fifth set  showed:

 

BS Key Size       Device Type Elapsed Time Completion Time    

------- ---------- ----------- ------------ --------------------

5507687 650.19M DISK        00:02:18     25-Aug-2013 10:54:53

        BP Key: 5507694   Status: AVAILABLE  Compressed: YES  Tag: TAG20130825T104156

Piece Name: /u01/backup/dbprod/ekoi643j_1_1.alog

 

  List of Archived Logs in backup set 5507687

  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time

  ---- ------- ---------- -------------------- ---------- ---------

  1    1338518 45632944587 25-Aug-2013 05:58:18 45632947563 25-Aug-2013 05:58:20

    >>>>>>>>>>>>> snip lengthy list <<<<<<<<<<<

  1    1338572 45633135750 25-Aug-2013 10:08:21 45633140240 25-Aug-2013 10:08:24

  1    1338573 45633140240 25-Aug-2013 10:08:24 45633141350 25-Aug-2013 10:30:06

  1    1338574 45633141350 25-Aug-2013 10:30:06 45633141705 25-Aug-2013 10:41:51

  1    1338575 45633141705 25-Aug-2013 10:41:51 45633141725 25-Aug-2013 10:41:55

 

Notice the availability of the archivelogs including the referenced scn.

Investigation of the ftp portion of the refresh script confirmed that all backup pieces were copied from the prod server.

So what am I overlooking?  Having reverted back to the original script to get the refresh completed,

  • 1. Re: RMAN-05556: not all datafiles have backups that can be recovered to SCN
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    So, technically, you only need the database and archivelogs backed up by the database script and not the noon run of the archivelog backup.

    backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

    backup as compressed backupset incremental level 0 database tag tag_dbprod;

    sql "alter system archive log current";

    backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

    Yet, why does backupset 5 of the noon archivelog backup show archivelogs from 10:30 to 10:40  if they had been deleted by the database backup script which has a delete input ?  It is as if the database backup script did NOT delete the archivelogs and the noon run was the one to backup the archivelogs (again ?)

     

    However, the error message seems to point to a datafile.  Why would reverting the recovery point to 11:55 make a difference, I wonder.

     

    Hemant K Chitale

  • 2. Re: RMAN-05556: not all datafiles have backups that can be recovered to SCN
    EdStevens Guru
    Currently Being Moderated

    HemantKChitale wrote:

     

    So, technically, you only need the database and archivelogs backed up by the database script and not the noon run of the archivelog backup.

    backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

    backup as compressed backupset incremental level 0 database tag tag_dbprod;

    sql "alter system archive log current";

    backup as compressed backupset archivelog all delete all input format "$BACKUP_LOC/%U.alog";

    Yet, why does backupset 5 of the noon archivelog backup show archivelogs from 10:30 to 10:40  if they had been deleted by the database backup script which has a delete input ?  It is as if the database backup script did NOT delete the archivelogs and the noon run was the one to backup the archivelogs (again ?)

     

    No, that is from the morning full backup.  Note the 'Completion Time" of 25-Aug-2013 10:54:53

     

     

     

    However, the error message seems to point to a datafile.  Why would reverting the recovery point to 11:55 make a difference, I wonder.

     

    As do I.

     

    Also puzzling to me are the times associated with the completion of the backups.  I don't recall ever having to scrutinize a backup listing this closely so I'm sure it's just a matter of filling in some gaps in my understanding, but I noticed this.  The backup report (list backup;) shows this for the inc 0 backup:

     

    BS Key  Type LV Size   Device Type Elapsed Time Completion Time

    ------- ---- -- ---------- ----------- ------------ --------------------

    5507388 Incr 0  206.03GDISK    08:30:00 25-Aug-2013 10:41:30   ------- NOTE THE COMPLETION TIME ----

      List of Datafiles in backup set 5507388

      File LV Type Ckp SCNCkp Time         Name

      ---- -- ---- ---------- -------------------- ----

      10  Incr 45628880495 25-Aug-2013 02:11:38 +SMALL/dbprod/datafile/system.258.713574775

     

    ------ SNIP ------

     

      74   0  Incr 45628880495 25-Aug-2013 02:11:38 +SMALL/dbprod/event_i2.dbf

     

      Backup Set Copy #1 of backup set 5507388

      Device Type Elapsed Time Completion Time  Compressed Tag

      ----------- ------------ -------------------- ---------- ---

      DISK    08:30:00 25-Aug-2013 10:41:36 YES    TAG_DBPROD   ------- NOTE THE COMPLETION TIME ----

     

    List of Backup Pieces for backup set 5507388 Copy #1
    BP Key  Pc# Status  Piece Name
    ------- --- ----------- ----------
    5507391 1   AVAILABLE   /u01/backup/dbprod/eeoi55iq_1_1.rman

     

    ------ SNIP ------

     

    5507442 52  AVAILABLE   /u01/backup/dbprod/eeoi55iq_52_1.rman

     

    Then the autobackup of the control file immediatly following:

     

    BS Key  Type LV Size   Device Type Elapsed Time Completion Time

    ------- ---- -- ---------- ----------- ------------ --------------------

    5507523 Full61.89M DISK    00:00:03 25-Aug-2013 10:41:47   ------- NOTE THE COMPLETION TIME ----
        BP Key: 5507587   Status: AVAILABLE  Compressed: NO  Tag: TAG20130825T104144
        Piece Name: /u01/backup/dbprod/DBPROD_c-3960114099-20130825-01_ctl.backup

      SPFILE Included: Modification time: 25-Aug-2013 05:57:15

      SPFILE db_unique_name: DBPROD   

      Control File Included: Ckp SCN: 45633141671   Ckp time: 25-Aug-2013 10:41:44

     

     

    Then the archivelog backup immediately following (remember, this created a total of 5 backupset, I'm showing number 4)

     

     

    BS Key  Size   Device Type Elapsed Time Completion Time

    ------- ---------- ----------- ------------ --------------------

    5507687 650.19MDISK    00:02:18 25-Aug-2013 10:54:53   ------- NOTE THE COMPLETION TIME ----
        BP Key: 5507694   Status: AVAILABLE  Compressed: YES  Tag: TAG20130825T104156
        Piece Name: /u01/backup/dbprod/ekoi643j_1_1.alog

     

      List of Archived Logs in backup set 5507687

      Thrd Seq Low SCNLow Time         Next SCN   Next Time

      ---- ------- ---------- -------------------- ---------- ---------

      11338518 45632944587 25-Aug-2013 05:58:18 45632947563 25-Aug-2013 05:58:20

     

    ------ SNIP ------

     

      11338572 45633135750 25-Aug-2013 10:08:21 45633140240 25-Aug-2013 10:08:24
      11338573 45633140240 25-Aug-2013 10:08:24 45633141350 25-Aug-2013 10:30:06
      11338574 45633141350 25-Aug-2013 10:30:06 45633141705 25-Aug-2013 10:41:51
      11338575 45633141705 25-Aug-2013 10:41:51 45633141725 25-Aug-2013 10:41:55

     

     

    and the controlfile autobackup immediately following:

     

    BS Key  Type LV Size   Device Type Elapsed Time Completion Time

    ------- ---- -- ---------- ----------- ------------ --------------------

    5507984 Full61.89M DISK    00:00:03 25-Aug-2013 10:55:07   ------- NOTE THE COMPLETION TIME ----
        BP Key: 5508043   Status: AVAILABLE  Compressed: NO  Tag: TAG20130825T105504
        Piece Name: /u01/backup/dbprod/DBPROD_c-3960114099-20130825-02_ctl.backup

      SPFILE Included: Modification time: 25-Aug-2013 05:57:15

      SPFILE db_unique_name: DBPROD

      Control File Included: Ckp SCN: 45633142131   Ckp time: 25-Aug-2013 10:55:04

     

     

    and yet, querying the rman catalog

     

    SQL> select to_char(max(completion_time),'yyyy-mm-dd hh24:mi:ss')

      2  from rc_backup_set_details

      3  where db_name='DBPROD'

      4  and backup_type='D'

      5  and incremental_level=0

      6  ;

     

    TO_CHAR(MAX(COMPLET

    -------------------

    2013-08-25 10:41:38

     

    SQL>

     

    which doesn't match (to the second) the completion time of either the full backup or the associated controlfile autobackp.

     

     

    Hemant K Chitale

     

    I hope this posts in a readable, understandable manner.  I really struggeled with the 'enhanced editor', which I normally use.  When I pasted in blocks from the rman report, it kept trying to make some sort of table structure out of it .... guess I'll have to follow that up with a question in the Community forum ....

  • 3. Re: RMAN-05556: not all datafiles have backups that can be recovered to SCN
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    TO_CHAR(MAX(COMPLET

    -------------------

    2013-08-25 10:41:38

    SQL>

    which doesn't match (to the second) the completion time of either the full backup or the associated controlfile autobackp.

     

    (another mystery), nevertheless it should still be able to do a RECOVER DATABASE.

     

     

    I hope this posts in a readable, understandable manner.  I really struggeled with the 'enhanced editor', which I normally use.  When I pasted in blocks from the rman report, it kept trying to make some sort of table structure out of it

     

    The posts are well formatted, readable and understandable.  Yet, they don't resolve our questions/confusion !

     

    Hemant  K Chitale

     

    PS: Neither of us should be someone making a newbie mistake.  Yet, we seem to be missing something !

  • 4. Re: RMAN-05556: not all datafiles have backups that can be recovered to SCN
    EdStevens Guru
    Currently Being Moderated

    HemantKChitale wrote:

     

    TO_CHAR(MAX(COMPLET

    -------------------

    2013-08-25 10:41:38

    SQL>

    which doesn't match (to the second) the completion time of either the full backup or the associated controlfile autobackp.

     

    (another mystery), nevertheless it should still be able to do a RECOVER DATABASE.

     

     

    I hope this posts in a readable, understandable manner.  I really struggeled with the 'enhanced editor', which I normally use.  When I pasted in blocks from the rman report, it kept trying to make some sort of table structure out of it

     

    The posts are well formatted, readable and understandable.  Yet, they don't resolve our questions/confusion !

     

    Hemant  K Chitale

     

    PS: Neither of us should be someone making a newbie mistake.  Yet, we seem to be missing something !

     

    Neither of us should be someone making a newbie mistake.  Yet, we seem to be missing something !


    Agreed.

     

    Looks like we have a "riddle wrapped in a mystery inside an enigma"

Legend

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