Home » RDBMS Server » Backup & Recovery » flashback (Oracle, 10.2.0.4.0)
flashback [message #561764] Wed, 25 July 2012 03:59 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

In my database, Undo_retention is 900 and undo tablespace size is 12 GB. Today I checked the Flashback query option in one of the table using the below query. I can able to go back upto 23rd 13:15 IST. But I am getting error if I go back 13:00 IST. So I need your help to understand the falshback concept clearly.

1. Since my undo_retention is 900 and db_flashback_retention_target value is 1440, how it is allowing me to go back untill 23rd 13:15 BST?
2. Why I am getting snapshot too old error if I go back to 13:00 BST?
3. If I add the space for UNDO, will it allow me to go back upto 13:00 BST?
4. Where it is reading the old data? is it from undo or from the archive log?


SQL> select systimestamp from dual;

SYSTIMESTAMP
--------------------------------------------------
25-JUL-12 09.18.04.683056 AM +01:00

SQL> insert into test1 (select * FROM DCA_CLA_BATCH AS OF TIMESTAMP TO_TIMESTAMP([b]'2012-07-23 13:15'[/b],'YYYY-MM-DD HH24:MI'));

1018 rows created.

SQL> rollback;

Rollback complete.

SQL> insert into test1 (select * FROM DCA_CLA_BATCH AS OF TIMESTAMP TO_TIMESTAMP([b]'2012-07-23 13:00'[/b],'YYYY-MM-DD HH24:MI'));
insert into test1 (select * FROM DCA_CLA_BATCH AS OF TIMESTAMP TO_TIMESTAMP('2012-07-23 13:00','YYYY-MM-DD HH24:MI'))
            *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20$" too small


SQL> show parameter flash

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------------------
db_flashback_retention_target        integer                          1440
SQL> show parameter undo

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



[Updated on: Wed, 25 July 2012 04:01]

Report message to a moderator

Re: flashback [message #561769 is a reply to message #561764] Wed, 25 July 2012 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some things are missing in your knowledge.
- db_flashback_retention_target has nothing to do with flashback query
- undo_retention is a target, it does not guarantee you cannot get the ORA-01555 error when trying to access some flashback data, if Oracle needs the space it will reuse some that is freeable (unless you specify the option RETENTION GUARANTEE when you created the undo tablespace).
- undo_retention does not mean it will delete all undo data older than this, if it does not need space it keeps them.

Regards
Michel
Re: flashback [message #561773 is a reply to message #561769] Wed, 25 July 2012 04:40 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
What I undertood from your answers is, I am having this table data in undo from 23rd 13:15 BST onwards. That's why I did not get the error. pls correct me if I am wrong?

Also if I add space for UNDO, will it allow me to read the old data in any case?
Re: flashback [message #561782 is a reply to message #561773] Wed, 25 July 2012 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Yes.
2/ No unless you define an appropriate undo_retention and create the undo tablespace with option RETENTION GUARANTEE.

Regards
Michel
Re: flashback [message #561794 is a reply to message #561782] Wed, 25 July 2012 06:10 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks for the input Michel.
Re: flashback [message #561859 is a reply to message #561794] Wed, 25 July 2012 16:35 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
900 seconds (15 minutes) is the default upon installation.
I usually want long running updates to not error out at 15
minutes so I unset this limit.

ECSCDAS1S > alter system set undo_retention=0 scope=both sid='*';

System altered.


You can see the amount of flashback that is actually being used by flashback logs
with the following query. In order to save more flashback logs, you have to increase
both db_recovery_file_dest_size and db_flashback_retention_target.
ECSCDAP1P > select     instance_name,name
  2  ,  floor(space_limit / 1024 / 1024) "Size MB"
  3  ,  ceil(space_used  / 1024 / 1024) "Used MB"
  4  from       v$recovery_file_dest,v$instance;

INSTANCE_N NAME               Size MB    Used MB
---------- --------------- ---------- ----------
CSCDAP1    +CCDA_FLASH_01      102400       9319

Here are my original init.ora parameters and the current parameters.
ORIGINAL_VALUE                           PARAMETER_NAME
---------------------------------------- -----------------------------
                                         db_flash_cache_file
0                                        db_flash_cache_size
+CCDA_FLASH_01                           db_recovery_file_dest
5344591872                               db_recovery_file_dest_size
1440                                     db_flashback_retention_target
900                                      undo_retention
on                                       recyclebin

CURRENT_VALUE                            PARAMETER_NAME
---------------------------------------- -----------------------------
                                         db_flash_cache_file
0                                        db_flash_cache_size
+CCDA_FLASH_01                           db_recovery_file_dest
107374182400                             db_recovery_file_dest_size
7200                                     db_flashback_retention_target
0                                        undo_retention
on                                       recyclebin
Re: flashback [message #561868 is a reply to message #561859] Wed, 25 July 2012 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can see the amount of flashback that is actually being used by flashback logs
with the following query. In order to save more flashback logs, you have to increase
both db_recovery_file_dest_size and db_flashback_retention_target.


This has nothing to do with flashback query which the question here.

Regards
Michel

[Updated on: Wed, 25 July 2012 23:18]

Report message to a moderator

Re: flashback [message #562001 is a reply to message #561868] Thu, 26 July 2012 13:58 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Michel,

On Wed, 25 July 2012 at 03:59 Jabarsathik is able to flashback to the 23rd at 13:15 which is more than 1440 minutes (24 hours) which the "db_flashback_retention_target" is set to. Jabarsathik asks as his first question why he is able to flashback longer than 24 hours.

My query shows that as long as the "Used MB" is lower than the "Size MB", Oracle is not limited by the db_recovery_file_dest_size and is not forced to purge flashback logs. Hence he is able to flashback more than one day.

In the following database I am not able to flashback one day because I am limited to the size of the db_recovery_file_dest_size.
ECSESBP1 > select     instance_name,name
  2  ,  floor(space_limit / 1024 / 1024) "Size MB"
  3  ,  ceil(space_used  / 1024 / 1024) "Used MB"
  4  from       v$recovery_file_dest,v$instance
  5  order by name;

INSTANCE_N NAME               Size MB    Used MB
---------- --------------- ---------- ----------
CSESBP1    +NFL_CS_FLASH        30720      30650

ECSESBP1 > select value from v$parameter
  2  where name='db_flashback_retention_target';

VALUE
----------------
1440

And I get errors in the alert log (along with an explanation in the alert log) like the following:
Errors in file /u01/app/oracle/admin/CSESBP/udump/csesbp1_ora_26461.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 32212254720 bytes is 100.00% used, and has 0 remaining bytes available.
Thu Jul 26 01:32:59 2012
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.

I might not have explained it but I use this query to determine if I am over-allocated and I can reduce db_recovery_file_dest_size or if I am under-allocated and I have to increase db_recovery_file_dest_size (which the alert log recommendation number 3 tells me).
Re: flashback [message #562014 is a reply to message #562001] Fri, 27 July 2012 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first part says! "I checked the Flashback query option in one of the table using the below query." so the question is about flashback query not flashback database as the question 2, 3 and 4 showed it. The mention of "db_flashback_retention_target" is just a misundertanding of this parameter.

Regards
Michel

[Updated on: Fri, 27 July 2012 01:04]

Report message to a moderator

Re: flashback [message #562094 is a reply to message #562014] Sat, 28 July 2012 00:41 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Thanks Michel,

I now see that I don't know this as well as I thought I did and will have to read more about flashback to get up to speed.

Alan
Re: flashback [message #562104 is a reply to message #562094] Sat, 28 July 2012 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many flashback features that depend on different things:
- flashback database that allows to put the database in state it was before without having to restore it and relies on flashback logs
- flashback drop that allows to retrieve a dropped table and relies on recycle bin
- flashback table that allows to put back the data in a table as they were back in the past and relies on undo data
- flashback query that allows to query the data as they were in the past and relies on undo data (and archived ones in 11g)
- flashback version query that allows to retrieve all the versions of the data of a table and relies on undo data
- flashback transaction query that allows a DBA to know the transaction stream on a table and relies on undo data.

Regards
Michel

[Updated on: Sat, 28 July 2012 03:23]

Report message to a moderator

Re: flashback [message #562261 is a reply to message #562104] Mon, 30 July 2012 18:04 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Michel,

Thanks again,

This will get me going in the right direction.

Alan
Previous Topic: Rman-0600 internal error [9018]
Next Topic: RMAN Backup oracle 10g to be restored on oracle 11g (3 merged)
Goto Forum:
  


Current Time: Thu Mar 28 17:15:03 CDT 2024