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!

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

Undo table problem. Physical file missing!

Status
Not open for further replies.
MCubitt

MCubitt

Programmer
Mar 14, 2002
1,081
GB
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!
 
Sort by date Sort by votes
Well I want to use the second one so it is correct. I have restarted the DB with this value.

Perhaps I need to set i tto UNDOTBS1 and THEN drop it?



There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1


Does this mean that its upon a reboot that init.ora is read (surely not!).
One would expect it to be upon DB startup.





There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
When you start the database, please explicitly state the init.ora file :

startup pfile='/.././init.ora'

This will ensure that the init.ora file you are looking at is the one the database is looking at.
 
Upvote 0 Downvote
How does Oracle get itself in such a mess.. okay, with a little help from humans ;)


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

I am shutting down, preparing for the start up.

SQL> startup pfile='/oracle/app/oracle/admin/TLE/pfile/initTLE.ora'
LRM-00109: could not open parameter file '/oracle/app/oracle/admin/TLE/pfile/initTLE.ora'
ORA-01078: failure in processing system parameters

Great!


There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Are you sure your database uses pfile, not spfile? Is it possible that you edited init.ora file of another instance or not related at all?

Regards, Dima
 
Upvote 0 Downvote
I have no idea, sorry! How can I check.



There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
I found this:
/oracle/app/oracle/product/9.2.0.1.0/dbs/spfileTLE.ora


^E^B^B^Cõ^_#oL*.aq_tm_processes=1
*.background_dump_dest='/oracle/app/oracle/admin/TLE/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/app/oracle/oradata/TLE/control01.ctl','/oracle/app/orac
le/oradata/TLE/control02.ctl','/oracle/app/oracle/oradata/TLE/control03.ctl'
*.core_dump_dest='/oracle/app/oracle/admin/TLE/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TLE'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TLEXDB)'
*.fast_start_mttr_target=0
*.hash_join_enabled=TRUE
*.instance_name='TLE'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=117440512
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/oracle/admin/TLE/udump'



Well, I learned something again!

Thanks Viv




There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Is the spfile editable and, if so, is it a problem that in vi (unix) it complains of null characters at the beginning of the file. Will these be lost if I save it?


bash-2.05a$ vi /oracle/app/oracle/product/9.2.0.1.0/dbs/spfileTLE.ora
"/oracle/app/oracle/product/9.2.0.1.0/dbs/spfileTLE.ora" 30 lines, 2560 charact
ers (1539 null characters)
[Press return to continue]
^E^B^B^Cõ^_#oL*.aq_tm_processes=1
*.background_dump_dest='/oracle/app/oracle/admin/TLE/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oracle/app/oracle/oradata/TLE/control01.ctl','/oracle/app/orac
le/oradata/TLE/control02.ctl','/oracle/app/oracle/oradata/TLE/control03.ctl'
*.core_dump_dest='/oracle/app/oracle/admin/TLE/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='TLE'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TLEXDB)'
*.fast_start_mttr_target=0
*.hash_join_enabled=TRUE
*.instance_name='TLE'
*.java_pool_size=117440512
*.job_queue_processes=10
*.large_pool_size=16777216
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
:quit!
bash-2.05a$



There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
You still have the database pointing to undotbs1

undo_tablespace string UNDOTBS1


it appears you are using spfile to startup

to prove this do

show parameters spfile

if this is the case you need to change the undo tablespace by

alter system set undo_tablespace=undotbs2 scope=both

restart the database

now try to drop your tablespace

Alex
 
Upvote 0 Downvote
No, this file is not manually-editable, in fact it's a binary one. You may change it by the mean of ALTER SYSTEM ... SCOPE = [BOTH|SPFILE]

Regards, Dima
 
Upvote 0 Downvote
Alex,

Okay..
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
2 FROM sys.v_$parameter WHERE name = 'spfile';

Init F
------
SPFILE


I get this:
SQL> alter system set undo_tablespace=undotbs2 scope=both
2 /
alter system set undo_tablespace=undotbs2 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invali
ORA-00376: file 12 cannot be read at this time



There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
SQL> alter system set undo_tablespace = undotbs2 scope = both;
alter system set undo_tablespace = undotbs2 scope = both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/app/oracle/oradata/TLE/undotbs02.dbf'

Getting closer?


There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Create one more undo tablespace, don't do anything else posted above, specify this new as UNDO by alter system.
BTW this means that undotbs02.dbf belongs to undotbs2, not undotbs1.

Regards, Dima
 
Upvote 0 Downvote
Can you do this ?

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Kind of late to ask but

Is this a production system ?

Do you have Oracle Support ?

Alex
 
Upvote 0 Downvote
Alex,
1* ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2
SQL> /
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/app/oracle/oradata/TLE/undotbs02.dbf'

It is a production system, yes.

We have no Oracle support (it is via a 3rd part) but we have asked them for help. And wait. And wait...



sem, so I create undotbs3, right?
create undo tablespace undotbs3 datafile '/oracle/app/oracle/oradata/TLE/undotbs31.dbf' size 5m autoextend on;


alter system set undo_tablespace=undotbs3 scope=both
SQL> alter system set undo_tablespace=undotbs3 scope=both
2
SQL> /

System altered.


Looks promising...

Restart DB?




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

why can you do

alter system set undo_tablespace=undotbs3 scope=both

but not

alter system set undo_tablespace=undotbs2 scope=both

any ideas sem ?

Alex
 
Upvote 0 Downvote
I created undotbs3 via that SQL command, used Enterprise Manager to create undotbs2, possible my error?

Does it matter there is only 1 datafile? Why might there have been 2? (no big issue?)

Back to problemn:
Tablespaces ...

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


SQL> drop tablespace undotbs2;

Tablespace dropped.




There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Locked
  • Question
Inserting Text Decimal to Table Error
Replies
4
Views
815
carp
carp
Server Guy
  • Locked
  • Question
Solaris 10 update 9 iso file
Replies
0
Views
226
Server Guy
Server Guy
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
678
carp
carp
Andrzejek
  • Locked
  • Question
Copy a table from Test to Production
Replies
1
Views
181
carp
carp
Vasu100
  • Locked
  • Question
Power Builder Reverse Engineer Physical Data Model
Replies
1
Views
125
johnherman
johnherman

Part and Inventory Search

Sponsor

Back
Top