Oracle 23c New Features for DBA'sDoes 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
Follow Me!!!