OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Renaming Datafiles

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Renaming Datafiles [message #181933]

Wed, 12 July 2006 04:51

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.

Report message to a moderator

Re: Renaming Datafiles [message #181934 is a reply to message #181933]

Wed, 12 July 2006 04:53

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You can't.

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

Re: Renaming Datafiles [message #181958 is a reply to message #181933]

Wed, 12 July 2006 06:20

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

Report message to a moderator

Re: Renaming Datafiles [message #181981 is a reply to message #181958]

Wed, 12 July 2006 07:35

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.

Report message to a moderator

Re: Renaming Datafiles [message #199302 is a reply to message #181933]

Mon, 23 October 2006 05:29

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.

Report message to a moderator

Re: Renaming Datafiles [message #199304 is a reply to message #199302]

Mon, 23 October 2006 05:53

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

@kamalakar_ora
Cross-post

Report message to a moderator

Previous Topic:	Shared library errors when I try to run SQLPLUS HELP!!
Next Topic:	Database Minimum Build

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 15:39:11 CST 2024