ora-600[25027][x][0] 插入大字段问题

一个朋友数据库断电后重启遭遇此问题
ORA-00600: internal error code, arguments: [25027], [22], [0], [], [], [], [], [], [], [], [], []
----- Current SQL Statement for this session (sql_id=8ywdkvd1vbrqm) -----
INSERT INTO VEH_XML_DATA (ID, JYLSH, JKID, HPHM, HPZL, CLSBDH, SCCS, ISSC, STATE, XMLDOC, HCSCCS, HCISSC, JYW) VALUES (:B7 , :B6 , '18C62', :B5 , :B4 , :B3 , 0, 0, 1, :B2 , 0, 0, :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1df9aaec0      2716  package body JCLW_DEV.PKG_JCGCSJ
0x1e3b17fb0         1  anonymous block


关于ORA-00600 25027的相关描述

ERROR: 

VERSIONS:
  versions 9.2 and above


ARGUMENTS:
  Arg [a]  Tablespace Number (TSN)
  Arg [b]  Decimal Relative Data Block Address (RDBA)



SUGGESTIONS:
  
 1. If the Arg [b] (the RDBA) is 0 (zero), then this could be due to fake indexes.

  The following query will list fake indexes:

     select do.owner,do.object_name, do.object_type,sysind.flags
     from dba_objects do, sys.ind$ sysind
     where do.object_id = sysind.obj#
     and bitand(sysind.flags,4096)=4096;

  If the above query returns any rows, check the objects involved and consider dropping them as they can cause this error. 

2. Run analyze table validate structure on the table referenced in the Current SQL statement in 
    the related trace file.

另外一个文档说明了如何判断是否是索引有问题还是INDEX有问题

1) SQL> Analyze table validate structure cascade ; 

2) query dba_indexes to get all indexes for failed table 'table_name'

    SQL> select index_name from user_indexes where table_name = 'TABLE_NAME'; 

3) validate table indexes :
    SQL > Analyze index validate structure ; 

If the analyze indicates corruption and that corruption is within an index, drop and recreate the index.

如果是0 文档说应该是索引，但是analyze 所有索引并没有问题。最后发现表中有大字段

可以根据 文档处理
Insert into table with lob fails with ora-600[25027][x][0] where x is ts# for the tablespace that has the lob.
Tracefile shows the stack function similar to:
krtd2abh  kcbgcur  ktspgfblk3  ktsplbfmb  ktsplbrecl  ktspgsp_main  kdlgsp_init  kdl_write1  kdlf_write   koklicbf  koklcre

CAUSE

The cause of this error can be LOST IO which may cause other errors like ORA-600 [kdlpdba:kcbz_objdchk] during INSERT.
The problem described in bug 13869187 is because a Block is marked as Formatted in the ASSM metadata L1 bitmap block but the block is unformatted for the LOB segment.  
The 3rd argument may not be always 0 (zero) as the problem is that if the block is unformatted, Oracle still tries to locate a pdba assuming that the block is formatted and that pdba offset may be zero when the block is empty (affected block has never formatted:block flag contains 1 - KCBHFNEW and type is zero).  If the block is formatted for a former dropped object, then the argument can be different than  zero.
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY with verify_option=>DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC and 
attrib=>DBMS_SPACE_ADMIN.BITMAPS_CHECK; however it may be canceled when visiting the first problematic block; thus may not identify all affected blocks.
Syntax example of executing the above procedure:
exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('SYS','T_C2_LOB','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK)
For more details reference Bug 18607613
SOLUTION
The error is fixed by:
recreating the table using exp-drop-import.
OR
Move the lob in a new tablespace.
Alter table move lob(&lob_column) store as (tablespace &tbsp);

进行处理,但是朋友处理的时候出现ORA-1555错误如下：
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

需要按照：
ORA-01555: snapshot too old: rollback segment number with name "" too small
 and sometimes followed by ORA-22924 error.  


ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
CAUSE


LOB data doesn't use the Undo segment for retaining the read consistent images. The old versions are stored in the LOB Segments itself, before any DMLs.


The ORA-01555 on a LOB segment is reported generally in two cases:


a) The query is accessing a LOB segment which is corrupted


OR


b) Read consistent images of the LOB data is not available in the LOB Segment. This happens due to the wrong setting of PCTVERSION / RETENTION attributes of the LOB segment.


SOLUTION


1) The first step to resolve ORA-1555 on  LOB column is to check for corruption. This is the most common case.


1.a) Create a dummy table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"


SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
1.b) Find the column names containing LOB data. You can DESCRIBE the table encountering the error and note down the columns names with datatype CLOB and BLOB.


SQL> DESC LOBDATA


