Re: ORA-01157

On Jul 7, 8:08 am, sybra..._at_hccnet.nl wrote:
> On Mon, 7 Jul 2008 04:09:13 -0700 (PDT), martin_ian_le..._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- 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 -- Received on Mon Jul 07 2008 - 14:32:05 CDT