1 Reply Latest reply: Mar 27, 2013 11:52 AM by damorgan RSS

    Flashback database to restore point

    Yoav
      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