Shulou(Shulou.com)06/01 Report--

When exporting database data using the data pump, the following error prompt was found:

ORA-31693: Table data object "CAMS_CORE". "BP_EXCEPTION_LOG" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing dataORA-01555: snapshot too old: rollback segment number with name "too smallORA-22924: snapshot too old"

1. View tablespace usage

SELECT UPPER (F.TABLESPACE_NAME) AS "Tablespace name", D.TOT_GROOTTE_MB AS "Tablespace size (M)", D.TOT_GROOTTE_MB-F.TOTAL_BYTES AS "used Space (M)", TO_CHAR (ROUND ((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100Pol 2), '990.99') | |'%'"usage ratio" F.TOTAL_BYTES AS "free space (M)", F.MAX_BYTES AS "maximum block (M)" FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND (MAX (BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME ROUND (SUM (DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 1

two。 When I saw the ORA-01555 error, I thought it was a classic error. Try to adjust the undo_retention parameter.

SYS@cams > alter system set undo_retention=30000 scope=both

Export again after modification, the problem still exists, obviously the problem has nothing to do with undo_retention, and then change the parameters back.

3. The guess is that there is a problem with the tablespace. Here we try to migrate the indexes and LOB under CAMS_CORE.

(1) create a new tablespace

(2) splicing tablespace migration statements. The previous article has written about the tablespace migration scheme.

(3) execute the table space migration statement

Alter table CAMS_CORE.BP_EXCEPTION_LOG move lob (EX_STACK) store as (tablespace cams_core_lob)

An error occurs when the statement is executed:

ORA-01555: snapshot is too old: fallback segment number (name ") is too small ORA-22924: snapshot is too old

Here, the problem should be more obvious, some of the LOB data is problematic.

4. Find a solution to the problem (MOS)

Use the keyword "expdp ORA-01555 ORA-22924 LOB" to find:

Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility (document ID 833635.1)

5. Refer to the solution given by MOS and deal with the problem hands-on

Set concat off create table corrupted_lob_data (corrupted_rowid rowid); set concat offdeclare error_1555 exception; pragma exception_init (error_1555,-1555); num 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_1555 then insert into corrupted_lob_data values (cursor_lob.r); commit; end; end loop; end / Enter value for table_owner: EX_STACKEnter value for table_owner: CAMS_COREEnter value for table_with_lob: BP_EXCEPTION_LOGold 6: for cursor_lob in (select rowid r, & & lob_column from & table_owner.&table_with_lob) loopnew 6: for cursor_lob in (select rowid r, EX_STACK from CAMS_CORE.BP_EXCEPTION_LOG) loopold 8: num: = dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911') New 8: num: = dbms_lob.instr (cursor_lob.EX_STACK, hextoraw ('889911')); PL/SQL procedure successfully completed.

View problematic data records:

Select * from CAMS_CORE.BP_EXCEPTION_LOGwhere rowid in (select * from CAMS_CORE.corrupted_lob_data)

There are indeed three pieces of data, and the data size of the CLOB field is obviously problematic.

The export scheme given on MOS is to exclude the problem data. Here, in order to solve the problem completely, export 3 pieces of data to csv files, and then delete them. Then export the database data again without prompting for an error.

6. Combined with the application, the origin of the problem is analyzed.

Based on the problematic data, let the developer check the application log. During the inspection, it is found that the application log at the corresponding time point is incomplete and cannot be further analyzed. At the same time, according to the time when the problem occurred, we learned that the engineer was migrating the server at that time, and as a result, the server was forced to restart (together with the application and database), resulting in some data corruption.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.