Name Null? Type 
---------- --------- ------------
ID NOT NULL NUMBER 
DOCUMENT BLOB


1.c) Execute the following PL/SQL block to identify the corrupted rows. Ensure to replace and with the respective LOB column and table name.


declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
  for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
    begin
      num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
After running the above procedure, it prompts for:


Enter value for lob_column    : EMP_XML 
Enter value for table_owner   : SCOTT 
Enter value for table_with_LOB: EMP
1.d)  After executing the above procedure, the table “CORRUPTED_LOB_DATA”  will contain the rowids of the corrupted rows.


select * from corrupted_lob_data;


1.e ) If you have multiple LOB columns in the same table or want to check multiple tables, please execute the above steps again.


If there are no corrupted rows found, please skip the next step and go to Step 4.


2) Once the corruption is identified, we need to get rid of it to resolve the ORA-1555 error. Its difficult to identify the cause of the corruption unless we have the steps to reproduce the corruption at will. So, the next steps will be to salvage the data and resolve ORA-1555 error.


2.a) Restore and recover the LOB segment using physical backup.


OR


2.b) Empty the affected LOBs using the UPDATE statement


update . 
set     = empty_blob()
where  rowid in (select corrupted_rowid from   corrupted_lob_data);
commit;
Eg:


update LOBDATA set document = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);


PS: for BLOB and BFILE columns use EMPTY_BLOB() and for CLOB and NCLOB columns use EMPTY_CLOB()
 OR


2.c) Export the table without the corrupted row, like:


% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_satc.dmp LOGFILE=expdp_satc.log TABLES=tc.lobdata QUERY=\"WHERE rowid NOT IN \(\'\'\)\"
Example using DataPump export:


#> expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"


Example using conventional export:


#> exp scott/tiger file=test.dmp log=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
This step is helpful to resolve the ORA-1555 error during Export or while taking backup.


OR


2.d) Delete the corrupted rows and then get the data from any other source (like standby database, flat file, or other databases etc) , if its available.


3). After resolving the corruption, upgrade your database to the latest version (or atleast above 10.2.0.4) as there are some bugs reported in the older version, which cause LOB corruption.


If corruption is resolved, you can skip Step 4.


4) If there is no corrupted rows found, then the solution is to ensure the read consistent images are retained in the LOB segment for adequate duration. You can increase either the RETENTION or the PCTVERSION attribute of the LOB column


4.a) Till 11gR1, the RETENTION attribute of the LOB segment will be equal to the UNDO_RETENTION parameter. Hence we recommend to set UNDO_RETENTION to the maximum of the duration of the queries in the database. Check the maxquerylen:


SQL> select max(maxquerylen) from v$UNDOSTAT;
and set this value for UNDO_RETENTION parameter


alter system set UNDO_RETENTION=
4.b) From 11gR2 and above, we can set the retention for the LOB column to a value other than the UNDO_RETENTION parameter:


SQL> alter table modify lob() (retention);
Verify the same:


SQL> select retention from dba_lobs where table_name=' ';
 OR


3.c) Increase the PCTVERSION attribute of the LOB segment


SQL> alter table modify lob() (pctversion 50);
Higher values of PCTVERSION will ensure the more space is allocated for the old versions of LOB data.

可能的有大字段数据丢失


PCTVERSION


这用于控制 LOB 的读一致性。在前面的几章中，我们已经讨论了读一致性、多版本和 undo 在其中
所起的作用。但 LOB 实现读一致性的方式有所不同。lobsegment 并不使用 undo 来记录其修改；而是直
接在 lobsegment 本身中维护信息的版本。lobindex 会像其他段一样生成 undo， 但是 lobsegment 不会。
相反，修改一个 LOB 时，Oracle 会分配一个新的 CHUNK，并且仍保留原来的 CHUNK。如果回滚了事务，
对 LOB 索引所做的修改会回滚，索引将再次指向原来的 CHUNK。因此，undo 维护会在 LOB 段本身中执
行。修改数据时，原来的数据库保持不动，此外会创建新数据。 他是一个百分比


RETENTION子句


这个子句与 PCTVERSION 子句是互斥的，如何数据库中使用自动 undo 管理，就可以使用这个子句。
RETENTION 子句并非在 lobsegment 中保留某个百分比的空间来实现 LOB 的版本化，而是使用基于时间
的机制来保留数据。数据库会设置参数 UNDO_RETENTION，指定要把 undo 信息保留多长时间来保证一
致读。在这种情况下，这个参数也适用于 LOB 数据。需要注意，不能使用这个子句来指定保留时间；而
要从数据库的 UNDO_RETENTION 设置来继承它。（11GR1以前）