Home » RDBMS Server » Backup & Recovery » Flashback confusion...
Flashback confusion... [message #267345] Thu, 13 September 2007 01:33 Go to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Hi all,
I'm using Oracle 10g Enterprise Edition Release 10.2.0.1.0 on a Linux Machine

I have enabled flashback on my DB and an retention target of 3 days:
SQL> show parameter retention;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
db_flashback_retention_target        integer     4320
undo_retention                       integer     900



As the documentation says, oracle manages the flashback files, by it's self also deleting de obsolete ones (the ones which are older than 3 days).
Now if i query v$flashback_database_log and v$flashback_database_logfile views i see that the oldest file dates from 07.09.2007:
SQL> select * from v$flashback_database_logfile order by first_time;

NAME                                                                                   LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- -----------
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlvp8d_.flb                             4          1        257    4964352      63125929 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlw13w_.flb                             5          1        258    6471680      63128932 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlwjyc_.flb                             6          1        259    8421376      63130690 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlznw0_.flb                             7          1        260   10821632      63135004 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fqk9jmo_.flb                           106          1        261    5406720      63138711 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fqkbyn3_.flb                           107          1        262    4055040      63139515 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fqlqwcn_.flb                           108          1        263    4063232      63142440 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fqpqwvy_.flb                           109          1        264    4030464      63144459 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhm0vrd_.flb                             8          1        265   13983744      63145856 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhx6lnh_.flb                             9          1        266    7159808      63149442 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0ncmf_.flb                           110          1        267    4063232      63150872 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0ny7t_.flb                           111          1        268    4096000      63151384 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0pzjp_.flb                           112          1        269    4120576      63153700 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0so09_.flb                           113          1        270    4153344      63154048 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0zhwl_.flb                           114          1        271    4177920      63154414 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0zvdq_.flb                           115          1        272    4210688      63154762 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr0zy7d_.flb                           116          1        273    4243456      63155264 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr1006l_.flb                           117          1        274    4268032      63155609 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr127k9_.flb                           118          1        275    4300800      63156054 07.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fr12gtf_.flb                           119          1        276    4333568      63158134 08.09.2007

.................

/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhddr9j_.flb                             1          1        402    8192000      63368761 13.09.2007


/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlv9gz_.flb                             2          1        403    4096000      63373840 13.09.2007
/oracle/10.2.0/dbs/BKPs/WINMDB2/flashback/o1_mf_3fhlvg9p_.flb                             3          1        404    3981312      63375769 13.09.2007

148 rows selected

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------------------- ---------------- -------------- ------------------------
            63125062 07.09.2007 09:26:41               4320      668450816                422240256


Also the flashback files listed above are on the disk,
So my question is:
Does Oracle failed to delete the obsolete flashback files?? if Yes, do i have to delete the obsolete files directly using OS utilities or is there any way to delete those files by using oracle utilities (maybe RMAN)??

Thanks !!!
Re: Flashback confusion... [message #267350 is a reply to message #267345] Thu, 13 September 2007 01:42 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
Does Oracle failed to delete the obsolete flashback files??

If it has permission it will not fail to delete.
But, Oracle always honor you to delete anything as much as disk has space.
Re: Flashback confusion... [message #267366 is a reply to message #267345] Thu, 13 September 2007 02:01 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Oracle has permission to delete those files, it doesn't delete the files since 2 or 3 days ago , since then it deleted the obsolete flashback files.
Re: Flashback confusion... [message #267368 is a reply to message #267345] Thu, 13 September 2007 02:07 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
it doesn't delete the files since 2 or 3 days ago


Because there is much space in the db_recovery_file_dest/ destination of flashback usage. As long as new operation don't claim disk space oracle retain the data.
Re: Flashback confusion... [message #267377 is a reply to message #267345] Thu, 13 September 2007 02:26 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
ok, now it's more clear to me, indeed there is enough free space in flashback are.
Thanks again !
Re: Flashback confusion... [message #277396 is a reply to message #267368] Mon, 29 October 2007 22:41 Go to previous messageGo to next message
akimeu
Messages: 5
Registered: March 2007
Junior Member
never the less, can we force a delete of these files? we're using that disk for other files as well, and it's eating away the space... please advice and thanks in advance.
Re: Flashback confusion... [message #277411 is a reply to message #277396] Tue, 30 October 2007 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can delete the files you want and you can limit the space used by flashback area using db_recovery_file_dest_size parameter.

Regards
Michel
Re: Flashback confusion... [message #412245 is a reply to message #277411] Wed, 08 July 2009 06:24 Go to previous messageGo to next message
xtvka
Messages: 1
Registered: July 2009
Junior Member
You must NEVER delete flb-files (flashback logs) manually. You will get ora-errors on startup of your database.
ex:
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.

The alert log would have error as shown below:

Errors in file d:\oracle\admin\orcl10g\udump\orcl10g_ora_38800.trc:
ORA-38701: Flashback database log 1 seq 1 thread 1: "D:\ORACLE\FLASH_RECOVERY_AREA\ORCL10G\FLASHBACK\O1_MF_1425DVK2_.FLB"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


When the current flashbacklog is deleted, then the database will crash with the following errors:

Read the folloowing metalinks :
404338.1
302488.1
Re: Flashback confusion... [message #412249 is a reply to message #412245] Wed, 08 July 2009 06:29 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
When the current flashbacklog is deleted, then the database will crash with the following errors:

Read the folloowing metalinks :
404338.1
302488.1


Thanks for the details. And great your replying two years old thread. Last update " Tue, 30 October 2007 10:42 "

Babu
Previous Topic: How to restart a restore after it failed ?
Next Topic: problem restoring database
Goto Forum:
  


Current Time: Thu Mar 28 06:22:58 CDT 2024