Alert errors relating to trace/undo

[MCubitt]

One of our Oracle DBs has a nasty lot of messages in its alert log. The system "appears" to be functioning okay but one never knows behind the scenes!

Messages:
Wed Jun 2 05:40:48 2004
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 117440512
large_pool_size = 16777216
java_pool_size = 117440512
control_files = /oracle/app/oracle/oradata/TLE/control01.ctl, /oracle/app/oracle/oradata/TLE/control02.ctl, /oracle/app/oracle/oradata/TLE/control03.ctl
db_block_size = 8192
db_cache_size = 33554432
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 0
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 900
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = TLE
dispatchers = (PROTOCOL=TCP) (SERVICE=TLEXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = /oracle/app/oracle/admin/TLE/bdump
user_dump_dest = /oracle/app/oracle/admin/TLE/udump
core_dump_dest = /oracle/app/oracle/admin/TLE/cdump
sort_area_size = 524288
db_name = TLE
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Wed Jun 2 05:40:50 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jun 2 05:40:50 2004
ALTER DATABASE MOUNT
Wed Jun 2 05:40:54 2004
Successful mount of redo thread 1, with mount id 1607154338.
Wed Jun 2 05:40:54 2004
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Wed Jun 2 05:40:55 2004
ALTER DATABASE OPEN
Wed Jun 2 05:40:55 2004
Thread 1 opened at log sequence 733
Current log# 3 seq# 733 mem# 0: /oracle/app/oracle/oradata/TLE/redo03.log
Successful open of redo thread 1.
Wed Jun 2 05:40:55 2004
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jun 2 05:40:55 2004
SMON: enabling cache recovery
Wed Jun 2 05:40:56 2004
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Successfully onlined Undo Tablespace 1.
Wed Jun 2 05:40:56 2004
SMON: enabling tx recovery
Wed Jun 2 05:40:56 2004
Database Characterset is WE8ISO8859P1
Wed Jun 2 05:40:56 2004
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:40:58 2004
Errors in file /oracle/app/oracle/admin/TLE/bdump/tle_smon_29684.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracle/oradata/TLE/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:40:59 2004
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Wed Jun 2 05:42:47 2004
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Jun 2 05:46:06 2004
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:46:06 2004
Errors in file /oracle/app/oracle/admin/TLE/bdump/tle_smon_29684.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracle/oradata/TLE/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:48:02 2004
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Jun 2 05:51:14 2004
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:51:14 2004
Errors in file /oracle/app/oracle/admin/TLE/bdump/tle_smon_29684.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracle/oradata/TLE/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:53:14 2004
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Jun 2 05:56:22 2004
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:56:22 2004
Errors in file /oracle/app/oracle/admin/TLE/bdump/tle_smon_29684.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracle/oradata/TLE/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 05:58:30 2004
Restarting dead background process QMN0
QMN0 started with pid=9
Wed Jun 2 06:01:30 2004
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Wed Jun 2 06:01:30 2004
Errors in file /oracle/app/oracle/admin/TLE/bdump/tle_smon_29684.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/oracle/app/oracle/oradata/TLE/undotbs01.dbf'
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
SMON: mark undo segment 5 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 7
SMON: mark undo segment 7 as needs recovery
SMON: about to recover undo segment 8
SMON: mark undo segment 8 as needs recovery
SMON: about to recover undo segment 9
SMON: mark undo segment 9 as needs recovery

And so it continues.. all day and night.


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

 

[alexhu]

This is AIX ?

You have a problem reading file 2

go into dba_data_files and find out which file this is

How big is it ?

do you have the filesystem large-file enabled ?

Alex

 

[MCubitt]

Alex,

Thanks for the quick reply. Yes, it is AIX.


FILE_NAME , FILE_ID,TABLESPACE_NAME
----------------------------------------------------------------------------------------------------,----------,-----------------
/oracle/app/oracle/oradata/TLE/system01.dbf , 1,SYSTEM
/oracle/app/oracle/oradata/TLE/undotbs01.dbf , 2,UNDOTBS1
/oracle/app/oracle/oradata/TLE/cwmlite01.dbf , 3,CWMLITE
/oracle/app/oracle/oradata/TLE/drsys01.dbf , 4,DRSYS
/oracle/app/oracle/oradata/TLE/example01.dbf , 5,EXAMPLE
/oracle/app/oracle/oradata/TLE/indx01.dbf , 6,INDX
/oracle/app/oracle/oradata/TLE/odm01.dbf , 7,ODM
/oracle/app/oracle/oradata/TLE/tools01.dbf , 8,TOOLS
/oracle/app/oracle/oradata/TLE/users01.dbf , 9,USERS
/oracle/app/oracle/oradata/TLE/xdb01.dbf , 10,XDB
/oracledata/TLETLE.dbf , 11,TLE
/oracle/app/oracle/oradata/TLE/undotbs02.dbf , 12,UNDOTBS1


Well, I think we have found the problem:
ls -la /oracle/app/oracle/oradata/TLE/
total 2355840
drwxr-sr-x 2 oracle oinstall 512 02 Apr 13:18 .
drwxr-sr-x 3 oracle oinstall 512 04 Feb 10:02 ..
-rw-r----- 1 oracle oinstall 1548288 02 Jun 11:47 control01.ctl
-rw-r----- 1 oracle oinstall 1548288 02 Jun 11:47 control02.ctl
-rw-r----- 1 oracle oinstall 1548288 02 Jun 11:47 control03.ctl
-rw-r--r-- 1 oracle oinstall 20979712 02 Jun 05:40 cwmlite01.dbf
-rw-r--r-- 1 oracle oinstall 20979712 02 Jun 05:40 drsys01.dbf
-rw-r--r-- 1 oracle oinstall 144842752 02 Jun 05:40 example01.dbf
-rw-r--r-- 1 oracle oinstall 26222592 02 Jun 05:40 indx01.dbf
-rw-r--r-- 1 oracle oinstall 20979712 02 Jun 05:40 odm01.dbf
-rw-r----- 1 oracle oinstall 104858112 02 Jun 05:40 redo01.log
-rw-r----- 1 oracle oinstall 104858112 02 Jun 05:40 redo02.log
-rw-r----- 1 oracle oinstall 104858112 02 Jun 11:46 redo03.log
-rw-r--r-- 1 oracle oinstall 524296192 02 Jun 11:46 system01.dbf
-rw-r--r-- 1 oracle oinstall 42999808 03 Sep 2003 temp01.dbf
-rw-r--r-- 1 oracle oinstall 10493952 02 Jun 05:40 tools01.dbf
-rw-r----- 1 oracle oinstall 5251072 02 Jun 09:56 undotbs02.dbf
-rw-r--r-- 1 oracle oinstall 26222592 02 Jun 05:40 users01.dbf
-rw-r--r-- 1 oracle oinstall 41951232 02 Jun 11:35 xdb01.dbf

There is no undotbs01!

How can I easily rectify this?


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

 

[vivekm]

Add another undo tablespace UNDOTBS2 using Enterprise Manager. Take the original undo tablespace UNDOTBS1 offline.

This should be sufficient but if you want to keep the name of your tablespace same as before, you can now create UNDOTBS1 and then offline and drop UNDOTBS2.

Vivek

 

[alexhu]

and in addition

keep the oracle and root passwords to this system restricted to DBA and UNIX admin ONLY ! - unless they are both the same person

Alex

 

[MCubitt]

vive,

Thanks. Will do.


Alex,

They are... ish. But both have "trainee" prefix in our company! And hey, it's me! ..and a few colleagues.



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

 

[MCubitt]

Actually, I cannot take it off line. Get Cannot offline the undo tablespace.


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

 

[alexhu]

Well 'someone' deleted that file :-(

have you added the other undo tablespace first?

Also you may have active transactions in the one you are trying to offline, so get all users off the database.

Alex

 

[MCubitt]

I have added the other tablespace, yes.

I shutdown and restarted the DB. Still no joy.

The other table space is being used, so that's all fine.


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

 

[MCubitt]

Okay, now things are very strange...

I have, physically:
-rw-r----- 1 oracle oinstall 5251072 02 Jun 14:28 undotbs02.dbf
-rw-rw---- 1 oracle oinstall 5251072 02 Jun 13:57 UNDOTBS21.dbf
-rw-rw---- 1 oracle oinstall 5251072 02 Jun 13:57 UNDOTBS22.dbf

And Oracle sees:
TABLESPACE_NAME FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------
CWMLITE 3 /oracle/app/oracle/oradata/TLE/cwmlite01.dbf
DRSYS 4 /oracle/app/oracle/oradata/TLE/drsys01.dbf
EXAMPLE 5 /oracle/app/oracle/oradata/TLE/example01.dbf
INDX 6 /oracle/app/oracle/oradata/TLE/indx01.dbf
ODM 7 /oracle/app/oracle/oradata/TLE/odm01.dbf
SYSTEM 1 /oracle/app/oracle/oradata/TLE/system01.dbf
TLE 11 /oracledata/TLETLE.dbf
TOOLS 8 /oracle/app/oracle/oradata/TLE/tools01.dbf
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
USERS 9 /oracle/app/oracle/oradata/TLE/users01.dbf
XDB 10 /oracle/app/oracle/oradata/TLE/xdb01.dbf
controlfile number : 1 /oracle/app/oracle/oradata/TLE/control01.ctl
2 /oracle/app/oracle/oradata/TLE/control02.ctl
3 /oracle/app/oracle/oradata/TLE/control03.ctl
online redo log group : 1 /oracle/app/oracle/oradata/TLE/redo01.log
2 /oracle/app/oracle/oradata/TLE/redo02.log
3 /oracle/app/oracle/oradata/TLE/redo03.log

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?


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

 

[MCubitt]

I am going to open a new issue, since the original query has been answered, this is a whole new ball game.



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