12 February 2007

Solution of ORA-01157 and ORA-01110

A few days ago one of my colleagues has asked me about a problem that does not allow to open database. Somehow one of datafiles of database has been removed. When we analyze, realize that the they do not need "the datafile" anymore. So we removed the datafile.
I printed out below which way i follow to solve the problem.

oracle@gnome_test> sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Feb 8 14:54:38 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area  236404368 bytes
Fixed Size                   724624 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/data02/oradata/test/testuser_.dbf
SQL>host more /data02/oradata/test/testuser_.dbf
/data02/oradata/test/testuser_.dbf: No such file or directory
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  236404368 bytes
Fixed Size                   724624 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
Database mounted.
SQL> alter database datafile  '/data02/oradata/test/testuser_.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  236404368 bytes
Fixed Size                   724624 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
SQL>  

No comments: