Re: deleted rollback-seg. tablespace

What Anita said, in spades! When I took the backup/recovery internals class, the instructor said he would tell us when the things he was telling us would leave the database in an unsupported state..... forcing it open like this was the first one he mentioned. DO NOT force it open and then continue to use it.

Rachel

>From: "A. Bardeen" <abardeen1_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: deleted rollback-seg. tablespace
>Date: Tue, 20 Jun 2000 11:49:09 -0800
>
>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
> > 6 11 28 10
> > email: oli_at_cubeoffice.de # web:
> > http://www.cubeoffice.de
> >
>---------------------------------------------------------------
>
>
>__________________________________________________
>Do You Yahoo!?
>Send instant messages with Yahoo! Messenger.
>http://im.yahoo.com/
>--
>Author: A. Bardeen
> INET: abardeen1_at_yahoo.com
>
>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
Received on Tue Jun 20 2000 - 18:02:58 CDT