This discussion is archived
1 Reply Latest reply: Mar 27, 2013 9:52 AM by damorgan RSS

Flashback database to restore point

Yoav Newbie
Currently Being Moderated
Hi,

We have upgraded our oracle ERP EBS application and i had to keep an open copy of the database as it was before the uprdae.
In order to do so , i cloned my production database , and took a restore point.
each night i am flashing the databse back to the restore point as follow:
sqlplus /nolog <<EOF
connect / as sysdba
WHENEVER SQLERROR EXIT SQL.SQLCODE ;
SHUTDOWN immediate;
startup mount exclusive;
FLASHBACK DATABASE TO RESTORE POINT BEFORE_R12_UPGRADE;
alter database open resetlogs; 
exit
in the FLASH RECOVERY area i have flashback logs:
SQL>
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE    NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ------------------
CONTROL FILE                          0                         0                  0
REDO LOG                              0                         0                  0
ARCHIVED LOG                          0                         0                  0
BACKUP PIECE                          0                         0                  0
IMAGE COPY                            0                         0                  0
FLASHBACK LOG                       101                         0               1412
FOREIGN ARCHIVED LOG                  0                         0                  0
I have a guaranteed restore point defined as:
SQL>    select FLASHBACK_ON from V$DATABASE;
FLASHBACK_ON                                                                                                     
------------------                                                                                               
RESTORE POINT ONLY                                                                                     
The problem is that the FRA is getting bigger and bigger and its size is already 150 GB.
I thought that flashing back the database will overwride the flb files , but its seems that it doest remove them .....
-rw-r----- 1 oracle dba 31883264 Mar 20 09:02 o1_mf_8nlq6wny_.flb
-rw-r----- 1 oracle dba 31883264 Mar 20 09:23 o1_mf_8nlq706s_.flb
-rw-r----- 1 oracle dba 31883264 Mar 20 09:23 o1_mf_8nlqrbnf_.flb
-rw-r----- 1 oracle dba 31883264 Mar 20 09:24 o1_mf_8nlryqfd_.flb
-rw-r----- 1 oracle dba 36167680 Mar 20 09:24 o1_mf_8nlrzm3y_.flb
----------------------
----> Alot of flb files has been created and not deleted every day sinc march 20,2013
--------------------
-rw-r----- 1 oracle dba 121528320 Mar 27 15:09 o1_mf_8o5vvm41_.flb
-rw-r----- 1 oracle dba 121528320 Mar 27 15:10 o1_mf_8o5vw4my_.flb
-rw-r----- 1 oracle dba 121528320 Mar 27 15:10 o1_mf_8o5vws17_.flb
-rw-r----- 1 oracle dba 121528320 Mar 27 15:30 o1_mf_8o5vxbly_.flb
-rw-r----- 1 oracle dba 121528320 Mar 27 15:10 o1_mf_8o5vxvyx_.flb
Since i am falshing back each and every night the databse to the same restore point , does i need the OLD flb files after the database has been restored and opened ?
Can i delete the old files ?

Thanks
Yoav

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points