How to safely remove parameter

[Former Member]

Hello,

DB16 is warning me that my Oracle DB (version 10.2.0.4.0) has the following parameter set:

OPTIMIZER_FEATURES_ENABLE

Which, according to SAP note 830576 ("Parameter recommendations for Oracle 10g), shouldn't be set.

However, it has the value "10.2.0.1".

I've searched and found this thread, here at SCN:

This thread has the following instructions:

SQL> create pfile from spfile;

Edit the pfile according to your needs

and after this:

sql> shutdown immediate

sql> create spfile from pfile;

sql> startup

Also, there will be many parameter which will take default value if you do not set it.

Since I'm an Oracle newbie, I'm wondering:

1st) Where will the pfile be created?

2nd) How can I be sure that I don't have any problems related to the last sentence: "Also, there will be many parameter which will take default value if you do not set it."

thanks

Antonio

[Former Member · Accepted solution]

hi...

thanks for both your answers...

this was solved with this command:

alter system reset optimizer_features_enable scope = spfile sid='*';

thanks for your help, anyway. Good information...

[former_member196518]

Hi Antonio,

firstly ,  you can check this parameter dynamically switchable or not .

i have checked this parameters for you and it's a dynamic parameter, you dont need restart to activate this parameter.

oracle you can check  v$parameter table for more information

SQL> select ISSYS_MODIFIABLE from v$parameter where name = 'optimizer_features_enable';

ISSYS_MOD

---------

IMMEDIATE    it means that change takes effect immediately.

SQL> ALTER SYSTEM SET optimizer_features_enable='10.2.0.4' scope=both;

secondly,  if you want to change by using pfile you can apply this steps,

1. create pfile from spfile;

2. pfile created in

for windows

Disk:\oracle\SID\11203\database

for Linux,Unix etc.

/oracle/SID/11203/dbs

after changed this parameter you must start with pfile

3. startup pfile=/oracle/SID/11203/dbs/initSID.ora

4. create spfile from pfile;

Regards,

Ümit Toptaş

[Former Member]

Hello Antonio,

1-Normally you can find the spfile and pfile (mostly initSID.ora) under this path: /oracle/SID/102_64/dbs/ .

2-You can use primitive Oracle parameter check script which is attached to that note.

My recommendation here is just takin a backup of your spfile and then reset the parameter:

For example:

cp -p spfileSID.ora spfileSID.oraORIG

sqlplus > alter system reset OPTIMIZER_FEATURES_ENABLE scope=spfile sid='*';

If that OPTIMIZER_FEATURES_ENABLE parameter is an online one you won't need a restart and if so you can use that command:

sqlplus > alter system reset OPTIMIZER_FEATURES_ENABLE scope=both sid='*';

"Both" forces updating both spfile and real-time (in-run) value of the parameter.

Regards,

Serhat