删除online日志测试及ORA-600 [4194]错误的处理 - 数据库编程

loc='ksasnd' id1=0 id2=0 name=? type=0
13139B3C:00000B6A? ? 15? 159 10444? 14 Rec rbs _SYSSMU5$
13139E47:00000B6B? ? 15? 159 10444? 13 UNDO SEG (BEFORE RECOVERY): usn = 5
1313B8FB:00000B71? ? 15? 159 10444? 14 Rec rbs _SYSSMU6$
1313BE12: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.

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

删除online日志测试及ORA-600 [4194]错误的处理(九)