ORA-01157

From: <martin_ian_lewis_at_yahoo.com>
Date: Mon, 7 Jul 2008 04:09:13 -0700 (PDT)
Message-ID: <3be9612d-8f29-4433-8777-0a11023728e0@m73g2000hsh.googlegroups.com>

Hello,
Have a tablespace to repair - its a new database so dataloss no problem.
The guy who created it originally is "not available" I get this error:
SQL> conn / as sysdba
Connected.
SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5133828096 bytes

Fixed Size                  1985752 bytes
Variable Size             939529000 bytes
Database Buffers         4177526784 bytes
Redo Buffers               14786560 bytes

Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6:
'/u01/app/oracle/product/10.2.0/Db_1/dbs/APP_DATA_THREE' And quite right this file is not present - I expect it was deleted at the unix prompt.
And is should not be created there in the first place I suppose. So I thought log in and find out which tablespace own this datafile: sqlplus system/XXXXX

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 7 12:04:32 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Unsurprisingly cannot log in - database not open.

From the conn / as sysdba no joy either.

SQL> select tablespace_name from dba_tablesspaces; select tablespace_name from dba_tablesspaces

                            *

ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

So er what can I do to troubleshoot this problem?

Thanks In Anticipation

Martin Received on Mon Jul 07 2008 - 06:09:13 CDT