DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown I am using oracle database 11gR2.

I increased db_recovery_file_dest size with alter command

alter system set db_recovery_file_dest_size=60G scope=both;


When I shutdown database and again open database then db_recovery_file_dest_size automatically changed to default size 10G.

Now I am getting error

the database is not available due to the following conditions stuck archiver


How to solve this error
This is normal. If you set a parameter without specifying sid, then it will create/modify the entry in spfile with sid=*. But if you have another value set with the actual sid (instance name), that takes precedence over *, and the value remains unchanged.

Demonstration:

SQL&gt; show parameter db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /fra
db_recovery_file_dest_size           big integer 20G

SQL&gt; show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 20G

SQL&gt; alter system set db_recovery_file_dest_size =10G sid='MIN19' scope=spfile;

System altered.

SQL&gt; show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G


My instance is called MIN19, so even though 20G is set for *, it will start with 10G:

SQL&gt; startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes

SQL&gt; show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G
SQL&gt; show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G


I may change it to whatever I want, it will not have effect:

SQL&gt; alter system set db_recovery_file_dest_size = 30G scope=spfile;

System altered.

SQL&gt; show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 30G
SQL&gt; startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL&gt; show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G


Make sure to clean up overlapping parameters and use only 1 of them to avoid confusion. Example:

SQL&gt; alter system reset db_recovery_file_dest_size sid = 'MIN19';

System altered.

SQL&gt; show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 30G
SQL&gt; startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL&gt; show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G
SQL&gt;