oracle 11G RAC环境中某数据文件（非system表空间）创建在本地，不停机迁移到ASM磁盘中

概述：有时在表空间扩容时误将数据文件创建在了本地，如何不停机维护将本地文件迁移至ASM磁盘组。

检查数据库归档模式：archive log list

添加错误的数据文件：alter tablespace test add datafile '"+datadg';由于单引号中添加了双引号导致创建在了本地

SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a50
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
  
FILE_NAME                                  FILE_ID  ONLINE_STATUS  TABLESPACE_NAME
---------------------- ------   ---- ---------   --------------------
+DATA/prod/datafile/undotbs1.258.1001122667      3  ONLINE         UNDOTBS1
+DATA/prod/datafile/users.259.1001073567         4  ONLINE         USERS
+DATA/prod/datafile/sysaux.257.1001073565        2  ONLINE         SYSAUX
+DATA/prod/datafile/system.256.1001073565        1  SYSTEM         SYSTEM
+DATA/prod/datafile/example.264.1001073679       5  ONLINE         EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909      6  ONLINE         UNDOTBS2
/u01/app/oracle/dbs/"+datadg.dbf                           7  ONLINE         TEST

Node2:
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/dbs/"+datadg.dbf'
no rows selected

调整：

Node1:

迁移文件：
SQL> alter database datafile 7 offline;
Database altered.
RMAN> copy datafile '/u01/app/oracle/dbs/"+datadg.dbf' to '+DATA';
Starting backup at 2019-02-28 03:13:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=PROD1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/asd01.dbf
output file name=+DATA/prod/datafile/test.269.1001387639 tag=TAG20190228T031358 RECID=2 STAMP=1001387638
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2019-02-28 03:13:59
RMAN> switch datafile '/u01/app/oracle/dbs/"+datadg.dbf' to copy;
datafile 7 switched to datafile copy "+DATA/prod/datafile/test.269.1001387639"

恢复文件：

Node1:
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DATA/prod/datafile/test.269.1001387639'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
 

查看恢复文件： 
Node1:
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
  
FILE_NAME                     FILE_ID  ONLINE_STATUS  TABLESPACE_NAME
---------------------- ------   ---- ---------   --------------------
+DATA/prod/datafile/undotbs1.258.1001122667      3 ONLINE           UNDOTBS1
+DATA/prod/datafile/users.259.1001073567        4 ONLINE           USERS
+DATA/prod/datafile/sysaux.257.1001073565       2 ONLINE           SYSAUX
+DATA/prod/datafile/system.256.1001073565       1 SYSTEM           SYSTEM
+DATA/prod/datafile/example.264.1001073679       5 ONLINE           EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909      6 ONLINE           UNDOTBS2
+DATA/prod/datafile/test.269.1001387639         7 ONLINE           TEST
7 rows selected.

删除本地文件

ok，本地迁移完成；主要将文件创建在一号节点，连接在二号节点的会话将无法访问到本地数据文件的数据而报错，而一号节点的会话都是可以正常进行业务的。