1、插入数据
SQL> select count(*) from t1;
COUNT(*)
----------
9156
SQL> insert into t1 select * from t1;9156 rows created.
SQL> select count(*) from t1;
COUNT(*)
----------
18312SQL> shutdown abort
ORACLE instance shut down.
2、删除undo
[oracle@target target]$ rm -rf undotbs01.dbf
3、启动数据库报错
SQL> startup
ORACLE instance started.Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'
4、将undo offline
SQL> alter database datafile '/u01/app/oracle/oradata/target/undotbs01.dbf' offline drop;
Database altered.
5、开启数据库
SQL> alter database open
6、查询表报错
SQL> select count(*) from test1.t1;
select count(*) from test1.t1
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'
7、重新创建undo1
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/target/undo1.dbf' size 100m;Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace='UNDO1';System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO1
7、查询报错,查询回滚段
SQL> select count(*) from test1.t1;
select count(*) from test1.t1
*
ERROR at line 1:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/target/undotbs01.dbf'SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU10_1197734989$ NEEDS RECOVERY
_SYSSMU9_1650507775$ NEEDS RECOVERY
_SYSSMU8_517538920$ NEEDS RECOVERY
_SYSSMU7_2070203016$ NEEDS RECOVERY
_SYSSMU6_1263032392$ NEEDS RECOVERY
_SYSSMU5_898567397$ NEEDS RECOVERY
_SYSSMU4_1254879796$ NEEDS RECOVERY
_SYSSMU3_1723003836$ NEEDS RECOVERY
_SYSSMU2_2996391332$ NEEDS RECOVERY
_SYSSMU1_3724004606$ NEEDS RECOVERYSEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU20_1207334500$ ONLINE
_SYSSMU19_1240195038$ ONLINE
_SYSSMU18_1271537130$ ONLINE
_SYSSMU17_3251087833$ ONLINE
_SYSSMU16_2848467477$ ONLINE
_SYSSMU15_2454094217$ ONLINE
_SYSSMU14_1072233045$ ONLINE
_SYSSMU13_2051731998$ ONLINE
_SYSSMU12_1292947808$ ONLINE
_SYSSMU11_1337237917$ ONLINE21 rows selected.
8、spfile中写入
_offline_rollback_segments=(_SYSSMU10_1197734989$,_SYSSMU9_1650507775$,_SYSSMU8_517538920$,_SYSSMU7_2070203016$,_SYSSMU6_1263032392$ ,_SYSSMU5_898567397$,_SYSSMU4_1254879796$,_SYSSMU3_1723003836$,_SYSSMU2_2996391332$,_SYSSMU1_3724004606$)
_corrupted_rollback_segments=(_SYSSMU10_1197734989$,_SYSSMU9_1650507775$,_SYSSMU8_517538920$,_SYSSMU7_2070203016$,_SYSSMU6_1263032392$ ,_SYSSMU5_898567397$,_SYSSMU4_1254879796$,_SYSSMU3_1723003836$,_SYSSMU2_2996391332$,_SYSSMU1_3724004606$)
9、重新生成spfile
SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
SQL> create spfile from pfile='/tmp/pfile';File created.
SQL> shutdown abort
ORACLE instance shut down.
10、启动数据库t1表可以查询到结果
SQL> startup
ORACLE instance started.Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select count(*) from test1.t1;COUNT(*)
----------
18312