11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback

September 4, 2008

11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback

Filed under: Oracle How To — H.Tonguç Yılmaz @ 9:40 pm

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;

Like this:

Be the first to like this post.

Comments (1)

1 Comment »

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

Comment by Mahek — January 20, 2012 @ 12:36 am | Reply

RSS feed for comments on this post. TrackBack URI

	gurcanorhan on İlk Türk Oracle ACE ‘in …
	Oracle Sertifikasyon… on On Oracle Certification
	coskan on On Innovation
	On Innovation … on Two videos that impressed me v…
	Kamran Agayev A. on Two videos that impressed me v…

H.Tonguç Yılmaz – Oracle Blog

September 4, 2008