Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with one tablespace 2

Status
Not open for further replies.

torturedmind

Programmer
Jan 31, 2002
1,052
PH
Hi to all Oracle gurus.

In our Oracle 10R2 DB, we unintentionally added a datafile in a tablespace where it's not suppose to be in. So we placed the concerned tablespace to offline then we restore/recover the needed datafiles and left the "rogue" datafile as is because we intend to drop it. Problem is DROPping a datafile in a locally managed tablespace is not possible (I've read it somewhere) unless it's online. We tried putting it in online mode but RMAN failed to do so. Message says "unable to perform media recovery because of missing log" (please see RMAN messages below). And we can't make the tablespace online because one datafile still needs to be recovered. Is there any way we can place the tablespace online? Thanks in advanced.
Code:
Recovery Manager: Release 10.2.0.3.0 - Production on Sat Dec 4 08:41:29 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> 
connected to target database: THERP (DBID=3602721984)
using target database control file instead of recovery catalog

RMAN> 
echo set on


RMAN> run {
2> recover datafile 18;
3> sql 'alter database datafile 18 online';
4> }
Starting recover at 04-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=500 instance=therp2 devtype=DISK

starting media recovery

archive log thread 1 sequence 11838 is already on disk as file /thp3/1_11838_632497279.dbf
archive log thread 1 sequence 11839 is already on disk as file /thp3/1_11839_632497279.dbf
archive log thread 1 sequence 11840 is already on disk as file /thp3/1_11840_632497279.dbf
archive log thread 1 sequence 11841 is already on disk as file /thp3/1_11841_632497279.dbf
archive log thread 1 sequence 11842 is already on disk as file /thp3/1_11842_632497279.dbf
archive log thread 1 sequence 11843 is already on disk as file /thp3/1_11843_632497279.dbf
archive log thread 1 sequence 11844 is already on disk as file /thp3/1_11844_632497279.dbf
archive log thread 1 sequence 11845 is already on disk as file /thp3/1_11845_632497279.dbf
archive log thread 1 sequence 11846 is already on disk as file /thp3/1_11846_632497279.dbf
archive log thread 1 sequence 11847 is already on disk as file /thp3/1_11847_632497279.dbf
archive log thread 1 sequence 11848 is already on disk as file /thp3/1_11848_632497279.dbf
archive log thread 1 sequence 11849 is already on disk as file /thp3/1_11849_632497279.dbf
archive log thread 1 sequence 11850 is already on disk as file /thp3/1_11850_632497279.dbf
archive log thread 1 sequence 11851 is already on disk as file /thp3/1_11851_632497279.dbf
archive log thread 1 sequence 11852 is already on disk as file /thp3/1_11852_632497279.dbf
archive log thread 1 sequence 11853 is already on disk as file /thp3/1_11853_632497279.dbf
archive log thread 1 sequence 11854 is already on disk as file /thp3/1_11854_632497279.dbf
archive log thread 1 sequence 11855 is already on disk as file /thp3/1_11855_632497279.dbf
archive log thread 1 sequence 11856 is already on disk as file /thp3/1_11856_632497279.dbf
archive log thread 1 sequence 11857 is already on disk as file /thp3/1_11857_632497279.dbf
archive log thread 1 sequence 11858 is already on disk as file /thp3/1_11858_632497279.dbf
archive log thread 1 sequence 11859 is already on disk as file /thp3/1_11859_632497279.dbf
archive log thread 1 sequence 11860 is already on disk as file /thp3/1_11860_632497279.dbf
archive log thread 1 sequence 11861 is already on disk as file /thp3/1_11861_632497279.dbf
archive log thread 1 sequence 11862 is already on disk as file /thp3/1_11862_632497279.dbf
archive log thread 1 sequence 11863 is already on disk as file /thp3/1_11863_632497279.dbf
archive log thread 1 sequence 11864 is already on disk as file /thp3/1_11864_632497279.dbf
archive log thread 1 sequence 11865 is already on disk as file /thp3/1_11865_632497279.dbf
archive log thread 1 sequence 11866 is already on disk as file /thp3/1_11866_632497279.dbf
archive log thread 1 sequence 11867 is already on disk as file /thp3/1_11867_632497279.dbf
archive log thread 1 sequence 11868 is already on disk as file /thp3/1_11868_632497279.dbf
archive log thread 1 sequence 11869 is already on disk as file /thp3/1_11869_632497279.dbf
archive log thread 1 sequence 11870 is already on disk as file /thp3/1_11870_632497279.dbf
archive log thread 1 sequence 11871 is already on disk as file /thp3/1_11871_632497279.dbf
archive log thread 1 sequence 11872 is already on disk as file /thp3/1_11872_632497279.dbf
archive log thread 1 sequence 11873 is already on disk as file /thp3/1_11873_632497279.dbf
archive log thread 1 sequence 11874 is already on disk as file /thp3/1_11874_632497279.dbf
archive log thread 1 sequence 11875 is already on disk as file /thp3/1_11875_632497279.dbf
archive log thread 1 sequence 11876 is already on disk as file /thp3/1_11876_632497279.dbf
archive log thread 1 sequence 11877 is already on disk as file /thp3/1_11877_632497279.dbf
archive log thread 1 sequence 11878 is already on disk as file /thp3/1_11878_632497279.dbf
archive log thread 1 sequence 11879 is already on disk as file /thp3/1_11879_632497279.dbf
archive log thread 1 sequence 11880 is already on disk as file /thp3/1_11880_632497279.dbf
archive log thread 1 sequence 11881 is already on disk as file /thp3/1_11881_632497279.dbf
archive log thread 1 sequence 11882 is already on disk as file /thp3/1_11882_632497279.dbf
archive log thread 1 sequence 11883 is already on disk as file /thp3/1_11883_632497279.dbf
archive log thread 1 sequence 11884 is already on disk as file /thp3/1_11884_632497279.dbf
archive log thread 1 sequence 11885 is already on disk as file /thp3/1_11885_632497279.dbf
archive log thread 1 sequence 11886 is already on disk as file /thp3/1_11886_632497279.dbf
archive log thread 1 sequence 11887 is already on disk as file /thp3/1_11887_632497279.dbf
archive log thread 1 sequence 11888 is already on disk as file /thp3/1_11888_632497279.dbf
archive log thread 1 sequence 11889 is already on disk as file /thp3/1_11889_632497279.dbf
archive log thread 1 sequence 11890 is already on disk as file /thp3/1_11890_632497279.dbf
archive log thread 1 sequence 11891 is already on disk as file /thp3/1_11891_632497279.dbf
archive log thread 1 sequence 11892 is already on disk as file /thp3/1_11892_632497279.dbf
archive log thread 1 sequence 11893 is already on disk as file /thp3/1_11893_632497279.dbf
archive log thread 1 sequence 11894 is already on disk as file /thp3/1_11894_632497279.dbf
archive log thread 2 sequence 10694 is already on disk as file /thp3/2_10694_632497279.dbf
archive log thread 2 sequence 10695 is already on disk as file /thp3/2_10695_632497279.dbf
archive log thread 2 sequence 10696 is already on disk as file /thp3/2_10696_632497279.dbf
archive log thread 2 sequence 10697 is already on disk as file /thp3/2_10697_632497279.dbf
archive log thread 2 sequence 10698 is already on disk as file /thp3/2_10698_632497279.dbf
archive log thread 2 sequence 10699 is already on disk as file /thp3/2_10699_632497279.dbf
archive log thread 2 sequence 10700 is already on disk as file /thp3/2_10700_632497279.dbf
archive log thread 2 sequence 10701 is already on disk as file /thp3/2_10701_632497279.dbf
archive log thread 2 sequence 10702 is already on disk as file /thp3/2_10702_632497279.dbf
archive log thread 2 sequence 10703 is already on disk as file /thp3/2_10703_632497279.dbf
archive log thread 2 sequence 10704 is already on disk as file /thp3/2_10704_632497279.dbf
archive log thread 2 sequence 10705 is already on disk as file /thp3/2_10705_632497279.dbf
archive log thread 2 sequence 10706 is already on disk as file /thp3/2_10706_632497279.dbf
archive log thread 2 sequence 10707 is already on disk as file /thp3/2_10707_632497279.dbf
archive log thread 2 sequence 10708 is already on disk as file /thp3/2_10708_632497279.dbf
archive log thread 2 sequence 10709 is already on disk as file /thp3/2_10709_632497279.dbf
archive log thread 2 sequence 10710 is already on disk as file /thp3/2_10710_632497279.dbf
archive log thread 2 sequence 10711 is already on disk as file /thp3/2_10711_632497279.dbf
archive log thread 2 sequence 10712 is already on disk as file /thp3/2_10712_632497279.dbf
archive log thread 2 sequence 10713 is already on disk as file /thp3/2_10713_632497279.dbf
archive log thread 2 sequence 10714 is already on disk as file /thp3/2_10714_632497279.dbf
archive log thread 2 sequence 10715 is already on disk as file /thp3/2_10715_632497279.dbf
archive log thread 2 sequence 10716 is already on disk as file /thp3/2_10716_632497279.dbf
archive log thread 2 sequence 10717 is already on disk as file /thp3/2_10717_632497279.dbf
archive log thread 2 sequence 10718 is already on disk as file /thp3/2_10718_632497279.dbf
archive log thread 2 sequence 10719 is already on disk as file /thp3/2_10719_632497279.dbf
archive log thread 2 sequence 10720 is already on disk as file /thp3/2_10720_632497279.dbf
archive log thread 2 sequence 10721 is already on disk as file /thp3/2_10721_632497279.dbf
archive log thread 2 sequence 10722 is already on disk as file /thp3/2_10722_632497279.dbf
archive log thread 2 sequence 10723 is already on disk as file /thp3/2_10723_632497279.dbf
archive log thread 2 sequence 10724 is already on disk as file /thp3/2_10724_632497279.dbf
archive log thread 2 sequence 10725 is already on disk as file /thp3/2_10725_632497279.dbf
archive log thread 2 sequence 10726 is already on disk as file /thp3/2_10726_632497279.dbf
archive log thread 2 sequence 10727 is already on disk as file /thp3/2_10727_632497279.dbf
archive log thread 2 sequence 10728 is already on disk as file /thp3/2_10728_632497279.dbf
archive log thread 2 sequence 10729 is already on disk as file /thp3/2_10729_632497279.dbf
archive log thread 2 sequence 10730 is already on disk as file /thp3/2_10730_632497279.dbf
archive log thread 2 sequence 10731 is already on disk as file /thp3/2_10731_632497279.dbf
archive log thread 2 sequence 10732 is already on disk as file /thp3/2_10732_632497279.dbf
archive log thread 2 sequence 10733 is already on disk as file /thp3/2_10733_632497279.dbf
archive log thread 2 sequence 10734 is already on disk as file /thp3/2_10734_632497279.dbf
archive log thread 2 sequence 10735 is already on disk as file /thp3/2_10735_632497279.dbf
archive log thread 2 sequence 10736 is already on disk as file /thp3/2_10736_632497279.dbf
archive log thread 2 sequence 10737 is already on disk as file /thp3/2_10737_632497279.dbf
archive log thread 2 sequence 10738 is already on disk as file /thp3/2_10738_632497279.dbf
archive log thread 2 sequence 10739 is already on disk as file /thp3/2_10739_632497279.dbf
archive log thread 2 sequence 10740 is already on disk as file /thp3/2_10740_632497279.dbf
archive log thread 2 sequence 10741 is already on disk as file /thp3/2_10741_632497279.dbf
archive log thread 2 sequence 10742 is already on disk as file /thp3/2_10742_632497279.dbf
archive log thread 2 sequence 10743 is already on disk as file /thp3/2_10743_632497279.dbf
archive log thread 2 sequence 10744 is already on disk as file /thp3/2_10744_632497279.dbf
archive log thread 2 sequence 10745 is already on disk as file /thp3/2_10745_632497279.dbf
archive log thread 2 sequence 10746 is already on disk as file /thp3/2_10746_632497279.dbf
archive log thread 2 sequence 10747 is already on disk as file /thp3/2_10747_632497279.dbf
archive log thread 2 sequence 10748 is already on disk as file /thp3/2_10748_632497279.dbf
archive log thread 2 sequence 10749 is already on disk as file /thp3/2_10749_632497279.dbf
archive log thread 2 sequence 10750 is already on disk as file /thp3/2_10750_632497279.dbf
archive log thread 2 sequence 10751 is already on disk as file /thp3/2_10751_632497279.dbf
archive log thread 2 sequence 10752 is already on disk as file /thp3/2_10752_632497279.dbf
archive log thread 2 sequence 10753 is already on disk as file /thp3/2_10753_632497279.dbf
archive log thread 2 sequence 10754 is already on disk as file /thp3/2_10754_632497279.dbf
archive log thread 2 sequence 10755 is already on disk as file /thp3/2_10755_632497279.dbf
archive log thread 2 sequence 10756 is already on disk as file /thp3/2_10756_632497279.dbf
archive log thread 2 sequence 10757 is already on disk as file /thp3/2_10757_632497279.dbf
archive log thread 2 sequence 10758 is already on disk as file /thp3/2_10758_632497279.dbf
archive log thread 2 sequence 10759 is already on disk as file /thp3/2_10759_632497279.dbf
archive log thread 2 sequence 10760 is already on disk as file /thp3/2_10760_632497279.dbf
archive log thread 2 sequence 10761 is already on disk as file /thp3/2_10761_632497279.dbf
archive log thread 2 sequence 10762 is already on disk as file /thp3/2_10762_632497279.dbf
archive log thread 2 sequence 10763 is already on disk as file /thp3/2_10763_632497279.dbf
archive log thread 2 sequence 10764 is already on disk as file /thp3/2_10764_632497279.dbf
archive log thread 2 sequence 10765 is already on disk as file /thp3/2_10765_632497279.dbf
archive log thread 2 sequence 10766 is already on disk as file /thp3/2_10766_632497279.dbf
archive log thread 2 sequence 10767 is already on disk as file /thp3/2_10767_632497279.dbf
archive log thread 2 sequence 10768 is already on disk as file /thp3/2_10768_632497279.dbf
archive log thread 2 sequence 10769 is already on disk as file /thp3/2_10769_632497279.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
[COLOR=red][b]RMAN-03002: failure of recover command at 12/04/2010 08:41:34
RMAN-06053: unable to perform media recovery because of missing log[/b][/color]
RMAN-06025: no backup of log thread 2 seq 10076 lowscn 293695795 found to restore
RMAN-06025: no backup of log thread 2 seq 10075 lowscn 293655736 found to restore
RMAN-06025: no backup of log thread 2 seq 10074 lowscn 293616001 found to restore
RMAN-06025: no backup of log thread 2 seq 10073 lowscn 293576326 found to restore
RMAN-06025: no backup of log thread 2 seq 10072 lowscn 293536806 found to restore
RMAN-06025: no backup of log thread 2 seq 10071 lowscn 293497689 found to restore
RMAN-06025: no backup of log thread 2 seq 10070 lowscn 293463871 found to restore
RMAN-06025: no backup of log thread 2 seq 10069 lowscn 293461208 found to restore
RMAN-06025: no backup of log thread 2 seq 10068 lowscn 293457563 found to restore
RMAN-06025: no backup of log thread 2 seq 10067 lowscn 293454143 found to restore
RMAN-06025: no backup of log thread 2 seq 10066 lowscn 293450627 found to restore
RMAN-06025: no backup of log thread 2 seq 10065 lowscn 293447221 found to restore
RMAN-06025: no backup of log thread 2 seq 10064 lowscn 293443758 found to restore
RMAN-06025: no backup of log thread 2 seq 10063 lowscn 293440572 found to restore
RMAN-06025: no backup of log thread 2 seq 10062 lowscn 293416606 found to restore
RMAN-06025: no backup of log thread 2 seq 10061 lowscn 293375341 found to restore
RMAN-06025: no backup of log thread 2 seq 10060 lowscn 293335636 found to restore
RMAN-06025: no backup of log thread 2 seq 10059 lowscn 293295434 found to restore
RMAN-06025: no backup of log thread 2 seq 10058 lowscn 293254048 found to restore
RMAN-06025: no backup of log thread 2 seq 10057 lowscn 293213324 found to restore
RMAN-06025: no backup of log thread 2 seq 10056 lowscn 293171832 found to restore
RMAN-06025: no backup of log thread 2 seq 10055 lowscn 293171811 found to restore
RMAN-06025: no backup of log thread 2 seq 10054 lowscn 293171779 found to restore
RMAN-06025: no backup of log thread 2 seq 10053 lowscn 293170472 found to restore
RMAN-06025: no backup of log thread 2 seq 10052 lowscn 293146716 found to restore
RMAN-06025: no backup of log thread 2 seq 10051 lowscn 293106160 found to restore
RMAN-06025: no backup of log thread 2 seq 10050 lowscn 293063732 found to restore
RMAN-06025: no backup of log thread 2 seq 10049 lowscn 293023711 found to restore
RMAN-06025: no backup of log thread 2 seq 10048 lowscn 292982725 found to restore
RMAN-06025: no backup of log thread 2 seq 10047 lowscn 292942210 found to restore
RMAN-06025: no backup of log thread 2 seq 10046 lowscn 292900802 found to restore
RMAN-06025: no backup of log thread 2 seq 10045 lowscn 292860728 found to restore
RMAN-06025: no backup of log thread 2 seq 10044 lowscn 292860698 found to restore
RMAN-06025: no backup of log thread 2 seq 10043 lowscn 292860668 found to restore
RMAN-06025: no backup of log thread 2 seq 10042 lowscn 292860589 found to restore
RMAN-06025: no backup of log thread 2 seq 10041 lowscn 292847803 found to restore
RMAN-06025: no backup of log thread 2 seq 10040 lowscn 292806606 found to restore
RMAN-06025: no backup of log thread 2 seq 10039 lowscn 292765114 found to restore
RMAN-06025: no backup of log thread 2 seq 10038 lowscn 292723484 found to restore
RMAN-06025: no backup of log thread 2 seq 10037 lowscn 292682675 found to restore
RMAN-06025: no backup of log thread 2 seq 10036 lowscn 292642579 found to restore
RMAN-06025: no backup of log thread 2 seq 10035 lowscn 292601511 found to restore
RMAN-06025: no backup of log thread 2 seq 10034 lowscn 292559475 found to restore
RMAN-06025: no backup of log thread 2 seq 10033 lowscn 292519123 found to restore
RMAN-06025: no backup of log thread 2 seq 10032 lowscn 292478156 found to restore
RMAN-06025: no backup of log thread 2 seq 10031 lowscn 292438352 found to restore
RMAN-06025: no backup of log thread 2 seq 10030 lowscn 292397741 found to restore
RMAN-06025: no backup of log thread 2 seq 10029 lowscn 292357650 found to restore
RMAN-06025: no backup of log thread 2 seq 10028 lowscn 292317647 found to restore
RMAN-06025: no backup of log thread 2 seq 10027 lowscn 292276199 found to restore
RMAN-06025: no backup of log thread 2 seq 10026 lowscn 292235928 found to restore
RMAN-06025: no backup of log thread 2 seq 10025 lowscn 292195374 found to restore
RMAN-06025: no backup of log thread 2 seq 10024 lowscn 292155816 found to restore
RMAN-06025: no backup of log thread 2 seq 10023 lowscn 292116968 found to restore
RMAN-06025: no backup of log thread 2 seq 10022 lowscn 292077062 found to restore
RMAN-06025: no backup of log thread 2 seq 10021 lowscn 292036336 found to restore
?????????????????????????????????????????????????????????????

