OraFAQ Forum: Server Administration » to change .dbf file to another location

Home » RDBMS Server » Server Administration » to change .dbf file to another location

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

to change .dbf file to another location [message #288338]

Mon, 17 December 2007 02:27

sd_md_rizwan@yahoo.com
Messages: 37
Registered: September 2007
Location: Saudi Arabia

Member

hi i am rizwan,
i am running my database very fine, but i am worried if the drive in which my .dbf file are located is full, then how to relocate them to another location i mean another drive, is it possible if yes how, please help me
thanks

Report message to a moderator

Re: to change .dbf file to another location [message #288341 is a reply to message #288338]

Mon, 17 December 2007 02:33

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Yes it is possible.
procedure is mention in documentation.
http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/dfiles.htm#i1006478

Report message to a moderator

Re: to change .dbf file to another location [message #288342 is a reply to message #288338]

Mon, 17 December 2007 02:35

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Put the file offline,
Move it to another location,
Use alter database to rename the file (inside Oracle).
Put the file online.

Note: it is only possible if you are in archivelog mode.

Regards
Michel

[Updated on: Mon, 17 December 2007 02:35]

Report message to a moderator

Re: to change .dbf file to another location [message #288347 is a reply to message #288341]

Mon, 17 December 2007 02:52

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

And database is running in no archivelog mode then use below procedure.

1.shutdown immediate;
2.move datafile through OS command.
3.startup mount
alter database
rename file 'oldlocation'
             to
             'new location';
4.alter database open;

[Updated on: Mon, 17 December 2007 02:57]

Report message to a moderator

Re: to change .dbf file to another location [message #288348 is a reply to message #288338]

Mon, 17 December 2007 02:54

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

According to your requirement you can add another datafile in your tablespace which will be located in your specified drive.

Anyway you can change your datafile through rman also.

SQL> select file_name from dba_data_files where file_id=6;

FILE_NAME
--------------------------------------------------------------------------------
/oradata/Arju/myonenew.dbf

SQL>  alter database datafile 6 offline;

Database altered.

SQL> !mv /oradata/Arju/myonenew.dbf /oradata2/arju/myonenew1.dbf

SQL> !rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Mon Dec 17 03:49:25 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ARJUDUP (DBID=234861497)

RMAN> catalog datafilecopy '/oradata2/arju/myonenew1.dbf';

using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy filename=/oradata2/arju/myonenew1.dbf recid=12 stamp=641533799
RMAN> run{
2> set newname for datafile 6 to '/oradata2/arju/myonenew1.dbf';
3> switch datafile 6;
4> recover datafile 6;
5> }

executing command: SET NEWNAME

datafile 6 switched to datafile copy
input datafile copy recid=12 stamp=641533799 filename=/oradata2/arju/myonenew1.dbf

Starting recover at 17-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=21 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 17-DEC-07

RMAN> ^D

Recovery Manager complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> select file_name from dba_data_files where file_id=6;

FILE_NAME
--------------------------------------------------------------------------------
/oradata2/arju/myonenew1.dbf

Report message to a moderator

Re: to change .dbf file to another location [message #288350 is a reply to message #288348]

Mon, 17 December 2007 02:58

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Really complicated compare to a simple "alter database rename file", doesn't it?

Regards
Michel

Report message to a moderator

Re: to change .dbf file to another location [message #288351 is a reply to message #288350]

Mon, 17 December 2007 02:59

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

>Really complicated compare to a simple "alter database rename >file", doesn't it?
Yes, but good demo. Smile

Report message to a moderator

Re: to change .dbf file to another location [message #288352 is a reply to message #288338]

Mon, 17 December 2007 03:01

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Yes, complicated. Just shown an alternative way to OP.

Report message to a moderator

Re: to change .dbf file to another location [message #288456 is a reply to message #288352]

Mon, 17 December 2007 10:56

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Why don't online

With ALTER TABLESPACE RANAME datafile!!??

Report message to a moderator

Re: to change .dbf file to another location [message #288460 is a reply to message #288456]

Mon, 17 December 2007 11:17

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no difference between alter database and alter tablespace. The datafile must be offline.
From doc:

Quote:

Specify RENAME DATAFILE to rename one or more of the tablespace datafiles. The database must be open, and you must take the tablespace offline before renaming it.

Regards
Michel

Report message to a moderator

Re: to change .dbf file to another location [message #288461 is a reply to message #288460]

Mon, 17 December 2007 11:23

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

I agree
But i didn't see your post i saw TAJ post!!! So thats why i said why not online!!

Sowwie

[Updated on: Mon, 17 December 2007 11:24]

Report message to a moderator

Re: to change .dbf file to another location [message #288466 is a reply to message #288461]

Mon, 17 December 2007 12:04

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oh! you meant DATABASE online and not DATAFILE online.
It's worth the precision.

Regards
Michel

Report message to a moderator

Re: to change .dbf file to another location [message #288524 is a reply to message #288456]

Mon, 17 December 2007 22:44

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Yes, It was my mistake.

Report message to a moderator

Previous Topic:	truncate not resetting high water mark
Next Topic:	Session history

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 18:16:52 CDT 2024