ora-01516 on alter database rename file

[paul@redfacedrhino]

I have upgraded an 8.0.4 database to 8i. I had problems with the
migration wizard moving files, so have moved the files manually. I
have had no problems with the majority of the files, but I have three
files which appear in the view dba_data_files with no path specified.

I have tried ;

"alter database rename file 'path\file.dbf' to 'newpath\file.dbf' and
"alter database rename file 'file.dbf' to 'newpath\file.dbf'

both of them return the same errors

ora-01511: error in renaming log/data files
ora-01516: nonexistent log file, datafile or tempfile etc.

I have tried copying the files to various other locations, such as the
same dir as init.ora, the oradata, database and dbs sub directories to
no avail.

The files do exist on the disk and are accessible to Oracle - I can
query data in these tablespaces....

Is there a way around this?

[ivan pellegrin@aedventure.com]

paul

try to verify the existing location of the datafile you want to move in v$datafile:

select name
from v$datafile;

then try to reissue the command:

ALTER DATABASE RENAME FILE '<OldPathAndName>' TO '<NewPathAndName>';

where:
<OldPathAndName> filename as it is listed in the v$datafile view

hope this solves the problem

ivan pellegrin

[paul@redfacedrhino]

Hi Ivan

Thanks for the post. No, the name in v$datafile is the unqualified
filename (i.e. no path) and is the same as in dba_data_files and the
alter database command does not work with either the qualified or
unqualified file name

Paul

[koert54]

I think your datadictionary is corrupted (file$) - contact oracle support
You might want to try recreating the control file but I think it won't help
Another option is restore your backup 8.0.4 and run the migration scripts
manual
instead of using a 'wizard' ....

"ivan pell...@aedventure.com" <ivanpellegri...@yahoo.com> wrote
in message news:e846ce1f.02022...@posting.google.com...

[paul@redfacedrhino]

I have found a workaround to this problem to get my instance up and
running but the underlying issue is still there.

In 8.0.4 you can issue a create tablespace command as follows

create tablespace fred datafile 'fred.dbf' size 2MB etc.

The tablespace will be created for you. If you then look in
v$datafile you will see that the file is just called fred.dbf with no
path specified.
the physical file will be in the same dir as the init.ora for the
instance

The command alter database rename file 'fred.dbf' to 'path\fred.dbf'
also works

In 8i if you try the same create statement it barfs on you and will
not create the tablespace, and as stated previously, the alter
database command will not work.

You can do an alter database close in 8i and copy the file to the new
location of init.ora, but the alter database command will still not
work. At least this way you get your instance up and running.

NB if you get an ora-01516 error with the data migration wizard on
upgrading this could be down to this problem also!

The moral of the tale is, if doing an 8.0.4 upgrade to 8i check the
name of the datafiles before attempting the upgrade in case some clown
has created a datafile with no path specified