FAQ
Guys,

i have a 9iR2/win2k test instance.
i just renamed CONTROL01.CTL once and tried to start the instance.

SQL>startup
But it gave a ORA-00205 error.

so i removed CONTROL01.CTL from INIT.ORA file and started the instance
SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
Now it worked fine.

BUT:

SQL>startup
This is not possible because the SPFILE still has CONTROL01.CTL in it.

How do i make my SPFILE in sync with the INIT.ORA ?

Regards,

Search Discussions

  • Sinardy Xing at Jul 15, 2003 at 2:18 am
    USE shortcut like soft link for unix

    -----Original Message-----
    From: Prem Khanna J
    Sent: 15 July 2003 10:59
    To: Multiple recipients of list ORACLE-L
    Subject: How to make SPFILE in sync with INIT.ORA ?

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:

    SQL>startup
    This is not possible because the SPFILE still has CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Kirtikumar Deshpande at Jul 15, 2003 at 2:22 am
    make spfile from pfile;

    something like that.....

    I would suggest just deleting it altogether. :)

    Kirti

    Prem Khanna J wrote:
    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    Do you Yahoo!?
    SBC Yahoo! DSL - Now only $29.95 per month!
  • Jay at Jul 15, 2003 at 2:38 am
    Try OEM it has a couple of ways to help you do this.
    ----- Original Message -----
    From: "Kirtikumar Deshpande"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 11:19 PM
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?
    make spfile from pfile;

    something like that.....

    I would suggest just deleting it altogether. :)


    - Kirti



    --- Prem Khanna J wrote:
    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    __________________________________
    Do you Yahoo!?
    SBC Yahoo! DSL - Now only $29.95 per month!
    http://sbc.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Kirtikumar Deshpande
    INET: kirtikumar_deshpande_at_yahoo.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Arup Nanda at Jul 15, 2003 at 2:48 am
    As user sys, issue

    CREATE SPFILE FROM PFILE;

    This will create the spfile. You must have started the database using the
    pfile to use this command.

    HTH.

    Arup Nanda
    ----- Original Message -----
    From: "Prem Khanna J"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM
    Subject: How to make SPFILE in sync with INIT.ORA ?
    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Niall Litchfield at Jul 15, 2003 at 8:09 pm
    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i installs
    :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the
    database using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
  • Arup Nanda at Jul 15, 2003 at 8:55 pm
    Absolutely! The idea of spfile as a uneditable file but maintained at the
    runtime and the pfile is a text file but not maintained is confusing and
    error prone - especially for junior DBAs who suddenly find that they changed
    something on the init.ora but the effect was not there after the startup.
    When gently remindd of the "wonderful" new feature called spfile - they
    smirk and use pfile=init.ora while starting up; suddenly you can't do an
    ALTER SYSTEM ... SCOPE=BOTH. What was the point behind the spfile then?

    Oracle could have made the spfile a little more user-friendly by making them
    editable as well as maintainable by the software, just like listener.ora, as
    long as the syntax is properly followed. this way there was no need to have
    two different files - spfile and pfile

    Arup Nanda

    Original Message -----
    From: "Niall Litchfield"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 5:09 PM
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?
    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i installs
    :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the
    database using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
  • Molina, Gerardo at Jul 15, 2003 at 10:31 pm
    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    From: Niall Litchfield
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?

    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now how
    to reverse the policy decision having mandated them for 9i installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the
    database using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
  • Arup Nanda at Jul 15, 2003 at 10:50 pm
    And how, exactly?

    Original Message -----
    From: "Molina, Gerardo"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?
    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now how
    to reverse the policy decision having mandated them for 9i installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the
    database using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
  • Tanel Poder at Jul 15, 2003 at 11:05 pm
    I don't appreciate spfile's much either, except in some cases of test
    instances, wheres quite easy to type alter system command followed by
    startup force to test effects of some parameters... But it all can be done
    old fashioned way as well.

    In RAC env I still use normal old-fashioned pfile (where settings for all
    instances are located) and a small script which is able to copy the init.ora
    to all instances appropriate directories. Btw, in production environment one
    shouldn't continuously (manually) modify init.ora parameters anyway,
    especially in 9i, where even SGA sizing parameters are dynamic..

    Tanel.

    Original Message -----
    From: "Arup Nanda"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 2:44 AM
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?
    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    howtoreversethepolicydecisionhavingmandatedthemfor9iinstallsNiall-----Origin
    alMessage-----Fromml-errors_at_fatcity.zl6[mailtoml-errors_at_fatcity.com]OnBehalf
    OfArupNandaSent15July20030444ToMultiplerecipientsof list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the
    database using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
  • Molina, Gerardo at Jul 15, 2003 at 11:10 pm
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory, but
    you would have to manually update init.ora file to reflect your change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    From: Arup Nanda
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?

    And how, exactly?

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM
    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
  • Arup Nanda at Jul 16, 2003 at 1:15 am
    The ability to change the system parameters without bouncing the system is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the change?
    You have to open up the old favorite init.ora file and start the database
    with pfile=init.ora option. At that stage the spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |
    both ] sid = * has no effect. You must create the spfile from the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make the
    changes to some parmeters using alter system persistent; but for all other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net Assistant.

    just my .02

    Arup Nanda

    Original Message -----
    From: "Molina, Gerardo"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory, but
    you would have to manually update init.ora file to reflect your change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  • DENNIS WILLIAMS at Jul 16, 2003 at 3:17 am
    Arup

    My understanding is that Oracle felt if it was going to be a 24x7x365
    database, it had to have more parameters that could be changed online. Some
    parameters were made changeable in Oracle9i, with hints of more to come.

    If the future is online changes, it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to just
    make the change once.

    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against Microsoft's super
    GUI interfaces, the idea of manually editing a text configuration file seems
    very last century.

    I think with the pfile and spfile, Oracle was trying to give us the best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    From: Arup Nanda
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?

    The ability to change the system parameters without bouncing the system is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the change?
    You have to open up the old favorite init.ora file and start the database
    with pfile=init.ora option. At that stage the spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |
    both ] sid = * has no effect. You must create the spfile from the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make the
    changes to some parmeters using alter system persistent; but for all other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net Assistant.

    just my .02

    Arup Nanda

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory, but
    you would have to manually update init.ora file to reflect your change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  • Arup Nanda at Jul 16, 2003 at 4:12 am
    Dennis,
    My understanding is that Oracle felt if it was going to be a 24x7x365
    database, it had to have more parameters that could be changed online
    I couldn't agree more. 9i had more parameters that can be changed online;
    hope there are more in line.
    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to just
    make the change once.
    Absolutely. That's the point I was trying to raise. Oracle did provide the
    spfile route to make _persistent_ changes on the fly, but for only those
    parameters that can be changed through alter system set ... The problem was
    there is a different file that needs to be updated when making those other
    parameter changes. There should be only one place to change - hopefully
    spfile - either through the alter system command or othe editing. This
    editing could be done through a GUI interface, too, if needed; although,
    call me old-fashioned (and I am old, anyway, at least in IT timeline),
    personally I prefer the vi editor. And this is not an impossible idea. I
    gave the example of the listener.ora file. It can be edited (so old
    fashioned), through the lsntctl SAVE_CONFIG command (so spfile-like) or
    through Net Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same approach could
    have been done in pfile-spfile case.
    I think with the pfile and spfile, Oracle was trying to give us the best
    of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked hair-brained
    split-pea honey-mustard-ketchup-coated germ of a solution - sort of like
    Oracle 6 _without_ the Transaction Processing Option, if anyone remembers
    that. After you wipe off all those condiments, you find a Dr. Jekyll & Mr.
    Hyde parameter file(s)!

    Regards,

    Arup

    Original Message -----
    From: "DENNIS WILLIAMS"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?
    Arup
    My understanding is that Oracle felt if it was going to be a 24x7x365
    database, it had to have more parameters that could be changed online. Some
    parameters were made changeable in Oracle9i, with hints of more to come.
    If the future is online changes, it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to just
    make the change once.
    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against Microsoft's super
    GUI interfaces, the idea of manually editing a text configuration file seems
    very last century.
    I think with the pfile and spfile, Oracle was trying to give us the best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the system is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the change?
    You have to open up the old favorite init.ora file and start the database
    with pfile=init.ora option. At that stage the spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |
    both ] sid = * has no effect. You must create the spfile from the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make the
    changes to some parmeters using alter system persistent; but for all other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net
    Assistant.
    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM

    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory,
    but
    you would have to manually update init.ora file to reflect your change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
  • Jack van Zanen at Jul 16, 2003 at 6:56 am
    Hi,

    The java_pool_size can also be edited through sqlplus just use
    'scope=spfile' (You'll need to bounce though). No need to open pfile edit
    and create spfile.
    Quite frankly I like to think that using SQL*Plus to edit the spfile
    directly gives me added benefit of a syntax check (Ran a very very tiny test
    once).

    Jack

    -----Original Message-----
    From: Arup Nanda
    Sent: Wednesday, July 16, 2003 4:14 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?

    The ability to change the system parameters without bouncing the system is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the change?
    You have to open up the old favorite init.ora file and start the database
    with pfile=init.ora option. At that stage the spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |
    both ] sid = * has no effect. You must create the spfile from the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make the
    changes to some parmeters using alter system persistent; but for all other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net Assistant.

    just my .02

    Arup Nanda

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory,
    but you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it.
    Now how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    --
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ------------------------------------------------------------------
    --
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
  • Niall Litchfield at Jul 16, 2003 at 8:01 pm
    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.

    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.
    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM

    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online. Some
    parameters were made changeable in Oracle9i, with hints of
    more to come.
    If the future is online changes, it doesn't make sense
    to rely on
    the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against
    Microsoft's
    super
    GUI interfaces, the idea of manually editing a text
    configuration file
    seems
    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single instance
    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance
    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using
    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from
    the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make
    the changes to some parmeters using alter system
    persistent; but for
    all other parametrs you are forced to use pfile. What
    happens if you
    ommit the pfile=init.ora clause? The database will pickup
    the spfile,
    which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should
    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of
    Oracle do offer that functionality. It's not that difficult
    or unheard
    of, either. Listener.ora, tnsnames.ora are all editable and also read
    by Net
    Assistant.
    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM

    Spfile is shared and can be modified dynamically without bouncing
    the instances.

    Eg. Alter system set db_cache_size = 800m scope = [
    memory | spfile
    both ]
    sid = *

    Without spfile, you can still make this change dynamically in
    memory,
    but
    you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just
    not worth it.
    Now how to reverse the policy decision having mandated
    them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started
    the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"

    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and
    started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and
    in the message BODY, include a line containing:
    UNSUB ORACLE-L (or
    the name of mailing list you want to be removed
    from). You may
    also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of
    mailing list you want to be removed from). You may also
    send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information (like
    subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
  • DENNIS WILLIAMS at Jul 16, 2003 at 8:15 pm
    Niall

    I think that was the intention of the COMMENT parameter. As you point
    out, that isn't foolproof. An improvement might be if Oracle tagged each
    change with a date stamp.

    Dennis Williams
    DBA, 80%OCP, 100% DBA

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    From: Niall Litchfield
    Sent: Wednesday, July 16, 2003 4:00 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?

    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.

    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.
    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM

    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online. Some
    parameters were made changeable in Oracle9i, with hints of
    more to come.
    If the future is online changes, it doesn't make sense
    to rely on
    the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against
    Microsoft's
    super
    GUI interfaces, the idea of manually editing a text
    configuration file
    seems
    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single instance
    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance
    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using
    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from
    the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make
    the changes to some parmeters using alter system
    persistent; but for
    all other parametrs you are forced to use pfile. What
    happens if you
    ommit the pfile=init.ora clause? The database will pickup
    the spfile,
    which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should
    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of
    Oracle do offer that functionality. It's not that difficult
    or unheard
    of, either. Listener.ora, tnsnames.ora are all editable and also read
    by Net
    Assistant.
    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM

    Spfile is shared and can be modified dynamically without bouncing
    the instances.

    Eg. Alter system set db_cache_size = 800m scope = [
    memory | spfile
    both ]
    sid = *

    Without spfile, you can still make this change dynamically in
    memory,
    but
    you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just
    not worth it.
    Now how to reverse the policy decision having mandated
    them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started
    the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"

    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and
    started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and
    in the message BODY, include a line containing:
    UNSUB ORACLE-L (or
    the name of mailing list you want to be removed
    from). You may
    also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of
    mailing list you want to be removed from). You may also
    send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information (like
    subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  • Joe Testa at Jul 16, 2003 at 8:19 pm
    then you vi the spfile, fix it and save it as a pfile, startup the db
    w/the pfile and recreate the spfile.

    joe

    Niall Litchfield wrote:
    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.



    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall

    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,


    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.


    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.


    I think with the pfile and spfile, Oracle was trying to
    give us the

    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM



    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online.
    Some

    parameters were made changeable in Oracle9i, with hints of
    more to come.

    If the future is online changes, it doesn't make sense
    to rely on

    the
    DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that
    doesn't

    think you can operate a computer without a mouse. Against
    Microsoft's
    super

    GUI interfaces, the idea of manually editing a text
    configuration file
    seems

    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best

    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single
    instance

    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the

    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance

    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using

    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and
    your

    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from

    the pfile
    and

    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make

    the changes to some parmeters using alter system
    persistent; but for

    all other parametrs you are forced to use pfile. What
    happens if you

    ommit the pfile=init.ora clause? The database will pickup
    the spfile,

    which will
    _not_

    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should

    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of

    Oracle do offer that functionality. It's not that difficult
    or unheard

    of, either. Listener.ora, tnsnames.ora are all editable and
    also read

    by Net
    Assistant.

    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM



    Spfile is shared and can be modified dynamically without bouncing
    the instances.

    Eg. Alter system set db_cache_size = 800m scope = [
    memory | spfile

    both ]

    sid = *

    Without spfile, you can still make this change dynamically in
    memory,
    but

    you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM



    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just
    not worth it.

    Now how to reverse the policy decision having mandated
    them for 9i

    installs :(

    Niall


    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started
    the database

    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"

    Sent: Monday, July 14, 2003 10:59 PM



    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and
    started the

    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services
    ----------------------------------------------------------------

    ----
    -

    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and

    in the message BODY, include a line containing:
    UNSUB ORACLE-L (or

    the name of mailing list you want to be removed
    from). You may

    also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web hosting
    services


    ----------------------------------------------------------------

    ----
    -
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    ------------------------------------------------------------------

    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the

    name of mailing list you want to be removed from). You
    may also send

    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    ------------------------------------------------------------------

    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the

    name of mailing list you want to be removed from). You
    may also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    --------------------------------------------------------------------

    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the

    message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of

    mailing list you want to be removed from). You may also
    send the HELP

    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    --------------------------------------------------------------------

    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may

    also send the HELP command for other information (like
    subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services


    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may
    also send

    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services


    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may
    also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
  • Joe Testa at Jul 16, 2003 at 8:19 pm
    oh i forgot its "binary" but not THAT binary.

    joe

    Niall Litchfield wrote:
    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.



    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall

    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,


    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.


    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.


    I think with the pfile and spfile, Oracle was trying to
    give us the

    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM



    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online.
    Some

    parameters were made changeable in Oracle9i, with hints of
    more to come.

    If the future is online changes, it doesn't make sense
    to rely on

    the
    DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that
    doesn't

    think you can operate a computer without a mouse. Against
    Microsoft's
    super

    GUI interfaces, the idea of manually editing a text
    configuration file
    seems

    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best

    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single
    instance

    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the

    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance

    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using

    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and
    your

    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from

    the pfile
    and

    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make

    the changes to some parmeters using alter system
    persistent; but for

    all other parametrs you are forced to use pfile. What
    happens if you

    ommit the pfile=init.ora clause? The database will pickup
    the spfile,

    which will
    _not_

    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should

    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of

    Oracle do offer that functionality. It's not that difficult
    or unheard

    of, either. Listener.ora, tnsnames.ora are all editable and
    also read

    by Net
    Assistant.

    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM



    Spfile is shared and can be modified dynamically without bouncing
    the instances.

    Eg. Alter system set db_cache_size = 800m scope = [
    memory | spfile

    both ]

    sid = *

    Without spfile, you can still make this change dynamically in
    memory,
    but

    you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM



    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just
    not worth it.

    Now how to reverse the policy decision having mandated
    them for 9i

    installs :(

    Niall


    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started
    the database

    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"

    Sent: Monday, July 14, 2003 10:59 PM



    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and
    started the

    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.

    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services
    ----------------------------------------------------------------

    ----
    -

    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and

    in the message BODY, include a line containing:
    UNSUB ORACLE-L (or

    the name of mailing list you want to be removed
    from). You may

    also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web hosting
    services


    ----------------------------------------------------------------

    ----
    -
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    ------------------------------------------------------------------

    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the

    name of mailing list you want to be removed from). You
    may also send

    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    ------------------------------------------------------------------

    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message

    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the

    name of mailing list you want to be removed from). You
    may also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    --------------------------------------------------------------------

    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the

    message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of

    mailing list you want to be removed from). You may also
    send the HELP

    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com

    San Diego, California -- Mailing list and web
    hosting services


    --------------------------------------------------------------------

    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in

    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may

    also send the HELP command for other information (like
    subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services


    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may
    also send

    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services


    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may
    also send

    the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
  • Arup Nanda at Jul 16, 2003 at 9:01 pm
    This can be solved by creating the PFILE again from the SPFILE; and this
    doesn't need the instance to be up. However, you did drive the nail home,
    Niall, with this example.

    Part of the problem could be avoided by restricting the ALTER SYSTEM, SYSDBA
    or SYSOPER privileges; but in a large shop with a lot of production support
    DBAS....!!

    Arup Nanda

    Original Message -----
    From: "Niall Litchfield"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 4:59 PM
    Subject: RE: How to make SPFILE in sync with INIT.ORA ?
    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.



    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.
    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM

    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online. Some
    parameters were made changeable in Oracle9i, with hints of
    more to come.
    If the future is online changes, it doesn't make sense
    to rely on
    the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against
    Microsoft's
    super
    GUI interfaces, the idea of manually editing a text
    configuration file
    seems
    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single instance
    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance
    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using
    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from
    the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make
    the changes to some parmeters using alter system
    persistent; but for
    all other parametrs you are forced to use pfile. What
    happens if you
    ommit the pfile=init.ora clause? The database will pickup
    the spfile,
    which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should
    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of
    Oracle do offer that functionality. It's not that difficult
    or unheard
    of, either. Listener.ora, tnsnames.ora are all editable and also read
    by Net
    Assistant.
    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM

    Spfile is shared and can be modified dynamically without bouncing
    the instances.

    Eg. Alter system set db_cache_size = 800m scope = [
    memory | spfile
    both ]
    sid = *

    Without spfile, you can still make this change dynamically in
    memory,
    but
    you would have to manually update init.ora file to reflect your
    change. Spfile allows you to automatically capture these dynamic
    changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just
    not worth it.
    Now how to reverse the policy decision having mandated
    them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started
    the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"

    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and
    started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and
    in the message BODY, include a line containing:
    UNSUB ORACLE-L (or
    the name of mailing list you want to be removed
    from). You may
    also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ----------------------------------------------------------------
    ----
    -
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    ------------------------------------------------------------------
    ---
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L (or the
    name of mailing list you want to be removed from). You
    may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of
    mailing list you want to be removed from). You may also
    send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information (like
    subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services

    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  • Kirtikumar Deshpande at Jul 16, 2003 at 10:12 pm
    The other parameters that will get you in the same trouble are related to the special buffer
    pools.. I ran into this issue with 9iR1 and decided to stay away from SPFILE stuff...
    The failure of Oracle (programmers) checking for such exclusiveness (?) tells their background.
    Doesn't it?
    I forget where I read this (may a post right here), but, someone suggested to create a database
    trigger (to fire before shutdown) to write out pfile from spfile. Good idea, I think. And keep a
    backup of the init.ora.


    SGA_MAX_SIZE is yet another such blunder. It is supposed to set the high water mark for the SGA
    size, so one can "dynamically" change memory allocation to certain SGA components.

    On SUN Solaris (with ISM) it's useless. And, just today I found out that it does not work with AIX
    5L either. Later tonight I will test it with AIX 4.3. The instance started with SGA sized to the
    value specified by this parameter.

    Kirti

    Arup Nanda wrote:
    This can be solved by creating the PFILE again from the SPFILE; and this
    doesn't need the instance to be up. However, you did drive the nail home,
    Niall, with this example.

    Part of the problem could be avoided by restricting the ALTER SYSTEM, SYSDBA
    or SYSOPER privileges; but in a large shop with a lot of production support
    DBAS....!!
    Arup Nanda

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 4:59 PM

    Not only that but this sort of thing should be avoidable.

    SQL> alter system set db_cache_size=10m scope=both;

    System altered.



    SQL> alter system set db_block_buffers=1000 scope=spfile;

    System altered.

    SQL> shutdown;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORA-00381: cannot use both new and old parameters for buffer cache size
    specific
    ation
    SQL>

    What do you do if you didn't have an old fashioned text file? I'd also
    like to know how spfile changes get documented, it scares me enough that
    someone can change parameters on the fly, let alone that that can be
    done without comment or recording who did it. I'll admit thugh that this
    last is actually a failure of management and not technology.

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf Of Arup Nanda
    Sent: 16 July 2003 06:09
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    Dennis,
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online
    I couldn't agree more. 9i had more parameters that can be
    changed online; hope there are more in line.
    it doesn't make sense to rely on the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Absolutely. That's the point I was trying to raise. Oracle
    did provide the spfile route to make _persistent_ changes on
    the fly, but for only those parameters that can be changed
    through alter system set ... The problem was there is a
    different file that needs to be updated when making those
    other parameter changes. There should be only one place to
    change - hopefully spfile - either through the alter system
    command or othe editing. This editing could be done through a
    GUI interface, too, if needed; although, call me
    old-fashioned (and I am old, anyway, at least in IT
    timeline), personally I prefer the vi editor. And this is not
    an impossible idea. I gave the example of the listener.ora
    file. It can be edited (so old fashioned), through the
    lsntctl SAVE_CONFIG command (so spfile-like) or through Net
    Assistant (so GUI-friendly). The end result is the same - one
    file - regardless of how you modify a parameter. The same
    approach could have been done in pfile-spfile case.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best of both worlds.
    Hmm! You think so? I think they just gave us a half-cooked
    hair-brained split-pea honey-mustard-ketchup-coated germ of a
    solution - sort of like Oracle 6 _without_ the Transaction
    Processing Option, if anyone remembers that. After you wipe
    off all those condiments, you find a Dr. Jekyll & Mr. Hyde
    parameter file(s)!

    Regards,

    Arup


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Wednesday, July 16, 2003 12:24 AM

    Arup
    My understanding is that Oracle felt if it was going to be a
    24x7x365 database, it had to have more parameters that could be
    changed online. Some
    parameters were made changeable in Oracle9i, with hints of
    more to come.
    If the future is online changes, it doesn't make sense
    to rely on
    the DBA
    to remember to also update the init.ora file. You should be able to
    just make the change once.
    Also, there is a new generation of I.S. people coming along that doesn't
    think you can operate a computer without a mouse. Against
    Microsoft's
    super
    GUI interfaces, the idea of manually editing a text
    configuration file
    seems
    very last century.
    I think with the pfile and spfile, Oracle was trying to
    give us the
    best
    of both worlds. How well they succeeded is a matter of judgement.

    Dennis Williams
    DBA
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 9:14 PM
    To: Multiple recipients of list ORACLE-L


    The ability to change the system parameters without bouncing the
    system is not provided by spfile, neither in RAC nor single instance
    databas. It depends upon the the parameter that can be changed
    dynamically or not.

    I guess you wanted to convey the impression that the using
    spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for
    single instance
    DBs, too.

    Now, suppose you want to set a parameter that can't be
    changed using
    ALTER SYSTEM, such as, say, java_pool_size. How do you plan to make
    the change? You have to open up the old favorite init.ora file and
    start the database with pfile=init.ora option. At that stage the
    spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory |
    spfile | both ] sid = * has no effect. You must create the
    spfile from
    the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed
    you to make
    the changes to some parmeters using alter system
    persistent; but for
    all other parametrs you are forced to use pfile. What
    happens if you
    ommit the pfile=init.ora clause? The database will pickup
    the spfile,
    which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter,
    Oracle should
    have allowed editing spfile directly and completely done away with
    pfile. Splitting functionality across two different implementations
    adds to difficulties, does not resolve them. I hope future
    versions of
    Oracle do offer that functionality. It's not that difficult
    or unheard
    of, either. Listener.ora, tnsnames.ora are all editable and also read
    by Net
    Assistant.
    just my .02

    Arup Nanda


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
  • Don Yu at Jul 17, 2003 at 2:38 pm
    Hi,

    I really need some helps to configure my database with audit_trail option. I
    have tried some but fail so far. My database is Oracle 9.2. I have read all
    email related to spfile. Can somebody provides simple steps for me to update my
    specified initialization parameter in init.ora?
    (1) Manually change the initialization parameter, e.g. audit_trail = db in
    init.ora. How to sync it when re-start Oracle database?
    (2) Can I change the audit_trail = db initialization parameters in init.ora
    on-line?

    Any comments are appreciated!
    Many thanks!

    Don

    Arup Nanda wrote:
    The ability to change the system parameters without bouncing the system is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the change?
    You have to open up the old favorite init.ora file and start the database
    with pfile=init.ora option. At that stage the spfile is not active and your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile |
    both ] sid = * has no effect. You must create the spfile from the pfile and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make the
    changes to some parmeters using alter system persistent; but for all other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net Assistant.

    just my .02

    Arup Nanda

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory, but
    you would have to manually update init.ora file to reflect your change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it. Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the instance.

    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  • Arup Nanda at Jul 17, 2003 at 3:10 pm
    Well, I thought I answered your question earlier. Here are the steps, in
    more detail.

    I assume your database instance name is ORCL; replace with the actual.

    (1) With your database open, issue a command as sys
    SQL> create pfile from spfile.
    (2) Go to the directory $ORACLE_HOME/dbs and make sue the timestamp of teh
    file initORCL.ora file is now.

    (3) Open the file and place the line audit_trail=db, if not already there.
    (4) Shutdown the database.
    (5) Restart

    SQL> startup pfile=$ORACLE_HOME/dbs/initORCL.ora
    (6) Make sure the audit trail is set.
    SQL> show parameter audit_trail.
    (7) resynch the SPfile.
    SQL> create spfile from pfile;
    (8) And, please let us know here if they worked. If any error occurs, copy
    and paste the error here.

    HTH.

    Arup Nanda

    Original Message -----
    From: "Don Yu"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, July 17, 2003 11:19 AM
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?
    Hi,

    I really need some helps to configure my database with audit_trail option. I
    have tried some but fail so far. My database is Oracle 9.2. I have read all
    email related to spfile. Can somebody provides simple steps for me to update my
    specified initialization parameter in init.ora?
    (1) Manually change the initialization parameter, e.g. audit_trail = db in
    init.ora. How to sync it when re-start Oracle database?
    (2) Can I change the audit_trail = db initialization parameters in init.ora
    on-line?

    Any comments are appreciated!
    Many thanks!

    Don



    Arup Nanda wrote:
    The ability to change the system parameters without bouncing the system
    is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance
    DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using
    ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the
    change?
    You have to open up the old favorite init.ora file and start the
    database
    with pfile=init.ora option. At that stage the spfile is not active and
    your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile
    both ] sid = * has no effect. You must create the spfile from the pfile
    and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make
    the
    changes to some parmeters using alter system persistent; but for all
    other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will
    _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should
    have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net
    Assistant.
    just my .02

    Arup Nanda

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory,
    but
    you would have to manually update init.ora file to reflect your
    change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it.
    Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.
    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L
    (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also
    send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also
    send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name
    of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Don Yu
    INET: donyu_at_jhu.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
  • Don Yu at Jul 17, 2003 at 5:10 pm
    Arup,

    Thank you very much. It works fine now. But my Oracle database instance name is
    bluejayt not ORCL. See the following message:
    $ ps -ef | grep bluejayt

    oracle 5796 1 0 13:01:08 ? 0:00 ora_s000_bluejayt
    oracle 5790 1 0 13:01:08 ? 0:00 ora_reco_bluejayt
    oracle 5800 5774 0 13:01:13 ? 0:03 oraclebluejayt
    (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
    oracle 5788 1 0 13:01:08 ? 0:01 ora_smon_bluejayt
    oracle 5804 5735 0 13:07:17 pts/2 0:00 grep bluejayt
    oracle 5798 1 0 13:01:08 ? 0:00 ora_d000_bluejayt
    oracle 5784 1 0 13:01:08 ? 0:00 ora_lgwr_bluejayt
    oracle 5786 1 0 13:01:08 ? 0:00 ora_ckpt_bluejayt
    oracle 5794 1 0 13:01:08 ? 0:02 ora_qmn0_bluejayt
    oracle 5792 1 0 13:01:08 ? 0:00 ora_cjq0_bluejayt
    oracle 5782 1 0 13:01:07 ? 0:00 ora_dbw0_bluejayt
    oracle 5780 1 0 13:01:07 ? 0:00 ora_pmon_bluejayt

    Thank you very much again!

    Don

    Arup Nanda wrote:
    Well, I thought I answered your question earlier. Here are the steps, in
    more detail.

    I assume your database instance name is ORCL; replace with the actual.

    (1) With your database open, issue a command as sys
    SQL> create pfile from spfile.
    (2) Go to the directory $ORACLE_HOME/dbs and make sue the timestamp of teh
    file initORCL.ora file is now.
    (3) Open the file and place the line audit_trail=db, if not already there.
    (4) Shutdown the database.
    (5) Restart
    SQL> startup pfile=$ORACLE_HOME/dbs/initORCL.ora
    (6) Make sure the audit trail is set.
    SQL> show parameter audit_trail.
    (7) resynch the SPfile.
    SQL> create spfile from pfile;
    (8) And, please let us know here if they worked. If any error occurs, copy
    and paste the error here.

    HTH.
    Arup Nanda

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, July 17, 2003 11:19 AM
    Hi,

    I really need some helps to configure my database with audit_trail option. I
    have tried some but fail so far. My database is Oracle 9.2. I have read all
    email related to spfile. Can somebody provides simple steps for me to update my
    specified initialization parameter in init.ora?
    (1) Manually change the initialization parameter, e.g. audit_trail = db in
    init.ora. How to sync it when re-start Oracle database?
    (2) Can I change the audit_trail = db initialization parameters in init.ora
    on-line?

    Any comments are appreciated!
    Many thanks!

    Don



    Arup Nanda wrote:
    The ability to change the system parameters without bouncing the system
    is
    not provided by spfile, neither in RAC nor single instance databas. It
    depends upon the the parameter that can be changed dynamically or not.

    I guess you wanted to convey the impression that the using spfiles the
    parameters can be changed and the changes can be persistent across
    shutdowns. But that is not just in RAC; it's true for single instance
    DBs,
    too.

    Now, suppose you want to set a parameter that can't be changed using
    ALTER
    SYSTEM, such as, say, java_pool_size. How do you plan to make the
    change?
    You have to open up the old favorite init.ora file and start the
    database
    with pfile=init.ora option. At that stage the spfile is not active and
    your
    issuing Alter system set db_cache_size = 800m scope = [ memory | spfile
    both ] sid = * has no effect. You must create the spfile from the pfile
    and
    then use the newly created spfile to use this "dynamic" parameter
    persistent. Note the complexity involved - spfile allowed you to make
    the
    changes to some parmeters using alter system persistent; but for all
    other
    parametrs you are forced to use pfile. What happens if you ommit the
    pfile=init.ora clause? The database will pickup the spfile, which will
    _not_
    have your changes.

    To fully appreciate the value of the spfile parameter, Oracle should
    have
    allowed editing spfile directly and completely done away with pfile.
    Splitting functionality across two different implementations adds to
    difficulties, does not resolve them. I hope future versions of Oracle do
    offer that functionality. It's not that difficult or unheard of, either.
    Listener.ora, tnsnames.ora are all editable and also read by Net
    Assistant.
    just my .02

    Arup Nanda

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 8:09 PM
    Spfile is shared and can be modified dynamically without bouncing the
    instances.

    Eg. Alter system set db_cache_size = 800m scope = [ memory | spfile | both ]
    sid = *

    Without spfile, you can still make this change dynamically in memory,
    but
    you would have to manually update init.ora file to reflect your
    change.
    Spfile allows you to automatically capture these dynamic changes.

    Gerardo

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 4:45 PM
    To: Multiple recipients of list ORACLE-L


    And how, exactly?

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, July 15, 2003 7:29 PM

    You'll appreciate spfiles if you're using RAC.

    -----Original Message-----
    Sent: Tuesday, July 15, 2003 2:09 PM
    To: Multiple recipients of list ORACLE-L


    And create pfile from spfile; does a similar job.

    I'm rapidly beginning to think that spfiles are just not worth it.
    Now
    how to reverse the policy decision having mandated them for 9i
    installs :(

    Niall
    -----Original Message-----
    From: ml-errors_at_fatcity.com On
    Behalf
    Of Arup Nanda
    Sent: 15 July 2003 04:44
    To: Multiple recipients of list ORACLE-L
    Subject: Re: How to make SPFILE in sync with INIT.ORA ?


    As user sys, issue

    CREATE SPFILE FROM PFILE;
    This will create the spfile. You must have started the database
    using the pfile to use this command.

    HTH.
    Arup Nanda
    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, July 14, 2003 10:59 PM

    Guys,

    i have a 9iR2/win2k test instance.
    i just renamed CONTROL01.CTL once and tried to start the
    instance.
    SQL>startup
    But it gave a ORA-00205 error.

    so i removed CONTROL01.CTL from INIT.ORA file and started the
    instance
    SQL>startup pfile='d:\oracle\admin\pe92\pfile\init.ora'
    Now it worked fine.

    BUT:
    SQL>startup
    This is not possible because the SPFILE still has
    CONTROL01.CTL in it.
    How do i make my SPFILE in sync with the INIT.ORA ?

    Regards,
    Jp.



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Prem Khanna J
    INET: jprem_at_kssnet.co.jp

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in the message BODY, include a line containing: UNSUB ORACLE-L
    (or
    the name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    --------------------------------------------------------------------
    -
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
    and in the message BODY, include a line containing: UNSUB
    ORACLE-L (or the name of mailing list you want to be removed
    from). You may also send the HELP command for other
    information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Niall Litchfield
    INET: niall.litchfield_at_dial.pipex.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also
    send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also
    send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name
    of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Molina, Gerardo
    INET: Gerardo.Molina_at_schwab.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Don Yu
    INET: donyu_at_jhu.edu

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Arup Nanda
    INET: orarup_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
  • Niall Litchfield at Jul 17, 2003 at 8:04 pm
    Kirti wrote
    The other parameters that will get you in the same trouble
    are related to the special buffer pools.. I ran into this
    issue with 9iR1 and decided to stay away from SPFILE stuff...
    The failure of Oracle (programmers) checking for such
    exclusiveness (?) tells their background. Doesn't it?
    I forget where I read this (may a post right here), but,
    someone suggested to create a database trigger (to fire
    before shutdown) to write out pfile from spfile. Good idea, I
    think. And keep a backup of the init.ora.
    Yeah that makes sense, and we mandate that any parameter change goes to
    both the spfile and the pfile and is documented a trigger would be a
    good way to do this. Then I got to thinking, well I have to keep the
    pfile backed up anyway in text form, I have to write cchange
    documentation for a change, so in fact the pfile saves me, um nothing.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 15, '03 at 2:03a
activeJul 17, '03 at 8:04p
posts25
users12
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase