Re: Stuffed SYSTEM tablespace.

I've been thinking about this some more since you seem to have taken my advice without noticing the caveats.

Put simply my original advice was bad. One uses offline drop in order to get the database open in order to drop your affected tablespace. I presume you do not wish to do this.

I do have a work around for you. However It depends critically on having either all archived logs since your failure or else all the redo being contained in the online log files.If you do not have all redo available then I think that the DB is dead - others might suggest alternatives based on error messages you supply. I attach the output from my sqlplus session below. At the end of this process your datafile will be back in the same place with the same name. Prior to this session I had deleted a 1mb datafile from the system, tablespace. Thanks should go to the excellent resources Howard maintains at www.hjrdba.com whence I 'stole' the alter database create datafile command.

The other lesson to learn of course is to critically evaluate all scripts and suggestions. In particular research on alter datafile blah offline drop; would have told you that the suggestion whilst it would tell Oracle to offline the datafile would result in an openable database.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

SQL> startup
ORACLE instance started.

Total System Global Area  142981148 bytes
Fixed Size                    75804 bytes
Variable Size              57573376 bytes
Database Buffers           85254144 bytes
Redo Buffers                  77824 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\1.ORA'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\1.ORA'


SQL> alter database datafile 'c:\1.ora' offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 8 is offline
ORA-01110: data file 8: 'C:\1.ORA'


SQL> alter database create datafile 'c:\1.ora' as 'c:\1.ora';

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 8 is offline
ORA-01110: data file 8: 'C:\1.ORA'


SQL> alter database datafile 8 online;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: 'C:\1.ORA'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.



"Jan Eliasen" <eliasen_at_cs.auc.dk> wrote in message
news:Pine.GSO.4.33.0112181413200.17874-100000_at_borg.cs.auc.dk...


> On Tue, 18 Dec 2001, Niall Litchfield wrote:

>

> > If there was no data in the datafile - and thats a pretty big if - you

might


> > get away with 'alter database datafile 'your file' offline drop;'

> Since the file was just added to the tablespace with a size of 1MB and I

> deleted it from the harddrive just after, I don't think there were any

> data in it.

>

> Anyway, I do a;

> sqlplus "/ as sysdba" and run the

> alter database datafile

> '/usr/local/oracle/product/9.0.1/oradata/database/system02.dbf' offline

> drop;

>

> command, and I get a "Database altered." in return.

>

> But next time I try to start up oracle, I get the same error as before. It

> does not seem to work.

>

> --

> Eliasen Jr.

>