oracel回退文件UNDOTBS01.DBF损坏后,oracle数据库不能装载,数据无法读取。上星期五,jh区局发生了这种情况。
发生问题后有两种处理方式:有备份的情况和没有备份的情况;
有备份的情况:
1、删除坏的UNDOTBS01.DBF,将备份的回退文件UNDOTBS01.DBF拷回原处;
2、进入SQLPLUS;
3、startup;
系统出错提示:
ORA-01157: cannot identify/lock data file 2 - see DBWR
trace file
ORA-01110: data file 2:
'/home/oracle/oradata/esal/undotbs01.dbf'
4、recover database;
系统自动恢复;
5、alert database open;
成功打开数据库;
无备份的情况:
1、删除坏的UNDOTBS01.DBF;
2、连接到数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.
错误同样
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.
...
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'
6将undotbs01离线;
SQL> alter database datafile
'/home/oracle/oradata/esal/undotbs01.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
7删除undotbs01
SQL>
drop tablespace undostb01;
Tablespace dropped.
8创建新的UNDO文件
SQL>
create undo tablespace undotbs1 datafile
'/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend
on;
Tablespace created.
9修改参数文件;
SQL> create spfile from pfile;
File created.
将参数文件修改如下:
###########################################
# System Managed Undo and Rollback
Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=undotbs01
10重新打开数据库
SQL> connect xxx/xxx@xxx as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Database mounted.
Database opened.
幸运的是,JH局有备份,情况就比较简单了,用第一种方法,10分钟就搞定了,但是时间花在了备份现场在,用了一个小时。
需要注意的是:
1、做以上操作前都要做冷备份,一但有问题至少可以恢复到问题现场点。
2、以上方法仅适用于只有UNDO表空间损坏,其它系统表和日志是好的情况。
3、出问题后先看看日志,并用SQLPLUS进命令行看看情况,不要匆忙关闭数据库重启;