Home » RDBMS Server » Server Administration » Renaming Datafiles
icon4.gif  Renaming Datafiles [message #181933] Wed, 12 July 2006 04:51 Go to next message
oranil
Messages: 5
Registered: July 2006
Location: Singapore
Junior Member

How to rename a datafile with out taking the Tablespace OFFLINE and without shutting the Database.
Re: Renaming Datafiles [message #181934 is a reply to message #181933] Wed, 12 July 2006 04:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can't.
what if u cant take DB down n cant take Tablespace offline [message #181951 is a reply to message #181934] Wed, 12 July 2006 05:55 Go to previous messageGo to next message
oranil
Messages: 5
Registered: July 2006
Location: Singapore
Junior Member

Hi Ebrain,
First let me thank u for the reply. Its seems what u said is true. But I faintly remember that I came across a procedure which uses inbuilt packages[not sure abt this] to move a datafile to new location.

Ok let me highlight a situation: One of my client has a big database and most of the activity is concentrated around USERHR tablespace which has 99 datafiles and the local DBA accidently created a new datafile the 100th one in wrong disk. Now they want to relocated that particular Datafile to correct disk. Just for this purpose you cant take USERHR tablespace [ and hence all of the 99 datafiles] OFFLINE. And neither u can shutdown the 24/7 Database. What will u do in this situation?

I am sure I came across a piece of information which is another way to rename datafiles. But Now I cant find it.

Cheers,
Anil
Re: what if u cant take DB down n cant take Tablespace offline [message #181956 is a reply to message #181951] Wed, 12 July 2006 06:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
To my knowledge there is no built-in package/procedure to facilitate this.

If Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle blocks. This way, Oracle will never consider this file for extent allocation.

You can create the tablespace later to exclude the incorrect datafile or rename the file once you get a chance.
Re: Renaming Datafiles [message #181958 is a reply to message #181933] Wed, 12 July 2006 06:20 Go to previous messageGo to next message
oranil
Messages: 5
Registered: July 2006
Location: Singapore
Junior Member

Hi Ebrain,
Good Suggestion, but what if that disk which has the unused datafile goes corrupt or wanted to be removed off. Then the DB signals 'datafile missing and needs recovery/restore' however small the datafile is?

Cheers,
Anil
Re: Renaming Datafiles [message #181981 is a reply to message #181958] Wed, 12 July 2006 07:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Correct. In regards to backup & recovery, it's going to be treated like any other datafile associated with that tablespace.
Re: Renaming Datafiles [message #199302 is a reply to message #181933] Mon, 23 October 2006 05:29 Go to previous messageGo to next message
kamalakar_ora
Messages: 30
Registered: October 2006
Location: Bangalore
Member
Hi Ebrain,

Can you please suggest me how can I restore a individual datafile ?

I backed up using the following command:

RMAN> copy nochecksum datafile 'D:/Oracle/oradata/dbdata/tools01.dbf' to 'C:\too
ls01.bak' level 0;

And the tried the following to restore the same:

RMAN>SQL 'ALTER DATABASE DATAFILE tools01.dbf OFFLINE';

But got the following error:

RMAN> SQL 'ALTER DATABASE tools01.dbf OFFLINE';

sql statement: ALTER DATABASE tools01.dbf OFFLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 10/23/2006 14:33:56
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE tool
s01.dbf OFFLINE
ORA-02231: missing or invalid option to ALTER DATABASE

Please suggest me the steps to Recover individual datafiles.

Regards,
Kamalakar.
Re: Renaming Datafiles [message #199304 is a reply to message #199302] Mon, 23 October 2006 05:53 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@kamalakar_ora
Cross-post
Previous Topic: Shared library errors when I try to run SQLPLUS HELP!!
Next Topic: Database Minimum Build
Goto Forum:
  


Current Time: Wed Dec 07 14:23:55 CST 2016

Total time taken to generate the page: 0.04960 seconds