I did notice SYS.SMON_SCN_TIME
A reader, October 14, 2002 - 4:02 pm UTC
Hi
I did see this sys table. Ok then the question is solved, we have to wait for around 5 minutes. My other question is when I tried to use enable_at_time in dbms_flahsback I always have to put the timestamp from SYS.SMON_SCN_TIME, if I put it any other time then it does not work, is this expected behaviour?
I doubt we would use SCN for dbms_flashback, if ever anything happens user would tell us the time not the SCN right?
October 14, 2002 - 7:41 pm UTC
I think you would almost ALWAYS use SCN for flashback.
Think about when a PROGRAM might want to flashback.... to join the data before an update with the data after the update.... -- get the SCN, update, join. No time there.
Or maybe to UNDO an update AFTER the user said "ok" 5 times on the web... You want to update the data back to what it was -- well, YOU would have saved the SCN for them right before the update.
Think about when you might want to flashback.... After finding the egregious statment in the logs (which will be accompanied by... SCN)
Timed based flashback is almost of academic or demo interest... Yes, the use might tell us to flashback but your program will remember points of interest to them and capture the SCN.
This:
ops$tkyte@ORA920.LOCALHOST> exec dbms_flashback.enable_at_time( sysdate-1/24 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.LOCALHOST> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.LOCALHOST> exec dbms_lock.sleep(15);
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.LOCALHOST> exec dbms_flashback.enable_at_time( sysdate-1/24 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.LOCALHOST> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
shows that you need not pick an exact time -- I flashed an "hour" back but neither of those times (at least ONE OF THEM anyway) were in the scn table.
just another thing
A reader, October 14, 2002 - 4:06 pm UTC
Hi again
I noticed you have not used dbms_flashback to enable or disable flashback, instead you used
from .... , emp of SCN xxx
is that new in 9i too :-?
October 14, 2002 - 7:42 pm UTC
Yes, that is new in "9i release TWO" (play on words ;)
sorry to bother again but
A reader, October 15, 2002 - 2:03 am UTC
Hi
I would like know if timestamp from SYS.SMON_SCN_TIME must be used...
October 15, 2002 - 8:06 am UTC
I thought that I showed -- NO, it does not.
So, I'll just say it, NO, it does not. We will find the time CLOSEST to the time you asked for and use the SCN from there.
flashback
Reader, June 10, 2003 - 7:44 pm UTC
Tom, I tried your example but i think i am making some mistake somewhere but could not figure it out what ithe mistake is. This example you gave in another thread but for some reason when i searched asktom i could not locate it so i am posting it in this thread. Kindly help. Thanks. I am using 9.2.0.
SQL> connect system/manager;
Connected.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS
SQL> connect hr/hr;
Connected.
SQL> create table employee_test as select * from employees
2 ;
Table created.
SQL> update employee_test set salary = salary * 1.5;
107 rows updated.
SQL> commit;
Commit complete.
SQL> pause
SQL> select a.last_name, a.salary new_sal, b.salary old_sal
from employee_test a, employee_test as of timestamp sysdate-5/24 b
where a.employee_id = b.employee_id
/ 2 3 4
from employee_test a, employee_test as of timestamp sysdate-5/24 b
*
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed
June 10, 2003 - 8:13 pm UTC
you have to wait a bit before flashing back on a newly created object.
create the table, get a cup of coffee (take about 5 minutes please) and then come back.
correction
Reader, June 10, 2003 - 7:46 pm UTC
In the above, i wanted to mention that in your example, you used emp table in scott schema.
Timing of queries before and after flashback
Eric Peterson, February 24, 2004 - 5:33 pm UTC
The time it takes to do the same query before and after a flashpoint seems to be widely different (1 minute versus 9 minutes). When I ran this the system was not under any great load nor had many users.
Is there something I need to take into account when running queries after issuing a flashback? What would explain the vast difference in the timing of the same query? Is this something I should expect when using this package?
We are on HPUX 11 and Oracle 9.2.0.4. This table (as most of the data on this machine) is on a 10 minute snapshot from a different Oracle instance.
Thanks for all the great help you provide.
Eric
epeters> SELECT
2 MAX ( creation_date ) AS max_dt
3 FROM
4 subscriber;
MAX_DT
--------------------
24-FEB-2004 11:26:30
1 row selected.
Elapsed: 00:01:105.70
epeters> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME ( TO_TIMESTAMP ( '23-FEB-2004 16:00:00', 'DD-MON-YYYY HH24:MI:SS') );
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.50
epeters> SELECT
2 MAX ( creation_date ) AS max_dt
3 FROM
4 subscriber;
MAX_DT
--------------------
23-FEB-2004 15:43:52
1 row selected.
Elapsed: 00:09:597.53
February 25, 2004 - 7:38 am UTC
You flashed back 1 day -- 24 hours.
Also, that it took over one minute to get the result in the first place indicates to me "probable full scan of really big table"
Well, every change -- every single change that occurred in the last 24 hours to that table would have to be undone.
So, lets take a 3.5 million row table and do that max on an unindexed date field:
big_table@ORA920PC> alter system set sql_trace=true;
Session altered.
big_table@ORA920PC> select max(last_ddl_time) from big_table;
MAX(LAST_
---------
09-JUL-03
1 row selected.
big_table@ORA920PC> a as of timestamp sysdate-1
1* select max(last_ddl_time) from big_table as of timestamp sysdate-1
big_table@ORA920PC> /
MAX(LAST_
---------
09-JUL-03
1 row selected.
And tkprof says:
select max(last_ddl_time) from big_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 13.56 16.02 50526 50541 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 13.56 16.04 50526 50541 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=50541 r=50526 w=0 time=16020097 us)
3500000 TABLE ACCESS FULL BIG_TABLE (cr=50541 r=50526 w=0 time=10404911 us)
********************************************************************************
select max(last_ddl_time) from big_table as of timestamp sysdate-1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.01 0.13 1 9 0 0
Fetch 2 13.43 14.71 50526 50541 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 13.44 14.88 50527 50550 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=50541 r=50526 w=0 time=14719081 us)
3500000 TABLE ACCESS FULL OBJ#(39083) (cr=50541 r=50526 w=0 time=9631509 us)
showing that hey -- if there isn't anything to undo, this flashback stuff has no effect on runtime performance.
Which makes sense since EVERY SINGLE QUERY in Oracle is a flashback query in effect -- always has been.
A flashback query differs not a bit from
a) open query on monday night, go home
b) come back tuesday night and fetch a row
In your case, over the course of 24 hours, enough work has in fact been done to that table to cause it to have to perform lots of work in order to undo the changes. Also, it is most likely that the UNDO needed from a couple of hours ago would no longer be in the buffer cache (checkpointed hours and hours ago) so you would see lots of additional PIO's to get this.
You can use SQL_TRACE as I did to see whats up.
very informative
Eric Peterson, February 25, 2004 - 4:35 pm UTC
Excellent as always.
Thanks much
Eric
Coding flashback in applications
AR, November 19, 2004 - 1:53 pm UTC
Tom,
You said
<quote>
I think you would almost ALWAYS use SCN for flashback.
Think about when a PROGRAM might want to flashback.... to join the data before an update with the data after the update.... -- get the SCN, update, join. No time there.
</quote>
I'm just trying to think this through. Are you essentially saying, you MUST store/map corresponding SCNs if you use flashback queries in the application? Say I was designing an application for a stock broker. At 10:00:00 I bought 100 shares of PSFT at a generous $30. Another 200 PSFT at $30.01 at 10:00:01. Then 300 PSFT at $30.02 at 10:00:02. At the end of day I want to calculate account totals. So I better store SCNs at times 10:00:00, 10:00:01, 10:00:02 in some table, in order to achieve this? (assuming I didn't explicitly store historical/realtime stock ticker price information in a separate table).
Thank you for your time.
PS : Flashback would probably not be very wise for such an application (??)..but I just cited it as an example.
November 19, 2004 - 2:24 pm UTC
since flashback is only "time sensitive" down to about 3 seconds in 10g and 5 MINUTES in 9i...
but in your case, I don't see where or why you would be using flashback? you have 3 rows with timestamps, you would just "query". I don't see flashback being an appropriate technology for your question at all.
SMON_SCN_TIME
Reader, January 24, 2005 - 10:47 pm UTC
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> select count(*) from smon_scn_time ;
COUNT(*)
----------
1700
Tom ,
1. Why is this showing me 1700 records instead of 1440 ?
2. In the flashback area -- what are these files ?
Where can I read more about them ?
./flashback
./flashback/o1_mf_0zbdw5xb_.flb
./flashback/o1_mf_0zbkh86o_.flb
./flashback/o1_mf_0zbnyx91_.flb
./flashback/o1_mf_0zbrjhb0_.flb
./flashback/o1_mf_0zbvz62m_.flb
./flashback/o1_mf_0zbynogt_.flb
./flashback/o1_mf_0zc20h8k_.flb
./flashback/o1_mf_0zc3y2nb_.flb
./flashback/o1_mf_0zc7k9d9_.flb
./flashback/o1_mf_0zcbtscf_.flb
./flashback/o1_mf_0zcfvx6x_.flb
./autobackup
./autobackup/2005_01_24
./autobackup/2005_01_24/o1_mf_s_548422894_0zbdw73k_.bkp
Thanks ,
January 25, 2005 - 9:07 am UTC
you'll find that table to be radically different between 9i and 10g (to allow for a 3 second resolution on the flashback times, instead of +- 5 minutes...)
those flashback files are the flashback data (checkpointed pre-images of blocks and other accounting information). there is nothing to read about -- you setup the flashback recovery area, tell us how much space we can use and we keep what we can to allow you to flashback as far as we can (as directed by you with the flashback retention). We might not be able to flashback that far (as you requested) if the space is not large enough.
the autobackup i believe was configured by you -- looks like it could be the rman autobackup of a controlfile/spfile.
Flashback
reader, January 25, 2005 - 5:25 pm UTC
1. Can I conclude that the max number of records in smon_scn_time can now be upto 3600/3(per hour) * 24 * 5 instead of 60/5 (per hour) * 24 * 5
2.If I have allocated enough space then can I flashback a table even after 5 days .?
3.The files which I see in the flash back area are from dumps of undo segments ?How can one control the individual size of these files.
Thanks ,
January 25, 2005 - 7:22 pm UTC
1) no, they are stuffing data into a big raw field there, it isn't that simple anymore.
2) 5 days of UPTIME is still the limit (eg: if you open the database for an hour every day and have it closed the other 23.... you can do 5*24 days ;)
3) you control the size of the area (they are not just undo blocks in there). the files are managed by the database.
Flash Area vs Flash Back Database
Reader, May 31, 2005 - 11:16 pm UTC
I presume both are different and have nothing to do with each other .Pl clarify
Thanks
June 01, 2005 - 8:52 am UTC
the flash recovery area is where data used by flashback database (and other recovery options) is stored.
So, flashback database is a "feature", a "function", a "thing you can do"
flash recovery area is a "place"
Retaining the flashbacked data
Yogesh, October 05, 2005 - 1:23 pm UTC
Small example of dbms_flashback,
SQL> select * from test;
NAME SAL
---------- ----------
abc 1000
pqr 2000
xyz 200
kkk 777
execute dbms_flashback.disable;
SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
361134
SQL> delete from test where sal = 777;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from test;
NAME SAL
---------- ----------
abc 1000
pqr 2000
xyz 200
declare
restore_scn date;
begin
select date_scn_tracking into restore_scn from keep_date;
dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER(361134);
end;
/
SQL> select * from test;
NAME SAL
---------- ----------
abc 1000
pqr 2000
xyz 200
kkk 777
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 5 18:18:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect flash/gordon
Connected.
SQL> select * from test;
NAME SAL
---------- ----------
abc 1000
pqr 2000
xyz 200
SQL>
Same thing happens even if I use ENABLE_AT_TIME.
How to preserve the flashbacked data?
October 05, 2005 - 1:46 pm UTC
I don't know what you mean by that? Everything seems to be working dandy?
(you don't need to enable/disable flashback in 9.2 and above, you can use "from t as of SCN nnnnn" instead
Retaining the flashbacked data
Yogesh, October 06, 2005 - 5:18 am UTC
I think I was not clear enough.
Lets say I removed 100 rows for a specified criterion. By using flashback I back to past, where I can see my old data. I want to get that data back in present.
What should I do? Use cursor for inserting that data back?
Because CTAS or Insert as select does not work in flashback mode.
I hope I'm clear this time.
October 06, 2005 - 7:52 am UTC
insert as select works in flashback mode???!?!? sure it does.
in 10g, you would use flashback table - to restore the table the way it was at whatever time.
in 9ir2, you can use insert as select from t AS OF <scn|timestamp> to get back data.
Retaining the flashbacked data
Yogesh, October 06, 2005 - 9:18 am UTC
I checked,
insert into new_test select * from test as of scn <>; works.
But when I was trying same thing with dbms_flashback, like
declare
restore_scn date;
begin
select date_scn_tracking into restore_scn from keep_date;
dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER(399516);
end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from test;
NAME SAL
---------- ----------
abc 1000
pqr 2000
xyz 200
ppp 77
SQL> insert into new_test select * from test;
insert into new_test select * from test
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
It this a wrong approach?
October 06, 2005 - 11:49 am UTC
you cannot do modifications with flashback enabled that way - those procedures (enable/disable) can be considered obsolete in 9ir2 and above - not needed, very restrictive.
Kirti Deshpande, October 06, 2005 - 11:30 am UTC
That's becauase DML is not allowed in a 'flashed' back session (using dbms_flashback). The 'AS OF' option SQL introduced in 9i R2 is precisely to overcome that limitation.
- Kirti
Yogesh, October 10, 2005 - 6:05 am UTC
Thanks a lot Tom & kirti
help me
jxau, December 05, 2005 - 12:50 am UTC
why can't i get the data about 4 days ago ?
My Sql is :
select b.* from
(select * from test
as of timestamp(systimestamp-interval '4' day)) b
The Error infomation is
snapshot too old : rollback segment number 8 with name "_syssmu8$" too small
What can i do to get the data ??
thank you tom !
December 05, 2005 - 12:57 am UTC
well, you either
a) did not have the undo_retention set to 4 days
b) you did, but you didn't permit us to grow the undo tablespace (you didn't allocate sufficient disk space for 4 days of undo to be held or you didn't let the files autoextend)
4 days is rather LONG to be reaching back, the theoretical maximum is 5 days of uptime - but you need to have configured undo_retention AND set up sufficient space to do that.
the data is no longer in the undo segments, if you need to recover it, you'll be using traditional recovery techniques from backup.
Continue Ask you
jxau, December 05, 2005 - 1:38 am UTC
Yes ,the UNDO_Retention is set by default 10800(seconds)
but why can i get another table's data about 18 hours ago ?
The sql is :
select b.* from
(select * from pop_region
as of timestamp(systimestamp-interval '18' hour)) b
I CAN get the data before i deleted yesterday
Why can i get this data ?
December 05, 2005 - 1:44 am UTC
undo retention is set to 3 hours, you have asked for "at least the last three hours, space permitting" to be saved in the undo tablespace.
Read that carefully - "at least the last three hours", not "only the last three", "at least"
And "space permitting"
Oracle will attempt to save at least the last three hours (and more if it happens to work out that way) in the undo segments.
You just didn't do so much work in the last 18 hours that the old 15 hours of undo got lucky and stuck around.
Curiosity
Bob B, December 05, 2005 - 9:42 am UTC
Out of curiosity, what is the undo retention of the database that AskTom is running off of?
December 06, 2005 - 4:49 am UTC
6 hours right now.
Undo_retention & flashback
Yogesh, January 25, 2006 - 2:02 pm UTC
In 10g Rel2, when recovering from
drop
delete
truncate
How undo_retention parameter will impact Flashback time?
Is there any relation between db_recovery_file_dest_size & undo_retention?
January 25, 2006 - 4:37 pm UTC
drop - drop table is recovered by using the recycle bin. The recycle bin is NOT undo based (we rename the table to "hide" it and reuse the space when forced to - to undrop a table, we just rename it back - no undo comes into play)
truncate - there is no "untruncate" a table short of FLASHBACK DATABASE - and that is not undo based, it is based on the flash recovery area. no undo comes into play (other than the normal use of undo during recovery to rollback uncommitted transactions)
to flashback over a delete - undo retention will help you figure out how far back in time you can flashback query. If you deleted the data 5 hours ago, but undo retention is set to 5 minutes - it is unlikely you will be able to "undelete" it.
snapshot too old
SM, May 31, 2006 - 1:49 pm UTC
oracle 9206
Tom,
I am trying to use as timestamp..and create table as..together..
when I do just a count(*) on the table (using as of timestamp)..it works fine..
but when I do create table as ..I get
ERROR at line 4:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
too small
Thanks
May 31, 2006 - 3:38 pm UTC
"sorry"
that is about all I can say with this much information.
One can only assume that when you actually run the create table as select, it takes a tad bit LONGER than just counting rows (which can have a RADICALLY DIFFERENT plan than the select itself as many times table accesses can be skipped with the count(*))
You cannot in any way shape or form compare:
select count(*) from (MY_QUERY);
and
MY_QUERY
they will typically have very different plans, massively different execution profiles.
Revoke from Users
V, June 14, 2006 - 4:44 pm UTC
What would have to be done to revoke all flashback capabilities from users other than sys?
Question about smon_scn_time
gzhang, July 27, 2006 - 4:43 pm UTC
Tom,
As always, very clear explanation about flahsbak time and smon_scn_time!
While playing with flashback (database is 9.2.0.6), I found, in one of my databases, that the smon_scn_time only has the one row as of today, the other 1439 rows are from 3/12/06 and before. Flashback does not work (ora-01555 error) except for the past several ~5 minutes. And SMON does update the last row every 5 minutes or so, but the old 1439 rows are not touched (It seemed that something strange happened on 3/12, didn't it?). I tried look that up in metalink, but did not find anything. How is that happening? Below is the query (only showing the last page):
SQL> select thread, scn_bas, to_char(time_dp, 'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time order by time_dp;
...
THREAD SCN_BAS TO_CHAR(TIME_DP,'YY
---------- ---------- -------------------
1 1646604960 2006-03-12 18:42:05
1 1646605194 2006-03-12 18:47:12
1 1646605406 2006-03-12 18:52:20
1 1646605671 2006-03-12 18:57:27
1 1646606047 2006-03-12 19:02:35
1 1646606292 2006-03-12 19:07:43
1 1646606519 2006-03-12 19:12:50
1 1646606750 2006-03-12 19:17:57
1 1646606924 2006-03-12 19:23:05
1 1646607141 2006-03-12 19:28:12
1 1646607292 2006-03-12 19:30:30
1 1646607524 2006-03-12 19:35:38
1 1646607799 2006-03-12 19:39:30
1 1646608128 2006-03-12 19:44:38
1 1724952619 2006-07-27 16:19:01
1440 rows selected.
Thanks in advance,
George
July 27, 2006 - 5:43 pm UTC
please contact support for assistance on this.
flashback procedure
Phil, November 22, 2006 - 9:04 am UTC
Hi Tom
I don't think I can but you might be able to confirm if I can flashback to recover a stored procedure that has been recompiled as an old version?
Cheers
Phil
November 24, 2006 - 12:37 pm UTC
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12793509021219
ops$tkyte%ORA10GR2> create or replace procedure p1
2 as
3 begin
4 null;
5 /* p1 original */
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
19887936
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1
2 as
3 begin
4 null;
5 /* p1 new */
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select text from user_source where name = 'P1' order by line;
TEXT
-------------------------------------------------------------------------------
procedure p1
as
begin
null;
/* p1 new */
end;
6 rows selected.
ops$tkyte%ORA10GR2> select text from user_source as of SCN &SCN where name = 'P1' order by line;
old 1: select text from user_source as of SCN &SCN where name = 'P1' order by line
new 1: select text from user_source as of SCN 19887936 where name = 'P1' order by line
TEXT
-------------------------------------------------------------------------------
procedure p1
as
begin
null;
/* p1 original */
end;
6 rows selected.
Excellent
Phil, November 24, 2006 - 5:08 pm UTC
Of course - you just always seem to know how to marry up old queries to new features...
10g maping table
A reader, August 30, 2007 - 9:32 pm UTC
Hi,Tom!
As you mentioned,on 9i the maping table for timestamp to scn is smon_scn+time,and every 5 minutes oracle will update it .
But I use oracle 10gr2,could you kindly tell me what is the the name of maping table in 10gr2 ,and is update by oracle every 3 seconds?
and others 2 questions:
1:Doest the flashback query on 10g can still flasback max to 5 day as you mentioned on 9i?
2:Any other defferences between 10g and 9i on flashback query?
Best regards¿
Alan
September 04, 2007 - 5:59 pm UTC
same name, different attributes - same limit (5 days about), but finer granularity (3 seconds)
It is maintained in the SGA and flushed periodically to disk.
1) pretty much, it can be a little longer, but I wouldn't bank on it - it isn't reasonable to expect 5 days of undo to be around
2) there are more things you can do - flashback table, undrop table, flashback database, flashback transaction history, flashback versions query. 10g added A LOT.
Flashback retention
Sharon, January 24, 2008 - 7:48 am UTC
Hello Tom,
I read your Answers and other material regularly. I find them excellent and very informative. Please can you help me in this regard. I am badly stuck up with this.
We have a problem that happen often, where the flashpoint recovery fills up and the database goes to mount mode. When you look at the flashpoint recovery area, the backup may only be using 20% of it, and it appears that the flashpoint area is not utilized by anything else. So, even though it is 20% used, we have to increase the size of the area (to even bigger than the physical disk), and then we are able to open the database again.
Log_Archive_Dest_10 is set to blank, but Log_Archive_Dest_1 is set to within the flashback recovery area. For the database I am looking at the moment, we are using RMAN. For the others, I have recently turned RMAN off as they are development databases. Not sure if this will stop the problem reoccuring for the development databases.
in Production, RMAN takes up 27% of the space at the moment. However, the flashback recovery area size is set to larger than the physical disk. Not sure of the ramifications of this but it was how we could bring the database up successfully.
The parameters in pfile are:
db_recovery_file_dest = 'E:/ORACLE/flash_recovery_area'
db_recovery_file_dest_size = 60G
log_archive_dest_1 = 'LOCATION=E:\ORACLE\flash_recovery_area\RDPP\ONLINELOG\'
SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------------------------------------- ----------- ---------- ----------------- ---------------
E:/ORACLE/flash_recovery_area 6.4425E+10 1.7087E+10 0 7
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 26.52 0 7
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
And where the disk system is only 50Gb in size:
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:/ORACLE/flash_recovery_area
db_recovery_file_dest_size big integer 60G
What would be using this that we aren't aware of? Why is not showing up in the graphical display usage for Flashpoint Recovery area?
Hoping you can clear up this knowledge gap of mine,
January 24, 2008 - 7:53 am UTC
not a single oracle message to diagnose....
Difference between undo_retention and flashback_retention_target
Parikh, February 11, 2009 - 2:20 pm UTC
Hi Tom,
What is the relation between undo_retention and flashback_retention_target parameters.
Or
Do they have any relation at all.
If I keep undo_retention to 15 minuts and keep flashback_retention_target = 1400 minutes, sometimes I am able to run flashback for more than 1400 minutes and sometimes not even for 900 minutes.
Kindly Help on this
February 11, 2009 - 3:04 pm UTC
undo retention controls how long undo is retained in an undo tablespace in support of
a) not getting ora-1555's snapshot to old
b) flashback versions query (without a flashback data archive new in 11g)
c) flashback query (without a flashback data archive new in 11g)
d) flashback table
flashback retention is about managing the flash recovery area used for flashback database
Undo retains even after the retention paeriod timeout
Parikh, February 11, 2009 - 4:54 pm UTC
Thank you Tom for your response but I still have some doubt.I will try to explain with a scenario.
Actually, the scenario is:
Database: 10g
Undo_retention: 15 minutes
Datafile : Name C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS01.DBF
Tablespace UNDOTBS1
Status Online
File Size (KB) 133120
AutoExtend Yes
Increment 5120KB
Maximum File Size 32767MB
Undo Retention Guarantee : Yes
Now,I execute a query on my enterprise edition Oracle 10g system:
SQL> delete from emp;
14 rows deleted.
Note: I waited for more 30 minutes so that the retention_period of 15 minutes is passed.
SQL> select * from emp as of timestamp(sysdate - interval '80' minute);
14 rows returned.
Result: It gets all the 14 rows from the above select statement which were deleted previously.
Now, my doubt is:
1) Why was the data retained in undo space for more than Undo_retention parameter(15 minutes).Even after by-passing the 15 minutes of retention period, why is it still in the undo space.
2) If we say that I was lucky because there was no other operation on the database, till what duration will this data be retained.
Thanks,
Parikh
February 12, 2009 - 10:54 am UTC
1) that is ok, we only overwrite undo when we HAVE TO.
you have an undo tablespace. It is X units in size. If you generate X/100 units of undo every minute, then it will take 100 minutes before we overwrite anything. You asked for at least 15, but since you have allocated enough for 100, you get 100.
you have an undo tablespace. It is X units in size. If you generate X/10 units of undo every minute, then when we get 10 minutes into it - we'll have filled all space and will want to overwrite the oldest stuff. But you have asked us to keep 15 minutes worth. So now we try to extend the datafiles if possible to get more space (and if so, we use it). Else, if we cannot grow - we look for extents of undo that we can prematurely expire (unless you set a guarantee in 10g for undo retention - in which case we fail the operation)
Your 15 minutes is a request to keep "at least" 15 minutes, if you have room for more than 15 - so be it, we'll keep it.
forgot commit in previous scenario
Parikh, February 11, 2009 - 4:57 pm UTC
Thank you Tom for your response but I still have some doubt.I will try to explain with a scenario.
Actually, the scenario is:
Database: 10g
Undo_retention: 15 minutes
Datafile : Name C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS01.DBF
Tablespace UNDOTBS1
Status Online
File Size (KB) 133120
AutoExtend Yes
Increment 5120KB
Maximum File Size 32767MB
Undo Retention Guarantee : Yes
Now,I execute a query on my enterprise edition Oracle 10g system:
SQL> delete from emp;
14 rows deleted.
SQL> commit;
Note: I waited for more 30 minutes so that the retention_period of 15 minutes is passed.
SQL> select * from emp as of timestamp(sysdate - interval '80' minute);
14 rows returned.
Result: It gets all the 14 rows from the above select statement which were deleted previously.
Now, my doubt is:
1) Why was the data retained in undo space for more than Undo_retention parameter(15 minutes).Even
after by-passing the 15 minutes of retention period, why is it still in the undo space.
2) If we say that I was lucky because there was no other operation on the database, till what
duration will this data be retained.
Thanks,
Parikh
How to find SCN before Transaction?
Maverick, February 12, 2009 - 9:13 am UTC
Tom, I was reading this article from top and it helped me understand If I have SCN , how I can flashback to that SCN [or that time]. However onething was not clear enough.
I have done a transaction [let's say Update] and then commited. now I need to flash back using SCN, But unlike your examples above, I didn't query before for SCN Number.
How can I get SCN and undo my update?
Is there anyway I can query some tables and find what was the SCN before my transaction and use it?
Hope I am explaining my question clearly.
Thanks
February 12, 2009 - 3:59 pm UTC
you can use flashback versions query on some row you updated....
scott%ORA11GR1> select ename, sal,
2 versions_operation,
3 versions_startscn,
4 versions_endscn
5 from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
6 where empno = 7788
7 order by versions_startscn nulls first
8 /
old 5: from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
new 5: from emp versions between timestamp to_timestamp('12-FEB-09 04.04.02.518682 PM') and to_timestamp('12-FEB-09 04.04.20.951264 PM')
ENAME SAL V VERSIONS_STARTSCN VERSIONS_ENDSCN
---------- ---------- - ----------------- ---------------
SCOTT 3000 3891810
SCOTT 3300 U 3891810 3891812
SCOTT 3630 U 3891812 3891814
SCOTT 3993 U 3891814 3891816
SCOTT 4392.3 U 3891816 3891818
SCOTT 4831.53 U 3891818 3891842
SCOTT 4831.53 D 3891842
SCOTT 4831.53 I 3891844 3891846
SCOTT 5314.68 U 3891846 3891848
SCOTT 5846.15 U 3891848 3891850
SCOTT 6430.77 U 3891850 3891852
SCOTT 7073.85 U 3891852 3891854
SCOTT 7781.24 U 3891854
13 rows selected.
it'll give you the SCN information (I had previously updated scotts salary in a loop with a delay built in) that tells you what SCN's that version of that row was valid from and to.
You can even find the transaction id:
scott%ORA11GR1> select ename, sal,
2 versions_operation,
3 versions_xid
4 from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
5 where empno = 7788
6 order by versions_startscn nulls first
7 /
old 4: from emp versions between timestamp to_timestamp('&START') and to_timestamp('&STOP')
new 4: from emp versions between timestamp to_timestamp('12-FEB-09 04.04.02.518682 PM') and to_timestamp('12-FEB-09 04.04.20.951264 PM')
ENAME SAL V VERSIONS_XID
---------- ---------- - ----------------
SCOTT 3000
SCOTT 3300 U 08000F0020110000
SCOTT 3630 U 0900120086100000
SCOTT 3993 U 02001D003B120000
SCOTT 4392.3 U 0300150056110000
SCOTT 4831.53 U 0400030034100000
SCOTT 4831.53 D 07002100E4100000 <<<=== interested in this
SCOTT 4831.53 I 06001900EA100000
SCOTT 5314.68 U 05001A00F5110000
SCOTT 5846.15 U 0A0012005A100000
SCOTT 6430.77 U 01000E0034110000
SCOTT 7073.85 U 08000C0021110000
SCOTT 7781.24 U 0900050088100000
13 rows selected.
and then find the sql to undo it all:
scott%ORA11GR1> select *
2 from flashback_transaction_query
3 where xid = hextoraw( '&XID' )
4 /
old 3: where xid = hextoraw( '&XID' )
new 3: where xid = hextoraw( '07002100E4100000' )
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE#
---------------- ---------- --------- ---------- --------- ------------------------------ ------------
OPERATION
--------------------------------
TABLE_NAME
-------------------------------------------------------------------------------------------------------------------------
TABLE_OWNER ROW_ID
-------------------------------- -------------------
UNDO_SQL
-------------------------------------------------------------------------------------------------------------------------
07002100E4100000 3891841 12-FEB-09 3891842 12-FEB-09 SCOTT 1
UPDATE
DEPT
SCOTT AAAQ+JAAEAAAAAPAAA
update "SCOTT"."DEPT" set "DNAME" = 'ACCOUNTING' where ROWID = 'AAAQ+JAAEAAAAAPAAA';
07002100E4100000 3891841 12-FEB-09 3891842 12-FEB-09 SCOTT 2
DELETE
EMP
SCOTT AAAQ+LAAEAAAAAeAAO
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7788','SCOTT','ANALYST',
'7566',TO_DATE('19-APR-87', 'DD-MON-RR'),'4831.53',NULL,'20');
07002100E4100000 3891841 12-FEB-09 3891842 12-FEB-09 SCOTT 3
(I had updated the dept table and delete from emp, this undoes those operations)
Exactly what I needed
Maverick, February 13, 2009 - 8:32 am UTC
Thanks Tom. That will help me find what to Undo exactly. Thanks for your help.
Value of DBMS_FLASHBACK.ENABLE_AT_{TIME|SCN}
carr_ar, March 12, 2012 - 9:34 am UTC
After you set the session-wide DBMS_FLASHBACK.ENABLE_AT_TIME() or ENABLE_AT_SYSTEM_CHANGE_NUMBER(), is there a way (view, function, etc) to determine that value's current setting later in the same session? Or even to determine that it is set and active? I can't seem to find anything in the documentation for this.
Thanks Again!
March 12, 2012 - 10:20 am UTC
not that I am aware of