RAC环境中ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

在RAC环境中，在某一节点创建本地文件系统的表空间，随后在另外的一个节点的alter日志里发现如下错误

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/database/diag/rdbms/mhxy/mhxy2/trace/mhxy2_dbw0_8842.trc:
ORA-01186: file 7 failed verification tests
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/t3.dbf'
File 7 not verified due to error ORA-01157

原因是错误的把本应该存放在共享磁盘组里面数据文件，存放在本地。造成除本节点外的其它节点无法方法。

按照如下步骤操作： 使用RMAN 的copy功能


[oracle@mhxy01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 28 23:26:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MHXY (DBID=4243361697)

RMAN> sql 'alter tablespace t3 offline';

using target database control file instead of recovery catalog
sql statement: alter tablespace t3 offline

RMAN> copy datafile '/home/oracle/t3.dbf' to '+DATA';

Starting backup at 2017-02-28 23:27:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 instance=mhxy1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/t3.dbf
output file name=+DATA/mhxy/datafile/t3.274.937178829 tag=TAG20170228T232706 RECID=1 STAMP=937178830
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2017-02-28 23:27:10

RMAN> sql "alter database rename file ''/home/oracle/t3.dbf'' to ''+DATA/mhxy/datafile/t3.274.937178829''";

sql statement: alter database rename file ''/home/oracle/t3.dbf'' to ''+DATA/mhxy/datafile/t3.274.937178829''

RMAN> sql 'alter tablespace t3 online';

sql statement: alter tablespace t3 online

RMAN> exit

Recovery Manager complete.

可以在两个节点的SQLPLUS 中，查询验证  

SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> set line 200
SQL>  select tablespace_name,file_name,status,online_status from dba_data_files;

当然，修复这个问题的手段多样，如asm 的cp 命令、 dbms_file_transfer.copy_file

参考博客： http://www.oraclema.com/oracle/rac-datafile-in-local-node.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27039319/viewspace-2134462/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27039319/viewspace-2134462/