Oracle测试案例：在线移动错误存储位置的数据文件

前提

1 开启归档模式

2 在线移动过程中，访问该数据文件中数据的业务会报错，其他业务不受影响

新建模拟的数据文件

SQL> select name from v$datafile;

NAME

/u01/app/oradata/test11g2/system01.dbf
/u01/app/oradata/test11g2/sysaux01.dbf
/u01/app/oradata/test11g2/undotbs01.dbf
/u01/app/oradata/test11g2/users01.dbf

SQL> alter tablespace users add datafile '/u01/app/oradata/users02.dbf' size 100m autoextend off;

Tablespace altered.

在新建的数据文件中填充数据

SQL> create table test.t100 tablespace users as select * from all_objects;

Table created.

SQL> insert into test.t100 select * from test.t100;

14679 rows created.

SQL> /

29358 rows created.

SQL> /

58716 rows created.

SQL> /

117432 rows created.

SQL> /

234864 rows created.

SQL> /

469728 rows created.

SQL> /
insert into test.t100 select * from test.t100

ERROR at line 1:
ORA-01653: unable to extend table TEST.T100 by 1024 in tablespace USERS

SQL> commit;

Commit complete.

SQL> select count(*) from test.t100;

COUNT(*)

939456

将数据文件Offline

SQL> select file_id,file_name,tablespace_name,status from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME STATUS

---

     1 /u01/app/oradata/test11g2/system01.dbf   SYSTEM                         AVAILABLE
     2 /u01/app/oradata/test11g2/sysaux01.dbf   SYSAUX                         AVAILABLE
     3 /u01/app/oradata/test11g2/undotbs01.dbf  UNDOTBS1                       AVAILABLE
     4 /u01/app/oradata/test11g2/users01.dbf    USERS                          AVAILABLE
     5 /u01/app/oradata/users02.dbf             USERS                          AVAILABLE

SQL> alter database datafile '/u01/app/oradata/users02.dbf' offline;

Database altered.

SQL> connect test
Enter password:
Connected.
SQL> select table_name from user_tables;

TABLE_NAME

T100
OBJTEST

SQL> select count(*) from objtest;

COUNT(*)

 15389

SQL> select count() from t100;
select count() from t100
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oradata/users02.dbf'

从操作系统移动该数据文件到预期位置

host:

cp -p /u01/app/oradata/users02.dbf /u01/app/oradata/test11g2/users02.dbf

数据库中更新数据文件位置

SQL> connect /as sysdba
Connected.
SQL> alter database rename file '/u01/app/oradata/users02.dbf' to '/u01/app/oradata/test11g2/users02.dbf';

Database altered.

SQL> select file_id,file_name,tablespace_name,status from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME STATUS

---

     1 /u01/app/oradata/test11g2/system01.dbf   SYSTEM                         AVAILABLE
     2 /u01/app/oradata/test11g2/sysaux01.dbf   SYSAUX                         AVAILABLE
     3 /u01/app/oradata/test11g2/undotbs01.dbf  UNDOTBS1                       AVAILABLE
     4 /u01/app/oradata/test11g2/users01.dbf    USERS                          AVAILABLE
     5 /u01/app/oradata/test11g2/users02.dbf    USERS                          AVAILABLE

在线恢复该数据文件

SQL> recover datafile '/u01/app/oradata/test11g2/users02.dbf';
Media recovery complete.

将该数据文件Online

SQL> alter database datafile '/u01/app/oradata/test11g2/users02.dbf' online;

Database altered.

SQL> select count(*) from test.t100;

COUNT(*)

939456

SQL>

适用场景

1 开启归档模式

2 单机

3 RAC使用RMAN copy或者ASMCMD cp亦可

4 适用所有Oracle版本

参考文档

RAC: How to move a datafile that was added by mistake on local storage to shared location (Doc ID 1678747.1)