Re: Recovering WITHOUT tablespaces

From: Kumanan Balasundaram <kumanan.balasundaram_at_qxl.com>
Date: Tue, 21 Aug 2001 08:11:23 -0700
Message-ID: <F001.00372611.20010821081636@fatcity.com>

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: Kumanan Balasundaram
  INET: kumanan.balasundaram_at_qxl.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).

Received on Tue Aug 21 2001 - 10:11:23 CDT

Re: Recovering WITHOUT tablespaces - 2001-08-16