ATS is my new trading system using Ichimoku Kinkō Hyō. It took me several years to find a good Trading system that controls your Bias and teaches patients in Tradings to succeed.
Visit ->. https://www.targetradings.com/
Trade for Passion
 |
| ORA-1555 |
ORA-1555: SNAPSHOT TOO OLD DUE TO CORRUPTED LOBS
It's interesting, spotting the issue during DB/schema export backup will take quite a time :-), but if get to the point then there you go👉, During schema(s) export or querying a table containing a clob column will give 'snapshot too old' error!
ORA-01555: snapshot too old: rollback segment number with name "" too small
Fix :
Usually the ORA-01555 error is related either to the undo retention and the undo guarantee parameters, or to an inadequate size of an undo tablespace.
But there is also another case when you’ll face the same error and modifying of undo parameters or even increasing an undo tablespace size won’t help you. It happens when a column of LOB datatype has corrupted row(s).
You have several options to fix the issue: reinsert, delete, exclude or empty corrupted LOBs.
TESTCASE :
In this test case I’ll try to perform a table export where there is one corrupted row of LOB data type.
This directory will be used for a table dump
SQL> create or replace directory EXPDIR as '/oracle/dumps';
Directory created.
Here - starting the export of a table with a corrupted row of LOB data type.
[oracle@akb ~] expdp \"/ as sysdba\" tables="AKISP"."TEXT_FOR_UK" directory=EXPDIR dumpfile=table.dmp logfile=table.log
Export: Release 12.2.0.1.0 - Production on Mon Jul 8 10:48:14 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "sys/******** AS SYSDBA" tables=AKISP.TEXT_FOR_UK directory=EXPDIR dumpfile=table.dmp logfile=table.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
ORA-31693: Table data object "AKISP"."TEXT_FOR_UK" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/dumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Mon Feb 8 10:49:01 2020 elapsed 0 00:00:45
The undo retention parameter is big enough in my case and the retention guarantee is also set for the UNDO tablespace. The UNDO tablespace size is big enough too.
The reason why it happens in the AKISP.TEXT_FOR_UK table, which has corrupted row of LOB datatype
COL COLUMN_NAME FOR A15
COL DATA_TYPE FOR A15
SELECT COLUMN_NAME, DATA_TYPE FROM DBA_TAB_COLUMNS WHERE OWNER='AKISP' AND TABLE_NAME='TEXT_FOR_UK';
COLUMN_NAME DATA_TYPE
--------------- ---------------
DOCUMENT_ID NUMBER
USER_ID NUMBER
USER_NAME VARCHAR2
REQUEST_ID NUMBER
CREATED DATE
MODIFIED DATE
IS_REPLICON NUMBER
TEXT CLOB <== This column
The next steps help you out to find out all corrupted rows in the table
a. Create a new temporary table for storing all rowids of the corrupted LOBs
SQL> CREATE TABLE CORRUPTED_ROWS (CORRUPTED_ROWID ROWID, ERROR_NUMBER NUMBER);
Table created.
b. Run the following PL/SQL block to get a list of ROWID(s) for all corrupted rows in the table. It would take a while as it reads the whole table.
SET TIMING ON
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 ROW IN (SELECT ROWID, TEXT FROM AKISP.TEXT_FOR_UK)
LOOP
BEGIN
N:=DBMS_LOB.INSTR(ROW.TEXT, HEXTORAW('889911'));
EXCEPTION
WHEN ERROR_1578 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1578);
COMMIT;
WHEN ERROR_1555 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 1555);
COMMIT;
WHEN ERROR_22922 THEN
INSERT INTO CORRUPTED_ROWS VALUES (ROW.ROWID, 22922);
COMMIT;
END;
END LOOP;
END;
/
NOTE: Modify this piece of code for your needs
(SELECT ROWID, TEXT FROM AKISP.TEXT_FOR_UK)
as
(SELECT ROWID, YOUR_LOB_COLUMN FROM YOUR_OWNER.YOUR_TABLE)
c. When step b is completed, query found corrupted ROWID(s) from the table of step a.
SQL> SELECT * FROM CORRUPTED_ROWS;
CORRUPTED_ROWID ERROR_NUMBER
------------------ ------------
AKWJ9AAGAAPJTWDEC 1555
d. Now if you query rows with ROWID(s) from step c you can easily confirm existence of ORA-01555. The error arises when a corrupted row is queried.
-- Without the LOB column (TEXT)
SQL> SELECT DOCUMENT_ID FROM AKISP.TEXT_FOR_UK WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);
DOCUMENT_ID
-----------
900061435
-- With the LOB column (TEXT)
SQL> SELECT DOCUMENT_ID, TEXT FROM AKISP.TEXT_FOR_UK WHERE ROWID IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS);
ERROR:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
Next, If you exclude all corrupted ROWID(s) from an export then it becomes possible to perform a new export successfully. Use the QUERY option of the datapump utility to exclude all unwanted rows.
[oracle@akb ~] expdp \"/ as sysdba\" tables="AKISP"."TEXT_FOR_UK" directory=EXPDIR dumpfile=table.dmp logfile=table.log QUERY=\"WHERE rowid NOT IN \(SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS\)\"
Export: Release 12.2.0.2.0 - Production on Mon Feb 8 11:37:24 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Release 12.2.0.2.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=AKISP.TEXT_FOR_UK directory=EXPDIR dumpfile=table.dmp logfile=table.log QUERY="WHERE rowid NOT IN (SELECT CORRUPTED_ROWID FROM CORRUPTED_ROWS)"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.108 GB
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "AKISP"."TEXT_FOR_UK" 4.489 GB 1368214 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/dumps/table.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 8 11:38:05 2020 elapsed 0 00:00:40
REFERENCES
LOBs and ORA-01555 troubleshooting (Doc ID 846079.1)
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (Doc ID 833635.1)
Avoid Snapshot too old error for LOB Segments by RETENTION or PCTVERSION
Read consistent are retained in the LOB segment with help of parameter RETENTION or PCTVERSION. You can increase the value of RETENTION or PCTVERSION attribute of the LOB column. A high value for RETENTION or PCTVERSION may be needed to avoid ‘snapshot too old’. RETENTION uses the same value as UNDO_RETENTION value.
Meaning:
RETENTION – time-based: this specifies how long older versions are to be retained.
PCTVERSION – space-based: this specifies what percentage of the LOB segment is to be used to hold older versions.
Identified which one is used RETENTION or PCTVERSION
col object_name for a24
col PCTVERS/RETENT for a25
select b.object_name, case bitand(a.flags,32) when 32 then 'RETENTION' when 0 then 'PCTVERSION' end "PCTVERS/RETENT" from sys.lob$ a, dba_objects b where a.obj# = b.object_id and b.owner='TEST' and object_name = 'TABLE_NAME';
Upto 11gR1 released,
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:
select max(maxquerylen) from v$UNDOSTAT;
Show parameter undo_retention
Increase the undo_retention
Alter system set undo_retention=value scope=both;
After 11gR1 released,
WE can set RETENTION of LOB Segments seperatly.
Check retention for LOB segments object like table:
col owner for a8
col table_name for a20
col segment_name for a28
select owner,table_name,segment_name,pctversion,retention from dba_lobs where table_name='Table_name';
Example:
col table_name for a20
col segment_name for a28
select owner,table_name, segment_name, pctversion, retention from dba_lobs where table_name in ('EMPLOYEE');
Increase the PCTVERSION attribute of the LOB segment
alter table tablename modify lob(lob column name) (pctversion 50);
Example:
alter table Employee modify lob(Employee_picture) (pctversion 50);
Note:
1. PCTVERSION=0: the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause’snapshot too old?
2. PCTVERSION=100: the space allocated by older versions of LOB data can never be reused by other transactions. LOB data storage space is never reclaimed and it always increases.
3. Higher values of PCTVERSION will ensure the more space is allocated for the old versions of LOB data.
4. A high value for RETENTION or PCTVERSION may be needed to avoid ‘snapshot too old’.
Subscribe to:
Posts (Atom)