oracle 10g回滚表空间数据文件损坏，数据库不能OPEN，求救啊...
我数据库版本是10.2.0.1.0，操作系统是aix 5.3,数据库是归档模式，没有相关文件的备份，数据库由于断掉异常关闭后，回滚表空间数据文件损坏，数据库不能正常OPEN,于是修改系统参数文件，如下：原来参数:
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
修改为:
undo_management='manual'
undo_tablespace='system'
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
用新的参数文件启动数据库。如下报错：
startup mount pfile='$ORACLE_HOME/dbs/init_study.ora'
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 1218412 bytes
Variable Size 62916756 bytes
Database Buffers 109051904 bytes
Redo Buffers 7168000 byt
SQL-SQL>recover database;
Media recovery complete.
SQL-SQL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
alert_log警告日志如下：
PSP0 started with pid=3, OS id=15610
MMAN started with pid=4, OS id=15612
DBW0 started with pid=5, OS id=15614
LGWR started with pid=6, OS id=15616
CKPT started with pid=7, OS id=15618
SMON started with pid=8, OS id=15620
RECO started with pid=9, OS id=15622
MMON started with pid=10, OS id=15624
Mon Oct 22 15:04:07 2012
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=11, OS id=15626
Mon Oct 22 15:04:07 2012
starting up 1 shared server(s) ...
Mon Oct 22 15:04:07 2012
ALTER DATABASE MOUNT
Mon Oct 22 15:04:14 2012
Setting recovery target incarnation to 4
Mon Oct 22 15:04:14 2012
Successful mount of redo thread 1, with mount id 2814921898
Mon Oct 22 15:04:14 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Mon Oct 22 15:16:02 2012
alter database datafile '/app/oracle/oradata/study/undotbs01.dbf' offline drop
Mon Oct 22 15:16:02 2012
Completed: alter database datafile '/app/oracle/oradata/study/undotbs01.dbf' offline drop
Mon Oct 22 15:16:08 2012
ALTER DATABASE RECOVER database
Media Recovery Start
parallel recovery started with 2 processes
Mon Oct 22 15:16:08 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
Mem# 0 errs 0: /app/oracle/oradata/study/redo03_1
Mem# 1 errs 0: /app/oracle/oradata/study/redo03_2
Mon Oct 22 15:16:09 2012
Media Recovery Complete (STUDY)
Completed: ALTER DATABASE RECOVER database
Mon Oct 22 15:18:25 2012
alter database open
Mon Oct 22 15:18:25 2012
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Mon Oct 22 15:18:25 2012
Started redo scan
Mon Oct 22 15:18:25 2012
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Mon Oct 22 15:18:25 2012
Started redo application at
Thread 1: logseq 15, block 2, scn 471823
Mon Oct 22 15:18:25 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
Mem# 0 errs 0: /app/oracle/oradata/study/redo03_1
Mem# 1 errs 0: /app/oracle/oradata/study/redo03_2
Mon Oct 22 15:18:25 2012
Completed redo application
Mon Oct 22 15:18:25 2012
Completed crash recovery at
Thread 1: logseq 15, block 3, scn 491825
0 data blocks read, 0 data blocks written, 1 redo blocks read
Mon Oct 22 15:18:25 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=16062
Mon Oct 22 15:18:25 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=16064
Mon Oct 22 15:18:25 2012
Thread 1 advanced to log sequence 16
Mon Oct 22 15:18:26 2012
ARC0: STARTING ARCH PROCESSES
Mon Oct 22 15:18:26 2012
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Mon Oct 22 15:18:26 2012
Thread 1 opened at log sequence 16
Current log# 2 seq# 16 mem# 0: /app/oracle/oradata/study/redo02_1
Current log# 2 seq# 16 mem# 1: /app/oracle/oradata/study/redo02_2
Successful open of redo thread 1
Mon Oct 22 15:18:26 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Oct 22 15:18:26 2012
SMON: enabling cache recovery
Mon Oct 22 15:18:26 2012
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=19, OS id=16066
Mon Oct 22 15:18:26 2012
Errors in file /app/oracle/admin/study/udump/study_ora_15632.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/app/oracle/oradata/study/undotbs01.dbf'
Mon Oct 22 15:18:26 2012
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 15632
ORA-1092 signalled during: alter database open...
相关trace文件如下：
$ more /app/oracle/admin/study/udump/study_ora_15632.trc
/app/oracle/admin/study/udump/study_ora_15632.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /app/oracle/product/10.2.0/db_1
System name: Linux
Node name: study
Release: 2.6.18-92.el5
Version: #1 SMP Fri May 23 22:17:30 EDT 2008
Machine: i686
Instance name: STUDY
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 15632, image: oracle@study (TNS V1-V3)
*** 2012-10-22 15:16:08.747
*** SERVICE NAME) 2012-10-22 15:16:08.746
*** SESSION ID160.3) 2012-10-22 15:16:08.746
Recovery target incarnation = 4, activation ID = 0
Influx buffer limit = 6487 (50% x 12974)
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 471823 logseq 15 block 2
*** 2012-10-22 15:16:08.835
Media Recovery add redo thread 1
*** 2012-10-22 15:16:08.840
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
*** 2012-10-22 15:16:09.891
Started resilvering redo thread 1 seq 15: blocks 2-2
*** 2012-10-22 15:16:09.934
Completed resilvering redo thread 1 seq 15
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 1.13s => 0.00 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/1 (0%)
Change moves: 0/1 (0%), moved: 0Mb
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.00073310 (471824)
----------------------------------------------
*** 2012-10-22 15:16:09.962
Media Recovery drop redo thread 1
File 1 (stop scn 471825) completed recovery at checkpoint scn 471825
File 3 (stop scn 471825) completed recovery at checkpoint scn 471825
File 4 (stop scn 471825) completed recovery at checkpoint scn 471825
ARCH: Connecting to console port...
*** 2012-10-22 15:18:25.649
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 15, block 2, scn 471823
on-disk rba: logseq 14, block 3, scn 451820
start recovery at logseq 15, block 2, scn 471823
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.01s => 0.05 Mb/sec
Total physical reads: 0Kb
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.00073310 (471824)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2012-10-22 15:18:25.721
KCRA: start recovery claims for 0 data blocks
*** 2012-10-22 15:18:25.721
KCRA: blocks processed = 0/0, claimed = 0, eliminated = 0
*** 2012-10-22 15:18:25.721
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/app/oracle/oradata/study/undotbs01.dbf'
SQL> startup
ORACLE instance started.
Total System Global Area 197132288 bytes
Fixed Size 1218484 bytes
Variable Size 71305292 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/opt/oracle/oradata/testdb/undotbs01.dbf'
SQL> create pfile from spfile;
vi pfile, add ...
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS1'
*._corrupted_rollback_segments=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SY
SSMU9$','_SYSSMU10$')
*._allow_resetlogs_corruption=true
SQL> startup pfile='/opt/oracle/admin/testdb/pfile/inittestdb.ora';
ORACLE instance started.
Total System Global Area 197132288 bytes
Fixed Size 1218484 bytes
Variable Size 71305292 bytes
Database Buffers 121634816 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/opt/oracle/oradata/testdb/undotbs01.dbf'
SQL> recover database until cancel;
SQL> alter database datafile '/opt/oracle/oradata/testdb/undotbs01.dbf' offline drop;
SQL> alter database open resetlogs;
SQL> drop tablespace undotbs1 including contents and datafiles;
SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/testdb/undotbs02.dbf' size 10m;
SQL> shutdown immediate;
vi pfile
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
SQL> create spfile from pfile;
SQL> startup force;
数据库恢复正常，但是有下列疑问？
1.为什么表t1不仅数据不存在，连表也不存在了？
SQL> desc t1;
ERROR:
ORA-04043: object t1 does not exist
2.为什么一定要采用resetlogs方式打开数据库才可以？
页:
[1]