RMAN> exit;

Recovery Manager complete.


kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
Hi,
According to this:

A tablespace can be dropped online OR offline.
It even states that Oracle recommends taking it offline before dropping.

You can drop a tablespace regardless of whether it is online or offline. Oracle recommends that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks Turkbear for the prompt reply. Perhaps I wasn't clear of the issue. We want only the rogue datafile be dropped and not the entire tablespace. My aplogies if I wasn't able to express myself clearly.

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
Hi,
actually you expressed it well:
torturedmind said:
DROPping a datafile in a locally managed tablespace is not possible (I've read it somewhere) unless it's online.

It was my hasty reading that failed[blush]

I'll see if I can find anything ( or SantaMufasa may appear to show us both)






[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Sorry to disappoint...I don't know. I'll defer to Dagon, KarlUK, Thargy, et. al.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
After searching for answers here and there, one blog suggested to query first the v$archived_log like so:
Code:
SQL> select sequence#, deleted, status from v$archived_log where sequence# between 10020 and 10076;

 SEQUENCE# DEL S
---------- --- -
     10020 YES D
     10021 YES D
     10022 YES D
     10023 YES D
     10024 YES D
     10025 YES D
     10026 YES D
     10027 YES D
     10028 YES D
     10029 YES D
     10030 YES D

 SEQUENCE# DEL S
---------- --- -
     10031 YES D
     10032 YES D
     10033 YES D
     10034 YES D
     10035 YES D
     10036 YES D
     10037 YES D
     10038 YES D
     10039 YES D
     10040 YES D
     10041 YES D

 SEQUENCE# DEL S
---------- --- -
     10042 YES D
     10043 YES D
     10044 YES D
     10045 YES D
     10046 YES D
     10047 YES D
     10048 YES D
     10049 YES D
     10050 YES D
     10051 YES D
     10052 YES D

 SEQUENCE# DEL S
---------- --- -
     10053 YES D
     10054 YES D
     10055 YES D
     10056 YES D
     10057 YES D
     10058 YES D
     10059 YES D
     10060 YES D
     10061 YES D
     10062 YES D
     10063 YES D

 SEQUENCE# DEL S
---------- --- -
     10064 YES D
     10065 YES D
     10066 YES D
     10067 YES D
     10068 YES D
     10069 YES D
     10070 YES D
     10071 YES D
     10072 YES D
     10073 YES D
     10074 YES D

 SEQUENCE# DEL S
---------- --- -
     10075 YES D
     10076 YES D

57 rows selected.
The above SQL statement clearly shows that the sequence numbers from 10020 to 10076 were already deleted. In the blog, the blogger suggested to validate the sequence above 10076 (or 10077 onwards) in RMAN. And so I did.
Code:
RMAN> run {
2> restore validate check logical archivelog from sequence 10077;
3>
4> }

Starting restore at 04-DEC-10
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/04/2010 16:11:47
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 10768 lowscn 294814152 found to restore
RMAN-06025: no backup of log thread 1 seq 10766 lowscn 294616051 found to restore
RMAN-06025: no backup of log thread 1 seq 10764 lowscn 294415676 found to restore
RMAN-06025: no backup of log thread 1 seq 10763 lowscn 294295216 found to restore
RMAN-06025: no backup of log thread 1 seq 10761 lowscn 294095497 found to restore
RMAN-06025: no backup of log thread 1 seq 10760 lowscn 293976698 found to restore
RMAN-06025: no backup of log thread 1 seq 10757 lowscn 293617320 found to restore
RMAN-06025: no backup of log thread 1 seq 10750 lowscn 293171802 found to restore
RMAN-06025: no backup of log thread 1 seq 10749 lowscn 293171781 found to restore
RMAN-06025: no backup of log thread 1 seq 10748 lowscn 293170469 found to restore
RMAN-06025: no backup of log thread 1 seq 10747 lowscn 293106396 found to restore
RMAN-06025: no backup of log thread 1 seq 10746 lowscn 292983117 found to restore
RMAN-06025: no backup of log thread 1 seq 10745 lowscn 292860731 found to restore
RMAN-06025: no backup of log thread 1 seq 10744 lowscn 292860696 found to restore
RMAN-06025: no backup of log thread 1 seq 10743 lowscn 292860671 found to restore
RMAN-06025: no backup of log thread 1 seq 10742 lowscn 292860592 found to restore
RMAN-06025: no backup of log thread 1 seq 10741 lowscn 292808389 found to restore
RMAN-06025: no backup of log thread 1 seq 10740 lowscn 292684257 found to restore
RMAN-06025: no backup of log thread 1 seq 10739 lowscn 292601103 found to restore
RMAN-06025: no backup of log thread 1 seq 10738 lowscn 292478695 found to restore
RMAN-06025: no backup of log thread 1 seq 10737 lowscn 292393628 found to restore
RMAN-06025: no backup of log thread 1 seq 10736 lowscn 292276212 found to restore
RMAN-06025: no backup of log thread 1 seq 10735 lowscn 292155998 found to restore
RMAN-06025: no backup of log thread 1 seq 10734 lowscn 292052851 found to restore
RMAN-06025: no backup of log thread 1 seq 10733 lowscn 291958063 found to restore
RMAN-06025: no backup of log thread 1 seq 10732 lowscn 291920789 found to restore
RMAN-06025: no backup of log thread 1 seq 10731 lowscn 291839671 found to restore
RMAN-06025: no backup of log thread 1 seq 10730 lowscn 291724595 found to restore
RMAN-06025: no backup of log thread 1 seq 10729 lowscn 291607732 found to restore
RMAN-06025: no backup of log thread 1 seq 10728 lowscn 291488809 found to restore
RMAN-06025: no backup of log thread 1 seq 10727 lowscn 291367772 found to restore
RMAN-06025: no backup of log thread 1 seq 10726 lowscn 291323413 found to restore
RMAN-06025: no backup of log thread 1 seq 10725 lowscn 291313670 found to restore
RMAN-06025: no backup of log thread 1 seq 10724 lowscn 291303393 found to restore
RMAN-06025: no backup of log thread 1 seq 10723 lowscn 291202515 found to restore
RMAN-06025: no backup of log thread 1 seq 10722 lowscn 291136461 found to restore
RMAN-06025: no backup of log thread 1 seq 10721 lowscn 291040210 found to restore
RMAN-06025: no backup of log thread 1 seq 10720 lowscn 290961863 found to restore
RMAN-06025: no backup of log thread 1 seq 10719 lowscn 290960390 found to restore
RMAN-06025: no backup of log thread 1 seq 10718 lowscn 290960370 found to restore
RMAN-06025: no backup of log thread 1 seq 10717 lowscn 290960357 found to restore
RMAN-06025: no backup of log thread 1 seq 10716 lowscn 290882044 found to restore
RMAN-06025: no backup of log thread 1 seq 10715 lowscn 290759953 found to restore
RMAN-06025: no backup of log thread 1 seq 10714 lowscn 290642496 found to restore
RMAN-06025: no backup of log thread 1 seq 10713 lowscn 290525374 found to restore
RMAN-06025: no backup of log thread 1 seq 10712 lowscn 290436501 found to restore
RMAN-06025: no backup of log thread 1 seq 10711 lowscn 290327501 found to restore
RMAN-06025: no backup of log thread 1 seq 10710 lowscn 290208786 found to restore
RMAN-06025: no backup of log thread 1 seq 10709 lowscn 290089139 found to restore
RMAN-06025: no backup of log thread 1 seq 10708 lowscn 289973604 found to restore
RMAN-06025: no backup of log thread 1 seq 10707 lowscn 289852489 found to restore
RMAN-06025: no backup of log thread 1 seq 10706 lowscn 289729556 found to restore
RMAN-06025: no backup of log thread 1 seq 10705 lowscn 289609081 found to restore
RMAN-06025: no backup of log thread 1 seq 10704 lowscn 289492040 found to restore
RMAN-06025: no backup of log thread 1 seq 10703 lowscn 289371721 found to restore
RMAN-06025: no backup of log thread 1 seq 10702 lowscn 289250836 found to restore
MAN-06025: no backup of log thread 1 seq 10701 lowscn 289131321 found to restore
RMAN>
Again, the next group of sequence numbers is most likely deleted. Oracle says I can just ignore them. How can I if it won't help us put the tablespace in online mode? Well, it did not bring me anywhere near our goal. Any other suggestions?

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
Are you using windoze? There is a known bug in Oracle to do with dropping datafiles. The work around is to do an OS drop of the file.

If the name of the game is to drop the file, then if you have sufficient mass storage, how about making a new temporary tablespace, and moving everything in the polluted tablespace to it?

drop tablespace including contents and datafiles for the dud, then recreate it (obviously only using desired files). Move everything from the temp to the new "good" tablespace, and drop the temp.



Regards

T
 
Might it be possible to alter the tablespace to read only so you could bring it online and drop the datafile? I haven't done this in a long time (several years, in fact) but it seems to me like the only way to get the database to ignore the out-of-synch file.
 
Are you using windoze?
sorry. not familiar with it.
If the name of the game is to drop the file, then if you have sufficient mass storage, how about making a new temporary tablespace, and moving everything in the polluted tablespace to it?

drop tablespace including contents and datafiles for the dud, then recreate it (obviously only using desired files). Move everything from the temp to the new "good" tablespace, and drop the temp.
we tried it following instructions from this link. unfortunately, it didn't work either. it just gave us the same rman messages as above.
Might it be possible to alter the tablespace to read only so you could bring it online and drop the datafile?
we tried it also but oracle said we need to recover some datafiles. when we recover, we were again given the messages like the ones above.

we are getting hopeless. am feeling christmas won't be happy this year [sadeyes]

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
The only other thing I can think of (and again, it has literally been years since I've had to do this) is to backup the control file to trace, go into the resultant script with a text editor, and eliminate any references to the rogue file. Then run the script to create the new control file and try to open your database. I cannot overemphasize the need for caution here, so make sure you try this on a backup copy of your database first. As many changes as Oracle makes to the way control files work, etc, it is hard to say if this will work or not. I would also solicit inputs from Thargy, SantaM, et al before trying this.
 
I hesitate to ask this, but do you have a known good cold backup? I think perhaps you misunderstood the Thargy 'Windoze' reference - it's a coloquialism for MS Windows. Which operating system are you working with?

The internet - allowing those who don't know what they're talking about to have their say.
 
I hesitate to ask this, but do you have a known good cold backup?
yes. actually we're currently trying to get the data from there.
I think perhaps you misunderstood the Thargy 'Windoze' reference - it's a coloquialism for MS Windows. Which operating system are you working with?
silly me. [hammer] anyways, here is our setup:
Code:
Oracle 10gR2 RAC on Red Hat 9 Ent. Ed.
Veritas NBU 6MP7 on WinServer2003 (SP?)


kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
ok, since you are using Red Hat, the "windoze" bug is obviously not the problem.

May I therefore suggest the following:-

Create a new holding tablespace and using "CREATE TABLE AS SELECT" (CTAS) recreate all the data tables in the new tablespace.

Use a productivity tool such as SQL Developer, TOAD or Embarcadero to generate a script for all the constraints, materialized views etc. in the original.

Drop the dud tablespace, including contents and datafiles. Create the tablespace again, but this time with only the desired data files. Move everything back from the holding tablespace (using CTAS once again) and then reinstate all the constraints, views, mviews etc.

Drop the holding tablespace.



Regards

T
 
If you are current on your maintenance contract with Oracle support, I strongly advise you to open up a Service Request with them. You are in real danger of losing all of the data in your tablespace and Oracle support gives you your best chance to avoid this.

It does seem to me, however, that your situation is the same as the one described in article 418476.1 at support.oracle.com - you have a database that can't be opened because a datafile needs recovery, but are missing some of the archive logs that are needed for recovery. There are three options given in article 418476.1. In brief, they are

1. Restore the database from backup and recover until the first missing archive log. Changes made after this point will be lost. This seems to be roughly what you are doing right now in investigating recovering from a cold backup.
2. Force the database open by using the startup parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE. There is no guarantee that this will work, but if it does it will allow you to do a full database export, after which you will need to import the data into a completely new database. Continuing to operate a database after opening it with _ALLOW_RESETLOGS_CORRUPTION=TRUE is not supported. You should also set event 10231 before the export so that Oracle will skip any corrupt/missing data blocks.
3. Pay Oracle an extra fee to retrieve your data using Oracle's Data Unloader (DUL). They have to send a team to your site to do this, so I imagine it could get quite expensive.
 
Thanks to all. Am a newbie DBA and just starting the feel of being one. I know being such is not an easy task and, for me, providing us with more options to use clearly deserves a star for each of you even tho we haven't arrived yet at where we need to be.

For everyone's update, our cold backup traces back Oct. 18 of the current year. But we'll meet with our superiors first if the data are acceptable before proceeding with the retrieval from that date. IMO, this is better than nothing.

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
Indeed it is, sometimes you just have to take the hit. Is the data vary volatile or can you live with the missing month or so? Good luck - as a newbie you've had something of a baptism of fire!

The internet - allowing those who don't know what they're talking about to have their say.
 
I would not accept the loss of a month and a half of data without a fight. I personally have successfully used the _ALLOW_RESETLOGS_CORRUPTION=TRUE option to fully recover a database with missing archive logs. I would say that, at the very least, it's worth a try.

Good luck with whatever you decide!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top