Subscriber Count

    464

Subscribe to Posts by Email

Pages

Flashback log: Your flashback option is disabled, does flashback logs get generated?

Does flashback logs generates even if the flashback option is disabled?

Answer: Yes, when you have guaranteed restore point exists in your database.

No, if you does not have guaranteed restore point. Please read further.

    SQL> select log_mode,flashback_on from v$database;

    LOG_MODE     FLASHBACK_ON
    ------------ ------------------
    ARCHIVELOG   NO

    SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

    Restore point created.

    SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE,
    STORAGE_SIZE FROM V$RESTORE_POINT

    NAME              SCN TIME                                    DI GUA STORAGE_SIZE
    ----------------- ---------- -----------------------------    ---------- --- ------------
    BEFORE_UPGRADE    451792 14-FEB-15 08.14.20.000000000 PM      2 YES      8192000

    SQL>

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   RESTORE POINT ONLY

SQL> insert into m select * from dba_objects;

49745 rows created.

49745 rows created.

49745 rows created.

49745 rows created.

49745 rows created.

49745 rows created.

49745 rows created.

...
49745 rows created.

SQL>
Commit complete.

Look at the flashback generation

    [oracle@Geek DBA flashback]$ ls -ltr
    total 16040
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:15 o1_mf_bfyq822w_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:17 o1_mf_bfyqx47g_.flb
    [oracle@Geek DBA flashback]$ ls -ltr
    total 16040
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:15 o1_mf_bfyq822w_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:17 o1_mf_bfyqx47g_.flb
    [oracle@Geek DBA flashback]$ ls -ltr
    total 16040
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:15 o1_mf_bfyq822w_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:17 o1_mf_bfyqx47g_.flb
    [oracle@Geek DBA flashback]$ ls -ltr
    total 24060
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:15 o1_mf_bfyq822w_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:17 o1_mf_bfyqx47g_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr0x6o_.flb

Okay flashback logs are still generated though the database flashback option is off. Let do some more work

    SQL> delete from m where rownum < 10000000;
    1989291 rows deleted.

    SQL> commit;

    Commit complete.

    SQL>

    [oracle@Geek DBA flashback]$ ls -ltr
    total 88220
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:15 o1_mf_bfyq822w_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:17 o1_mf_bfyqx47g_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr0x6o_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr16df_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr1kmz_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr1vkk_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr273z_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:18 o1_mf_bfyr2j6n_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:19 o1_mf_bfyr2tmz_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:19 o1_mf_bfyr34h6_.flb
    -rw-r----- 1 oracle oinstall 8200192 Feb 14 20:22 o1_mf_bfyr3lc9_.flb

So it means though the flashback option off, when you create guaranteed restore point
oracle will switch on the flashback on option implicitly and maintain the flashback logs.

Let's try to drop the restore point

    SQL> drop restore point before_upgrade;

    Restore point dropped.

    SQL>

    SQL> select * from v$restore_point;

    no rows selected

    SQL>

    Implicit change of flashback_on NO automatically when you drop the restore point.

    SQL> select open_mode,log_mode,flashback_on from v$database;

    OPEN_MODE  LOG_MODE     FLASHBACK_ON
    ---------- ------------ ------------------
    READ WRITE ARCHIVELOG   NO

    SQL>

Check the flashback logs, wow they are gone automatically,

    [oracle@Geek DBA flashback]$ ls -ltr
    total 0
    [oracle@Geek DBA flashback]$ date
    Sat Feb 14 20:26:20 IST 2015
    [oracle@Geek DBA flashback]$ ls -ltr
    total 0
    [oracle@Geek DBA flashback]$

    Lets check one more round with normal restore point. what could be the status of flashback

    SQL> create restore point test;

    Restore point created.

    SQL> select * from v$restore_point;

           SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                   NAME
    ---------- --------------------- --- ------------ -----------------------------------    -------
        480565                     2 NO             0 14-FEB-15 08.28.41.000000000 PM        TEST

    SQL>

    SQL> select open_mode,log_mode,flashback_on from v$database;

    OPEN_MODE  LOG_MODE     FLASHBACK_ON
    ---------- ------------ ------------------
    READ WRITE ARCHIVELOG   NO

See Flashback on is still no, with normal restore point, so it wont generate flashbacks,lets test that too. Let do some DML,

    SQL> insert into m select * from dba_objects;

    49745 rows created.

    SQL> /

    49745 rows created.

    SQL> /

    49745 rows created.

    SQL> /

    49745 rows created.

    SQL> /

    49745 rows created.

    SQL> /

    49745 rows created.

    SQL> /

    Commit complete.

    SQL> alter system switch logfile ;

    System altered.

    SQL> delete from m where rownum < 1000000;
    999999 rows deleted.

Check flashback logs. None generated.

    [oracle@Geek DBA flashback]$ date
    Sat Feb 14 20:30:14 IST 2015
    [oracle@Geek DBA flashback]$ ls -ltr
    total 0
    [oracle@Geek DBA flashback]$ ls -ltr
    total 0
    [oracle@Geek DBA flashback]$ ls -ltr
    total 0

[oracle@Geek DBA flashback]$Some other notes on this from AskTom.

Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

Bottom Line:- When you are experiencing the flashback log generation even in the flashback option off, guaranteed restore point might exists in the database. option off, guaranteed restore point might exists in the database.

-Thanks

Geek DBA

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>