Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Saturday, July 9, 2011

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

5 comments:

Vinoth - Oracle DBA said...

Thanks for this and it helped me.

Steve Dunnings said...

Hi, excellent example - thank you.

Regards
Steve

Mahesh Parmar said...

Hi sameer,

I am facing below errro while i am doing a recovery on standby db

SQL> recover standby database until cancel using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 4: '/home/oracle/app/oracle/admin/mdbsty/df/mdbsty1.dbf'
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/app/oracle/admin/mdbsty/df/mdbsty1.dbf'

Please mail me then soution if possible to my mail id mahesh.v.parmar2012@gmail.com

dinuĊ› said...

Hi, your article helped me too. Thank you.

Best Regards,
Daniel

Genious Person said...

Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! PCfreegallerylockfolderlock - Don't Spend Time Looking, Study Information On Cell Desktops On this web page how to encrypt a file or folder