Re: ORA-01157

From: <sybrandb_at_hccnet.nl>
Date: Mon, 07 Jul 2008 14:08:42 +0200
Message-ID: <go1474dvn7k97g83od6tofb7b5ot8hel2l@4ax.com>


On Mon, 7 Jul 2008 04:09:13 -0700 (PDT), martin_ian_lewis_at_yahoo.com wrote:

>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

shutdown abort

startup mount

use ts$, file$ and possible v$datafiles (not sure about the last one)

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Mon Jul 07 2008 - 07:08:42 CDT

Original text of this message