:00000B72 15 159 10444 13 UNDO SEG (BEFORE RECOVERY): usn = 6 1313C276:00000B73 15 159 10005 4 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 1313C536:00000B74 15 159 10444 14 Rec rbs _SYSSMU7$ 1313CAEA:00000B75 15 159 10444 13 UNDO SEG (BEFORE RECOVERY): usn = 7 1313D804:00000B76 15 159 10444 14 Rec rbs _SYSSMU8$ 1313E48F:00000B7C 15 159 10444 13 UNDO SEG (BEFORE RECOVERY): usn = 8 1313E9E3:00000B7D 15 159 10005 4 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 1313ECF3:00000B7E 15 159 10444 14 Rec rbs _SYSSMU9$ 1313F050:00000B7F 15 159 10444 13 UNDO SEG (BEFORE RECOVERY): usn = 9 1313F7F2:00000B80 15 159 10444 14 Rec rbs _SYSSMU10$ 1313FEED:00000B81 15 159 10444 13 UNDO SEG (BEFORE RECOVERY): usn = 10
SYS@ora10g> select * from v$rollname; ERROR: ORA-03114: not connected to ORACLE
SYS@ora10g> conn / as sysdba; Connected to an idle instance. SYS@ora10g> startup mount ORACLE instance started.
Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 327155936 bytes Database Buffers 192937984 bytes Redo Buffers 2973696 bytes Database mounted. SYS@ora10g> select * from v$rollname; select * from V$rollname * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only
SYS@ora10g> select segment_name from dba_rollback_segs; select segment_name from dba_rollback_segs * ERROR at line 1: ORA-01219: database not open: queries allowed on fixed tables/views only
此时通过修改pfile参数文件，然后将undo_management='AUTO'改为undo_management='MANUAL'; 并添加隐含参数"_corrupted_rollback_segments"，即添加以下内容到pfile文件中： _corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$'
目的是将undo管理方式改为手动，并标记这10个默认的回滚段为损坏，然后再用该pfile启动数据库
SYS@ora10g> shutdown abort ORACLE instance shut down. SYS@ora10g> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora ORACLE instance started.
Total System Global Area 524288000 bytes Fixed Size 1220384 bytes Variable Size 327155936 bytes Database Buffers 192937984 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SYS@ora10g>
SYS@ora10g> conn zlm/zlm Connected. ZLM@ora10g> select count(*) from t1;
COUNT(*) ---------- 20
此时数据库已经能够打开，连接到非
系统用户zlm，确认新增的10条数据已经丢失
ZLM@ora10g> select * from v$rollname;
USN NAME ---------- ------------------------------ 0 SYSTEM
ZLM@ora10g> select segment_name from dba_rollback_segs;
SEGMENT_NAME ------------------------------ SYSTEM _SYSSMU1$ _SYSSMU2$ _SYSSMU3$ _SYSSMU4$ _SYSSMU5$ _SYSSMU6$ _SYSSMU7$ _SYSSMU8$ _SYSSMU9$ _SYSSMU10$
11 rows selected.
由于数据库已经正常open,现在回滚段现在已经可以查询到了
还需要将原undo表空间删除并重建，否则还是会有问题，如:非系统用户无法使用系统回滚段
ZLM@ora10g> insert into t1 select * from dba_objects where rownum<11; insert into t1 select * from dba_objects where rownum<11 * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ZLM'
ZLM@ora10g> conn / as sysdba Connected. SYS@ora10g> insert into zlm.t1 select * from dba_objects where rownum<11; insert into zlm.t1 select * from dba_objects where rownum<11 * ERROR at line 1: ORA-01552: cannot use system rollback segment for non-system tablespace 'ZLM'
--重建UNDO表空间（先删除，后创建） SYS@ora10g> show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SYS@ora10g> set line 130 pages 130 SYS@ora10g> col file_name for a80 SYS@ora10g> col tablespace_name for a15 SYS@ora10g> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME ----------------------------------------------------------------- |