0

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

  • Which edition are you on? – Michael Kutz Jul 12 at 9:15
  • @Michael Kutz what does mean which edition? – Ramiz Tariq Jul 12 at 9:32
  • Post result of select * from v$version – Michael Kutz Jul 12 at 9:51
  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production – Ramiz Tariq Jul 12 at 10:43
0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.