How should I use RMAN clone refresh using set until time

oraclerman

I am using this technique to refresh dev. environments with production data.

The business process is to request a tape hold for the full backup I want to use for a clone refresh to dev. Once this is imported, I use the exact server end time of the database backup (not the logs backup) for my set until time clause.

run {
set until time = "TO_DATE('08/09/2014 20:24:47','MM/DD/YYYY HH24:MI:SS')" ;
allocate auxiliary channel t1 type 'sbt_tape';
duplicate target database to DEVDB;
}

This works only some of the time. More often than not, the script goes looking for a previous backup which is not in tape hold.

Media required
Detected Criteria: Media Handling Required
Job ID:
Status: Media is not in Library
Library Name: SEA-TAPELIB07 (i500 B)
Media label: SVL432L4
Failure Reason: The required media [SVL432L4] for the job [2518965] is not in the Library [TAPELIB (9999)]. Please use the import functionality to import the media from the location [Not Available] into the library [TAPELIB (9999)].
Media List: Not Applicable

As a workaround, I requested the previous backup and re-ran the script. It completed but it used the previous day's backup and the request was fulfilled two days late. What am I doing wrong with this technique or should I be using a different method? Any suggestions would be helpful.

Best Answer

First you need to ensure that that the backup is in your library (surely you know better ways for find it, I put my query):

select
    D.DB_NAME, B.DB_ID, BASET.BCK_TYPE,
decode(BASET.CONTROLFILE_INCLUDED,'BACKUP','YES','NO') as CONTROLFILE_INCLUDED,
BASET.INCR_LEVEL, bapiece.tag, bapiece.handle, bapiece.start_time,
bapiece.completion_time,
round(bapiece.bytes/1024/1024,2) as Mb
from rman.bp bapiece,
        rman.dbinc d,
        rman.bs baset,
        rman.db b
where BAPIECE.DB_KEY=D.DB_KEY
   and D.DB_NAME='SFPROD'
   and BASET.DB_KEY=D.DB_KEY
   and BASET.BS_KEY = BAPIECE.BS_KEY
   and  bapiece.start_time>sysdate-1
   and B.DB_KEY=D.DB_KEY
---   and bapiece.handle like '%.dbf%'
order by tag desc, handle desc    ;

And... I hate to use "set until time" to restore/duplicate database. It is more human friendly but you can be surprised with an rman error at the end of the restore. Try to find the last commit (SCN) for duplicate process:

SQL> select max(next_change#)
    from v$archived_log
    where archived = 'YES'
    group by thread#;

or with rman:

RMAN> list backup of archivelog all;
--Next SCN

And then:

run { set until scn ... ; allocate auxiliary channel t1 type 'sbt_tape'; duplicate target database to DEVDB; }
Related Question