ACCIDENTALLY data file is added without “+” sign

In RAC or ASM environment, we missed the ‘+’ sign while adding datafile in tablespace. we got error in ora-01110, ora-27037 in alert log.

ORA-01157: cannot identify/lock data file 3129 - see DBWR trace file
ORA-01110: data file 3129: '/home/oracle/product/10.2.0/db/dbs/DATA'
ORA-27037: unable to obtain file status
ORA-01186: file 3129 failed verification tests
ORA-01157: cannot identify/lock data file 3129 - see DBWR trace file
ORA-01110: data file 3129: '/home/oracle/product/10.2.0/db/dbs/DATA'
File 3129 not verified due to error ORA-01157

Solution

1.  Find out the data file number( It is available in the alert log)
2.  use the rman to copy the data to ASM disk group.
3.  offline the data file
4.  copy the data file to the ASM disk group
5.  switch the data file from old name to new COPY file
6.  Recover the new data file
7.  make data file on line.

-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Dec 10 12:10:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: (DBID=732088347)

RMAN> sql 'alter database datafile 3129 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 3129 offline

RMAN> copy datafile '/home/oracle/product/10.2.0/db/dbs/DATA' to '+DATA';

Starting backup at 10-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2179 instance= devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=2125 instance= devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=2139 instance= devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=2057 instance= devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=2101 instance= devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=2121 instance= devtype=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: sid=2122 instance= devtype=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: sid=2124 instance= devtype=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: sid=2108 instance= devtype=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: sid=2149 instance= devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=03129 name=/home/oracle/product/10.2.0/db/dbs/DATA
output filename=+DATA/datafile/ymedia_cookie_tdata_200907.4732.705240801 tag=TAG20091210T121321 recid=5381 stamp=705240808
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 10-DEC-09

Starting Control File and SPFILE Autobackup at 10-DEC-09
piece handle=/home/oracle/product/10.2.0/db/dbs/c-732088347-20091210-03 comment=NONE
Finished Control File and SPFILE Autobackup at 10-DEC-09

RMAN> switch datafile '/home/oracle/product/10.2.0/db/dbs/DATA' TO COPY;

datafile 3129 switched to datafile copy "+DATA/datafile/ymedia_cookie_tdata_200907.4732.705240801"

RMAN> recover datafile '+DATA/datafile/ymedia_cookie_tdata_200907.4732.705240801';

Starting recover at 10-DEC-09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=2056 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=2111 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=2085 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=2113 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: sid=2091 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_5: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_6
channel ORA_SBT_TAPE_6: sid=2129 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_6: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_7
channel ORA_SBT_TAPE_7: sid=2116 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_7: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_8
channel ORA_SBT_TAPE_8: sid=2150 instance=devtype=SBT_TAPE
channel ORA_SBT_TAPE_8: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_9
channel ORA_SBT_TAPE_9: sid=2133 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_9: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_10
channel ORA_SBT_TAPE_10: sid=2134 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_10: NetVault Oracle APM v.5.5.11

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 10-DEC-09
channel ORA_SBT_TAPE_1: sid=2056 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=2111 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=2085 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=2113 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: sid=2091 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_5: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_6
channel ORA_SBT_TAPE_6: sid=2129 instance= devtype=SBT_TAPE
channel ORA_SBT_TAPE_6: NetVault Oracle APM v.5.5.11
allocated channel: ORA_SBT_TAPE_7

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 10-DEC-09

RMAN> sql 'alter database datafile 3129 online';

sql statement: alter database datafile 3129 online

RMAN> quit