oracle 9i回滚断表空间数据文件损坏恢复方法：
1 删除undotbs01.dbf
2 连接到数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/esal/undotbs01.dbf'
3 查看rollback_segments
SQL> show parameter rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
max_rollback_segments integer 37
rollback_segments string
transactions_per_rollback_segment integer 5
4 修改初始化参数
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800
undo_tablespace=UNDOTBS01
rollback_segments='SYSTEM'
5 启动数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/esal/undotbs01.dbf'
SQL> alter database /home/oracle/oradata/esal/undotbs01.dbf' offline;
alter database /home/oracle/oradata/esal/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database datafile '/home/oracle/oradata/esal/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> create spfile from pfile;
File created.
SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;
create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists
SQL> drop tablespace undtotbs1;
drop tablespace undtotbs1
*
ERROR at line 1:
ORA-00959: tablespace 'UNDTOTBS1' does not exist
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS01
SQL> alter system set undo_management=auto scope=both;
alter system set undo_management=auto scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set undo_management=auto scope=spfile;
alter system set undo_management=auto scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
#rollback_segments='SYSTEM'
"initxzh.ora" 99L, 2989C written
[oracle@WWW2 dbs]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 5 13:17:17 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/oracle@xzh as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 336662768 bytes
Fixed Size 450800 bytes
Variable Size 117440512 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
