Undo table problem. Physical file missing!

[MCubitt]

We have a DB which has (somehow) lost it's undo table. There are usually 2, one was missing.

I created a new tablespace UNDOTBS2 (as opposed to UNDOTBS1) but cannot drop the UNDOTBS1.

Here's the physical sitaution on our AIX box:
oracle oinstall 5251072 02 Jun 14:28 undotbs02.dbf
oracle oinstall 5251072 02 Jun 13:57 UNDOTBS21.dbf
oracle oinstall 5251072 02 Jun 13:57 UNDOTBS22.dbf



And Oracle sees:
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------
UNDOTBS1 2 /oracle/app/oracle/oradata/TLE/undotbs01.dbf
12 /oracle/app/oracle/oradata/TLE/undotbs02.dbf
UNDOTBS2 13 /oracle/app/oracle/oradata/TLE/UNDOTBS21.dbf
14 /oracle/app/oracle/oradata/TLE/UNDOTBS22.dbf

I changed the init.ora file to use UNDOTBS2 and restarted the DB.


If i try to drop the originals, I get:
alter database datafile ' /oracle/app/oracle/oradata/TLE/undotbs02.dbf' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile " /oracle/app/oracle/oradata/TLE/undotbs02.dbf"



Any ideas how to resolve this, please?



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

Appears you are not trying to drop the tablespace, but the datafile.

Try

alter tabespace undotbs1 offline;

drop tablespace undotbs1;

Alex

 

[MCubitt]

Yes, I am unable to offline the tablespace too:
Connected.
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

OK - just done this on a test system - the database has to be in mount state,but not open so shut it down and do 'startup mount'

then

alter database datafile ' /oracle/app/oracle/oradata/TLE/undotbs02.dbf' offline drop

then

drop tablespace undotbs01

then alter database open

Alex

 

[MCubitt]

Unable to drop datafile:
SQL> alter database datafile ' /oracle/app/oracle/oradata/TLE/undotbs02.dbf' offline drop
2 /
alter database datafile ' /oracle/app/oracle/oradata/TLE/undotbs02.dbf' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile "
/oracle/app/oracle/oradata/TLE/undotbs02.dbf"

But the file does exist, physically:
pwd
/oracle/app/oracle/oradata/TLE
ls -la undo*
-rw-r----- 1 oracle oinstall 5251072 02 Jun 14:28 undotbs02.dbf




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Sorry, there was a space (teaches me to copy/paste!)

However, unable to drop tablespace, needs to be open?
SQL> drop tablespace undotbs01
2 /
drop tablespace undotbs01
*
ERROR at line 1:
ORA-01109: database not open

I have opened it and tried again...
SQL> drop tablespace undotbs01;
drop tablespace undotbs01
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS01' does not exist

That'll be naming error, tried again:
1* drop tablespace undotbs1
SQL> /
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

Can I just clarify - as I seem to have lost the plot

You have tablespace UNDOTBS1 which did contain 2 datafiles, but someone deleted one

You are now trying to remove th remaining file and the tablespace ?

Alex

 

[alexhu]

Is the database in 'mount' state ?

Alex

 

[sem]

Have you issued alter tabespace undotbs1 offline; after restarting database?

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Sem,

yes:
1* alter tablespace undotbs1 offline
SQL> /
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Just did:
1* alter database datafile '/oracle/app/oracle/oradata/TLE/undotbs01.dbf' offline drop
SQL> /

Database altered.

then:
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

You now need to do 'drop tablespace undotbs1;' with the database open.

Alex

 

[MCubitt]

I have shutdown the DB and started as open. Restarted my SQLPlus session and:

1* alter database datafile '/oracle/app/oracle/oradata/TLE/undotbs01.dbf' offline drop
SQL> /

Database altered.

1* alter database datafile '/oracle/app/oracle/oradata/TLE/undotbs02.dbf' offline drop
SQL> /

Database altered.

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

Is it online ?

Alex

 

[MCubitt]

SQL> drop tablespace undotbs1
2
SQL> /
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


Do I need to change init.ora to point to undotbs2 or should this do that anyway?



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

According to Enterprise Manager, the status of the datafiles are "offline - needs recovery".


The tablespace is online.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

Whats in the init.ora then - never having had a DB with 2 undo tablespaces

Alex

 

[MCubitt]

Oh okay!

Actually, it is pointing to UNDOTBS2:

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDOTBS2






There's no need for sarcastic replies, we've not all been this sad for that long!

 

[alexhu]

UNDO_TABLESPACE in init.ora - need to point to the one you want active only

Alex

 

[alexhu]

As you're using init.ora - a restart will be required

Alex