Restore datafiles from backup directory to their original locations

Home

Toolkit:

My OraFAQ Blog

Contact me

When you're doing a full restore, you need your datafiles to be where the controlfile expects them to be. (At the very least, you need to know where your controlfile expects them to be.)

Run this script when you do your backups, and keep the results with your backups. It generates a DOS shell script to move your datafiles from the backup directory back to their original locations. Original directory structure got blown away? Not a problem if you'd also run the directory tree generation script and kept the output with your backups.

Don't forget to set %backup_dir% in the script below.


set heading off
set pagesize 0
set echo off
set feedback off
set verify off
set linesize 500
col mything for a500
spool copy_files.out
prompt REM Script to copy database files from backup directory to correct locations
prompt REM generated by copy_files.sql
prompt REM
prompt REM Copyright 2002-2003 N Roshak
prompt REM
prompt REM don't forget to fill in location of %backup_dir% below
prompt set backup_dir=
select distinct 'copy %backup_dir%' || substr(leaf,instr(leaf,'\',-1,1), 
length(leaf))|| ' ' || leaf
as  cmdstr
from 
(select name as LEAF from v$datafile
union
select name from v$controlfile
union
select member from v$logfile)
/
spool off


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...