我先描述一下情况,往各位高手指点迷津oracle 10.2.0.1的数据库 非归档模式 没备份 undo表空间损坏了,我按照网上的帖子 修改了参数文件 并从参数文件启动 ,能mount但不能打开,为何? 参数文件如下:*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)运行过程如下 SQL> startup mount pfile='D:\pfile2.ora'
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1332324 bytes
Variable Size 153725852 bytes
Database Buffers 448790528 bytes
Redo Buffers 8519680 bytes
数据库装载完毕。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF'
offline drop;
数据库已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接SQL>后台日志信息为:
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 142606336
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 612368384
control_files = D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL01.CTL, D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL02.CTL, D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 448790528
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = D:\oracle\product/flash_recovery_area
db_recovery_file_dest_size= 2147483648
_allow_resetlogs_corruption= TRUE
_corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$
undo_management = MANUAL
undo_tablespace = SYSTEM
_kgl_large_heap_warning_threshold= 8388608
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=openviewXDB)
job_queue_processes = 10
audit_file_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\ADUMP
background_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\BDUMP
user_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\UDUMP
core_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\CDUMP
db_name = openview
open_cursors = 3000
pga_aggregate_target = 203423744
PMON started with pid=2, OS id=40408
PSP0 started with pid=4, OS id=41148
MMAN started with pid=6, OS id=41140
DBW0 started with pid=8, OS id=41132
DBW1 started with pid=10, OS id=41160
DBW2 started with pid=12, OS id=41144
DBW3 started with pid=14, OS id=41152
LGWR started with pid=16, OS id=40080
CKPT started with pid=18, OS id=41000
SMON started with pid=20, OS id=41156
RECO started with pid=22, OS id=41164
CJQ0 started with pid=24, OS id=40952
MMON started with pid=26, OS id=41168
MMNL started with pid=28, OS id=41172
Wed Feb 04 11:50:49 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Feb 04 11:50:50 2009
ALTER DATABASE MOUNT
Wed Feb 04 11:50:54 2009
Setting recovery target incarnation to 3
Wed Feb 04 11:50:54 2009
Successful mount of redo thread 1, with mount id 2648135770
Wed Feb 04 11:50:54 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Feb 04 11:51:08 2009
alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF' offline drop
Wed Feb 04 11:51:08 2009
Completed: alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF' offline drop
Wed Feb 04 11:51:25 2009
alter database open
Wed Feb 04 11:51:25 2009
Beginning crash recovery of 1 threads
parallel recovery started with 16 processes
Wed Feb 04 11:51:26 2009
Started redo scan
Wed Feb 04 11:51:26 2009
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Wed Feb 04 11:51:26 2009
Started redo application at
Thread 1: logseq 5, block 2, scn 943218202
Wed Feb 04 11:51:26 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\REDOLOG04.LOG
Wed Feb 04 11:51:26 2009
Completed redo application
Wed Feb 04 11:51:26 2009
Completed crash recovery at
Thread 1: logseq 5, block 3, scn 943238204
0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Feb 04 11:51:27 2009
Thread 1 advanced to log sequence 6
Thread 1 opened at log sequence 6
Current log# 5 seq# 6 mem# 0: D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\REDOLOG05.LOG
Successful open of redo thread 1
Wed Feb 04 11:51:27 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Feb 04 11:51:27 2009
SMON: enabling cache recovery
Wed Feb 04 11:51:27 2009
Errors in file d:\oracle\product\admin\openview\udump\openview_ora_41304.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF'
Wed Feb 04 11:51:27 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_psp0_41148.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_mman_41140.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_lgwr_40080.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw1_41160.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw2_41144.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw0_41132.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw3_41152.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_ckpt_41000.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_pmon_40408.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:29 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_reco_41164.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:29 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_smon_41156.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 41304
ORA-1092 signalled during: alter database open...
请各位指点 如需其他信息 我再贴上
*.undo_tablespace='SYSTEM'
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)运行过程如下 SQL> startup mount pfile='D:\pfile2.ora'
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1332324 bytes
Variable Size 153725852 bytes
Database Buffers 448790528 bytes
Redo Buffers 8519680 bytes
数据库装载完毕。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF'
offline drop;
数据库已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接SQL>后台日志信息为:
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 142606336
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
sga_target = 612368384
control_files = D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL01.CTL, D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL02.CTL, D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 448790528
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = D:\oracle\product/flash_recovery_area
db_recovery_file_dest_size= 2147483648
_allow_resetlogs_corruption= TRUE
_corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$
undo_management = MANUAL
undo_tablespace = SYSTEM
_kgl_large_heap_warning_threshold= 8388608
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=openviewXDB)
job_queue_processes = 10
audit_file_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\ADUMP
background_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\BDUMP
user_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\UDUMP
core_dump_dest = D:\ORACLE\PRODUCT\ADMIN\OPENVIEW\CDUMP
db_name = openview
open_cursors = 3000
pga_aggregate_target = 203423744
PMON started with pid=2, OS id=40408
PSP0 started with pid=4, OS id=41148
MMAN started with pid=6, OS id=41140
DBW0 started with pid=8, OS id=41132
DBW1 started with pid=10, OS id=41160
DBW2 started with pid=12, OS id=41144
DBW3 started with pid=14, OS id=41152
LGWR started with pid=16, OS id=40080
CKPT started with pid=18, OS id=41000
SMON started with pid=20, OS id=41156
RECO started with pid=22, OS id=41164
CJQ0 started with pid=24, OS id=40952
MMON started with pid=26, OS id=41168
MMNL started with pid=28, OS id=41172
Wed Feb 04 11:50:49 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Wed Feb 04 11:50:50 2009
ALTER DATABASE MOUNT
Wed Feb 04 11:50:54 2009
Setting recovery target incarnation to 3
Wed Feb 04 11:50:54 2009
Successful mount of redo thread 1, with mount id 2648135770
Wed Feb 04 11:50:54 2009
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Feb 04 11:51:08 2009
alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF' offline drop
Wed Feb 04 11:51:08 2009
Completed: alter database datafile 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF' offline drop
Wed Feb 04 11:51:25 2009
alter database open
Wed Feb 04 11:51:25 2009
Beginning crash recovery of 1 threads
parallel recovery started with 16 processes
Wed Feb 04 11:51:26 2009
Started redo scan
Wed Feb 04 11:51:26 2009
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Wed Feb 04 11:51:26 2009
Started redo application at
Thread 1: logseq 5, block 2, scn 943218202
Wed Feb 04 11:51:26 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\REDOLOG04.LOG
Wed Feb 04 11:51:26 2009
Completed redo application
Wed Feb 04 11:51:26 2009
Completed crash recovery at
Thread 1: logseq 5, block 3, scn 943238204
0 data blocks read, 0 data blocks written, 1 redo blocks read
Wed Feb 04 11:51:27 2009
Thread 1 advanced to log sequence 6
Thread 1 opened at log sequence 6
Current log# 5 seq# 6 mem# 0: D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\REDOLOG05.LOG
Successful open of redo thread 1
Wed Feb 04 11:51:27 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Feb 04 11:51:27 2009
SMON: enabling cache recovery
Wed Feb 04 11:51:27 2009
Errors in file d:\oracle\product\admin\openview\udump\openview_ora_41304.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF'
Wed Feb 04 11:51:27 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_psp0_41148.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_mman_41140.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_lgwr_40080.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw1_41160.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw2_41144.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw0_41132.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_dbw3_41152.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_ckpt_41000.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:28 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_pmon_40408.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:29 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_reco_41164.trc:
ORA-00704: bootstrap process failure
Wed Feb 04 11:51:29 2009
Errors in file d:\oracle\product\admin\openview\bdump\openview_smon_41156.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 41304
ORA-1092 signalled during: alter database open...
请各位指点 如需其他信息 我再贴上
2.停掉数据库,新建UNDO表空间,修改初始化文件,然后启动服务.
offline drop;
数据库已更改。
------------------
这一步你好象做错了吧,前面直接startup 到open状态,然后再去处理undo空间文件.
If ORA-1589 "must use RESETLOGS or NORESETLOGS option for database open"
occurs:
SQL> ALTER DATABASE OPEN NORESETLOGS;
但问题是:引用chj733的回复:
------------
使用*._corrupted_rollback_segments参数打开数据库,这个是ORACLE不提倡的,成功之后你敢继续使用这个数据库吗?EXP/IMP是为了保险
对于这种情况使用更换UNDO表空间,我觉得不大可取
楼主数据库的意外断电,这个说明可能还有事务在运行,在UNDO中可能存在活动事务,也许部分UNDO的REDO信息已经到达了LOGFILE,当数据库进行RECOVER时,我门就需要回滚这些没有提交的活动事务,我们必须将LOGFILE中的REDO应用到回滚段来进行回滚,这些REDO记录都是有 UBA对应的,现在你更换了UNDO表空间,他将如何根据UBA去应用日志并回滚事务???
--------------各位大侠也请讨论一下,这种情况怎么办!
SQL> startup pfile = 'd:\init.ora';
ORACLE instance started.Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 92275852 bytes
Database Buffers 109051904 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'SQL> alter database datafile 2 offline drop;Database altered.SQL> alter database open
2 ;Database altered.SQL> create undo tablespace undo datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF'size
180m reuse autoextend on;Tablespace created.SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--修改pfile重新定向undo
SQL> STARTUP PFILE = 'D:\INIT.ORA';
ORACLE instance started.Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 92275852 bytes
Database Buffers 109051904 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.SQL> select file#,status,name from v$datafile; FILE# STATUS
---------- -------
NAME
---------------------------------------------------------------------------------
1 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 2 OFFLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 3 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 4 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 5 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 6 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\FDIRTB0101.DBF 7 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF
7 rows selected.
执行到alter database open
还是报ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\PRODUCT\ORADATA\OPENVIEW\UNDOTBS01.DBF'
*.undo_tablespace='新的UNDO表空间名'
把undo表空间重定向,就可以通过pfile打开,然后通过pfile创建spfile, 就完成了
如果这样,可能有些麻烦了,
你把trc和alert中出现的相关信息贴上来请大侠们看看
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
这与楼主的过程可能有些不同