Oliver,
The tablespace *does* exist as the drop tablespace
command didn't succeed since you have active tx's in
one of the rbs's. If you mean that the datafile that
needs recovery doesn't exist at the OS level, that's a
different story. Even if you remove a datafile at the
OS level, it's not removed from the db until the
tablespace to which it belongs is dropped.
If the db is in archivelog mode, restore the datafile
from backup and recover it, but I think you already
mentioned that you don't have a backup.
Without a backup or an export, from which to rebuild,
you're out of luck.
<soapbox>
You can force the db open with certain undocumented
init.ora parameters, but I'm not going there, although
I'm sure someone else on the list will be more than
happy to do so.
There are many restrictions and issued involved with
doing this and I'm not comfortable giving such advice
freely over the list since improper use can leave you
in a worse state than you were originally. These
options introduce logical corruption as you are
essentially telling Oracle to ignore whatever is in
the RBS and treat whatever is on disk as committed.
In addition, unless you are 99.9%+ sure of what txs
are in those RBS's you should use these options only
to force the db open in order to immediately do an
export and rebuild the db. Continuing to use a db
which has been forced open this way, without
rebuilding it, can cause serious problems which may
not manifest themselves until much further down the
road so what would have been a simple, albeit
time-consuming, rebuild initially turns into a major
headache (been there, done that, it wasn't pretty).
Should you decide to go down this route, I highly
recommend shutting down and doing a cold backup first
so you can come back to this point, if needed.
</soapbox>
HTH,
- Anita
- Oliver Artelt <oli_at_cubeoffice.de> wrote:
> On Tue, 20 Jun 2000, A. Bardeen wrote:
> > Oliver,
> >
> > The answer is in your query of V$ROLLSTAT which
> shows
> > your rbs's with a status of "NEEDS RECOVERY"
> >
> > Checking the status of the datafiles in V$DATAFILE
> > will most likely show that one or more of the
> > datafiles belonging to the RBS tablespace are also
> > needing recovery.
> >
> > If the db is in archivelog mode and the datafile
> still
> > exists at the OS level you can simply issue:
> >
> > RECOVER DATAFILE '<filename>';
> >
> > If the db is not in archivelog mode, you're pretty
> > much hosed since there's no way to recover the
> > datafile so it can be brought online, which is
> needed
> > to allow the active tx in the rbs to commit or
> > rollback, which is needed to allow you to drop
> that
> > rbs.
> >
> > HTH,
> >
> > -- Anita
>
> only the rbs-ts needs recovery, but it doesn't
> exists
>
> NAME
> |STATUS
>
--------------------------------------------------|-------
> /ora/u02/oradata/stock1/system01.dbf
> |SYSTEM
> /ora/u02/oradata/stock1/oemrep01.dbf
> |OFFLINE
> /ora/u03/oradata/stock1/rbs01.dbf
> |RECOVER
> /ora/u02/oradata/stock1/temp01.dbf
> |OFFLINE
> /ora/u04/oradata/stock1/indx01.dbf
> |OFFLINE
> /ora/u04/oradata/stock1/user01.dbf
> |OFFLINE
> /dev/raw1
> |OFFLINE
> /dev/raw2
> |OFFLINE
> /dev/raw3
> |OFFLINE
> /ora/u03/oradata/stock1/rbs02.dbf
> |OFFLINE
>
>
> >
> > --- Oliver Artelt <oli_at_cubeoffice.de> wrote:
> > >
> > > Hi everyone, could someone help me?
> > >
> > > I've deleted various tablespaces in a database
> > > (linux 2.2.14, EE 8.1.5.0.2) -
> > > some kind of big whoop. O.K., I use this
> database to
> > > prepare myself for the
> > > OCP-tests and so no backup were taken (Yes, I
> had to
> > > know that backups are
> > > necessary but the backup/recovery exam follows
> later
> > > :-). Recreating the
> > > tablespaces and copying the logs/controls from
> > > existing members was easy but
> > > I've got no plan how to restore/kill-rebuild the
> > > deleted tablespace with the
> > > rollback segs. Seems to me like an deadlock
> problem
> > > over various things:
> > >
> > >
> > > SET TRANSACTION USE ROLLBACK SEGMENT system;
> > >
> > > Try to drop the tablespace:
> > > drop tablespace rbs01 including contents;
> > > -> ORA-01548: active rollback segment 'R05'
> found,
> > > terminate dropping tablespace
> > >
> > > Try to kill the seg:
> > > drop rollback segment r05;
> > > -> ORA-01545: rollback segment 'R05' specified
> not
> > > available
> > >
> > > Try to set it offline:
> > > alter rollback segment r05 offline;
> > > -> ORA-01598: rollback segment 'R05' is not
> online
> > >
> > > Try to set up another tablespace to temporary
> solve
> > > the problem:
> > > create tablespace rbs02 datafile
> > > '/ora/u03/oradata/stock1/rbs02.dbf' size 20m;
> > > -> ORA-00604: error occurred at recursive SQL
> level
> > > 1
> > > ORA-00376: file 3 cannot be read at this time
> > > ORA-01110: data file 3:
> > > '/ora/u03/oradata/stock1/rbs01.dbf'
> > >
> > > Recover tablespace (maybe resetting rbs)
> > > recover tablespace rbs01;
> > > ->ORA-01157: cannot identify/lock data file 3 -
> see
> > > DBWR trace file
> > >
> > > alter tablespace rbs01 offline immediate;
> > > ->no help.
> > >
> > > alter tablespace rbs01 add
> > > datafile'/ora/u03/oradata/stock1/rbs02.dbf' size
> 200
> > > m;
> > > alter tablespace rbs01 online;
> > > ->ORA-01157: cannot identify/lock data file 3 -
> see
> > > DBWR trace file
> > > ORA-01110: data file 3:
> > > '/ora/u03/oradata/stock1/rbs01.dbf'
> > >
> > > Examine views:
> > > SELECT segment_name, tablespace_name, status
> FROM
> > > sys.dba_rollback_segs;
> > >
> > > SEGMENT_NAME |TABLESPACE_NAME
>
> > > |STATUS
> > >
> >
>
------------------------------|-----------------------------
> > > SYSTEM |SYSTEM
>
> > > |ONLINE
> > > R05 |RBS01
>
> > > |NEEDS RECOVERY
> > > R01 |RBS01
>
> > > |NEEDS RECOVERY
> > > R02 |RBS01
>
> > > |NEEDS RECOVERY
> > > R03 |RBS01
>
> > > |NEEDS RECOVERY
> > > R04 |RBS01
>
> > > |NEEDS RECOVERY
> > > R06 |RBS01
>
> > > |NEEDS RECOVERY
> > > R07 |RBS01
>
> > > |NEEDS RECOVERY
> > > R08 |RBS01
>
> > > |NEEDS RECOVERY
> > > R09 |RBS01
>
> > > |NEEDS RECOVERY
> > > R10 |RBS01
>
> > > |NEEDS RECOVERY
> > >
> > > select * from v$rollstat;
> > > -> only system-rbs is listed.
> > >
> > >
> > > I've done RTFM, (server concepts, adminitration
> > > guide, backup guide and several
> > > books). I could not find any hint, they say to
> set
> > > the segs offline but I
> > > couldn't do that. Has someone another plan?
> > >
> > > Thank you very much,
> > > oli.
> > >
> > > Oliver Artelt, System- und
> Datenbankadministration
> > >
> >
>
> > > cubeoffice GmbH & Co.KG # jordanstrasse 7 #
> 39112
> > > magdeburg
> > > telefon: +49 (0)391 6 11 28 10 # telefax: +49
> (0)391
> > > 6 11 28 10
> > > email: oli_at_cubeoffice.de # web:
> > > http://www.cubeoffice.de
> > >
> >
>
> > > --
> > > Author: Oliver Artelt
> > > INET: oli_at_cubeoffice.de
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> >
>
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send instant messages with Yahoo! Messenger.
> > http://im.yahoo.com/
> --
> ---
>
> Oliver Artelt, System- und Datenbankadministration
>
> cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112
> magdeburg
> telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391
Received on Tue Jun 20 2000 - 13:41:06 CDT
