Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to do ORA-01110 and ORA-01157 errors when Oracle tablespaces are deleted by mistake". The explanation in this article is simple and clear, and easy to learn and understand. Please follow the ideas of Xiaobian to study and learn together "how to do ORA-01110 and ORA-01157 errors when Oracle tablespaces are deleted by mistake"!

Today encountered a more magical problem, a customer set of test database power restart, restart found that the database prompt ORA-01157: cannot identify/lock data file and ORA-01110 errors, after checking found that the system is not mounted after the boot storage, table space is placed on the storage disk, all problems solved after manual mounting storage. The problem was not recorded at the time, and is reproduced here through a test environment simulation.

Manufacturing experimental data [oracle@XLJ181 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 10 19:27:14 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@cams> create tablespace test datafile '/home/oracle/test.dbf' size 100M;Tablespace created.SYS@cams> create user test identified by 123456 default tablespace test;User created.SYS@cams> grant connect,resource to test;Grant succeeded.TEST@cams> create table test(id number primary key,name varchar2(20));Table created.TEST@cams> insert into test values(1,'bob');1 row created.TEST@cams> insert into test values(2,'joe');1 row created.TEST@cams> select count(*) from test; COUNT(*)---------- 2TEST@cams> conn / as sysdbaConnected.SYS@cams> shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.SYS@cams> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@XLJ181 ~]$ mv /home/oracle/test.dbf /home/oracle/test.dbf.bak Failure occurs

Start the database and find that the data file does not exist:

[oracle@XLJ181 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 10 19:38:26 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SYS@cams> startup;ORACLE instance started.Total System Global Area 5344731136 bytesFixed Size 2262656 bytesVariable Size 1040189824 bytesDatabase Buffers 4294967296 bytesRedo Buffers 7311360 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 63 - see DBWR trace fileORA-01110: data file 63: '/home/oracle/test.dbf'

View trace files:

Mon Dec 10 19:38:35 2018ALTER DATABASE OPENErrors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_dbw0_21153.trc:ORA-01157: cannot identify/lock data file 63 - see DBWR trace fileORA-01110: data file 63: '/home/oracle/test.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_ora_21175.trc:ORA-01157: cannot identify/lock data file 63 - see DBWR trace fileORA-01110: data file 63: '/home/oracle/test.dbf'ORA-1157 signalled during: ALTER DATABASE OPEN...

Check cams_ora_21175.trc file, error message is as follows:

DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)ORA-01110: data file 63: '/home/oracle/test.dbf'ORA-01157: cannot identify/lock data file 63 - see DBWR trace fileORA-01110: data file 63: '/home/oracle/test.dbf'

Check cams_dbw0_21153.trc file, error message is as follows:

ORA-01157: cannot identify/lock data file 63 - see DBWR trace fileORA-01110: data file 63: '/home/oracle/test.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

Data file 63: '/home/oracle/test. dbf'.

How should we deal with this problem? Especially in the test environment, in order to save resources, the archive will not be opened, and there will be no RMAN backup. How can the database run and minimize the data loss?

Common solutions: offline drop+recreateSQL> shutdown immediate;SQL> startup mount;SQL> alter database datafile '/home/oracle/test.dbf' offline drop;SQL> alter database open;SQL> drop tablespace test including contents; SQL> create tablespace test datafile '/home/oracle/test.dbf' size 100M;

Since this is a test environment, find ways to rebuild the data or import it using the most recent logical backup or other tests to minimize data loss.

If you delete the core system's tablespaces, you might as well rebuild the tablespaces and clean up the relevant data and import them again.

Thank you for reading, the above is the content of "Oracle table space error deletion causes startup prompt ORA-01110 and ORA-01157 error how to do", after learning this article, I believe you have a deeper understanding of Oracle table space error deletion causes startup prompt ORA-01110 and ORA-01157 error how to do this problem, the specific use situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.