OraFAQ Forum: Backup & Recovery » Flashback Table gives snapshot too old

Home » RDBMS Server » Backup & Recovery » Flashback Table gives snapshot too old (Oracle 11.2.0.3, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Flashback Table gives snapshot too old [message #620385]

Thu, 31 July 2014 17:15

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

Hi,

We have Flashback enabled on our Oracle 11.2.0.3 database, AIX.

We had a strange issue:

Here are our parameter settings:

SQL> show parameter undo 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
undo_management string AUTO 
undo_retention integer 900 
undo_tablespace string UNDOTBS1 

We have 20GB + for Undo tablespace 

SQL> show parameter flashback 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
db_flashback_retention_target integer 2880

We had a TEST table with about 1000 rows.

1- we deleted some rows, committed 2014-07-29 19:00:00
2- updated some rows, committed 2014-07-29 19:10:10
3- and then the Flashback

FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP('2014-07-30 19:01:00', 'YYYY-MM-DD HH24:MI:SS');

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P006, instance lnx01:TEST1 (1)
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_2588170940$" too small

FLASHBACK TABLE FWACBI_RAW.TEST2 TO TIMESTAMP TO_TIMESTAMP('2014-07-30 19:11:00', 'YYYY-MM-DD HH24:MI:SS');
Flashback complete.

Why do I receive the snapshot too old error when I have enough UNDO.

FYI: There is no other transaction on the Database, no load at all.

Please advise.

Thanks,
Rick

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620386 is a reply to message #620385]

Thu, 31 July 2014 17:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>FYI: There is no other transaction on the Database

I bet you Big Dollars & give you big odds that the statement above is not correct.
Do you realize & understand that the Oracle background processes regularly issue DML & COMMIT as part of keeping the database operational?

you can use DBMS_LOGMNR to identify all the COMMITs which occur.

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620387 is a reply to message #620386]

Thu, 31 July 2014 17:36

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

Thank you BlackSwan for the quick reply.

Well, I meant to say that there isn't any load on the database for the past 4-5 days, for me to do the flashback test.

So, do you mean that even when there is nothing happening, Oracle background processes use the Undo, and the space might have been filled in there?
And yes I understand that there may be some commits occurring internally, but does that use 20GB + from the Undo?

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620388 is a reply to message #620387]

Thu, 31 July 2014 17:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when was the FLASHBACK TABLE statement issued?

http://docs.oracle.com/database/121/ADMQS/backrest.htm#ADMQS09432

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620389 is a reply to message #620388]

Thu, 31 July 2014 18:15

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

This morning

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620390 is a reply to message #620389]

Thu, 31 July 2014 18:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ricky_1362002 wrote on Thu, 31 July 2014 16:15

This morning

This certainly narrows it down to some arbitrary 24 hour window, since it is always morning some place on this planet.

How much wall clock time elapsed between the DML and FLASHBACK TABLE?

post result from SQL below

WITH rollup 
     AS (SELECT To_char(first_time, 'YYYY-MM-DD HH24') HOUR, 
                Count(*) 
         FROM   v$log_history 
         GROUP  BY To_char(first_time, 'YYYY-MM-DD HH24') 
         ORDER  BY 1 DESC) 
SELECT * 
FROM   rollup 
WHERE  ROWNUM < 100 

/

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620391 is a reply to message #620390]

Thu, 31 July 2014 19:09

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

SQL> WITH rollup
     AS (SELECT To_char(first_time, 'YYYY-MM-DD HH24') HOUR,
  2    3                  Count(*)
  4           FROM   v$log_history
  5           GROUP  BY To_char(first_time, 'YYYY-MM-DD HH24')
  6           ORDER  BY 1 DESC)
  7  SELECT *
  8  FROM   rollup
  9  WHERE  ROWNUM < 100 ;

HOUR            COUNT(*)
------------- ----------
2014-07-31 15          1
2014-07-31 14          3
2014-07-31 12          9
2014-07-31 04          3
2014-07-30 14          3
2014-07-30 04          3
2014-07-29 19          3
2014-07-29 04          3
2014-07-28 14          3
2014-07-28 04          3
2014-07-27 14          3

HOUR            COUNT(*)
------------- ----------
2014-07-26 14          3
2014-07-26 04          3
2014-07-25 14          3
2014-07-25 04          3
2014-07-24 04          6
2014-07-23 04          6
2014-07-22 04          6
2014-07-21 11          5
2014-07-21 04          6
2014-07-19 06          3
2014-07-19 04          3

HOUR            COUNT(*)
------------- ----------
2014-07-18 14          3
2014-07-18 11          4
2014-07-18 04          6
2014-07-17 13          5
2014-07-17 11         16
2014-07-16 17         11
2014-07-16 14          6
2014-07-16 04          6
2014-07-15 04          6
2014-07-14 04          6
2014-07-12 08          3

HOUR            COUNT(*)
------------- ----------
2014-07-12 04          3
2014-07-11 17          7
2014-07-11 15          4
2014-07-11 09          1
2014-07-11 04          6
2014-07-10 04          6
2014-07-09 04          6
2014-07-08 04          6
2014-07-07 04          6
2014-07-05 08          3
2014-07-05 04          3

HOUR            COUNT(*)
------------- ----------
2014-07-04 04          6
2014-07-02 20          6
2014-07-01 20          6
2014-06-30 20          6
2014-06-29 00          3
2014-06-28 20          3
2014-06-27 20          6
2014-06-26 20          6
2014-06-26 10          3
2014-06-25 22          7
2014-06-25 17          3

HOUR            COUNT(*)
------------- ----------
2014-06-24 20          6
2014-06-23 20          6
2014-06-22 00          3
2014-06-21 20          3
2014-06-20 20          6
2014-06-20 16          7
2014-06-20 14          3
2014-06-19 20          6
2014-06-18 20          6
2014-06-17 20          6
2014-06-16 20          6

HOUR            COUNT(*)
------------- ----------
2014-06-14 23          3
2014-06-14 20          3
2014-06-13 20          6
2014-06-12 20          6
2014-06-11 20          6
2014-06-10 20          6
2014-06-09 20          3
2014-06-09 14          2
2014-06-09 13          2
2014-06-09 12          3
2014-06-09 11          1

HOUR            COUNT(*)
------------- ----------
2014-06-07 22          3
2014-06-07 20          3
2014-06-06 20          6
2014-06-05 20          6
2014-06-04 20          6
2014-06-04 17          4
2014-06-03 20          6
2014-06-02 20          6
2014-05-31 20          6
2014-05-30 20          6
2014-05-29 20          6

HOUR            COUNT(*)
------------- ----------
2014-05-28 20          6
2014-05-27 20          6
2014-05-27 12          3
2014-05-27 07          1
2014-05-16 09          6
2014-05-14 10          3
2014-05-13 14          1
2014-05-13 09          3
2014-05-08 07          3
2014-05-07 04          3
2014-05-06 21         13

99 rows selected.

[Updated on: Thu, 31 July 2014 19:13]

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620392 is a reply to message #620391]

Thu, 31 July 2014 19:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

In which hour did the DML occur?
In which hour did the FLASHBACK statement occur?

how big are each of the REDO log files?

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620395 is a reply to message #620392]

Thu, 31 July 2014 19:27

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

Quote:

In which hour did the DML occur?

07/29/2014 19:01

Quote:

In which hour did the FLASHBACK statement occur?

07/31/2014 12:14

Quote:

how big are each of the REDO log files?

5 GB

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620398 is a reply to message #620395]

Thu, 31 July 2014 19:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

How many REDO log file switches occurred between 07/29/2014 19:01 and 07/31/2014 12:14?
BTW - by modifying my SQL query Oracle will report exactly the number of archived REDO logfiles exists for the period of time above

>5 GB
so it would take no more than 5 REDO log file switches to consume the whole 20GB UNDO

I contend that ORA-01555 is legitimate & if you disagree the submit Service Request with My Oracle support

Report message to a moderator

Re: Flashback Table gives snapshot too old [message #620407 is a reply to message #620385]

Fri, 01 August 2014 01:00

John Watson
Messages: 8968
Registered: January 2010
Location: Global Village

Senior Member

The db_flashback_retention_target is nothing to do with the undo based flashback that you are testing. You would to set set your undo_retention for that, with an undo tablespace the size of Jupiter to use flashback table over two days.

Report message to a moderator

Previous Topic:	Backup fails
Next Topic:	Rman backup restore

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 22:19:17 CDT 2025