| How to move the datafiles from one place to another? [message #380688] |
Tue, 13 January 2009 00:42  |
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 #380705 is a reply to message #380700] |
Tue, 13 January 2009 01:20  |
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 #380763 is a reply to message #380688] |
Tue, 13 January 2009 04:45 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
