I have completed a DB upgrade from 10.2.0.4 to 220.127.116.11 and there is one issue that still has me puzzled related to removing unused/unneeded/deprecated DB parameters.
We are on EBS 18.104.22.168 so I followed this MOS doc (and I am using an spfile):
-> Database Initialization Parameters for Oracle Applications Release 11i [ID 216205.1]
---> Section 2.6.1: Removal List for Oracle Database 11g Release 2
To remove DB parameters I thought that I could use this syntax:
-- alter system reset PARAM scope=spfile sid='*';
But, after running this command for multiple parameters they are still seen, although with their default values.
I opened an MOS ticket for this question and they said that I needed to do the following:
1) Create pfile from memory;
2) Confirm that new pfile created from memory DOES NOT contain "deprecated" parameter value.
3) Then you can create spfile from newly created pfile
I tried this and the spfile was 3 times larger than the original spfile and the DB params I was trying to remove were still in the new spfile.
Can someone please shed some light on what it means to "remove a DB parameter"? Is this the same as setting it to a default value?
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.