Subscribe to Posts by Email

Subscriber Count

    700

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

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

Comments are closed.