ORA-01157 in Oracle Database: unable to identify/lock data files

Source: Internet
Author: User

ORA-01157 in Oracle Database: unable to identify/lock data files

Environment: SLES 11 sp1 + RAC 11.2.0.3.7

I am about to take a vacation. I checked the database and found that node 2 of the billable billingdb database has the following error:
Fri Apr 25 21:51:13 2014
Errors in file/Oracle/app/oracle/diag/rdbms/ispace/ispace2/trace/ispace2_m000_15002.trc:
ORA-01157: unable to identify/lock data file 54-see DBWR trace file
ORA-01110: Data File 54: '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/DATADG'
Fri Apr 25 22:01:14 2014
Errors in file/oracle/app/oracle/diag/rdbms/ispace/ispace2/trace/ispace2_m000_30469.trc:
ORA-01157: unable to identify/lock data file 54-see DBWR trace file
ORA-01110: Data File 54: '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/DATADG'
 

So I checked the status of the data file and found that the data file could not be found on node 2.
When checking Node 1, we found that the data file was created in the local directory:
TABLESPACE_NAME FILE_ID FILE_NAME
MB STATUS AUT
------------------------------------------------------------------------------------------------------------------
SRPTSP 54/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/DATADG
32500 AVAILABLE NO
SRPTSP 55 + DATADG/ispace/datafile/srptsp.4718.845812309
32500 AVAILABLE NO
 

Depressed, how can I create a data file in a local directory?
 
After checking, we found that a new DBA wrote "+ DATADG" incorrectly as "DATADG" when creating the data file, which is equivalent to missing "+". The database is in OMF format, by default, data files are created under $ ORACLE_HOME/dbs in the local directory. More importantly, the monitoring system has just encountered a fault. Otherwise, a warning is triggered. Fortunately, this is to store the historical data of the transfer, which has no impact on the business. Unfortunately, we are lucky to know that ....
 

Solution:
Create a tablespace, move all objects in the No. 54 data file to the new tablespace, rebuild the index to the new tablespace, and then:
Alter table xxxx move tablespace yyyyy;
Alter tablesapce SRPTSP drop datafile 54;
Then, add the data file to the DATADG disk group and move the data back to the original tablespace.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.