What occurred:
The Primary database instance failed to start using srvctl citing an ‘ORA-1105 signalled during: alter database mount…’ in the alert log of the primary instance.
Upon a manual attempt at starting up the instance we also received ‘ORA-19808: recovery destination parameter mismatch’ at the SQL prompt.
A review of the parameters of both instances showed the following
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string
db_recovery_file_dest_size big integer 70G
A review of the spfile showed
*.db_recovery_file_dest = ”
*.db_recovery_file_dest_size = 70G
At first glance it would appear that the values are all the same. However a check of v$system_parameter showed that the second instance parameter had in fact been modified.
SQL> select inst_id, name||’=’||value from gv$system_parameter where ISMODIFIED=’MODIFIED’ order by 2;
INST_ID
———-
NAME||’=’||VALUE
——————————————————————————–
2
db_recovery_file_dest=
The command likely was:
alter system set db_recovery_file_dest = ” scope=BOTH SID=’*’;
Though this looks acceptable in fact this command sets different values in memory and the spfile.
In memory set db_recovery_file_dest = ” is interpreted as a NULL value while in the spfile the value is taken as a literal. This means that the next time a single oracle instance starts up if the other remains running you will get a mismatch.
The Resolution:
Since the recovery_file_dest is not actually in use when there is a primary log destination in place, I set the db_recovery_file_dest to an explicit valid value. Again this will not be used for archiving if log_archive_dest_N is set to a location.
To set the db_recovery_file_dest to an explicit valid value I issued the command
SQL> alter system set db_recovery_file_dest = ‘+PRDIH_DG_FLASH’ scope=BOTH sid=’*’;
system altered
This aligned both instance’s parameter with a valid literal value and allowed both instances to start.
Note: A restart of the surviving instance with the original value would have also resolved this but that would have meant a complete downtime for the database.
Lessons learned
In the future if the db_recovery_file_dest is really desired to be set to a NULL value the proper way is to set it to null in memory and unset it in the spfile using 2 commands as follows:
SQL> alter system set db_recovery_file_dest = ” scope=MEMORY sid=’*’;
SQL> alter system reset db_recovery_file_dest scope=SPFILE sid=’*’;
This is a subtle difference but will prevent issues at startup in RAC instances in parameters such as DB_RECOVERY_FILE_DEST that must match across RAC instances.
Hope this helps.