This forum is currently locked. You can't register or post questions at this time. (read more)

ORA-01555: snapshot too old on 11g expdp.

All posts relating to Oracle database administration.

Moderator: Tim...

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 10:55 am

Hi Tim,
i am facing problem in production server as well as demo server with ORA-01555 error while taking logical backup using expdp..

my server:- 11.2.0.3.0 64 bit . linux 5.5

I tried with increasing undo tablespace size as well as undo_retention parameter also...

still i am facing that problem.. in production server that table contains long raw datatype column and demo server contains clob datatype column

please help me to avoid the problem

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Mon May 06, 2013 11:18 am

Hi.

If you are getting that message it means that the system is not able to hold enough undo to allow a consistent view of the data during a transaction. The only options you have are:

1) Increase the amount of UNDO, so you don't get the message.
2) Make sure your transactions complete within a time period that is acceptable for the level of undo you want to retain.

There really isn't anything more you can do than that.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 11:28 am

Hi Tim,
in that server transaction getting complete with less time only..

Undo tablespace around 45Gb is free now.
i tried with undo_retention with even 50000 also..

still i am facing problem..
problem with long raw columtype...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Mon May 06, 2013 12:06 pm

Hi.

The important bit is not how much UNDO is free now, but how much is free the split second before your operation fails. My guess would be it is pretty much zero. :) So that begs the question, what are you doing that requires 45G of undo for a single transaction to complete?

From your posts it appears you are attempting to get a consistent export when this is happening. That is not surprising if the export is of something big, since the way Oracle makes the data in the export consistent is to use UNDO for the flashback operation. If a lot of changes are being made to the database while you are attempting the export, you can easily run out of UNDO space and be forced to overwrite the existing undo, making the consistent export impossible.

Your options are:

1) Allow for more UNDO. Sounds like you don't want to do that.
2) Not do a consistent export. This will mean each table is consistent to the point in time when that object started to be exported.
3) Stop all DML on your system while you take the export.
4) Make a duplicate of your databases, then perform the export from the duplicate. That fact it is not used by any processes other than your export means it will not suffer the snapshot too old messages.

Option 4 sounds quite heavy, but if you use image copy backups and you have access to NFS, you can consider using Clonedb to fire up a quick clone based on your backups, then throw it away once your export is complete.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 1:58 pm

Hi Tim,
45GB undo we increased after we faced that issue.. even we restarted that machine and took backup.. even at that time also we faced problem.. so it won't be problem with active dml or transaction.

we are not using consistent parameter.. even i tried with that.. that time also i faced that problem..

could it be a data corruption..?

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 2:31 pm

Hi,
Please check below log ...

Code: Select all

expdp directory=datapump dumpfile=tbl_bkp_060513.dmp logfile=tbl_bkp_060513.log tables=ECS_EDI_REQUEST_ARCHIVE

Export: Release 11.2.0.3.0 - Production on Mon May 6 19:33:28 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: ICESPROD010412/ices

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "ICESPROD010412"."SYS_EXPORT_TABLE_01":  ICESPROD010412/******** directory=datapump dumpfile=tbl_bkp_060513.dmp logfile=tbl_bkp_060513.log tables=ECS_EDI_REQUEST_ARCHIVE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.686 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "ICESPROD010412"."ECS_EDI_REQUEST_ARCHIVE" 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 "ICESPROD010412"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ICESPROD010412.SYS_EXPORT_TABLE_01 is:
  /backup/tbl_bkp_060513.dmp
Job "ICESPROD010412"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 19:33:37

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Mon May 06, 2013 3:03 pm

Hi.

Even if you are not using flashback, data pump still tries to keep single tables consistent within themselves, so if there is any DML on the table, it's still going to need UNDO to keep it consistent.

I can only suggest you follow the notes I've given earlier. It comes down to allowing more undo space or using less undo space...

Note. You may get better luck if you replace your LONG and LONG RAW columns with CLOBs and BLOBs, since it is Oracle's recommendation that you don't use LONG and LONG RAW anymore. In fact, there were several issues in 10g associated with data pump not allowing import of LONG and LONG RAW columns.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Mon May 06, 2013 3:06 pm

Hi.

Maybe you could exclude the table(s) using LONG and LONG RAW, then export those separately using the old exp utility?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 3:33 pm

Hi,
Even i tried that too... i am getting same issue with old exp also...

Code: Select all


exp file=tbl_bkp_060513_exp1.dmp log=tbl_bkp_060513_exp1.log tables=ECS_EDI_REQUEST_ARCHIVE

Export: Release 11.2.0.3.0 - Production on Mon May 6 21:07:17 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Username: ICESPROD010412/ices

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table        ECS_EDI_REQUEST_ARCHIVE
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
Export terminated successfully with warnings.


Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Mon May 06, 2013 3:36 pm

Hi.

Question: I assume you are using automatic undo management and using an user other than SYS to do these exports right?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Mon May 06, 2013 3:50 pm

Hi,
Yes.. Now i am trying to export only particular table through schema name and password to reduce time for other objects backup.. initialy we faced problem while taking backup of schema through sys only...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Tue May 07, 2013 7:56 am

Hi.

And do you get better luck when you are exporting from a user other than SYS?

I do all my exports and imports from a DBA user that is not SYS or SYSTEM. I seem to remember an issue in the past where exports from SYS were limited to the single undo segment associated with the SYS user, rather than using the UNDO tablespace properly. I'm sure this was a bug and probably fixed now, but I try to use the SYS and SYSTEM users as little as possible.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mail.madhankumar
Senior Member
Posts: 87
Joined: Tue May 15, 2012 2:47 pm

Re: ORA-01555: snapshot too old on 11g expdp.

Postby mail.madhankumar » Thu May 09, 2013 2:55 pm

Hi Tim,
Thanks for Your Support.. i think Data corruption in that table... after confirmation from customer, we deleted more than 1 month old data in that table.. now we are able to take backup..

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-01555: snapshot too old on 11g expdp.

Postby Tim... » Thu May 09, 2013 2:58 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests