This content has been marked as final. Show 4 replies
Removing a parameter means exactly that - it needs to be removed because either (1) it is no longer used or available in 11gR2 or (2) unsetting it will cause the database to use the default value of the parameter.
In the MOS Doc, for example, "db_block_buffers" needs to be removed. This default value of 0 of this init.ora parameter in 11gR2 will be used - the SGA will be sized based on the SGA_TARGET init.ora parameter instead.
To unset all of these parameters, create a pfile from spfile, shut down the database, modify the pfile to remove the init.ora parameters, startup the database using the edited pfile, then recreate the spfile from pfile.
How to Modify the Content of an SPFILE Parameter File [ID 137483.1]
Can a SPFILE be used with the E-Business Suite Database ? [ID 780576.1]
With regards to the db_block_buffers parameter ...
Using these steps that you suggest -- create pfile, edit, create spfiile -- produces the exact same result as using this SQL command --> alter system reset PARAM scope=spfile sid='*'; <-- That is, the parameter is not found in either the pfile or spfile but when starting the DB assigns a default value and this value can be seen in both the v$parameter and v$spparameter views.
Also, the MOS note that you referenced states the following under the "Clearing Initialization Parameter Values" section:
"You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance."
The same thing happens with the "user_dump_dest" parameter. If it is not found in the pfile of spfile a default value is created for it when starting the DB.
Thus, it appears that clearing, removing and resetting are synonymous and can be done quickly and easily using the "alter system reset" syntax.
Please let me know if this is not an accurate assessment of this issue.
Correct - either process will provide the same results - as I stated, unsetting a parameter implies that the parameter will take a default value (assuming the parameter is still valid on 11gR2) or because it no longer is available in 11gR2 (e.g. row_locking). All of the init.ora parameters can be queried using V$PARAMETER view
Check the ISDEFAULT column for those parameters which do not have a default value (i.e. which have been explicitly set using pfile or spfile)
Pl see this MOS Doc for a script that will evaluate init.ora parameters in your instance
bde_chk_cbo.sql - EBS initialization parameters - Healthcheck [ID 174605.1]
OK, so it appears that both methods work -- I didn't understand that from your comments nor from my MOS ticket.
It just seems a bit strange to even mention the "create pfile, edit, create spfile" method since it is much more work than using the SQL statement.