请教大家一个表空间移数据的问题

lickey-ji

SQL> alter tablespace test_data1 offline;

Tablespace altered.

SQL> alter tablespace test_data1 rename datafile '/home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf' to '/home/test/test/device/test_data1.dbf';
alter tablespace test_data1 rename datafile '/home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf' to '/home/test/test/device/test_data1.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 3 - new file
'/home/test/test/device/test_data1.dbf' not found
ORA-01110: data file 3:
'/home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

然后我用oracle用户touch了/home/test/test/device/test_data1.dbf这个文件：
oracle@P690a:/home/oracle>touch /home/test/test/device/test_data1.dbf
oracle@P690a:/home/oracle>l /home/test/test/device/test_data1.dbf
-rw-r--r--   1 oracle   oracle            0 Nov 28 11:05 /home/test/test/device/test_data1.dbf


SQL> alter tablespace test_data1 rename datafile '/home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf' to '/home/test/test/device/test_data1.dbf';

Tablespace altered.

SQL> alter tablespace test_data1 online;
alter tablespace test_data1 online
*
ERROR at line 1:
ORA-01115: IO error reading block from file 3 (block # 1)
ORA-01110: data file 3: '/home/test/test/device/test_data1.dbf'
ORA-27091: unable to queue I/O
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1


请问大家我哪里出问题了，应该怎么解决．谢谢

numenhuang

原来的数据文件是按照OMF方式来创建的，现在要重新命名或者更改数据文件的存放位置？

一般做法：

1. sqlplus / as sysdba
2. shutdown immediate
3. su - oracle
cp -p /home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf  /home/test/test/device/test_data1.dbf
or
mv /home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf  /home/test/test/device/test_data1.dbf
4. startup mount;
    alter database rename file '/home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf' to '/home/test/test/device/test_data1.dbf';
    alter database open;

note:
使用alter tablespace rename datafile也可，一般步骤
1. alter tablespace xx offline normal;
2. su - oracle
    cp -p /home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf  /home/test/test/device/test_data1.dbf
or
mv /home/test/test/device1/DEFAULT/datafile/o1_mf_test_dat_2pq9dcm5_.dbf  /home/test/test/device/test_data1.dbf
3. alter tablespace xxxx rename datafile ....
4. alter tablespace xxxx online;

oracle rename只是更新controlfile的相关信息，物理文件不会自动复制到新位置，所以要通过OS命令来复制或者移动文件。

lickey-ji

我看ＯＲＡＣＬＥ的培训ＰＰＴ里面讲的，原来只是把文件ＣＯＰＹ过去，然后再修改他在数据库里面的定义而已．谢谢，我试试看．

在ＣＯＰＹ之前一定要把tablespace　offline了，否则之后做online的时候就会报错：
ORA-01113: file 3 needs media recovery

[ 本帖最后由 lickey-ji 于 2006-11-28 11:30 编辑 ]