Thursday, June 20, 2013

Increasing Flash Recovery Area Size.

In my test database server I got the following errors while taking the database backup through RMAN.
RMAN-03002: failure of backup plus archivelog command at 06/20/2013 18:40:28
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 67108864 bytes disk space from 2147483648 limit

Workaround.

SQL> SELECT 
OBJECT_TYPE,MESSAGE_TYPE,MESSAGE_LEVEL,REASON,SUGGESTED_ACTION
FROM DBA_OUTSTANDING_ALERTS;

OBJECT_TYPE          MESSAGE_TYPE MESSAGE_LEVEL REASON                                             SUGGESTED_ACTION
-------------------- ------------ ------------- -------------------------------------------------- --------------------------------------------------
EVENT_CLASS          Warning                  5 Metrics "Database Time Spent Waiting (%)" is at 94 Run ADDM to get more performance analysis about yo
                                                .37279 for event class "Network"                   ur system.

RECOVERY AREA        Warning                  1 db_recovery_file_dest_size of 1914853376 bytes is  Choices to free up space from recovery area: 1. Co
                                                100.00% used and has 0 remaining bytes available.  nsider changing RMAN RETENTION POLICY. If you are
                                                                                                   using Data Guard, then consider changing RMAN ARCH
                                                                                                   IVELOG DELETION POLICY. 2. Backup files to tape us
                                                                                                   ing RMAN BACKUP RECOVERY AREA command. 3. Add disk
                                                                                                    space and increase db_recovery_file_dest_size par
                                                                                                   ameter. 4. Delete unnecessary files using RMAN DEL
                                                                                                   ETE command. If OS command was used to delete file
                                                                                                   s, then use RMAN CROSSCHECK and DELETE EXPIRED com
                                                                                                   mands.

 SQL> SELECT
  2  NAME,
  3  TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
  4  TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,'999,999,999,999') AS SPACE_AVAILABLE,
  5  ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
  6  FROM V$RECOVERY_FILE_DEST;

NAME                                SPACE_LIMIT      SPACE_AVAILABLE  PERCENT_FULL
----------------------------------- ---------------- ---------------- ------------
/u01/app/oracle/fast_recovery_area     2,147,483,648      536,246,272           75
SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 2G
 

SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.

No comments:

Post a Comment