11.1 Flashback Database Test and Flashback Tablespace Off Options Drawback

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_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_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;