Sunday, October 12, 2014

How to change db_recovery_file_dest and db_recovery_file_dest_size parameter

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 ?

No comments:

Post a Comment