ORA-01092 ORA-30012

[alfie002]

Dear all,

I am experiencing a problem restarting an Oracle 9.2.0.6 database after a power failure.

When I try to start the DB uwing the Oracle Enterprise Manager front end it reports a

ORA-01092

and then drops the connection.

I have checked the alert.log and it reports a problem as follows;

I have copied the log file

"
Tue Nov 06 12:23:10 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.6.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 100663296
sga_max_size = 546382476
large_pool_size = 67108864
java_pool_size = 67108864
control_files = F:\oracle\oradata\<instance_ID>\CONTROL01.CTL, F:\oracle\oradata\<instance_ID>\CONTROL02.CTL, F:\oracle\oradata\<instance_ID>\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 50331648
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS01
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain = <hostname>
instance_name = <instance_ID>
dispatchers = (PROTOCOL=TCP) (SERVICE=OVPIUBS1XDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = E:\oracle\admin\<instance_ID>\bdump
user_dump_dest = E:\oracle\admin\<instance_ID>\udump
core_dump_dest = E:\oracle\admin\<instance_ID>\cdump
sort_area_size = 524288
db_name = <instance_ID>
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 140509184
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
Tue Nov 06 12:23:13 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Nov 06 12:24:12 2007
/* OracleOEM */ ALTER DATABASE MOUNT
Tue Nov 06 12:24:16 2007
Successful mount of redo thread 1, with mount id 2734449452
Tue Nov 06 12:24:16 2007
Database mounted in Exclusive Mode.
Completed: /* OracleOEM */ ALTER DATABASE MOUNT
Tue Nov 06 12:24:42 2007
/* OracleOEM */ ALTER DATABASE OPEN
Tue Nov 06 12:24:42 2007
Beginning crash recovery of 1 threads
Tue Nov 06 12:24:42 2007
Started redo scan
Tue Nov 06 12:24:42 2007
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Tue Nov 06 12:24:42 2007
Started recovery at
Thread 1: logseq 3067, block 2, scn 0.220230347
Tue Nov 06 12:24:42 2007
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3067 Reading mem 0
Mem# 0 errs 0: F:\ORACLE\ORADATA\<instance_ID>\REDO03.LOG
Tue Nov 06 12:24:42 2007
Completed redo application
Tue Nov 06 12:24:42 2007
Ended recovery at
Thread 1: logseq 3067, block 3, scn 0.220250348
0 data blocks read, 0 data blocks written, 1 redo blocks read
Crash recovery completed successfully
Tue Nov 06 12:24:42 2007
Thread 1 advanced to log sequence 3068
Thread 1 opened at log sequence 3068
Current log# 1 seq# 3068 mem# 0: F:\ORACLE\ORADATA\<instance_ID>\REDO01.LOG
Successful open of redo thread 1
Tue Nov 06 12:24:42 2007
SMON: enabling cache recovery
Tue Nov 06 12:24:43 2007
Errors in file e:\oracle\admin\<instance_ID>\udump\<instance_ID>_ora_4852.trc:
ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type

Tue Nov 06 12:24:43 2007
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 4852
ORA-1092 signalled during: /* OracleOEM */ ALTER DATABASE OPEN ...
"

it would appeart there is a problem with the Tablespace "UNDOTSB01" and the error message 30012 is reported. I suspect there is a problem locating the tablespace file, though the file is present on the system, or there is a problem with the tablespace itself. If it is the case there is a problem with the tablespace, would someone please outline what has to be done ??

If anyone can shed any light on this, I'd appreciate this.

Thanks in advance.

Alf

 

[dbtoo2001]

Can you login to your server with a local administrator account (typically the one that installed oracle) that the instance is on? I've never done this through OEM.

review this document:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#9505

start sqlplus /nolog
conn /as sysdba

startup nomount

rem alter the instance to not use an undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = '';

rem - assuming this to be the only issue after the power failure....

alter database mount;
alter database open;

rem create a new undo - change the path/filename to yours

create undo tablespace undotbs3 datafile '/oriondev/oradata/dba10/undotbs03.dbf' SIZE 2M autoextend on;

rem - adjust the size to what you need it to be

rem Then switch the instance to use the new undo
ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTSB3'; -- whatever you named your new undo.

shutdown immediate

Then change the initfile to use the new undo (assuming you are not using an spfile.)

startup the instance, drop the old undo.

Take a backup!

My typical practice is to create two undo tablespaces. If/when the one in use corrupts, then alter the system to use the 'new undo', drop the bad one, take a backup, create an additional new undo.

 

[alfie002]

Hello dbtoo2001,

yip, I can login to the server and get the command line.

So this command

rem alter the instance to not use an undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = '';

will configure the server not to use an UNDO_TABLESPACE, or use one that I specify inside the single quotes.
Then it is a case of mounting and starting the database.
Creating the new tablespace, fine!
Using the new tablespace, fine !

I am using an SPFILE, I presume I update this file with the new tablespace name.

In this case, where the SPFILE contains the reference to the original tablespace, why would the tablespace not be there ??

Thanks

Alf

 

[dbtoo2001]

Probably the header of the file corrupted when the database crashed. The spfile should be 'changed' when you do the alter command. If you aren't, I'd recommend creating a pfile after you change the spfile.

 

[dbtoo2001]

Let us know how it went.