Once you export expdb job to backup the database table this error ORA-01555: snapshot too old: rollback segment number 12 with name might raise. Why is this error raised? One of the reasons below may course in your problem. I describe each course with a solution that may suit your Oracle expdp ora-01555 Snapshot Too Old problem. Below are the courses
Why did Oracle expdp ora-01555 raise?
- UNDO_RETENTION parameter values are not enough for the export database or insufficient rollback segments.
- A corrupted row containing LOB.
- Retention is not set for the LOBs columns specifically.
If you encounter one of the courses above in your export job there will be the below error in your log. Let’s try to drill down through the courses and narrow down your problem.
-- Error messages are: ORA-31693: Table data object "SCHEMA"."TABLE_NAME_HERE" 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 12 with name “” too small
What is the Oracle expdp ora-01555 problem?
For example, one who exports expdb job at 1.00 A.M and another user making update data from another location. But Oracle must contain data that appears as it is at 1.00 A.M before changes were made by other users.
Oracle Automatic UNDO Management (AUM) plays a role in these types of situations. Oracle cannot access the original data once after starting the export job. Therefore have to keep committed versions of blocks and uncommitted blocks to keep the data at the time of export job started.
Insufficient UNDO_RETENTION and rollback segments parameters are related to the problem Oracle expdp ora-01555. If those parameters are not large enough then raise our error ora-01555 Snapshot Too Old for export jobs.
There are several solutions for the issue and let’s see how I fix the issue oracle expdp ora-01555 Snapshot Too Old. When fixing the issue you have to know about the below information.
You will see how to fetch each parameter value in the middle of the article. Now Let’s move on to the solutions that we are going to describe today. Try each fix accordingly because We are going to dig each course in some order. Below are the solutions.
Solutions for Oracle expdp ora-01555 problem
- Make sure undo table space is enough for expdb export job.
- Make sure to run the export job when there is less DML load.
- Increase the size of the undo_retention parameter value greater than the total time the export is taking.
- Set undo_retantion for the LOBs.
- Identify the Corrupted LOBS then delete or exclude or reinsert them again.
1. Make sure undo table space is enough for expdb export job
Undo tablespace sizing depends on their parameters. According to those parameters undo space change at a given time.
-- UndoSpace = UNDO_RETENTION in seconds * Undo data block per second * db_block_size --
- Select undo table space
-- SQL> select bytes/1024/1024 from v$datafile where ts#=2; BYTES/1024/1024 --------------- 36794 --
You must have enough undo table space for generating or holding UNDO operations. Otherwise, you will get an Oracle expdp ora-01555 problem. Refer to Undo tablespace from Oracle for more information.
2. Make sure to run the export job when less DML load.
This is a commonly followed practice by database experts due to peak hours make more DML operation can occur more UNDO data blocks per second. Normally run the export job on less peak time that has the minimum number of DML operations in the database.
That will reduce this error because block space does not exceed due to fewer operations that have to be handled through the UNDO block segment space.
3. Increase the size of the undo_retention parameter value.
This is the most important one of my suggested solutions that fix the Oracle expdp ora-01555 problem by increasing the undo_retention parameter, which is used to define how long to keep committing to UNDO segments. Let’s see how to increase the size of the undo_retention parameter.
- Find current undo_retention
-- SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 --
- My current undo_retention time is 15 minutes. Let’s find the longest query time to complete the operation
-- SQL> select max(maxquerylen) from v$undostat; MAX(MAXQUERYLEN) ---------------- 3605 --
In this example, the longest query takes around 60 minutes to complete the operation. But in our case, undo_retention 15 is less than compared to the maximum query completion time. there we have to increase the undo_retention parameter.
But I have to note that when increasing the undo_retention you can add another 30-minute buffer if this time can vary from time to time. If the export time is 3 hours then add the undo_retention value as 3.5 hr to fix your Oracle expdp ora-01555 problem.
- Increase undo_retention time
-- SQL> ALTER SYSTEM SET UNDO_RETENTION = 5400 scope=both; System altered. --
This will most probably fix the issue. This is my main suggestion for the problem. Let’s move on to the next solution.
4. Set undo_retantion for the LOBs
If you currently use LOB segments then you have to increase or set the same retention for the lobs specifically. Otherwise identical ORA-01555: snapshot too old can raise when you going to export EXPDP job.
- How to calculate lob retention of the table
-- SQL> select retention from dba_lobs where owner='DB_USER' and table_name='DB_TABLE'; RETENTION ---------- 900 --
- How to increase the lob retention value of the table with according to UNDO_RETENTION
-- SQL> alter table USER.TABLE modify lob(LOB_COLUMN_NAME) (pctversion 5); Table altered. SQL> alter table USER.TABLE modify lob(LOB_COLUMN_NAME) (retention); Table altered. --
- Now run the above query again that should be equals with UNDO_RETENTION
-- SQL> select retention from dba_lobs where owner='DB_USER' and table_name='DB_TABLE'; RETENTION ---------- 5400 --
Once you have done this fix according to the above steps if earlier had an issue with lobs then that should be solved. If you like that can assure by running the below query as well.
-- SQL> select column_name,pctversion,retention from dba_lobs where table_name='TBL_DATA' and owner='USER'; COLUMN_NAME PCTVERSION RETENTION ---------- ---------- ---------- LOB1 5400 LOB2 5400 --
Let’s move on to our last and least helpful solution. That will fix LOB corruption in your tables. But that may be the issue for you. Therefore need to make sure there are no LOB corruptions in your tables.
Identify the Corrupted LOBS and delete or exclude or reinsert them
Usually, Oracle expdp ora-01555 raises due to undo_retention or undo parameters. But Still problem available after completing first four steps. Then you can check whether there is any LOB corruption in the tables. If there are records like that you have to delete, exclude or reinsert again before exporting expdp job Let’s see how to find records like that.
- Run expdp export job example
-- expdp \"/ as sysdba\" tables="YOUR TABLE" directory=DROPZONE dumpfile=table.dmp logfile=table.log " --
- Identify error log contains error log like below
Once you run the export job and you end up in an error with ORA-01555: snapshot too old on LOB objects then you can closely watch the below error existing with the log.
-- ORA-31693: Table data object "YOUR TABLE" failed to load/unload and is being skipped due to error: --
The above error is not due to UNDO parameter. It’s because of corrupted data LOBs in YOUR TABLE. There for next step is to filter out the corrupted LOB datatypes.
- Filter out corrupted LOBS
--
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 YOUR_TABLE)
LOOP
BEGIN
N:=DBMS_LOB.INSTR(ROW.TEXT, HEXTORAW('889911'));
EXCEPTION
WHEN ERROR_1578 THEN
DBMS_OUTPUT.PUT_LINE(‘1578’||ROW.ROWID);
WHEN ERROR_1555 THEN
DBMS_OUTPUT.PUT_LINE(‘1555’||ROW.ROWID);
WHEN ERROR_22922 THEN
DBMS_OUTPUT.PUT_LINE(‘22922’||ROW.ROWID);
END;
END LOOP;
END;
/
--Now corrupted record will print as a output in your console. Then you can decide to remove record or repair records before run the export job. Also you can run by excluding them as well.
- Run export job excluding corrupted LOBS
-- expdp \"/ as sysdba\" tables="YOUR TABLE" directory=DROPZONE dumpfile=table.dmp logfile=table.log QUERY=\"WHERE rowid NOT IN \(YOUR CORRUPTED ROW IDS\)\" --
This is the way of fixing the Oracle expdp ora-01555 Snapshot Too Old issue. I hope this will help to identify the exact location of the problem. Add your comments and issues in the comment section.
Best Practices avoid error Oracle expdp ora-01555
- Use off-peak hours to export jobs that will reduce rollback changes.
- Avoid committing inside the loops.
- Export jobs with CONSISTENT = no parameter.
- Use large optimal rollback segments.
- UNDO tablespace in GUARANTEE mode.
- When you are in the PLSQL block commit less and reduce transaction slot reuse.
- Don’t fetch between commits on your codes.
Above are the way of reducing the raise on error Oracle expdp ora-01555. I here try to complete all types of possibilities to reduce export expdp error by using best practices or best solutions we can make in database settings.
Summary
I would like to summarize again the Solutions for oracle expdp ora-01555 problem.
- Make sure undo table space is enough for expdb export job.
- Make sure to run the export job when less DML load.
- Increase the size of the undo_retention parameter value greater than the total time the export is taking.
- Set undo_retantion for the LOBs.
- Identify the Corrupted LOBS then delete or exclude or reinsert them again.
I would like to add reference sites you can get further information related to this. Thank you.
Reference
ORA-01555: SNAPSHOT TOO OLD ERROR BECAUSE OF LOB CORRUPTION
https://www.dba-oracle.com/ora-01555_expdp.htm
ORA-01555: snapshot too old (rollback segment too small)
