help me recover

[gma800]

platform: win nt

problem: how to restore from a different drive, note impoossible to use rename file there is about 1500 dbf's

Background:

I am a trainee dba,Our sysadmin recently CAHNGED the drive letter assigment will be replaced OF OUR SERVERS.

When I tried to recover the database and since I have a very acceptable backup of the files, I just copied it to the assigned drive.

I started the DB, and error occured it seem that it cannot locate all my files

But upon checking the V$DATAFILE, the drive letter that was reflected was the old drive letter.

How do I change this I have 1500 dbf it will take long time to restore this, it should be up at least tom.


Help Please, are there scripts or anything that can help me??

thanks

[marist89]

If your directory structure is the same, query v$datafile, spool the output to a .sql file, edit the .sql file, and run through svrmgrl. You will be up in 15 minutes.

[marist89]

spool myfile.sql
set trimspool on
set pages 0
set linesize 222
select 'alter database rename file ''' || file_name || ''' to d''' || substr(2,len(file_name),file_name) | ';'
select 'alter database rename file ''' || member || ''' to d''' || substr(2,len(member),member) | ';'
spool off

[gma800]

what do you mean by spool,

do you have any suggestion on where I can find some spooling samples

This is my first recovery

[DBAtrix]

startup the db in mount.

execute
alter database rename file 'oldfile' to 'newfile'; -- case sensitive.

Since you have so many files, you will have to use sql to generate your commands.

use something like the following
select 'alter database rename file '''||NAME||''' to '''||replace(name, 'olddrive', 'newdrive')||''';'
from v$datafile;
You may have to tweak this command some for your purposes.

Don't forget your control files (init.ora) & redo logs (v$logfile).

Addendum==> oops, jeff already has it covered.
he has given you a spool example. < spool > just opens up a file in your current directory and writes your output to the file name you give (myfile). Just try it & you;ll see what it does.

d.

[Edited by DBAtrix on 12-22-2000 at 12:06 PM]

[marist89]

spool is a command that sends the output from a command to a file (from sqlplus).

As long as it's NT, could you use SUBST?

[gma800]

just to confirm,

spool myfile.sql
set trimspool on
set pages 0
set linesize 222
select 'alter database rename file ''' || file_name || ''' to d''' || substr(2,len(file_name),file_name) | ';'
select 'alter database rename file ''' || member || ''' to d''' || substr(2,len(member),member) | ';'
spool off


Myfile = file that I will convert to an sql file generated by the V$datafile query

file_name = is a field in the myfile query right??

d''' - is this my drive

Or this is the script I need to run , I would just be editing the converted file.

To you all thanks for help, and Merry Christmas