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!

 

[sem]

Alexhu, just read error message: deleted file undotbs02.dbf belongs to undotbs2 tablespace, thus the whole tablespace is unusable. I don't beleive that information in the first message (about files and tablespaces) is still up to date.

MCubitt, can you provide the text of the query that produced that result? And execute it again.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

sem,

Sorry - which query for which result?



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

 

[alexhu]

sem

ye, I see now - its that really smart naming convention the suppliers have in place

Alex

 

[sem]

The one mentioned as And Oracle sees: in the first (starting) message.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

sem,

the results are now:
TABLESPACE_NAME FILE_ID FILE_NAME
UNDOTBS1 2 /oracle/app/oracle/oradata/TLE/undotbs01.dbf
12 /oracle/app/oracle/oradata/TLE/undotbs02.dbf
UNDOTBS3 15 /oracle/app/oracle/oradata/TLE/undotbs31.dbf

The SQL is:
SELECT 'online redo log group :' tablespace_name, b.group#
file_id, b.member file_name, a.bytes
FROM v$log a, v$logfile b
WHERE a.group# = b.group#
UNION
SELECT 'controlfile number : ' tablespace_name, rownum file_id,
name file_name, 4071424
FROM v$controlfile
UNION
SELECT tablespace_name, file_id, file_name, bytes
FROM dba_data_files
ORDER BY tablespace_name



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

 

[sem]

It seems I've missinterpreted Oracle message. Probably Oracle tried (and failed!) to move undo data from corrupted undotbs1 to undotbs2. For some reason (commit?), there were no active transactions (and nothing to move!) when it was switched to undotbs3. Only assumption...

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Remaining issue is removing the UNDOTBS1.

I continue to get:

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace




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

 

[stefanhei]

MCubitt did you check if the filesystem your datafile is located on is ok?
AIX-command for that:

fsck <Filesystem>

Stefan

 

[MCubitt]

I had not, I will, I am, I have!

Here's the results... (cannot unmount it since it houses other DBs)

bash-2.05a# fsck /oracle



** Checking /dev/oracle_lv (/oracl) MOUNTED FILE SYSTEM; WRITING SUPPRESSED;
Checking a mounted filesystem does not produce dependable results.
** Phase 1 - Check Blocks and Sizes
** Phase 2 - Check Pathnames
** Phase 3 - Check Connectivity
** Phase 4 - Check Reference Counts
** Phase 5 - Check Inode Map
Bad Inode Map (NOT SALVAGED)
** Phase 6 - Check Block Map
Bad Block Map (NOT SALVAGED)
0 blocks missing
0 blocks missing
Filesystem integrity is not guaranteed
85809 files 14728520 blocks 2048688 free





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