[2020-12-22]undo数据文件存在活动事物时损坏会导致事物异常

1、上面有事物，没有提交，然后异常关闭

SQL> select * from t;

        ID
----------
        10

SQL>
SQL> update t set id = 11;     --//没有提交

1 row updated.

SQL> select * from t;

        ID
----------
        11

SQL> shutdown abort;
ORACLE instance shut down.

2、启动报错

SQL> startup 
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oradata/QXY/undotbs01.dbf'


SQL> 

3、offline 有问题的数据文件

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> 


SQL> alter database open;

Database altered.

SQL> 

4、查询表表提示错误

SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oradata/QXY/undotbs01.dbf'


SQL> 

5、创建新的undo并替换

SQL> create undo tablespace undotbs2 datafile '/u01/app/oradata/QXY/undotbs02.dbf' size 50m autoextend off;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU21_3175179606$' found, terminate
dropping tablespace


SQL> 

6、_corrupted_rollback_segments到pfile文件

SQL> col seg_name for a3000
SQL> set long 20000
SQL> select '*._corrupted_rollback_segments=('||wm_concat(SEGMENT_NAME)||')' seg_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEG_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*._corrupted_rollback_segments=(_SYSSMU30_1758879321$,_SYSSMU29_1563183015$,_SYSSMU28_206038335$,_SYSSMU27_3813036844$,_SYSSMU26_1516330146$,_SYSSMU25_2467098251$,_SYSSMU24_3600532437$,_SYSSMU23_3232256354$,_SYSSMU22_532275744$,_SYSSMU21_3175179606$)

SQL> 

--//创建spfile
 

create pfile='/tmp/pfile.txt' from spfile;

--//把*._corrupted_rollback_segments添加到新创建的pfile里面，然后用新pfile启动数据库

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile.txt';
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
Database opened.
SQL> 

7、再次查询表t

SQL> select * from t;

        ID
----------
        11

SQL> 

--//其实第一步的update并没有提交，但是最后查询到的数据update之后的数据，正确的数据应该是10