DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown

Question

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

Answer 1

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> show parameter db_reco

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

SQL> show spparameter db_recovery_file_dest_size

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

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

System altered.

SQL> 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> 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> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G
SQL> 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> alter system set db_recovery_file_dest_size = 30G scope=spfile;

System altered.

SQL> 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> 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> 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> alter system reset db_recovery_file_dest_size sid = 'MIN19';

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 30G
SQL> 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> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G
SQL>