grunt121
asked on
Repairng undo tablespace from ORA-01092 Error
Hi,
I'm trying to repair a the undo tablespace file which is stopping the database from opening. However the file that it needs arc00040.001 is not present the closet one there is is arc00006.001 but it is nearly 3 months old. As they are not too fussy about time is there any way that I can use this file instead or start the database up by creating a new undo tablespace.
Thanks
I'm trying to repair a the undo tablespace file which is stopping the database from opening. However the file that it needs arc00040.001 is not present the closet one there is is arc00006.001 but it is nearly 3 months old. As they are not too fussy about time is there any way that I can use this file instead or start the database up by creating a new undo tablespace.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have to mount the database:
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
After that try to drop the undo segment and to cerate new one:
ALTER ROLLBACK SEGMENT rbs01
OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (
INITIAL 100K
NEXT 100K
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 2000K );
Use the following command to make a rollback
segment available:
• Specify the following initialization parameter to
ensure rollback segments are brought online at
startup:
ROLLBACK_SEGMENTS=(rbs01, rbs02) ROLLBACK_SEGMENTS=(rbs01, rbs02)
ALTER ROLLBACK SEGMENT rbs01 ONLINE; ALTER ROLLBACK SEGMENT rbs01 ONLINE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
After that try to drop the undo segment and to cerate new one:
ALTER ROLLBACK SEGMENT rbs01
OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (
INITIAL 100K
NEXT 100K
MINEXTENTS 20
MAXEXTENTS 100
OPTIMAL 2000K );
Use the following command to make a rollback
segment available:
• Specify the following initialization parameter to
ensure rollback segments are brought online at
startup:
ROLLBACK_SEGMENTS=(rbs01, rbs02) ROLLBACK_SEGMENTS=(rbs01, rbs02)
ALTER ROLLBACK SEGMENT rbs01 ONLINE; ALTER ROLLBACK SEGMENT rbs01 ONLINE;
ASKER
Sorry I should have said that this is 9i i'm using
I tried commenting out that the
undo_tablespace
undo_management
which didn't seem to work whats the code to create a new undo tablespace please
I tried commenting out that the
undo_tablespace
undo_management
which didn't seem to work whats the code to create a new undo tablespace please
1) Creating an undo ts
CREATE UNDO TABLESPACE <name of undotbs>
DATAFILE '<full path of the file>' SIZE <size>M REUSE AUTOEXTEND ON;
For eg.,
CREATE UNDO TABLESPACE undotbs01
DATAFILE 'C:\Oracle\Database\MyDB\u
2)Dropping an Undo Ts:
DROP TABLESPACE undotbs;
-----
Rememeber to include the name of the new undo tablespace "undotbs01" in the init file.
CREATE UNDO TABLESPACE <name of undotbs>
DATAFILE '<full path of the file>' SIZE <size>M REUSE AUTOEXTEND ON;
For eg.,
CREATE UNDO TABLESPACE undotbs01
DATAFILE 'C:\Oracle\Database\MyDB\u
2)Dropping an Undo Ts:
DROP TABLESPACE undotbs;
-----
Rememeber to include the name of the new undo tablespace "undotbs01" in the init file.
For 9i:
Use the DROP TABLESPACE statement to drop an undo tablespace. The following
example drops the undo tablespace undotbs_01:
DROP TABLESPACE undotbs_01;
An undo tablespace can only be dropped if it is not currently used by any instance.
If the undo tablespace contains any outstanding transactions (for example, a
transaction died but has not yet been recovered), the DROP TABLESPACE statement
fails. However, since DROP TABLESPACE drops an undo tablespace even if it
contains unexpired undo information (within retention period), you must be careful
not to drop an undo tablespace if undo information is needed by some existing
queries.
DROP TABLESPACE for undo tablespaces behaves like DROP TABLESPACE ...
INCLUDING CONTENTS. All contents of the undo tablespace are removed.
Switching Undo Tablespaces
You can switch from using one undo tablespace to another. Because the UNDO_
TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM
SET statement can be used to assign a new undo tablespace.
The following statement effectively switches to a new undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
Assuming undotbs_01 is the current undo tablespace, after this command
successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its
undo tablespace.
If any of the following conditions exist for the tablespace being switched to, an error
is reported and no switching occurs:
n The tablespace does not exist,
n The tablespace is not an undo tablespace
n The tablespace is already being used by another instance
Using the CREATE UNDO TABLESPACE Statement
The CREATE UNDO TABLESPACE statement is the same as the CREATE
TABLESPACE statement, but the UNDO keyword is specified. Oracle determines
most of the attributes of the undo tablespace, you can specify only the DATAFILE
clause.
This example creates the undotbs_02 undo tablespace:
CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u01/oracle/rbdb1/undo020
Use the DROP TABLESPACE statement to drop an undo tablespace. The following
example drops the undo tablespace undotbs_01:
DROP TABLESPACE undotbs_01;
An undo tablespace can only be dropped if it is not currently used by any instance.
If the undo tablespace contains any outstanding transactions (for example, a
transaction died but has not yet been recovered), the DROP TABLESPACE statement
fails. However, since DROP TABLESPACE drops an undo tablespace even if it
contains unexpired undo information (within retention period), you must be careful
not to drop an undo tablespace if undo information is needed by some existing
queries.
DROP TABLESPACE for undo tablespaces behaves like DROP TABLESPACE ...
INCLUDING CONTENTS. All contents of the undo tablespace are removed.
Switching Undo Tablespaces
You can switch from using one undo tablespace to another. Because the UNDO_
TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM
SET statement can be used to assign a new undo tablespace.
The following statement effectively switches to a new undo tablespace:
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
Assuming undotbs_01 is the current undo tablespace, after this command
successfully executes, the instance uses undotbs_02 in place of undotbs_01 as its
undo tablespace.
If any of the following conditions exist for the tablespace being switched to, an error
is reported and no switching occurs:
n The tablespace does not exist,
n The tablespace is not an undo tablespace
n The tablespace is already being used by another instance
Using the CREATE UNDO TABLESPACE Statement
The CREATE UNDO TABLESPACE statement is the same as the CREATE
TABLESPACE statement, but the UNDO keyword is specified. Oracle determines
most of the attributes of the undo tablespace, you can specify only the DATAFILE
clause.
This example creates the undotbs_02 undo tablespace:
CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u01/oracle/rbdb1/undo020
ASKER
is the rollback segment the same as a undo tablespace?
Plus if i can't open the database how can i create new tablespace?
Plus if i can't open the database how can i create new tablespace?
No rollback segments have been phased out in 9i. Instead you can use undo tablespaces (though rbs is still avlb).
Why can't you open the db?Did you comment out the init<sid>.ora file "undo" contents. Also look into alert<sid>.log for details on why you can't start the db.
Why can't you open the db?Did you comment out the init<sid>.ora file "undo" contents. Also look into alert<sid>.log for details on why you can't start the db.
ASKER
i have commented out the undo comment , please see below but the alert log is still indicating that the file needs to be repaired, but that it can't access that file at the moment. Sorry about the information overload
init.ora
# Copyright (c) 1991, 2001 by Oracle Corporation
##########################
##########################
# Cache and I/O
##########################
db_block_size=8192
db_cache_size=33554432
##########################
# Cursors and Library Cache
##########################
open_cursors=300
##########################
# Diagnostics and Statistics
##########################
background_dump_dest=D:\or
core_dump_dest=D:\oracle\a
timed_statistics=TRUE
user_dump_dest=D:\oracle\a
##########################
# Distributed, Replication and Snapshot
##########################
db_domain=oxoid
remote_login_passwordfile=
##########################
# File Configuration
##########################
control_files=("D:\oracle\
##########################
# MTS
##########################
dispatchers="(PROTOCOL=TCP
##########################
# Miscellaneous
##########################
compatible=9.0.0
db_name=weighahd
##########################
# Network Registration
##########################
instance_name=weighahd
##########################
# Pools
##########################
java_pool_size=33554432
shared_pool_size=46497404
##########################
# Processes and Sessions
##########################
processes=300
##########################
# Redo Log and Recovery
##########################
fast_start_mttr_target=300
##########################
# Sort, Hash Joins, Bitmap Indexes
##########################
hash_area_size=1048576
sort_area_size=1048576
##########################
# System Managed Undo and Rollback Segments
##########################
#undo_management=AUTO
#undo_retention=10800
#undo_tablespace=UNDOTBS
##########################
##########################
#Archive Logged Files
##########################
log_archive_start=true
log_archive_dest= d:\oracle\admin\weighahd\a
log_archive_format= "weighadlog%s.arc"
Alert log file
Starting ORACLE instance (normal)
Starting up ORACLE RDBMS Version: 9.0.1.1.1.
System parameters with non-default values:
processes = 300
timed_statistics = TRUE
shared_pool_size = 67108864
java_pool_size = 33554432
control_files = D:\oracle\oradata\weighahd
db_block_size = 8192
db_cache_size = 33554432
compatible = 9.0.0
log_archive_start = TRUE
log_archive_dest = d:\oracle\admin\weighahd\a
log_archive_format = weighadlog%s.arc
fast_start_mttr_target = 0
remote_login_passwordfile=
db_domain = oxoid
instance_name = weighahd
dispatchers = (PROTOCOL=TCP)(SER=MODOSE)
hash_area_size = 1048576
background_dump_dest = D:\oracle\admin\weighahd\b
user_dump_dest = D:\oracle\admin\weighahd\u
core_dump_dest = D:\oracle\admin\weighahd\c
sort_area_size = 1048576
db_name = weighahd
open_cursors = 300
Data Guard broker requires Oracle Enterprise Edition license
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
Tue Apr 13 12:44:59 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC0: Thread not mounted
Tue Apr 13 12:45:00 2004
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Apr 13 12:45:00 2004
ALTER DATABASE MOUNT
Tue Apr 13 12:45:07 2004
Successful mount of redo thread 1, with mount id 2291775182.
Tue Apr 13 12:45:07 2004
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Apr 13 12:45:07 2004
ALTER DATABASE OPEN
Tue Apr 13 12:45:07 2004
Beginning crash recovery of 1 threads
Tue Apr 13 12:45:07 2004
Started recovery at
Thread 1: logseq 51, block 2, scn 0.1230646
Recovery of Online Redo Log: Thread 1 Group 2 Seq 51 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\WEIGHAHD
Tue Apr 13 12:45:07 2004
Ended recovery at
Thread 1: logseq 51, block 4294967295, scn 0.1250729
5 data blocks read, 5 data blocks written, 48 redo blocks read
Crash recovery completed successfully
Tue Apr 13 12:45:08 2004
Thread 1 advanced to log sequence 52
Thread 1 opened at log sequence 52
Current log# 3 seq# 52 mem# 0: D:\ORACLE\ORADATA\WEIGHAHD
Successful open of redo thread 1.
Tue Apr 13 12:45:09 2004
SMON: enabling cache recovery
Tue Apr 13 12:45:09 2004
ARC0: Beginning to archive log 2 thread 1 sequence 51
Tue Apr 13 12:45:09 2004
SMON: enabling tx recovery
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
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
Tue Apr 13 12:45:13 2004
kccrsz: expanded controlfile section 11 from 13 to 27 records
requested to grow by 1 record(s); added 1 block(s) of records
ARC0: Completed archiving log 2 thread 1 sequence 51
Tue Apr 13 12:45:13 2004
Errors in file D:\oracle\admin\weighahd\u
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
init.ora
# Copyright (c) 1991, 2001 by Oracle Corporation
##########################
##########################
# Cache and I/O
##########################
db_block_size=8192
db_cache_size=33554432
##########################
# Cursors and Library Cache
##########################
open_cursors=300
##########################
# Diagnostics and Statistics
##########################
background_dump_dest=D:\or
core_dump_dest=D:\oracle\a
timed_statistics=TRUE
user_dump_dest=D:\oracle\a
##########################
# Distributed, Replication and Snapshot
##########################
db_domain=oxoid
remote_login_passwordfile=
##########################
# File Configuration
##########################
control_files=("D:\oracle\
##########################
# MTS
##########################
dispatchers="(PROTOCOL=TCP
##########################
# Miscellaneous
##########################
compatible=9.0.0
db_name=weighahd
##########################
# Network Registration
##########################
instance_name=weighahd
##########################
# Pools
##########################
java_pool_size=33554432
shared_pool_size=46497404
##########################
# Processes and Sessions
##########################
processes=300
##########################
# Redo Log and Recovery
##########################
fast_start_mttr_target=300
##########################
# Sort, Hash Joins, Bitmap Indexes
##########################
hash_area_size=1048576
sort_area_size=1048576
##########################
# System Managed Undo and Rollback Segments
##########################
#undo_management=AUTO
#undo_retention=10800
#undo_tablespace=UNDOTBS
##########################
##########################
#Archive Logged Files
##########################
log_archive_start=true
log_archive_dest= d:\oracle\admin\weighahd\a
log_archive_format= "weighadlog%s.arc"
Alert log file
Starting ORACLE instance (normal)
Starting up ORACLE RDBMS Version: 9.0.1.1.1.
System parameters with non-default values:
processes = 300
timed_statistics = TRUE
shared_pool_size = 67108864
java_pool_size = 33554432
control_files = D:\oracle\oradata\weighahd
db_block_size = 8192
db_cache_size = 33554432
compatible = 9.0.0
log_archive_start = TRUE
log_archive_dest = d:\oracle\admin\weighahd\a
log_archive_format = weighadlog%s.arc
fast_start_mttr_target = 0
remote_login_passwordfile=
db_domain = oxoid
instance_name = weighahd
dispatchers = (PROTOCOL=TCP)(SER=MODOSE)
hash_area_size = 1048576
background_dump_dest = D:\oracle\admin\weighahd\b
user_dump_dest = D:\oracle\admin\weighahd\u
core_dump_dest = D:\oracle\admin\weighahd\c
sort_area_size = 1048576
db_name = weighahd
open_cursors = 300
Data Guard broker requires Oracle Enterprise Edition license
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
Tue Apr 13 12:44:59 2004
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PR
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC0: Thread not mounted
Tue Apr 13 12:45:00 2004
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Apr 13 12:45:00 2004
ALTER DATABASE MOUNT
Tue Apr 13 12:45:07 2004
Successful mount of redo thread 1, with mount id 2291775182.
Tue Apr 13 12:45:07 2004
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Apr 13 12:45:07 2004
ALTER DATABASE OPEN
Tue Apr 13 12:45:07 2004
Beginning crash recovery of 1 threads
Tue Apr 13 12:45:07 2004
Started recovery at
Thread 1: logseq 51, block 2, scn 0.1230646
Recovery of Online Redo Log: Thread 1 Group 2 Seq 51 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\WEIGHAHD
Tue Apr 13 12:45:07 2004
Ended recovery at
Thread 1: logseq 51, block 4294967295, scn 0.1250729
5 data blocks read, 5 data blocks written, 48 redo blocks read
Crash recovery completed successfully
Tue Apr 13 12:45:08 2004
Thread 1 advanced to log sequence 52
Thread 1 opened at log sequence 52
Current log# 3 seq# 52 mem# 0: D:\ORACLE\ORADATA\WEIGHAHD
Successful open of redo thread 1.
Tue Apr 13 12:45:09 2004
SMON: enabling cache recovery
Tue Apr 13 12:45:09 2004
ARC0: Beginning to archive log 2 thread 1 sequence 51
Tue Apr 13 12:45:09 2004
SMON: enabling tx recovery
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
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
Tue Apr 13 12:45:13 2004
kccrsz: expanded controlfile section 11 from 13 to 27 records
requested to grow by 1 record(s); added 1 block(s) of records
ARC0: Completed archiving log 2 thread 1 sequence 51
Tue Apr 13 12:45:13 2004
Errors in file D:\oracle\admin\weighahd\u
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
Did you check the file D:\oracle\admin\weighahd\u
Did you try recover:
recover datafile 2;
Did you try recover:
recover datafile 2;
ASKER
The udump file ora02084.trc is present is contents is listed below. When i try to recover the data file it is looking for the file arc00040.001 which is not on the system. Which is the original error. See below.
======================
Contents of file ora02084.trc
======================
Dump file D:\oracle\admin\weighahd\u
Tue Apr 13 12:45:13 2004
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle9i Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: weighahd
Redo thread mounted by this instance: 1
Oracle process number: 13
Windows thread id: 2084, image: ORACLE.EXE
*** SESSION ID:(8.7) 2004-04-13 12:45:13.000
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
========================
Screen dump from recover
========================
Total System Global Area 114061244 bytes
Fixed Size 282556 bytes
Variable Size 79691776 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
SQL> recover datafile 2;
ORA-00279: change 1008248 generated at 04/08/2004 06:02:54 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC0
ORA-00280: change 1008248 for thread 1 is in sequence #40
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'D:\ORACLE\ORA92\RDBMS\ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
======================
Contents of file ora02084.trc
======================
Dump file D:\oracle\admin\weighahd\u
Tue Apr 13 12:45:13 2004
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle9i Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: weighahd
Redo thread mounted by this instance: 1
Oracle process number: 13
Windows thread id: 2084, image: ORACLE.EXE
*** SESSION ID:(8.7) 2004-04-13 12:45:13.000
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
========================
Screen dump from recover
========================
Total System Global Area 114061244 bytes
Fixed Size 282556 bytes
Variable Size 79691776 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
SQL> recover datafile 2;
ORA-00279: change 1008248 generated at 04/08/2004 06:02:54 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC0
ORA-00280: change 1008248 for thread 1 is in sequence #40
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'D:\ORACLE\ORA92\RDBMS\ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Do a thing :
1)startup mount
2)When DB is mounted:
alter database datafile 'D:\ORACLE\ORADATA\WEIGHAH
3)Open the DB
alter database open;
4)Drop Old Undo Tablespace
drop tablespace undotbs;
5)Create a New Undo Tablespace
<see the post earlier for the syntax>
6) Shutdown the DB
7)Make modifications in init file
8)Start the DB & backup
1)startup mount
2)When DB is mounted:
alter database datafile 'D:\ORACLE\ORADATA\WEIGHAH
3)Open the DB
alter database open;
4)Drop Old Undo Tablespace
drop tablespace undotbs;
5)Create a New Undo Tablespace
<see the post earlier for the syntax>
6) Shutdown the DB
7)Make modifications in init file
8)Start the DB & backup
ASKER
i was able to drop the datafile offline but I am still unable to alter the database to open
why?
ASKER
i have no idea i still get the same message from oracle. I do have a copy that i took as a backup in march is it possible I can use that file instead of the corrupt one, is that possible?
SQL> alter database datafile 'd:\oracle\oradata\weighah
drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
==========================
here is what is happening in the alert log
==========================
SQL> alter database datafile 'd:\oracle\oradata\weighah
drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> alter database datafile 'd:\oracle\oradata\weighah
drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
==========================
here is what is happening in the alert log
==========================
SQL> alter database datafile 'd:\oracle\oradata\weighah
drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ASKER
sorry i pasted the wrong information here is what is going on in the alert log
Tue Apr 13 14:14:28 2004
alter database datafile 'd:\oracle\oradata\weighah
Tue Apr 13 14:14:28 2004
Completed: alter database datafile 'd:\oracle\oradata\weighah
Tue Apr 13 14:14:43 2004
alter database open
Tue Apr 13 14:14:44 2004
Beginning crash recovery of 1 threads
Tue Apr 13 14:14:44 2004
Started recovery at
Thread 1: logseq 56, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 1 Seq 56 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\WEIGHAHD
Tue Apr 13 14:14:45 2004
Ended recovery at
Thread 1: logseq 56, block 4294967295, scn 0.1351085
5 data blocks read, 5 data blocks written, 47 redo blocks read
Crash recovery completed successfully
ARCH: Beginning to archive log 2 thread 1 sequence 54
ARCH: Completed archiving log 2 thread 1 sequence 54
Tue Apr 13 14:14:46 2004
Thread 1 advanced to log sequence 57
Thread 1 opened at log sequence 57
Current log# 2 seq# 57 mem# 0: D:\ORACLE\ORADATA\WEIGHAHD
Successful open of redo thread 1.
Tue Apr 13 14:14:46 2004
SMON: enabling cache recovery
Tue Apr 13 14:14:47 2004
Successfully onlined Undo Tablespace 1.
Tue Apr 13 14:14:47 2004
SMON: enabling tx recovery
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
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
Tue Apr 13 14:14:48 2004
Errors in file D:\oracle\admin\weighahd\u
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
Tue Apr 13 14:14:28 2004
alter database datafile 'd:\oracle\oradata\weighah
Tue Apr 13 14:14:28 2004
Completed: alter database datafile 'd:\oracle\oradata\weighah
Tue Apr 13 14:14:43 2004
alter database open
Tue Apr 13 14:14:44 2004
Beginning crash recovery of 1 threads
Tue Apr 13 14:14:44 2004
Started recovery at
Thread 1: logseq 56, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 1 Seq 56 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\WEIGHAHD
Tue Apr 13 14:14:45 2004
Ended recovery at
Thread 1: logseq 56, block 4294967295, scn 0.1351085
5 data blocks read, 5 data blocks written, 47 redo blocks read
Crash recovery completed successfully
ARCH: Beginning to archive log 2 thread 1 sequence 54
ARCH: Completed archiving log 2 thread 1 sequence 54
Tue Apr 13 14:14:46 2004
Thread 1 advanced to log sequence 57
Thread 1 opened at log sequence 57
Current log# 2 seq# 57 mem# 0: D:\ORACLE\ORADATA\WEIGHAHD
Successful open of redo thread 1.
Tue Apr 13 14:14:46 2004
SMON: enabling cache recovery
Tue Apr 13 14:14:47 2004
Successfully onlined Undo Tablespace 1.
Tue Apr 13 14:14:47 2004
SMON: enabling tx recovery
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
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
Tue Apr 13 14:14:48 2004
Errors in file D:\oracle\admin\weighahd\u
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\WEIGHAH
what does this say ?
D:\oracle\admin\weighahd\u
Have you uncommented the undo section in init file?
D:\oracle\admin\weighahd\u
Have you uncommented the undo section in init file?
ASKER
Yes here is the init.ora file
##########################
# Copyright (c) 1991, 2001 by Oracle Corporation
##########################
##########################
# Cache and I/O
##########################
db_block_size=8192
db_cache_size=33554432
##########################
# Cursors and Library Cache
##########################
open_cursors=300
##########################
# Diagnostics and Statistics
##########################
background_dump_dest=D:\or
core_dump_dest=D:\oracle\a
timed_statistics=TRUE
user_dump_dest=D:\oracle\a
##########################
# Distributed, Replication and Snapshot
##########################
db_domain=oxoid
remote_login_passwordfile=
##########################
# File Configuration
##########################
control_files=("D:\oracle\
##########################
# MTS
##########################
dispatchers="(PROTOCOL=TCP
##########################
# Miscellaneous
##########################
compatible=9.0.0
db_name=weighahd
##########################
# Network Registration
##########################
instance_name=weighahd
##########################
# Pools
##########################
java_pool_size=33554432
shared_pool_size=46497404
##########################
# Processes and Sessions
##########################
processes=300
##########################
# Redo Log and Recovery
##########################
fast_start_mttr_target=300
##########################
# Sort, Hash Joins, Bitmap Indexes
##########################
hash_area_size=1048576
sort_area_size=1048576
##########################
# System Managed Undo and Rollback Segments
##########################
#undo_management=AUTO
#undo_retention=10800
#undo_tablespace=UNDOTBS
##########################
##########################
#Archive Logged Files
##########################
log_archive_start=true
log_archive_dest= d:\oracle\admin\weighahd\a
log_archive_format= "weighadlog%s.arc"
##########################
# Copyright (c) 1991, 2001 by Oracle Corporation
##########################
##########################
# Cache and I/O
##########################
db_block_size=8192
db_cache_size=33554432
##########################
# Cursors and Library Cache
##########################
open_cursors=300
##########################
# Diagnostics and Statistics
##########################
background_dump_dest=D:\or
core_dump_dest=D:\oracle\a
timed_statistics=TRUE
user_dump_dest=D:\oracle\a
##########################
# Distributed, Replication and Snapshot
##########################
db_domain=oxoid
remote_login_passwordfile=
##########################
# File Configuration
##########################
control_files=("D:\oracle\
##########################
# MTS
##########################
dispatchers="(PROTOCOL=TCP
##########################
# Miscellaneous
##########################
compatible=9.0.0
db_name=weighahd
##########################
# Network Registration
##########################
instance_name=weighahd
##########################
# Pools
##########################
java_pool_size=33554432
shared_pool_size=46497404
##########################
# Processes and Sessions
##########################
processes=300
##########################
# Redo Log and Recovery
##########################
fast_start_mttr_target=300
##########################
# Sort, Hash Joins, Bitmap Indexes
##########################
hash_area_size=1048576
sort_area_size=1048576
##########################
# System Managed Undo and Rollback Segments
##########################
#undo_management=AUTO
#undo_retention=10800
#undo_tablespace=UNDOTBS
##########################
##########################
#Archive Logged Files
##########################
log_archive_start=true
log_archive_dest= d:\oracle\admin\weighahd\a
log_archive_format= "weighadlog%s.arc"
what does this say ?
D:\oracle\admin\weighahd\u
D:\oracle\admin\weighahd\u
1)Start the DB
2)Create a new undo tablespace
3)Alter system and set undo tablespace to the newly created one.
alter system set undo_tablespace=undotbs1 scope=both ; [Note use scope=both if you are using sp file]
4)Drop the old undo tablespace
5)Make changes in the init file.