在线日志被删除恢复

来源：互联网发布：pes2018意甲球员数据库编辑：程序博客网时间：2024/05/23 13:25

今天一个测试库数据文件空间满了，开发人员去删除文件，结果把在线日志给全部删掉了

。

一个同事用隐藏参数加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

至此数据库正常运行，但是丢掉了一些数据。