undo 恢复场景一之数据库正常关闭后undo丢失

1、启动报错：


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 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/app/oradata/orcl/undotbs01.dbf'



2、把数据文件offline drop后启库
//alter database datafile  offline 和offline drop本质是一样的，都是只更新控制文件，不更新数据字典。区别是前者只能归档模式下使用，后者都可以
//在非归档模式下datafile offline会报错：ORA-01145: offline immediate disallowed unless media recovery enabled



SQL> alter database datafile 3 offline drop;  //非归档模式不能offline，必须offline drop

Database altered.

SQL> alter database open;

Database altered.


4、创建新的undo表空间后删除原来的

create undo tablespace undotbs2   datafile 'E:\APP\ADMINISTRATOR\ORADATA\HNJKJG\undotbs2.dbf' size 200m autoextend on;
alter system set undo_tablespace='UNDOTBS2' scope=both;
drop tablespace undotbs3 including contents and datafiles;

5、重启测试
shutdown immediate
startup

setlinesize  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                       ONLINE
_SYSSMU9_2163261212$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU8_2784542178$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU7_2589196769$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU6_3940562857$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU5_2774485487$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU4_1510272623$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU3_1335688256$           PUBLIC UNDOTBS2                       ONLINE
_SYSSMU2_65913654$             PUBLIC UNDOTBS2                       ONLINE
_SYSSMU1_534704560$            PUBLIC UNDOTBS2                       ONLINE

11 rows selected.

//都是online就正常。