4 Replies Latest reply on Jul 28, 2011 4:40 PM by 677029

    Flashback log on the standby database growing huge

    677029
      Hello,

      I am using a 2 node RAC primary and a 2 node RAC standby on version 11.2.0.1 and have a space issue being caused due to flashback logs on the standby database.
      The flashback logs grows continuously eventhough i've set the retention target to a lesser value and indicates no space to be reclaimable.Moreover i see the oldest flashback time going as far as 4 months back in time eventhough i don't have any restore points.

      Any help/advice is appreciated on this issue!!

      SQL> show parameter recovery

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_recovery_file_dest string +FRA
      db_recovery_file_dest_size big integer 100G
      recovery_parallelism integer 0
      SQL> show parameter flashback

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      db_flashback_retention_target integer 720
      SQL> select OLDEST_FLASHBACK_TIME from gv$flashback_database_log;

      OLDEST_FLASHBAC
      ---------------
      032911 13:47:43
      032911 13:47:43

      SQL> select sysdate from dual;

      SYSDATE
      ---------------
      072711 13:09:34

      SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

      FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
      -------------------- ------------------ ------------------------- ---------------
      CONTROL FILE .05 0 1
      REDO LOG 0 0 0
      ARCHIVED LOG .27 0 70
      BACKUP PIECE 0 0 0
      IMAGE COPY 0 0 0
      FLASHBACK LOG 73.69 0 4716
      FOREIGN ARCHIVED LOG 0 0 0

      7 rows selected.

      SQL> select banner from gv$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production

      10 rows selected.


      SQL> select name, space_limit as Total_size ,space_used as Used,
      2 SPACE_RECLAIMABLE as reclaimable ,NUMBER_OF_FILES as "number"
      3 from V$RECOVERY_FILE_DEST;

      NAME TOTAL_SIZE USED RECLAIMABLE number
      ---------- ---------- ---------- ----------- ----------
      FRA       1.0737E11 7.9469E+10 0 4788

      SQL>