ORA-01157 and ORA-01110 solutions

Berikut solusi untuk problem yang menyebabkan kita tidak bisa open database, entah bagaimana salah satu datafile dari database telah hilang. Saat kita analisa, database ternyata tidak membutuhkan “datafile” tsb lagi. Jadi kita remove datafile tsb. step2 nya seperti berikut untuk solusinya:

C:\Documents and Settings\Administrator>sqlplus “/as sysdba”

SQL*Plus: Release 9.0.1.4.0 – Production on Tue Oct 21 15:21:11 2008

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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.