ORA-01157

martin_i...@yahoo.com

unread,

Jul 7, 2008, 7:09:13 AM7/7/08

to

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

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

sybr...@hccnet.nl

unread,

Jul 7, 2008, 8:08:42 AM7/7/08

to

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

shutdown abort

startup mount

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

--
Sybrand Bakker
Senior Oracle DBA

Mark D Powell

unread,

Jul 7, 2008, 3:32:05 PM7/7/08

to

On Jul 7, 8:08 am, sybra...@hccnet.nl wrote:
> On Mon, 7 Jul 2008 04:09:13 -0700 (PDT), martin_ian_le...@yahoo.com

> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Also try v$recover_file.

Did you check to see if someone moved the file to where it should be
and did not rename the file in the database?

Do you have a backup?

If in archive log mode then from a backup you can restore the file and
after doing so run recovery. If the database is in noarchivelog mode
then you would need to restore the entire database.

Otherwise, since you said this is new and data loss is OK then you
could re-run the create script (providing it was saved) or after
gathering some information use DBCA to recreate the database. If you
have an export file you can use it to repopulate your objects to the
time of the export.

HTH -- Mark D Powell --