Ora 1157 Error
Contents |
data file / ORA-01110: data file/ EXP-00056: ORACLE error 1157encountered June 1, 2011 by samadhan Dear all, yesterday I face small issue which I would like ora-01157 cannot identify/lock data file tempfile to share with you guy's.It was temporary tablespace issue.This was development ora-01157 cannot identify/lock data file 201 - see dbwr trace file server for storage was recently changed.As mount point was changed but control file was having the previous
Ora 01157 Ora 01110 System01 Dbf
temp file details so got the error while exporting schema.Just drop the old tempfile and created new one. Issue got resolved after that. bash-2.05$ ls -lrt total
Ora-01157 Ora-01110 Solution
562420 -rw-r-r- 1 oracle10g oinstall 1729 Jan 30 14:57 abc.sh -rw-r--- 1 oracle10g oinstall 77631488 Jan 30 23:50 reporter_sam_exp30JAN2011.dmp -rw-r--- 1 oracle10g oinstall 77635584 Apr 9 23:19 reporter_sam_exp09042011.dmp -rw-r--- 1 oracle10g oinstall 132513792 Jun 1 04:01 SAM1_exp31052011.dmp bash-2.05$ exp Export: Release 10.2.0.3.0 - Production on Wed Jun 1 04:13:42 2011 Copyright (c) 1982, 2005, Oracle. All ora-01157 cannot identify/lock data file standby rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Enter array fetch buffer size: 4096 > Export file: expdat.dmp > SAM1_orig_exp31052011.dmp (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2 Export grants (yes/no): yes > y Export table data (yes/no): yes > y Compress extents (yes/no): yes > y Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified users … User to be exported: (RETURN to quit) > SAM1 User to be exported: (RETURN to quit) > . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SAM1 . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SAM1 About to export SAM1's objects … . exporting database links . exporting sequence numbers . exporting cluster definitions EXP-00056: ORACLE error 1157 enc
of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the
Ora-01110 Data File
first instance to open the database will need to access all online data dbwr trace file location files. Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make ora-01110 data file 201 file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES. Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e900.htm#ORA-01157 ORA-01157 is raised when Database Writer (DBWR) is unable to find and lock a Datafile. This may https://samadhandba.wordpress.com/2011/06/01/ora-01157-cannot-identifylock-data-file-ora-01110-data-file-exp-00056-oracle-error-1157-encountered/ be due to various reasons like - Datafile is deleted or corrupt - Datafile is renamed or moved - Mount point is incorrect - Issues with Read/write permission on Datafile To solve ORA-01157 we can use one of the following steps: - If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recoved it by using a valid backup. - http://nimishgarg.blogspot.com/2014/01/ora-01157-cannot-identifylock-data-file.html If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not containt important segments, that can be dropped offline - If datafile is renamed or moved then we need to get it in its original position - If Mount point is incorrect, simply recreate the mount point - if it is due to permission then we need to grant the permission at OS level I faced ORA-01157 at my local environment, when I was restoring a cold backup. Fortunatially it was with a tablespace which was added for testing purpose and was not critical. So I simply drop the datafile using "OFFLINE DROP" clause and opened the database. C:\>sqlplus sys/sys as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 30 19:07:16 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 778387456 bytes Fixed Size 1384856 bytes Variable Size 520097384 bytes Database Buffers 251658240 bytes Redo Buffers 5246976 bytes Database mounted. ORA-01157: cannot identify/lock data file 11 - see DBWR trace file ORA-01110: data file 16: 'E:\oracle\app\nimish.garg\oradata\orcl\test_ts.dbf' SQL> select NAME from v$datafile where file#=16; NAME -------------------------------------------------------------------------------- E:\ORACLE\APP\NIMISH.GARG\ORADATA\ORCL\TEST_TS.DBF SQL> alter data
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About http://dba.stackexchange.com/questions/60031/i-have-mistakenly-deleted-datafiles Us Learn more about Stack Overflow the company Business Learn more about hiring http://www.oraclenutsandbolts.net/index.php/knowledge-base/rman/56-what-does-error-ora-01157-cannot-identify-lock-data-file-mean developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how data file it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top I have mistakenly deleted Datafiles. up vote 2 down vote favorite 1 In my production server I have mistakenly deleted datafiles. When I am trying to open the database I am getting an error. The files are not important but the database is not opening up. ora-01157 cannot identify/lock SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open RESETLOGS; alter database open RESETLOGS * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/mars/oradata/MARS/system01.dbf' I have tried plenty of ways to recover but it is not opening. LIKE SQL> recover database until cancel; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ; ORA-00283: recovery session canceled due to errors ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/mars/oradata/MARS/example01.dbf' oracle share|improve this question asked Mar 3 '14 at 12:11 temp 16113 migrated from stackoverflow.com Mar 3 '14 at 12:51 This question came from our site for professional and enthusiast programmers. 3 You need to restore your backup. –a_horse_with_no_name Mar 3 '14 at 12:13 2 The files are not important made me giggle, because the database already decided, that those ARE important. Restore backup from tape –Najzero Mar 3 '14 at 12:13
Application ClusterScriptsDocumentation LinksAdv. Search Knowledge Base RMAN What does error ORA-01157: cannot identify/lock data file mean Did you find the article interesting? KB Subcategories: ASM and CRS Data Guard Database Administration Oracle Streams Performance Monitoring and Tuning RMAN Real Application Cluster (RAC) Datapump Oracle Grid Infrastructure OS (Unix, AIX, Linux, Windows) Scripts What does error ORA-01157: cannot identify/lock data file mean Written by D.A. Why do I get error: ORA-01157: cannot identify/lock data file every time I do a duplicate database? What does "ORA-01157: cannot identify/lock data file" error mean?The error is related to locking the data file. If the duplication was already done once in the past, and the files from the previous duplication still exist, then error ORA-01157 will be raised.Oracle will automaticly recreate the temp files for example, if the temp files already exist on the disk. But before doing that, Oracle will raise "ORA-01157: cannot identify/lock data file" for each temp file that is physically on disk. Bellow are the logs generated during a duplicate database with the command: duplicate target database to newsid as show in this article: How to duplicate/replicate a database in Oracle In the alert log of duplicated database I have:Errors in file /u01/oracle/admin/mysiddb/bdump/mysiddb_dbw0_836014.trc:ORA-01186: file 203 failed verification testsORA-01157: cannot identify/lock data file 203 - see DBWR trace fileORA-01110: data file 203: '/data02/oradata/mysiddb/proxytemp01.dbf'File 203 not verified due to error ORA-01157Mon Jun 04 17:23:45 WET 2012Dictionary check completeMon Jun 04 17:23:45 WET 2012SMON: enabling tx recoveryMon Jun 04 17:23:45 WET 2012Re-creating tempfile /data02/oradata/mysiddb/temp02.dbfRe-creating tempfile /data02/oradata/mysiddb/temp01.dbfRe-creating tempfile /data02/oradata/mysiddb/proxytemp01.dbfDatabase Characterset is CL8ISO8859P5Opening with internal Resource Manager planreplication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCIn the tracefile I have the errors:ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/data02/oradata/mysiddb/temp01.dbf'ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: d