Home » RDBMS Server » Backup & Recovery » Flashback Table gives snapshot too old (Oracle 11.2.0.3, AIX)
Flashback Table gives snapshot too old [message #620385] Thu, 31 July 2014 17:15 Go to next message
Ricky_1362002
Messages: 104
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
Re: Flashback Table gives snapshot too old [message #620386 is a reply to message #620385] Thu, 31 July 2014 17:19 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
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.
Re: Flashback Table gives snapshot too old [message #620387 is a reply to message #620386] Thu, 31 July 2014 17:36 Go to previous messageGo to next message
Ricky_1362002
Messages: 104
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?
Re: Flashback Table gives snapshot too old [message #620388 is a reply to message #620387] Thu, 31 July 2014 17:42 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
when was the FLASHBACK TABLE statement issued?

http://docs.oracle.com/database/121/ADMQS/backrest.htm#ADMQS09432
Re: Flashback Table gives snapshot too old [message #620389 is a reply to message #620388] Thu, 31 July 2014 18:15 Go to previous messageGo to next message
Ricky_1362002
Messages: 104
Registered: February 2009
Senior Member
This morning
Re: Flashback Table gives snapshot too old [message #620390 is a reply to message #620389] Thu, 31 July 2014 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
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 

/ 

Re: Flashback Table gives snapshot too old [message #620391 is a reply to message #620390] Thu, 31 July 2014 19:09 Go to previous messageGo to next message
Ricky_1362002
Messages: 104
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 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
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?
Re: Flashback Table gives snapshot too old [message #620395 is a reply to message #620392] Thu, 31 July 2014 19:27 Go to previous messageGo to next message
Ricky_1362002
Messages: 104
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
Re: Flashback Table gives snapshot too old [message #620398 is a reply to message #620395] Thu, 31 July 2014 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
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
Re: Flashback Table gives snapshot too old [message #620407 is a reply to message #620385] Fri, 01 August 2014 01:00 Go to previous message
John Watson
Messages: 4555
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.
Previous Topic: Block corruption found during rman
Next Topic: Rman backup restore
Goto Forum:
  


Current Time: Wed Sep 17 05:05:29 CDT 2014

Total time taken to generate the page: 0.11708 seconds