ORA-01157: cannot identify/lock data file -- see DBWR trace file

Yesterday I refreshed this DB from Prod,since it was a friday and I was in a hurry so I left the process after it completed the duplicate DB command.

Over the weekend I saw


Fri Jul 08 16:59:18 2011
Errors in file /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_19878.trc:
ORA-01157: cannot identify/lock data file 513 - see DBWR trace file
ORA-01110: data file 513: '+QA_DATA_01'
Fri Jul 08 17:04:19 2011


first thing I checked is to make sure I dont have any offline datafiles:

SQL> select name,status from v$datafile where status!='ONLINE' and status !='SYSTEM';

no rows selected

cool, so everything looks good.

Now check the alert log:

Fri Jul 08 17:28:17 2011
Errors in file /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_6623.trc:
ORA-01157: cannot identify/lock data file 523 - see DBWR trace file
ORA-01110: data file 523: '+QA_DATA_01'

no usefule info, Oh ooo.. so check the trace file:


>vi /icm01/u0001/app/diag/rdbms/QC/QC2/trace/QC2_j000_19878.trc


*** 2011-07-08 16:59:20.276
*** SESSION ID:(1522.9) 2011-07-08 16:59:20.276
*** SERVICE NAME:(SYS$USERS) 2011-07-08 16:59:20.276
*** MODULE NAME:(DBMS_SCHEDULER) 2011-07-08 16:59:20.276
*** ACTION NAME:(QUEST_PPCM_JOB_PM_2) 2011-07-08 16:59:20.276

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 516: '+QA_DATA_01'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 523: '+QA_DATA_01'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 513: '+QA_DATA_01'

nothing much useful info either in the trace file.

so lets try to offline the datafile:


SQL> alter database datafile 523 offline;
alter database datafile 523 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "523"


nope...doesn't work...

Only thing I left to check is TEMPFILEs,since they won't come across the backups so, lets check the tempfiles.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01
+QA_DATA_01


Bingo...there we go...so we dont have the tempfiles.


SQL> select name from v$tablespace where name like '%TEMP%';

NAME
------------------------------
TEMP2
TEMP1

SQL> drop tablespace temp1;
drop tablespace temp1
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> drop tablespace temp2;

Tablespace dropped.

SQL> create temporary tablespace temp2 ;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp1;

Tablespace dropped.

SQL> create temporary tablespace temp1;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> alter tablespace temp1 add tempfile;

Tablespace altered.


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+QA_DATA_01/QC/tempfile/temp2.450.756037653
+QA_DATA_01/QC/tempfile/temp1.466.756037693
+QA_DATA_01/QC/tempfile/temp1.454.756037771