Home » RDBMS Server » Server Administration » to change .dbf file to another location
to change .dbf file to another location [message #288338] Mon, 17 December 2007 02:27 Go to next message
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
Re: to change .dbf file to another location [message #288341 is a reply to message #288338] Mon, 17 December 2007 02:33 Go to previous messageGo to next message
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

Re: to change .dbf file to another location [message #288342 is a reply to message #288338] Mon, 17 December 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: to change .dbf file to another location [message #288350 is a reply to message #288348] Mon, 17 December 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: to change .dbf file to another location [message #288351 is a reply to message #288350] Mon, 17 December 2007 02:59 Go to previous messageGo to next message
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
Re: to change .dbf file to another location [message #288352 is a reply to message #288338] Mon, 17 December 2007 03:01 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes, complicated. Just shown an alternative way to OP.
Re: to change .dbf file to another location [message #288456 is a reply to message #288352] Mon, 17 December 2007 10:56 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Why don't online

With ALTER TABLESPACE RANAME datafile!!??
Re: to change .dbf file to another location [message #288460 is a reply to message #288456] Mon, 17 December 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: to change .dbf file to another location [message #288461 is a reply to message #288460] Mon, 17 December 2007 11:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: to change .dbf file to another location [message #288524 is a reply to message #288456] Mon, 17 December 2007 22:44 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes, It was my mistake.
Previous Topic: truncate not resetting high water mark
Next Topic: Session history
Goto Forum:
  


Current Time: Sun Dec 04 04:31:29 CST 2016

Total time taken to generate the page: 0.10840 seconds