Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Recovering WITHOUT tablespaces - 2001-08-16

Re: Recovering WITHOUT tablespaces - 2001-08-16

From: JOE TESTA <>
Date: Wed, 22 Aug 2001 09:05:24 -0700
Message-ID: <>

how about this as a solution:
alter user sys default tablespace tools;  
@?/rdbms/admin/Put SCRIPT HERE :)
>>> 08/22/01 12:02PM >>>SYSTEM objects don't matter, but I wonder how the SYSobjects ended up in the TOOLS tablespace.Audit tables perhaps?JaredOn 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>> 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>>> 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: <A href=""> Author: Jared Still  INET: jkstill_at_cybcon.comFat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051San Diego, California        -- Public Internet access / Mailing
REMOVE yourself from this mailing list, send an E-Mail messageto: (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing). Received on Wed Aug 22 2001 - 11:05:24 CDT

Original text of this message