Re: Recovering WITHOUT tablespaces - 2001-08-16

SYSTEM objects don't matter, but I wonder how the SYS objects ended up in the TOOLS tablespace.

Audit tables perhaps?

Jared

On Tuesday 21 August 2001 09:16, Kumanan Balasundaram wrote:
> I was bitten but this badly and thanks to all those people who
> helped me. This may some of you, so I'm posting it.
>
> Scenario:
> I wanted to extract one table as of a time the previous day. So I decided
> to recover to the closest time as possible before the unwanted change
> was made.
> This meant that I only wanted two application tablespaces (data and
> index) out of many.
>
> As I was told its possible, I assumed it would be OK if I went with the
> basics - system TS data files, RBS (3 out of 4 files), the datafiles of the
> application TS's and even member of each redo-logs group .
>
> I recreated the control file, did the recovery to the point in time and
> opened it.
> Now when I did a select on any object, I was hit with the following:
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-00376: file 2 cannot be read at this time
> ORA-01111: name for data file 2 is unknown - rename to correct file
> ORA-01110: data file 2: '/emc/vol1/oracle/product/8.1.6/dbs/MISSING00002'
>
> I tried recovering again with all the RBS files as the last one also had
> online
> public rollback segments.
> That also didn't help and file2 was constantly being a pain - checking
> dba_data_files showed that it was belonging to the TOOLS tablespace.
> On closer examination I discovered it had 237 SYS and SYSTEM
> objects.
>
> Another DBA must have loaded these objects in TOOLS t/s.
> Recovering DB with tools and the other essentials resolved the problem.
>
> So, watch out for this in the future.
>
> Kumanan Balasundaram
> Database Administrator, IT
> QXL ricardo plc
> www.qxl.com
> P: +44 (0)208 962 7409
>
> > QXL ricardo plc Registered Office Landmark House, Hammersmith Bridge
> > Road, London W6 9DP
> > Registered in England No 3430894 VAT number - GB 701 8915 43
> >
> > The information transmitted is intended only for the person or entity to
> > which it is addressed and may contain confidential and/or privileged
> > material. Any review, retransmission, dissemination or other use of, or
> > taking of any action in reliance upon, this information by persons or
> > entities other than the intended recipient is prohibited. If you
> > received this in error, please contact the sender and delete the
> > material from any computer
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> postmaster_at_qxl.com
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper and Nortons Anti-Virus, for the presence of computer viruses.
>
> **********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).