How to change db_recovery_file_dest and db_recovery_file_dest_size parameter

Applies to:

 Oracle Std & Enterprise Edition - Version: 11.1.1.1.0 and later   [Release: 11gR1 and later ]Information in this document applies to any platform.

DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery area backups. The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed.


Below are the steps to Change  DB_RECOVERY_FILE_DEST & DB_RECOVERY_FILE_DEST_SIZE Parameter:


[oracle@]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 19 18:30:39 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
-------------------------------- ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/test01/flash_recovery_area
db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='/opt/oracle/test01/dbs/arch' scope=both;
System altered.

SQL> show parameter db_recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch
db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest_size

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

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch
db_recovery_file_dest_size           big integer 20G
SQL>

You can also face "ORA-19809: limit exceeded for recovery files" due to space crunch in db_recovery_file_dest location. To resolve this issue see How to Solve ORA-19809 ?

5 comments:

  1. Pretty! This has been an incredibly wonderful article.
    Thank you for supplying this information.

    my weblog ... buy garcinia cambogia

    ReplyDelete
  2. After spending the better part of my day reading blogs and trying to figure out how to get my database back online, this blog got me going within minutes.. very good. Thanks.

    ReplyDelete
  3. thanks friends for finding solution and i more thing where i can find ora errors with there solution

    ReplyDelete