数据库UNDO异常及出现坏块处理V

• UNDO出现数据库坏块处理方案

• 设置manual模式使用system回滚段。

• 使用system回滚段

（1）用spfile 创建pfile，然后修改参数：

#*.undo_tablespace='UNDOTBS1'

#*.undo_management='AUTO'

#*.undo_tablespace

#*.undo_retention

undo_management='MANUAL'

rollback_segments='SYSTEM'

（2）用修改之后的pfile，重启DB

SQL> STARTUP MOUNT pfile='F:/initorcl.ora' ;

（3）删除原来的表空间，创建新的UNDO 表空间

SQL> drop tablespace undotbs;

SQL> create undo tablespace undotbs1 datafile '/u01/oradata/undotbs1.dbf' size 10M;

（4）关闭数据库，修改pfile参数，然后用新的pfile创建spfile，在正常启动数据库。

*.undo_tablespace='UNDOTBS1'

*.undo_management='AUTO'

#undo_management='MANUAL'

#rollback_segments='SYSTEM'

offline问题回滚段，重建undo，切换UNDO

SQL> shutdown immediate
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/rman/oradata/mat/undo2.dbf'
 
-- 修改参数文件pfile加上如下两个参数：
$ vi initmat.ora
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
 
-- 保存后用pfile启动
SQL> startup pfile=initmat.ora
-- 重新创建undo表空间
create undo tablespace undo1 datafile '/data/mat/undo1.dbf' size 100m
/
-- 切换undo表空间
alter system set undo_tablespace=undo1  scope=both;
drop tablespace UNDO2 including contents and datafiles;
 
-- 再次查询需要恢复的文件，已经不存在了
SQL> select * from v$recover_file;
no rows selected
-- 再次关闭数据库，并重启，沒有发现报错。
SQL> shutdown immediate
 
-- 把原来的参数进行修改，去掉隐含参数，再次启动
SQL> startup pfile=initmat.ora
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup

操作手册2

• 可以看到，损坏的文件2是undotbs01.dbf,
  查看资料，undotbs损坏或丢失时可以采用隐含参数临时启动数据库，然后进行修复。

  修改init文件,加入
  *._allow_resetlogs_corruption=true
  （注：允许在数据库文件SCN不一致的情况下启动数据库）
  *._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
  （注：允许在rollback segments损坏的情况下启动数据库）

  SQL> shutdown abort
  ORACLE instance shut down.
  SQL>  startup pfile='d:/oracle92/ora92/database/inittest.ora'
  ORACLE instance started.

  Total System Global Area   93395628 bytes
  Fixed Size                   453292 bytes
  Variable Size              75497472 bytes
  Database Buffers           16777216 bytes
  Redo Buffers                 667648 bytes
  Database mounted.
  Database opened.

  启动成功，查看下当前的rollback segments
  SQL> select segment_name,status from dba_rollback_segs;

  SEGMENT_NAME                   STATUS
  ------------------------------ ----------------
  SYSTEM                         ONLINE
  _SYSSMU1$                      NEEDS RECOVERY
  _SYSSMU2$                      NEEDS RECOVERY
  _SYSSMU3$                      NEEDS RECOVERY
  _SYSSMU4$                      NEEDS RECOVERY
  _SYSSMU5$                      NEEDS RECOVERY
  _SYSSMU6$                      NEEDS RECOVERY
  _SYSSMU7$                      NEEDS RECOVERY
  _SYSSMU8$                      NEEDS RECOVERY
  _SYSSMU9$                      NEEDS RECOVERY
  _SYSSMU10$                     NEEDS RECOVERY

  SEGMENT_NAME                   STATUS
  ------------------------------ ----------------
  _SYSSMU11$                     ONLINE

  12 rows selected.

  新建一重做表空间undo
  SQL> create undo tablespace undo datafile 'D:/oracle92/oradata/test/undo01.dbf' size 50M reuse autoe
  xtend on;

  Tablespace created.

  SQL> select segment_name,status from dba_rollback_segs;

  SEGMENT_NAME                   STATUS
  ------------------------------ ----------------
  SYSTEM                         ONLINE
  _SYSSMU1$                      OFFLINE
  _SYSSMU2$                      NEEDS RECOVERY
  _SYSSMU3$                      NEEDS RECOVERY
  _SYSSMU4$                      NEEDS RECOVERY
  _SYSSMU5$                      NEEDS RECOVERY
  _SYSSMU6$                      NEEDS RECOVERY
  _SYSSMU7$                      NEEDS RECOVERY
  _SYSSMU8$                      NEEDS RECOVERY
  _SYSSMU9$                      NEEDS RECOVERY
  _SYSSMU10$                     NEEDS RECOVERY

  SEGMENT_NAME                   STATUS
  ------------------------------ ----------------
  _SYSSMU11$                     ONLINE
  _SYSSMU12$                     OFFLINE
  _SYSSMU13$                     OFFLINE
  _SYSSMU14$                     OFFLINE
  _SYSSMU15$                     OFFLINE
  _SYSSMU16$                     OFFLINE
  _SYSSMU17$                     OFFLINE
  _SYSSMU18$                     OFFLINE
  _SYSSMU19$                     OFFLINE
  _SYSSMU20$                     OFFLINE
  _SYSSMU21$                     OFFLINE

  22 rows selected.

  SQL> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.

  修改init文件
  *.undo_tablespace=undo

  SQL>  startup pfile='d:/oracle92/ora92/database/inittest.ora'
  ORACLE instance started.

  Total System Global Area   93395628 bytes
  Fixed Size                   453292 bytes
  Variable Size              75497472 bytes
  Database Buffers           16777216 bytes
  Redo Buffers                 667648 bytes
  Database mounted.
  Database opened.

  删除损坏的undotbs1表空间：
  SQL> alter tablespace undotbs1 offline normal;

  Tablespace altered.

  SQL> drop tablespace UNDOTBS1 including contents and datafiles;

  Tablespace dropped.

  SQL>  select * from v$recover_file;

  no rows selected

  SQL> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.

  修改init文件,注释参数
  #*._allow_resetlogs_corruption=true
  #*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

  SQL>  startup pfile='d:/oracle92/ora92/database/inittest.ora'
  ORACLE instance started.

  Total System Global Area   93395628 bytes
  Fixed Size                   453292 bytes
  Variable Size              75497472 bytes
  Database Buffers           16777216 bytes
  Redo Buffers                 667648 bytes
  Database mounted.
  Database opened.
  SQL>
  至此数据库已经成功修复。

  **********************************************************************************
  需要提醒的是，在删除损坏的重做表空间时，一定要先offline，
  否则注释掉隐含参数后就会出现下面的情况。

  SQL>  startup pfile='d:/oracle92/ora92/database/inittest.ora'
  ORACLE instance started.

  Total System Global Area   93395628 bytes
  Fixed Size                   453292 bytes
  Variable Size              75497472 bytes
  Database Buffers           16777216 bytes
  Redo Buffers                 667648 bytes
  Database mounted.
  ORA-01092: ORACLE instance terminated. Disconnection forced

  查看alert log，本例中会发现下面的信息，oracle标记刚才删除的
  重做表空间需要恢复，这时就无法去掉隐含参数了。
   ......
   drop tablespace UNDOTBS1 including contents and datafiles
  SMON: about to recover undo segment 6
  SMON: mark undo segment 6 as needs recovery
  SMON: about to recover undo segment 6
  SMON: mark undo segment 6 as needs recovery
  SMON: about to recover undo segment 6
  SMON: mark undo segment 6 as needs recovery