I am sorry and complete take the blame if any confusion added to my question. This is certainly unintended and Please forgive me. Here is a time line I am stating to make things clear.
###### database crashed most probably due to a power failure. We bounced back the database and it opened without any error.
###### We check the alert.log and found that its giving message consistently 'SMON: mark undo segment XXX as needs recovery .
###### We run the dba_rollback_segs and found the undo segment XXX listed as needed recovery
###### We created another undo tablespace Say NEW and set that as default. Then shutdown the database and restart it. We checked that the NEW undo tablespace is our intended undo tablespace and then take the OLD undo tablespace offline.
###### Then we invoke rman and try to recover the OLD undo tablespace which ran without any trouble.We then bring the OLD undo Tablespace online but still we could see the same XXX rollback segs need recovery.
###### We found that if we keep the OLD undo Tablespace offline, then we could not query some tables and gave the ORA-00376: file xx cannot be read at this time
Here are the questions:
01. How to find out which tables are depending on the OLD undo tablespace. Once we know them, we may try to replace them from some old exports.
02. How we get rid of this OLD undo tablespace without any harm on the database .
Any help, advise, suggestion will be highly appreciated.
0