From jkstill@cybcon.com Wed, 22 Aug 2001 07:55:30 -0700 From: Jared Still Date: Wed, 22 Aug 2001 07:55:30 -0700 Subject: Re: Recovering WITHOUT tablespaces - 2001-08-16 Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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@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@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@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).