Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555) (Doc ID 253131.1)
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 9.2.0.3 [Release 9.2]Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Symptoms
Due to unpublished bug 2643723 in the space allocation algorithm, concurrent writes to
the same LOB segment may cause LOB data corruption. One session may
overwrite blocks in the LOB segment that have just been allocated and
written to by another session, which would cause a block to be
allocated to 2 different LOBs - of which one will become corrupted.
This issue only happens in the event of a race condition where process
A needs a rollback segment extended while trying to write to a LOB
segment, and process B tries to write to the LOB segment at the same time.
Both sessions will act as if the LOB write was completed succesfully,
but subsequent reads of the affected LOB will fail with
ORA-01555: snapshot too old: rollback segment number with name "" too small.
This issue only affects LOB segments that have been created in locally
managed tablespaces that make use of Auto Segment Space Management (ASSM).
The problem described here can affect customers making use of LOB segments in
tablespaces with Auto Segment Space Management (ASSM).
To verify if you have LOB segments that could be affected,
execute the following select statement as a DBA user (system / sys ):
SQL>; select dbal.owner,dbal.table_name,dbal.column_name,dbat.tablespace_name
from dba_lobs dbal,dba_segments dbas,dba_tablespaces dbat
where dbal.segment_name=dbas.segment_name
and dbas.tablespace_name=dbat.tablespace_name
and dbat.segment_space_management='AUTO';
The following procedure can be used to determine if any LOB segments
have been corrupted by reading all LOBs from a particular table LOB column:
NOTE: Please ensure no other sessions are accessing LOB segment while running
this procedure to avoid the possibility of normal ORA-1555 errors.
E.g. shutdown the listener to avoid connections or use a
'startup restrict' to prohibit access to non-admin users.
NOTE: This procedure was written to check tables with BLOBs. To verify
tables with CLOBs, replace the following two lines
buffer RAW(32767);
this_lob BLOB;
with
buffer VARCHAR2(32767);
this_lob CLOB;
CREATE OR REPLACE PROCEDURE SYSTEM.READLOBSFROMTABLE (lob_owner VARCHAR2, lob_table_name VARCHAR2, lob_column_name VARCHAR2)IS
buffer RAW(32767);
this_lob BLOB;
amt BINARY_INTEGER := 32767; -- Read Size
pos INTEGER := 1; -- Position within LOB
TYPE LobCurTyp IS REF CURSOR;
lob_cv LobCurTyp;
this_rowid ROWID;
sql_stmt VARCHAR2(2000);
errors_encountered INTEGER := 0;
lob_size INTEGER := 0;
total_lobs_read INTEGER := 0;
BEGIN
dbms_output.enable(1048576); -- Set output buffer to 1 MB
-- Only retrieve LOBs which have non-zero size
sql_stmt := 'SELECT rowid, '||lob_column_name
|| ' FROM ' || lob_owner||'.'||lob_table_name
|| ' WHERE ' || lob_column_name || ' IS NOT NULL '
|| ' AND DBMS_LOB.GETLENGTH(' || lob_column_name || ') != 0 ';
OPEN lob_cv FOR sql_stmt;
LOOP
FETCH lob_cv INTO this_rowid, this_lob;
EXIT WHEN lob_cv%NOTFOUND;
lob_size := DBMS_LOB.GETLENGTH(this_lob);
amt := 32767;
pos := 1;
total_lobs_read := total_lobs_read + 1;
BEGIN
LOOP
DBMS_LOB.READ (this_lob, amt, pos, buffer);
pos := pos + amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN null; -- Normal end of LOB reached
WHEN OTHERS THEN
-- Abnormal error; if ORA-01555
-- and SEGMENT_SPACE_MANAGEMENT = 'AUTO'
-- and no other LOBs are being read from the system,
-- then this LOB may have been affected
-- by data corruption bug 2643723
BEGIN
errors_encountered:=errors_encountered+1;
dbms_output.put_line('Error encountered at rowid '||
this_rowid||': '||substr(SQLERRM,1, 100));
END;
END;
END LOOP;
dbms_output.put_line('Total Lobs Read : '||total_lobs_read);
dbms_output.put_line('Total Errors Encountered: '||errors_encountered);
END;
/
Example:
SQL> set serveroutput on
SQL> exec system.readlobsfromtable('SCOTT','MYLOBTABLE','LOBCOLUMN');
Total Lobs Read : 1374
Total Errors Encountered: 0
PL/SQL procedure successfully completed.
Customers running into this bug may receive errors when attempting
to access LOBs that have been (partially) overwritten:
ORA-01555: snapshot too old: rollback segment number with name "" too small.
When the LOB blocks are freed (as a result of deleting or updating the
affected LOBs) customers may encounter the following error indicating
that there is a duplicate row in the LOB index free list:
ORA-00600 internal error code, arguments: [7999], [9], [], [], [], [], [], []
Changes
Cause
Solution
Fix
~~~~~~
- Create a tablespace with 'SEGMENT SPACE MANAGEMENT MANUAL' and move/recreate
the lobsegment in the new tablespace. Export/Import could be used for this step.
- Create or use a Dictionary-managed tablespace for the LOB segments.
NOTE: LOB segments can be moved to another tablespace using the following SQL syntax:
ALTER TABLE <name>
MOVE LOB(<lob_name>)
STORE AS (TABLESPACE <new_tablespace_name>
STORAGE (initial <new_initial_size> next <new_next_size>))
This issue is fixed in the
Oracle Server PatchSet 9.2.0.4 (PatchID 3095277)
Oracle Server 10g
NOTE: the 9.2.0.4 patchset prevents further occurrances of this bug.
Customers who encountered an ORA-1555 and believe they have
a corrupted LOB segment should contact an Oracle Support
representative to resolve the issue.
References
~~~~~~~~~~| Bug:2643723 AQ PROPAGATION OF LOB ENCOUNTERED "ORA-01555: SNAPSHOT TOO OLD" AND ORA-00600 [This section is not visible to customers.] |
Note:2643723.8 Support Description of Bug 2643723
_
KEYWORDS: ALERTINFO GENERIC SPACEINFO
Comments
Post a Comment