ORA-00824: Cannot Set sga_target Due to Existing Internal Settings

Question: I reset the memory structure, and now I cannot start the database. I'm getting the following error message:

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

How can I get rid of this ORA-00824 error and get my database back online?

Answer: To diagnose any error, you start by using the oerr utility to display the ORA-00824: cannot set sga_target due to existing internal settings error:

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

Cause: Unable to set sga_target due to current parameter settings.

Action: See alert log for more information.

The alert log will contain specific information on actions to take to rectify the condition causing the ORA-00824 error. Let's assume the alert.log contains the following message:

Cannot set sga_target with db_block_buffers set

The obvious answer is to not set db_block_buffers when using sga_target. Enabling automatic SGA Management by setting sga_target>0 in the same init.ora file when using the obsolete db_block_buffers parameter, pretty much guarantees an ORA-00824 error.

Either set sga_target=0 to disable SGA Management or remove the db_block_buffer paramenter from your spfile. Since the db_block_buffer parameter is obsolete, it makes sense to remove it. Attempting to edit the spfile directly with a text editor can cause a corruption issue. To safely remove the db_block_buffer parameter, do the following:

1. Create a pfile from the spfile
2. Remove the line containing the db_block_buffer parameter using a text editor
3. Create a new spfile from the updated pfile
4. Startup the database

The ORA-00824 should no longer be an issue.