Oracle’s flashback features are one of the most loved, read posts on my blog. The three studies I have done before were;
Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management
Oracle 10g flashback features’ limitations and restrictions
Flashback database and NOLOGGING operations problem demo
Today I tested flashback database feature on 11.1 and my experiences were as following;
connect / as sysdba create smallfile tablespace flashtbs datafile 'flashtbs1.dbf' size 500K logging extent management local segment space management auto; create user flash identified by flash default tablespace flashtbs temporary tablespace temp; grant connect, resource, dba to flash; -- check if database is in archivelog mode archive log list -- check if flashback option is enabled select flashback_on from v$database; -- check for the flashback log files host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback connect flash/flash create table emp tablespace flashtbs as select * from hr.employees; select sum(salary) from emp; SUM(SALARY) ----------- 691400 -- note as scn2 select current_scn from v$database; CURRENT_SCN ----------- 1713454 select undoblks from v$undostat; UNDOBLKS ---------- 49 42 select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 1664754 31-MAY-08 1440 516628480 338558976 select * from V$FLASHBACK_DATABASE_STAT; BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE --------- --------- -------------- ---------- ---------- ------------------------ 04-SEP-08 04-SEP-08 4726784 1294336 1688064 0 begin for i in 1..10000 loop update emp set salary=salary+1; end loop; commit; end; / select undoblks from v$undostat; UNDOBLKS ---------- 6177 42 select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 1664754 31-MAY-08 1440 516628480 378716160 select * from V$FLASHBACK_DATABASE_STAT; BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE --------- --------- -------------- ---------- ---------- ------------------------ 04-SEP-08 04-SEP-08 55263232 3080192 105243648 0 -- check for new flashback log files host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback [ 14 new 3989504 sized flb files ] begin for i in 1..10000 loop update emp set salary=salary+1; end loop; commit; end; / select undoblks from v$undostat; UNDOBLKS ---------- 12328 42 select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 1664754 31-MAY-08 1440 516628480 417447936 select * from V$FLASHBACK_DATABASE_STAT; BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE --------- --------- -------------- ---------- ---------- ------------------------ 04-SEP-08 04-SEP-08 105086976 4974310 210600960 0 host ls -lt $ORACLE_BASE/flash_recovery_area/*/flashback [ again 14 new 3989504 sized flb files ]
Undo and Redo stats are very similar for both runs, as expected.
V$UNDOSTAT.UNDOBLKS
1st) 6177 – 49 = 6128
2nd) 12328 – 6177 = 6151
V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
1st) 378716160 – 338558976 = 40157184
2nd) 417447936 – 378716160 = 38731776
V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
1st) 55263232 – 4726784 = 50536448
2nd) 105086976 – 55263232 = 49823744
V$FLASHBACK_DATABASE_STAT.REDO_DATA
1st) 105243648 – 1688064 = 103555584
2nd) 210600960 – 105243648 = 105357312
V$FLASHBACK_DATABASE_STAT.DB_DATA
1st) 3080192 – 1294336 = 1785856
2nd) 4974310 – 3080192 = 1894118
Now lets repeat the test with a tablespace which does not use flashback option.
connect / as sysdba create smallfile tablespace flashtbs2 datafile 'flashtbs2.dbf' size 500K logging extent management local segment space management auto; alter tablespace flashtbs2 flashback off; select name, flashback_on from v$tablespace; NAME FLA ------------------------------ --- .. FLASHTBS YES FLASHTBS2 NO .. connect flash/flash create table emp2 tablespace flashtbs2 as select * from hr.employees; select undoblks from v$undostat; UNDOBLKS ---------- 6 11 0 12334 42 select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 1664754 31-MAY-08 1440 516628480 414031872 select * from V$FLASHBACK_DATABASE_STAT; BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE --------- --------- -------------- ---------- ---------- ------------------------ 04-SEP-08 04-SEP-08 106602496 107986944 211301888 0 begin for i in 1..10000 loop update emp2 set salary=salary+1; end loop; commit; end; / select undoblks from v$undostat; UNDOBLKS ---------- 6138 11 0 12334 42 select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- --------- ---------------- -------------- ------------------------ 1664754 31-MAY-08 1440 516628480 453746688 select * from V$FLASHBACK_DATABASE_STAT; BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE --------- --------- -------------- ---------- ---------- ------------------------ 04-SEP-08 04-SEP-08 157245440 113696768 314953216 0 [ this time just 5 new 3989504 sized flb files are created ]
Other than the flashback log files undo and Redo stats are again very similar for the third run as well, interesting.
V$UNDOSTAT.UNDOBLKS
3rd) 6138 – 6 = 6132
V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
3rd) 453746688 – 414031872 = 39714816
V$FLASHBACK_DATABASE_STAT.FLASHBACK_DATA
3rd) 157245440 – 106602496 = 50642944
V$FLASHBACK_DATABASE_STAT.REDO_DATA
3rd) 314953216 – 211301888 = 103651328
but this time DB_DATA stats has a significant increase,
V$FLASHBACK_DATABASE_STAT.DB_DATA
3rd) 113696768 – 107986944 = 5709824
Now lets try to flashback the database with a tablespace which this feature is disabled.
select sum(salary) from emp; SUM(SALARY) ----------- 2831400 -- note as scn1 select current_scn from v$database; CURRENT_SCN ----------- 1775827 -- temporary action insert into emp select * from emp; commit; select sum(salary) from emp; SUM(SALARY) ----------- 5662800 connect / as sysdba shutdown immediate; startup mount; -- flashback to scn1 flashback database to scn 1775827 ; * ERROR at line 1: ORA-38753: Cannot flashback data file 7; no flashback log data. ORA-01110: data file 7: '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' -- in order to flashback we need to get rid of the datafiles which have not this feature enabled alter database datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ; -- try again flashback to scn1 flashback database to scn 1775827; alter database open read only; select sum(salary) from flash.emp; SUM(SALARY) ----------- 2831400 shutdown immediate; startup mount; alter database datafile '/u01/app/oracle/product/11.1.0/db_1/dbs/flashtbs2.dbf' offline ; -- flashback to scn2 flashback database to scn 1713454; alter database open resetlogs; select sum(salary) from flash.emp; SUM(SALARY) ----------- 691400
So the bottom line is if you think you will be using any flashback options be careful and get prepared about their limitations(mentioned in my previous post linked above), for this case flashback disabled tablespace was the drawback and workaround was to offline it(of course if this is acceptable).
ps: If you want to clean up the mess;
shutdown immediate; startup drop user flash cascade; drop tablespace flashtbs including contents and datafiles; -- this one is not needed because of the second flashback database -- drop tablespace flashtbs2 including contents and datafiles;
Hi nice information. I have a question with nologging and flashback database. We have database running in no logging mode and force logging is off. Now if I create a restore point (guarantee) and do a restore with the flashback database option, do I see any corruption in the data blocks or not. One more thing is that it has some tables created in nologging mode and bulk of insert was happened. It also have standby database running, so after opening it with resetlogs standby gets corrupted or not.
This are the steps I am gone a perform in primary.
— Differ the standby database from primary
— db running in force_logging=off
— We do shutdown immediate
— startup mount
— create restore point
— Not using flashback logging because we just want to put it at restore point creation time only
— alter database open;
— Something goes wrong within 24 hours of retention period
— shutdown immediate
— startup mount
— flashback database to restore point