oracle 9i problems.. renaming datafiles

mikeDesouza

unread,

Jun 19, 2002, 12:44:34 PM6/19/02

to

Hi

I am trying to rename a datafile in oracle 9 release 1. I perform the
following steps but somehow it fails and i have to perform media recovery .
when I try and rename the datafile its say that some process is using in ,
so when i try and bring it online it says it needs media recovery

any help would be appreciated
Mike

SYS@PRACTICE> alter database datafile 4 offline;

Database altered.

SYS@PRACTICE> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF'

--
_________________________________
Mike Desouza
Archoak Consulting Limited

Raymond

unread,

Jun 19, 2002, 12:55:15 PM6/19/02

to

>
> SYS@PRACTICE> alter database datafile 4 offline;
>
> Database altered.
>
> SYS@PRACTICE> alter database datafile 4 online;
> alter database datafile 4 online
> *
> ERROR at line 1:
> ORA-01113: file 4 needs media recovery
> ORA-01110: data file 4: 'C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF'
>

You should take the tablespace offline, not the datafile.

R.

Knut Talman

unread,

Jun 19, 2002, 1:07:12 PM6/19/02

to

mikeDesouza wrote:

> I am trying to rename a datafile in oracle 9 release 1. I perform the
> following steps but somehow it fails and i have to perform media recovery .
> when I try and rename the datafile its say that some process is using in ,
> so when i try and bring it online it says it needs media recovery

AFAIK you can't rename the file when the database is open. Shutdown the
instance, startup mount, rename the file (on OS level *and* in the database) and
open the db.

Regards,

Knut

Jim Glockling

unread,

Jun 19, 2002, 1:22:05 PM6/19/02

to

In article <aeqcdv$1vh$1...@news6.svr.pol.co.uk> "mikeDesouza" <mi...@archoak.fsworld.co.uk> writes:
>Hi
>
>I am trying to rename a datafile in oracle 9 release 1. I perform the
>following steps but somehow it fails and i have to perform media recovery .
>when I try and rename the datafile its say that some process is using in ,
>so when i try and bring it online it says it needs media recovery
>
> any help would be appreciated
>Mike
>
>SYS@PRACTICE> alter database datafile 4 offline;
>
>Database altered.
>
>SYS@PRACTICE> alter database datafile 4 online;
>alter database datafile 4 online
>*
>ERROR at line 1:
>ORA-01113: file 4 needs media recovery
>ORA-01110: data file 4: 'C:\ORACLE\ORADATA\PRACTICE\INDX01.DBF'

alter tablespace FOO offline;

do an OS-level move to the new location ("mv oldPath newPath")

alter tablsespace FOO rename datafile 'oldPath' to 'newPath';
alter tablespace FOO online;

HTH, Jim G.

Howard J. Rogers

unread,

Jun 19, 2002, 8:18:15 PM6/19/02

to

"Knut Talman" <knut....@mytoys.de> wrote in message
news:3D10BA40...@mytoys.de...

Of course you can. Taking the tablespace offline is like shutting down part
of the database, so the procedure is: take tablespace offline, drop to the
O/S and do the necessaries, issue the 'alter database datafile rename x to
y' command to tell the controlfile what you've been up to, bring the
tablespace back on line.

The only time this doesn't work, of course, is when it's the SYSTEM datafile
that's being renamed, since you can't offline the system tablespace. If
that's the case, then your shutdown-rename-mount-tell controlfile-open
method is the only way of doing it.

Regards
HJR

> Regards,
>
> Knut

Knut Talman

unread,

Jun 20, 2002, 5:58:44 AM6/20/02

to

"Howard J. Rogers" wrote because I wrote:

> > AFAIK you can't rename the file when the database is open. Shutdown the
> > instance, startup mount, rename the file (on OS level *and* in the
> database) and
> > open the db.

> Of course you can. Taking the tablespace offline is like shutting down part
> of the database, so the procedure is: take tablespace offline, drop to the
> O/S and do the necessaries, issue the 'alter database datafile rename x to
> y' command to tell the controlfile what you've been up to, bring the
> tablespace back on line.
>
> The only time this doesn't work, of course, is when it's the SYSTEM datafile
> that's being renamed, since you can't offline the system tablespace. If
> that's the case, then your shutdown-rename-mount-tell controlfile-open
> method is the only way of doing it.

Thanks for clearing it up, Howard. I was a bit confused with system vs. other
tablespaces.

Regards,

Knut