Problem
It happens few times a year I have to prepare some test environments, which are complete copies of production databases. Because in such situation everything is compliant with a production environment, I do not have to deal with architecture change so the best way is simply to make a use of RMAN backups, which are stored on tapes somewhere within a backup system.
The task is then quite easy. I produce something like the following:
CONNECT target /
CONNECT catalog rman/xxx@rman_catalog
SPOOL LOG TO restore_db_20141202.log
RUN {
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE' FORMAT '<backup file name format>'
PARMS 'ENV=(<backup system specific options>)';
SET UNTIL TIME "to_date('2014-11-30 00:11:00','yyyy-mm-dd hh24:mi:ss')";
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
sql "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";
SET NEWNAME FOR DATABASE TO NEW;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 90G;
RELEASE CHANNEL t1;
}
As You may see I rename all the database files to something new. I do not do this everytime, but sometimes I have on such new environment differently named mount points or sometimes a production database has some history so not all files are kept on the same mount point, while on test environment this is convenient to calculate whole database size, allocate such amount in one resource and mount it under one location.
In this script this location is not specified explicitly - I 'd rather like to set
db_create_file_dest parameter of instance - thus also a possibly newly created files are placed on the same path.
Then I run the script and very often I do not have to do anything until the process will end successfully. Yet from time to time shit happens :-( and 9 of 10 cases in our shop it is a temporary problem with the backup system or at least with a communication with it - in case of small databases this would not be a big deal, but first I never met it and second even starting from scratch for a 50G means only a part of an hour.
The real problem is with quite big databases (few TB) and we have one case where it is almost frequent, where the connection to the backup system is done through LAN (in opposition to SAN). Such operation takes at least few days and after few TB restored, while still in a restore phase, we meet a crash.
Some of datafiles are fully restored, some partially, some not at all and yet in v$datafile exist only old file names and for a first sight there is no mapping between newly restored files and metadata content stored within the controlfile.
Below there is a short how to deal with this situation.
Solution
First of all You have to look into views v$datafile_header and v$datafile_copy. The v$datafile_copy should keep names of the newly created files (name is not null and completion_time><some date before running the restore and recover operation>) - then it is easy to create a script, which instead of switching to a new name would switch to the already restored files.
select
'set newname for datafile '||x.file#||' to '||x.descr||';' rman_cmd
from (
select d.file#, 'new' descr from v$datafile d
minus
select c.file#, 'new' descr
from v$datafile_copy c where c.completion_time > sysdate-5
union all
select c.file#, ''''||c.name||'''' descr
from v$datafile_copy c where c.completion_time > sysdate-5
) x
order by x.file#
Now we take the above output, paste it into previous script, remove restore controlfile and mount commands and run - the RMAN will check the restored datafiles status, keeps the fully restored intact while restoring those fuzzy or not-yet-restored. And that's it.
Final output will look like this:
CONNECT target /
CONNECT catalog rman/xxx@rman_catalog
SPOOL LOG TO restore_db_20141202.log
RUN {
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE' FORMAT '<backup file name format>'
PARMS 'ENV=(<backup system specific options>)';
SET UNTIL TIME "to_date('2014-11-30 00:11:00','yyyy-mm-dd hh24:mi:ss')";
# here changes - while it may seem trivial and unimportant let's look at the file id
# with few thousands of files this saves a lot of work
SET NEwNAME FOR DATAFILE 1 TO '/{path_to_data}/o1_mf_system_c99zgdp4_.dbf';
SET NEwNAME FOR DATAFILE 2 TO '/{path_to_data}/o1_mf_undotbs1_c99nq5c3_.dbf';
SET NEwNAME FOR DATAFILE 3 TO '/{path_to_data}/o1_mf_sysaux_c99op8h2_.dbf';
SET NEwNAME FOR DATAFILE 4 TO '/{path_to_data}/o1_mf_users_c99ysv13_.dbf';
SET NEwNAME FOR DATAFILE 5 TO new;
SET NEwNAME FOR DATAFILE 6 TO new;
[..]
SET NEwNAME FOR DATAFILE 1419 TO '/{path_to_data}/o1_mf_tstas1_d__c99tghry_.dbf';
SET NEwNAME FOR DATAFILE 1422 TO '/{path_to_data}/o1_mf_tstidx_d_c99zswmb_.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
RECOVER DATABASE DELETE ARCHIVELOG MAXSIZE 90G;
RELEASE CHANNEL t1;
}
