Ora Error 1157
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Ora-01157 Ora-01110
ORA-01157 tips Oracle Database Tips by Burleson Consulting December 15, 2015 ora-01157 cannot identify/lock data file tempfile Question: I'm getting the ORA-01157 and ORA-01110 when trying to bring my tablespaces online: SQL> alter tablespace example online; alter tablespace
Ora 01157 Ora 01110 System01 Dbf
example online * ERROR at line 1: ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' How do I fix this ORA-01157 error? Answer: First, the Oracle docs note ora-01157 ora-01110 solution this on the ORA-01157 error: ORA-01157: cannot identify/lock data file string - see DBWR trace file Cause: The background process was either unable to find one 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 first instance to open the database will need to access all online data files. ora-01110 data file 201 Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.
As we see, the ORA-01157 is caused by a locking issue with the database writer (DBWR) background process. During a recovery, this can be caused by a unopened data files (i.e. database mounted but not open), a missing file, a permission problem in the file (e.g. no write permissions 770 on the files owned by Oracle). If the background process is unable to reach a data file, or is unable to lock it because it is in use , ORA-01157 is thrown because the database does not allow access. The other files will not be affected, but it is important to know that opening the database using the first instance will need to use online data files. there should be other errors which appear along with ORA-01157 to aid in resolving the problem. Straightening out ORA-01157 consists of making the files of the database available, and either regularly opening the database, or use ALTER SYSTEM CHECK DATAFILES . If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tipdb to dump file, ORA-01157 error If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to
Ora-01157 Cannot Identify/lock Data File Standby
register before you can post: click the register link above to proceed. dbwr trace file location To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to
Alter System Check Datafiles
8 of 8 Thread: exporting db to dump file, ORA-01157 error Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode http://www.dba-oracle.com/t_ora_01157_cannot_identify_lock_data_file_string_see_DBWR_trace_file.htm Switch to Threaded Mode 03-08-2005,04:39 AM #1 chandrasekhar20 View Profile View Forum Posts Junior Member Join Date Mar 2005 Posts 4 exporting db to dump file, ORA-01157 error Hello oh good men of DBASupport!!! I am trying to make a dump file out of my database and have been encountering the error . exporting tablespace definitions EXP-00008: ORACLE error 1157 encountered ORA-01157: cannot http://www.dbasupport.com/forums/showthread.php?46668-exporting-db-to-dump-file-ORA-01157-error identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: '/opt/oracle/oradata/orcl1/temp01.dbf' EXP-00000: Export terminated unsuccessfully The same error occurs when I try to load data using the "imp" command. Please help me out here. This is getting critical for my project. Thanks in advance. Chandra Reply With Quote 03-08-2005,04:42 AM #2 OracleDoc View Profile View Forum Posts The Doc is in! Join Date Sep 2003 Location over the hill and through the woods Posts 995 What did Metalink have to say about it? Oracle it's not just a database it's a lifestyle! -------------- BTW....You need to get a girlfriend who's last name isn't .jpg Reply With Quote 03-08-2005,04:47 AM #3 anandkl View Profile View Forum Posts Senior Advisor Join Date Aug 2001 Location chennai,bangalore Posts 840 Hi, Check the status of this file select status from dba_data_file where file_name='FILE_NAME'; Also see if this files needs recovery. select * from v$recover_file; Also check the most recent tracefile generated. It would tell you exactly what would be the issue. Try running dbverfiy on this datafile. It could be that this particular datafile is corrupt. regards an
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 http://nimishgarg.blogspot.com/2014/01/ora-01157-cannot-identifylock-data-file.html unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make 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 data file and lock a Datafile. This may 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 ora-01157 cannot identify/lock to recoved it by using a valid backup. - 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-