undo 恢复场景二之数据库abort后undo丢失(undo里面没未提交数据)

 非归档模式下：数据库abort后undo丢失（没业务连接）


//不需要修改undo_management和undo_tablespace 也行


1、启动报错

[oracle@test orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 7 10:56:11 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             478154376 bytes
Database Buffers          289406976 bytes
Redo Buffers                6828032 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/home/oracle/app/oradata/orcl/undotbs2.dbf'

2、把数据文件 offline drop后open库

SQL> alter database datafile  9 offline drop;

Database altered.

SQL> alter database open;


3、创建新的undo表空间后尝试删除原来的

create undo tablespace undotbs3   datafile '/home/oracle/app/oradata/orcl/undotbs3.dbf' size 200m autoextend on;
drop tablespace undotbs2 including contents and datafiles;--删除报错

//查看状态会发现段状态是"NEEDS RECOVERY"
set linesize  1000
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU10_1050530359$          PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU9_2163261212$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU8_2784542178$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU7_2589196769$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU6_3940562857$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU5_2774485487$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU4_1510272623$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU3_1335688256$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU2_65913654$             PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU1_534704560$            PUBLIC UNDOTBS2                       NEEDS RECOVERY

11 rows selected.


尝试删除会报错：

SQL>  drop rollback segment "_SYSSMU10_1050530359$";
 drop rollback segment "_SYSSMU10_1050530359$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU10_1050530359$' (in undo tablespace) not allowed



SQL>

4、删除报错解决

*.undo_management='MANUAL'
*._offline_rollback_segments=(_SYSSMU10_1050530359$,_SYSSMU9_2163261212$,_SYSSMU8_2784542178$,_SYSSMU7_2589196769$,_SYSSMU6_3940562857$,_SYSSMU5_2774485487$,_SYSSMU4_1510272623$,_SYSSMU3_1335688256$,_SYSSMU2_65913654$,_SYSSMU1_534704560$)


 startup pfile='/home/oracle/app/product/11.2.0/db_1/dbs/initorcl.ora';


//删除回滚段：

SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU10_1050530359$          PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU9_2163261212$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU8_2784542178$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU7_2589196769$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU6_3940562857$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU5_2774485487$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU4_1510272623$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU3_1335688256$           PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU2_65913654$             PUBLIC UNDOTBS2                       NEEDS RECOVERY
_SYSSMU1_534704560$            PUBLIC UNDOTBS2                       NEEDS RECOVERY


 drop rollback segment "_SYSSMU10_1050530359$";
 ...........
 drop rollback segment "_SYSSMU9_2163261212$";


set linesize  1000
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU20_396271923$           PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU19_2583787361$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU18_1195614185$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU17_2733651704$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU16_1541838185$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU15_2955322099$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU14_115047527$           PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU13_1407492751$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU12_1420694976$          PUBLIC UNDOTBS3                       OFFLINE
_SYSSMU11_3664798350$          PUBLIC UNDOTBS3                       OFFLINE

11 rows selected.

//确保状态“NEEDS RECOVERY”的都已经删除
//删除有问题的undo表空间
drop tablespace undotbs2 including contents and datafiles;

5、设置undo参数：
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS3'
#*._offline_rollback_segments  //注释或者删除掉

6、根据pfile生成spfile后启动库
create spfile from pfile;
startup
set linesize  1000
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;