今天一个测试库数据文件空间满了,开发人员去删除文件,结果把在线日志给全部删掉了
。
一个同事用隐藏参数加resetlogs来启动数据库。
备份spfile文件:
SQL> create pfile from spfile;
File created.
添加如下参数:
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
结果启动之后,数据库报600错误。过不了多久就会被SMON关掉数据库。
这个错误很明显:数据库已经open成功了,但是因为有事务不能正常被回滚,然后数据库的smon进程异常,从而使得数据库不能正常启动,解决该问题的方法也是很简单,就是常规的undo处理思路(使用人工undo管理,event屏蔽事务,隐含参数屏蔽回滚段),然后重建undo表空间,这个时候可以结合txchecker来检测是否有异常事务:如果有重要基表对象异常,需要重建库;如果是个别其他对象异常,可以通过重建该对象解决
发现是回滚段有问题
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 OFFLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS1 OFFLINE
_SYSSMU12$ UNDOTBS1 OFFLINE
_SYSSMU13$ UNDOTBS1 OFFLINE
_SYSSMU14$ UNDOTBS1 OFFLINE
_SYSSMU15$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU16$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU17$ UNDOTBS1 OFFLINE
考虑将回滚段修改为手动管理,然后用隐藏参数屏蔽掉有问题的回滚段。
--将undo段设置为手动管理
alter system set undo_management=manual scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU1$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU2$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU3$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU4$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU5$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU6$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU7$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU8$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU9$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU15$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU16$' scope=spfile;
alter system set "_corrupted_rollback_segments"='_SYSSMU9$' scope=spfile;
show parameter _corrupted_rollback_segments
对于无法屏蔽的,直接drop
drop rollback segment "_SYSSMU16$";
创建一个新的表空间
create undo tablespace undo2 datafile '/opt/u01/oradata/undo2.dbf' size 900M;
SQL> alter system set undo_tablespace=undo2 scope=spfile;
System altered.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> reate undo tablespace undotbs1 datafile '/u02/oradata/ORCL/datafile/undotbs1.dbf' size 500M;
Tablespace created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2083560 bytes
Variable Size 234882328 bytes
Database Buffers 973078528 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system set undo_tablespace=undotbs1 scope=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.
SQL> show parameters undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> SQL> alter system set undo_management=auto scope=spfile;
SQL> STARTUP FORCE;
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2083560 bytes
Variable Size 234882328 bytes
Database Buffers 973078528 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> show parameters undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
至此数据库正常运行,但是丢掉了一些数据。