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>
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:
Post a Comment