Skip to Content
avatar image
Former Member

How to safely remove parameter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 23, 2014 at 03:34 PM

    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...

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 22, 2014 at 09:05 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 22, 2014 at 12:50 PM

    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ş

    Add comment
    10|10000 characters needed characters exceeded