Part of your disaster recovery plan should include restoring the database from scratch on a clean machine with nothing but the backed-up datafiles and controlfiles. If you don't remember the directory names that held your datafiles, you're in for a long, painful restore.
Better to periodically run this script and keep a copy of the output with your backups. In the event of a full restore, just run this script to recreate directory structure and this one to move all your datafiles from a backup location to their original directories.
Instructions: Scroll down to the commented block at the end. Copy and paste into your SQL window. Run it repeatedly, entering increasing values for &N. Start with 2 and keep going until the result is
MYTHING ------- MKDIRWhatever the value of N was that got you this result, you want N-1 copies of this block of SQL in your script. For example, in the listing below, you'll see that there are identical copies of that block of SQL with N set from 1 through 5. This is because on my system, N=6 gets me the single-line MKDIR result set.
If this all sounds confusing, just play around with the script a bit --
it's pretty obvious once you run it.
--Script to generate a DOS script to make the directory tree, for restores
--Copy and paste select blocks until you've reached the required depth
--(depth of your deepest node)
--copyright N Roshak 2002
--
set heading off
set pagesize 0
set echo off
set feedback off
set verify off
set linesize 500
col mything for a500
spool gen_tree.out
prompt REM Script to recreate directory structure
prompt REM generated by gen_tree.sql
prompt
select decode(br,'mkdir ',null,br) as mything
from (
select distinct substr(leaf,0,instr(leaf,'\',1)-1) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
union
select distinct 'mkdir ' || substr(leaf,0,instr(leaf,'\',1,2)) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
union
select distinct 'mkdir ' || substr(leaf,0,instr(leaf,'\',1,3)) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
union
select distinct 'mkdir ' || substr(leaf,0,instr(leaf,'\',1,4)) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
union
select distinct 'mkdir ' || substr(leaf,0,instr(leaf,'\',1,5)) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
) order by substr(br,instr(br,':',1)-1,length(br))
;
spool off
--Use this block to test for your deepest node
--When you've got the correct depth, all that will show up is a "mkdir "
/* select distinct 'mkdir ' || substr(leaf,0,instr(leaf,'\',1,&N)) br
from (
select substr(name,0,instr(name,'\',-1,1)) leaf from v$datafile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$tempfile
union
select substr(name,0,instr(name,'\',-1,1)) leaf from v$controlfile
union
select substr(member,0,instr(member,'\',-1,1)) leaf from v$logfile
)
; */
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...