Home » RDBMS Server » Server Administration » How to move the datafiles from one place to another? (Oracle 10g.)
How to move the datafiles from one place to another? [message #380688] Tue, 13 January 2009 00:42 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
When I tried to move a datafile from one place to another it throwed the below error.First I made the tablespace users offline and tried the below steps.I wonder whether i need to shutdown the database or i can move it without shutdown.Waiting for your kind suggestion.


SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME
='USERS';

FILE_NAME
--------------------------------------------------------------------------------

TABLESPACE_NAME
------------------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
USERS


SQL> ALTER DATABASE RENAME FILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.D
BF' TO 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF';
ALTER DATABASE RENAME FILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' T
O 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 4 - new file
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF' not found
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL>



Thanks & Regards,
Hammer.



Re: How to move the datafiles from one place to another? [message #380700 is a reply to message #380688] Tue, 13 January 2009 01:09 Go to previous messageGo to next message
mymailbox.21
Messages: 14
Registered: May 2007
Location: hyderabad
Junior Member
hi hammer,
did you performed OS copy of the datafile?

1) take the tablespace offline
2) copy the file to new location (if it is renaming, change the file name)
3) issue rename command
4) make tablespace online.
Re: How to move the datafiles from one place to another? [message #380705 is a reply to message #380700] Tue, 13 January 2009 01:20 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
@Mymailbox.21,

1) take the tablespace offline
2) copy the file to new location (if it is renaming, change the file name)
3) issue rename command
4) make tablespace online.


Yes, they all are done.

did you performed OS copy of the datafile?


??


Thanks & Regards,
Hammer.




Re: How to move the datafiles from one place to another? [message #380708 is a reply to message #380705] Tue, 13 January 2009 01:28 Go to previous messageGo to next message
mymailbox.21
Messages: 14
Registered: May 2007
Location: hyderabad
Junior Member
did you performed OS copy of the datafile? it is same in 2nd step. even it is trying to access 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' but it is not available.
Re: How to move the datafiles from one place to another? [message #380763 is a reply to message #380688] Tue, 13 January 2009 04:45 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I think i am missing something.why the below error is throwed when we try to change the location of the datafile.
@mymailbox.21,
even it is trying to access 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' but it is not available. 


The datafile is available in the path which i have mentioned.

SQL> ALTER TABLESPACE USERS OFFLINE;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=
'USERS';

FILE_NAME
--------------------------------------------------------------------------------

TABLESPACE_NAME
------------------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
USERS


SQL> ALTER DATABASE RENAME FILE  'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.
DBF' TO 'D:\oracle\product\10.1.0\db_1\oradata\sample\USERS01.DBF'
  2  /
ALTER DATABASE RENAME FILE  'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'
TO 'D:\oracle\product\10.1.0\db_1\oradata\sample\USERS01.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 4 - new file
'D:\oracle\product\10.1.0\db_1\oradata\sample\USERS01.DBF' not found
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL>


I have copied the location pasted correctly to where it has to reside.But the issue seems to be continuing .


Thanks & regards,
Hammer
Re: How to move the datafiles from one place to another? [message #380779 is a reply to message #380763] Tue, 13 January 2009 05:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The error message tells you the problem:

ORA-01141: error renaming data file 4 - new file
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF' not found

You need to do an O/S copy of the file to D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF before you can rename it in Oracle. Oracle does not copy the file for you.
Re: How to move the datafiles from one place to another? [message #380785 is a reply to message #380779] Tue, 13 January 2009 05:57 Go to previous message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
Thank You very much for your suggestions it got worked..


Thanks & Regards,
Hammer

[Updated on: Tue, 13 January 2009 06:17]

Report message to a moderator

Previous Topic: sys user problem
Next Topic: Clarificatons - oracle dba concepts
Goto Forum:
  


Current Time: Fri Dec 09 15:18:28 CST 2016

Total time taken to generate the page: 0.12161 seconds