rac环境数据文件误建到本地文件系统处理 ora-01157: cannot identify/lock data file 66 - see dbwr trace file ora-01110: data file 66: (文档 id 1612244.1) 修改时间: 2014-3-26 类型: problem in this document symptoms changes cause solution applie
rac环境数据文件误建到本地文件系统处理ora-01157: cannot identify/lock data file 66 - see dbwr trace file ora-01110: data file 66: (文档 id 1612244.1)
修改时间:2014-3-26类型:problem
in this document
  symptoms
changes
cause
solution
applies to:
oracle database - enterprise edition - version 10.2.0.1 and later
information in this document applies to any platform. symptoms user add datafile for rac database in the local node
sql> alter tablespace system add datafile 'fra' size 250m autoextend on next 50m maxsize unlimited;
then when he query dba_data_files
ora-01157: cannot identify/lock data file 66 - see dbwr trace file
ora-01110: data file 66: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/fra' 
changes datafiles add to system tablespace by mistake on the local node instead of shared diskgroup
cause this is expected as it is rac db and it suppose to create the new datafile on the shared disk no on the local node
when user add data file using sql> alter tablespace system add datafile 'fra' size 250m autoextend on next 50m maxsize unlimited;
its add the datafile in the default omg location db_create_file_dest
sql> show parameter db_create_file_dest
name type value
------------------------------------ ----------- ------------------------------
db_create_file_dest string
and when it is null the default locaion $oh/dbs
solution - open the db in mount state
- copy the created datafile to the shared disk group
please note that the db must be in the archive mode .
rman> copy datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/fra' to '+fra';
starting backup at 06-jan-14
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=8 instance=testdb12 device type=disk
channel ora_disk_1: starting datafile copy
input datafile file number=00066 name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/fra
output file name=+fra/testdb1/datafile/system.284.836141149 tag=tag20140106t132548 recid=1 stamp=836141162
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:15
finished backup at 06-jan-14 - using sql plus rename the datafile to the new location
sql>alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/fra' to '+fra/testdb1/datafile/system.284.836141149'; - open the db
sql> alter databse open; - remove the old file
$ rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/fra