FAQ
I suppose that will work. I typically just stage them vi 'ALTER SYSTEM SET
= .ora
startup

Bradd Piontek
"Next to doing a good job yourself,

the greatest joy is in having someone
else do a first-class job under your
direction."

William Feather
On Fri, Jul 31, 2009 at 12:09 PM, wrote:

Hello:

I want to make sure that I am making this change correctly. I have a
10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
SGA_TARGET parameters. My plan is to

- modify the init.ora file
- make a copy of an existing spfile
- shut down the database
- restart as startup using pfile=initXXX.ora
- when database is started execute create spfile from pfile

That should guarantee that these changes are in the spfile and will not be
lost next time the database is bounced. Am I correct?

thank you

Gene Gurevich




Please consider the environment before printing this email.


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Joel.Patterson_at_crowley.com at Jul 31, 2009 at 5:18 pm
    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • TESTAJ3_at_nationwide.com at Jul 31, 2009 at 6:29 pm
    My only problem with creating pfile from spfile is I lose all of the
    internal documentation, why can't oracle keep all of those # lines in the
    spfile, dooohhh.

    joe

    Joe Testa, Oracle Certified Professional
    (Work) 614-677-1668
    (Cell) 614-312-6715

    Interested in helping out your marriage?
    Ask me about "Weekend to Remember"
    Dec 11-13, 2009 here in Columbus.

    From:

    To:,
    Date:
    07/31/2009 01:19 PM
    Subject:
    RE: changing init.ora and spfile
    Sent by:
    oracle-l-bounce_at_freelists.org

    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Mayen.Shah_at_lazard.com at Jul 31, 2009 at 6:39 pm
    For the same reason, I prefer init.ora over spfile. I can put comments for
    reason for specific parameter value, group similar parameters (e.g. all
    memory related parameter together etc).
    Putting comments also satisfy auditors.

    Thanks
    Mayen

    TESTAJ3_at_nationwide.com
    Sent by: oracle-l-bounce_at_freelists.org
    Jul 31 2009 02:29 PM
    Please respond to
    TESTAJ3_at_nationwide.com

    To
    Joel.Patterson_at_crowley.com
    cc
    genegurevich_at_discover.com, oracle-l@freelists.org
    Subject
    RE: changing init.ora and spfile

    My only problem with creating pfile from spfile is I lose all of the
    internal documentation, why can't oracle keep all of those # lines in the
    spfile, dooohhh.

    joe

    Joe Testa, Oracle Certified Professional
    (Work) 614-677-1668
    (Cell) 614-312-6715

    Interested in helping out your marriage?
    Ask me about "Weekend to Remember"
    Dec 11-13, 2009 here in Columbus.

    From:

    To:,
    Date:
    07/31/2009 01:19 PM
    Subject:
    RE: changing init.ora and spfile
    Sent by:
    oracle-l-bounce_at_freelists.org

    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Jul 31, 2009 at 7:03 pm
    Hmm...it would be a nice enhancement to be able to do something like:
    alter system set db_cache_size = 4G scope=both comment='Updated by M.Bobak as per Change Control Request #78512';

    And have that comment recorded somewhere in the data dictionary, and automatically dumped when you did a 'create pfile from spfile;'.

    Hmm....how do I file an enhancement request?

    -Mark

    From: oracle-l-bounce_at_freelists.org On Behalf Of Mayen.Shah_at_lazard.com
    Sent: Friday, July 31, 2009 2:39 PM
    To: TESTAJ3_at_nationwide.com
    Cc: genegurevich_at_discover.com; Joel.Patterson_at_crowley.com; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
    Subject: RE: changing init.ora and spfile

    For the same reason, I prefer init.ora over spfile. I can put comments for reason for specific parameter value, group similar parameters (e.g. all memory related parameter together etc).
    Putting comments also satisfy auditors.

    Thanks
    Mayen

    TESTAJ3_at_nationwide.com
    Sent by: oracle-l-bounce_at_freelists.org

    Jul 31 2009 02:29 PM
    Please respond to
    TESTAJ3_at_nationwide.com

    To

    Joel.Patterson_at_crowley.com

    cc

    genegurevich_at_discover.com, oracle-l@freelists.org

    Subject

    RE: changing init.ora and spfile

    My only problem with creating pfile from spfile is I lose all of the internal documentation, why can't oracle keep all of those # lines in the spfile, dooohhh.

    joe

    Joe Testa, Oracle Certified Professional
    (Work) 614-677-1668
    (Cell) 614-312-6715

    Interested in helping out your marriage?
    Ask me about "Weekend to Remember"
    Dec 11-13, 2009 here in Columbus.

    From:

    To:,

    Date:

    07/31/2009 01:19 PM

    Subject:

    RE: changing init.ora and spfile

    Sent by:

    oracle-l-bounce_at_freelists.org

    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).

    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bradd Piontek at Jul 31, 2009 at 7:25 pm
    Mark, In 10g and 11g (pretty sure in 9i), you can do just that. The
    comments are stored in the UPDATE_COMMENT field in V$SPPARAMETER
    When the pfile is dumped out, the comments show up at the end of the line

    Bradd Piontek
    "Next to doing a good job yourself,

    the greatest joy is in having someone
    else do a first-class job under your
    direction."

    William Feather
    On Fri, Jul 31, 2009 at 2:03 PM, Bobak, Mark wrote:

    Hmm…it would be a nice enhancement to be able to do something like:

    alter system set db_cache_size = 4G scope=both comment=’Updated by M.Bobak
    as per Change Control Request #78512’;



    And have that comment recorded somewhere in the data dictionary, and
    automatically dumped when you did a ‘create pfile from spfile;’.



    Hmm….how do I file an enhancement request?



    -Mark



    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Mayen.Shah_at_lazard.com
    *Sent:* Friday, July 31, 2009 2:39 PM
    *To:* TESTAJ3_at_nationwide.com
    *Cc:* genegurevich_at_discover.com; Joel.Patterson_at_crowley.com;
    oracle-l@freelists.org; oracle-l-bounce_at_freelists.org

    *Subject:* RE: changing init.ora and spfile




    For the same reason, I prefer init.ora over spfile. I can put comments for
    reason for specific parameter value, group similar parameters (e.g. all
    memory related parameter together etc).
    Putting comments also satisfy auditors.

    Thanks
    Mayen





    *TESTAJ3_at_nationwide.com*
    Sent by: oracle-l-bounce_at_freelists.org

    Jul 31 2009 02:29 PM

    Please respond to
    TESTAJ3_at_nationwide.com

    To

    Joel.Patterson_at_crowley.com

    cc

    genegurevich_at_discover.com, oracle-l@freelists.org

    Subject

    RE: changing init.ora and spfile




    My only problem with creating pfile from spfile is I lose all of the
    internal documentation, why can't oracle keep all of those # lines in the
    spfile, dooohhh.

    joe

    _______________________________________
    Joe Testa, Oracle Certified Professional
    (Work) 614-677-1668
    (Cell) 614-312-6715

    Interested in helping out your marriage?
    Ask me about "Weekend to Remember"
    Dec 11-13, 2009 here in Columbus.

    From:



    To:

    ,

    Date:

    07/31/2009 01:19 PM

    Subject:

    RE: changing init.ora and spfile

    Sent by:

    oracle-l-bounce_at_freelists.org







    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).



    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    [mailto:oracle-l-bounce_at_freelists.org ] On
    Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    - modify the init.ora file
    - make a copy of an existing spfile
    - shut down the database
    - restart as startup using pfile=initXXX.ora
    - when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich




    Please consider the environment before printing this email.


    --*
    *http://www.freelists.org/webpage/oracle-l


    --*
    *http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Jul 31, 2009 at 7:39 pm
    �Cool, I learned something new today. I'd also wished for a feature like
    that, and behold, it already existed! Now if that column could somehow be
    populated by the "create spfile from pfile" command, the feature would be
    complete. I suppose that would be difficult though, due to varying
    commenting conventions.

    Here it is in the docs for 10g:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2282157

    --
    Jason Heinrich
    On Fri, Jul 31, 2009 at 2:25 PM, Bradd Piontek wrote:

    Mark, In 10g and 11g (pretty sure in 9i), you can do just that. The
    comments are stored in the UPDATE_COMMENT field in V$SPPARAMETER
    When the pfile is dumped out, the comments show up at the end of the line



    Bradd Piontek
    "Next to doing a good job yourself,
    the greatest joy is in having someone
    else do a first-class job under your
    direction."
    -- William Feather

    On Fri, Jul 31, 2009 at 2:03 PM, Bobak, Mark wrote:

    Hmm�it would be a nice enhancement to be able to do something like:

    alter system set db_cache_size = 4G scope=both comment=�Updated by M.Bobak
    as per Change Control Request #78512�;



    And have that comment recorded somewhere in the data dictionary, and
    automatically dumped when you did a �create pfile from spfile;�.



    Hmm�.how do I file an enhancement request?



    -Mark
    --
    http://www.freelists.org/webpage/oracle-l
  • Vordos, Suzy at Aug 3, 2009 at 7:07 pm
    If in your PFILE, the comments are placed to the right of the parameter, when you convert to SPFILE they are retained in v$parameter.update_comments. Example:

    *.db_block_checking='true' # Default=false Required for NFS
    *.db_block_checksum='true' # Default=true Required for NFS

    From: oracle-l-bounce_at_freelists.org On Behalf Of Jason Heinrich
    Sent: Friday, July 31, 2009 3:39 PM
    To: piontekdd_at_gmail.com; Mark.Bobak_at_proquest.com
    Cc: Oracle List
    Subject: Re: changing init.ora and spfile

    Cool, I learned something new today. I'd also wished for a feature like that, and behold, it already existed! Now if that column could somehow be populated by the "create spfile from pfile" command, the feature would be complete. I suppose that would be difficult though, due to varying commenting conventions.

    Here it is in the docs for 10g:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2282157

    --
    Jason Heinrich

    On Fri, Jul 31, 2009 at 2:25 PM, Bradd Piontek > wrote:
    Mark,
    In 10g and 11g (pretty sure in 9i), you can do just that. The comments are stored in the UPDATE_COMMENT field in V$SPPARAMETER
    When the pfile is dumped out, the comments show up at the end of the line

    Bradd Piontek
    "Next to doing a good job yourself,
    the greatest joy is in having someone
    else do a first-class job under your
    direction."
    -- William Feather

    On Fri, Jul 31, 2009 at 2:03 PM, Bobak, Mark > wrote:

    Hmm...it would be a nice enhancement to be able to do something like:

    alter system set db_cache_size = 4G scope=both comment='Updated by M.Bobak as per Change Control Request #78512';

    And have that comment recorded somewhere in the data dictionary, and automatically dumped when you did a 'create pfile from spfile;'.

    Hmm....how do I file an enhancement request?

    -Mark

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Aug 3, 2009 at 7:44 pm
    Thanks Suzy, and everyone else!

    From: Vordos, Suzy
    Sent: Monday, August 03, 2009 3:07 PM
    To: jheinrichdba_at_gmail.com; piontekdd_at_gmail.com; Bobak, Mark
    Cc: Oracle List
    Subject: RE: changing init.ora and spfile

    If in your PFILE, the comments are placed to the right of the parameter, when you convert to SPFILE they are retained in v$parameter.update_comments. Example:

    *.db_block_checking='true' # Default=false Required for NFS
    *.db_block_checksum='true' # Default=true Required for NFS

    From: oracle-l-bounce_at_freelists.org On Behalf Of Jason Heinrich
    Sent: Friday, July 31, 2009 3:39 PM
    To: piontekdd_at_gmail.com; Mark.Bobak_at_proquest.com
    Cc: Oracle List
    Subject: Re: changing init.ora and spfile
    Cool, I learned something new today. I'd also wished for a feature like that, and behold, it already existed! Now if that column could somehow be populated by the "create spfile from pfile" command, the feature would be complete. I suppose that would be difficult though, due to varying commenting conventions.

    Here it is in the docs for 10g:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2282157

    --
    Jason Heinrich

    On Fri, Jul 31, 2009 at 2:25 PM, Bradd Piontek > wrote:
    Mark,
    In 10g and 11g (pretty sure in 9i), you can do just that. The comments are stored in the UPDATE_COMMENT field in V$SPPARAMETER
    When the pfile is dumped out, the comments show up at the end of the line

    Bradd Piontek
    "Next to doing a good job yourself,
    the greatest joy is in having someone
    else do a first-class job under your
    direction."
    -- William Feather

    On Fri, Jul 31, 2009 at 2:03 PM, Bobak, Mark > wrote:

    Hmm...it would be a nice enhancement to be able to do something like:

    alter system set db_cache_size = 4G scope=both comment='Updated by M.Bobak as per Change Control Request #78512';

    And have that comment recorded somewhere in the data dictionary, and automatically dumped when you did a 'create pfile from spfile;'.

    Hmm....how do I file an enhancement request?

    -Mark

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Aug 14, 2009 at 1:55 pm
    Hello:

    We have a GATHER_STATS_JOB running daily at 3AM and analyzing tables with
    stale stats. Does this job analyze these tables
    one after another or does it run analyze on multiuple tables concurrently?
    Does anyone know?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.
  • Stefan Knecht at Jul 31, 2009 at 8:45 pm
    Hey Mark

    Someone must've been quicker than you :-) It's already there ...

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2282157

    Cheers

    Stefan

    Stefan P Knecht
    CEO & Founder
    s_at_10046.ch

    10046 Consulting GmbH
    Schwarzackerstrasse 29
    CH-8304 Wallisellen
    Switzerland

    Phone +41-(0)8400-10046
    Cell +41 (0) 79 571 36 27
    info_at_10046.ch
    http://www.10046.ch
    On Fri, Jul 31, 2009 at 9:03 PM, Bobak, Mark wrote:

    Hmm�it would be a nice enhancement to be able to do something like:

    alter system set db_cache_size = 4G scope=both comment=�Updated by M.Bobak
    as per Change Control Request #78512�;



    And have that comment recorded somewhere in the data dictionary, and
    automatically dumped when you did a �create pfile from spfile;�.



    Hmm�.how do I file an enhancement request?



    -Mark



    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Mayen.Shah_at_lazard.com
    *Sent:* Friday, July 31, 2009 2:39 PM
    *To:* TESTAJ3_at_nationwide.com
    *Cc:* genegurevich_at_discover.com; Joel.Patterson_at_crowley.com;
    oracle-l@freelists.org; oracle-l-bounce_at_freelists.org

    *Subject:* RE: changing init.ora and spfile




    For the same reason, I prefer init.ora over spfile. I can put comments for
    reason for specific parameter value, group similar parameters (e.g. all
    memory related parameter together etc).
    Putting comments also satisfy auditors.

    Thanks
    Mayen





    *TESTAJ3_at_nationwide.com*
    Sent by: oracle-l-bounce_at_freelists.org

    Jul 31 2009 02:29 PM

    Please respond to
    TESTAJ3_at_nationwide.com

    To

    Joel.Patterson_at_crowley.com

    cc

    genegurevich_at_discover.com, oracle-l@freelists.org

    Subject

    RE: changing init.ora and spfile




    My only problem with creating pfile from spfile is I lose all of the
    internal documentation, why can't oracle keep all of those # lines in the
    spfile, dooohhh.

    joe

    _______________________________________
    Joe Testa, Oracle Certified Professional
    (Work) 614-677-1668
    (Cell) 614-312-6715

    Interested in helping out your marriage?
    Ask me about "Weekend to Remember"
    Dec 11-13, 2009 here in Columbus.

    From:



    To:

    ,

    Date:

    07/31/2009 01:19 PM

    Subject:

    RE: changing init.ora and spfile

    Sent by:

    oracle-l-bounce_at_freelists.org







    You can do that. What I usually do is create a pfile from spfile (don't
    overwrite your changes if you have already edited yours. Then I can
    keep a human readable copy around. So do your renaming or whatever, and
    put in place the new init<>.ora file.

    Shutdown
    Create spfile from pfile;
    (sometimes I do the reverse also for same reasons as above, create pfile
    from spfile).
    Startup (it will use the spfile you just created). (Works as a test
    also, redundant or not).



    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    [mailto:oracle-l-bounce_at_freelists.org ] On
    Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, July 31, 2009 1:09 PM
    To: oracle-l@freelists.org
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    - modify the init.ora file
    - make a copy of an existing spfile
    - shut down the database
    - restart as startup using pfile=initXXX.ora
    - when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich




    Please consider the environment before printing this email.


    --*
    *http://www.freelists.org/webpage/oracle-l


    --*
    *http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jul 31, 2009 at 5:19 pm
    Kind of the hard way to do it. This would be easier I think:

    Assuming database is up and running:

    You don't need to make an entry for SGA_MAX_SIZE if you are setting SGA_TARGET. SGA_MAX_SIZE will default to SGA_TARGET.

    This changes the value SGA_MAX_SIZE in the SPFILE. This change is persistent, and will take place after the next reboot.

    2. Change SGA_TARGET using alter system command: alter system set sga_target=500m scope=spfile;
    This changes the value SGA_TARGET in the SPFILE. This change is persistent, and the scope=spfile indicates that the change will take place after the next reboot.

    3. Restart the database (startup force)

    Thats it.

    If something bad happens and the database won't nomount (for example, you set sga_target to high) you can easily convert an spfile to a pfile and back when a database is down using the create spfile from pfile and create pfile from spfile commands. This should not happen often though.

    RF

    Robert G. Freeman
    Oracle ACE
    Author:
    Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
    OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Features (Oracle Press)
    Other various titles out of print now...
    Blog: http://robertgfreeman.blogspot.com
    The LDS Church is looking for DBA's. You do have to be a Church member in
    good standing. A lot of kind people write me, concerned I may be breaking
    the law by saying you have to be a Church member. It's legal I promise! :-)
    http://pages.sssnet.com/messndal/church/parachurch.pdf

    Original Message ----
    From: "genegurevich_at_discover.com"
    To: oracle-l@freelists.org
    Sent: Friday, July 31, 2009 11:09:16 AM
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.
  • Joel.Patterson_at_crowley.com at Jul 31, 2009 at 5:28 pm
    Is it not true now that automatic memory management will not occur is
    sga_max = target? Id resizing buffer cache, shared etc.

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Friday, July 31, 2009 1:19 PM
    To: genegurevich_at_discover.com; oracle-l_at_freelists.org
    Subject: Re: changing init.ora and spfile

    Kind of the hard way to do it. This would be easier I think:

    Assuming database is up and running:

    You don't need to make an entry for SGA_MAX_SIZE if you are setting
    SGA_TARGET. SGA_MAX_SIZE will default to SGA_TARGET.

    This changes the value SGA_MAX_SIZE in the SPFILE. This change is
    persistent, and will take place after the next reboot.

    2. Change SGA_TARGET using alter system command: alter system set
    sga_target=500m scope=spfile;
    This changes the value SGA_TARGET in the SPFILE. This change is
    persistent, and the scope=spfile indicates that the change will take
    place after the next reboot.

    3. Restart the database (startup force)

    Thats it.

    If something bad happens and the database won't nomount (for example,
    you set sga_target to high) you can easily convert an spfile to a pfile
    and back when a database is down using the create spfile from pfile and
    create pfile from spfile commands. This should not happen often though.

    RF

    Robert G. Freeman
    Oracle ACE
    Author:
    Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S
    WAY SOON!

    OCP: Oracle Database 11g Administrator Certified Professional Study
    Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Features (Oracle Press)
    Other various titles out of print now...
    Blog: http://robertgfreeman.blogspot.com
    The LDS Church is looking for DBA's. You do have to be a Church member
    in
    good standing. A lot of kind people write me, concerned I may be
    breaking
    the law by saying you have to be a Church member. It's legal I promise!
    :-)
    http://pages.sssnet.com/messndal/church/parachurch.pdf

    Original Message ----
    From: "genegurevich_at_discover.com"
    To: oracle-l@freelists.org
    Sent: Friday, July 31, 2009 11:09:16 AM
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Jul 31, 2009 at 5:43 pm
    Joel:

    I don't think this is true. In my database currently

    SQL> show parameter sga

    NAME TYPE VALUE
    ------------------------------------ -----------
    ------------------------------
    lock_sga boolean TRUE
    pre_page_sga boolean FALSE
    sga_max_size big integer 7348420608
    sga_target big integer 7348420608

    and I do know for a fact (watching OEM's memory advice) that oracle does
    adjust the sizes for db_cache and shared_pool over time.

    thank you

    Gene Gurevich

    To
    07/31/2009 12:30,
    PM,

    cc

    Subject
    RE: changing init.ora and spfile

    Is it not true now that automatic memory management will not occur is
    sga_max = target? Id resizing buffer cache, shared etc.

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Robert Freeman
    Sent: Friday, July 31, 2009 1:19 PM
    To: genegurevich_at_discover.com; oracle-l_at_freelists.org
    Subject: Re: changing init.ora and spfile

    Kind of the hard way to do it. This would be easier I think:

    Assuming database is up and running:

    You don't need to make an entry for SGA_MAX_SIZE if you are setting
    SGA_TARGET. SGA_MAX_SIZE will default to SGA_TARGET.

    This changes the value SGA_MAX_SIZE in the SPFILE. This change is
    persistent, and will take place after the next reboot.

    2. Change SGA_TARGET using alter system command: alter system set
    sga_target=500m scope=spfile;
    This changes the value SGA_TARGET in the SPFILE. This change is
    persistent, and the scope=spfile indicates that the change will take
    place after the next reboot.

    3. Restart the database (startup force)

    Thats it.

    If something bad happens and the database won't nomount (for example,
    you set sga_target to high) you can easily convert an spfile to a pfile
    and back when a database is down using the create spfile from pfile and
    create pfile from spfile commands. This should not happen often though.

    RF

    Robert G. Freeman
    Oracle ACE
    Author:
    Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S
    WAY SOON!

    OCP: Oracle Database 11g Administrator Certified Professional Study
    Guide (Sybex)
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Features (Oracle Press)
    Other various titles out of print now...
    Blog: http://robertgfreeman.blogspot.com
    The LDS Church is looking for DBA's. You do have to be a Church member
    in
    good standing. A lot of kind people write me, concerned I may be
    breaking
    the law by saying you have to be a Church member. It's legal I promise!
    :-)
    http://pages.sssnet.com/messndal/church/parachurch.pdf

    Original Message ----
    From: "genegurevich_at_discover.com"
    To: oracle-l@freelists.org
    Sent: Friday, July 31, 2009 11:09:16 AM
    Subject: changing init.ora and spfile

    Hello:

    I want to make sure that I am making this change correctly. I have a
    10.2.0.3 DB which users spfile and I need to increase the SGA_MAX and
    SGA_TARGET parameters. My plan is to

    modify the init.ora file
    make a copy of an existing spfile
    shut down the database
    restart as startup using pfile=initXXX.ora
    when database is started execute create spfile from pfile

    That should guarantee that these changes are in the spfile and will not
    be
    lost next time the database is bounced. Am I correct?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l
  • Anonymous at Aug 14, 2009 at 2:01 pm
    Gene,

    It runs sequentially.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, August 14, 2009 9:55 AM
    To: oracle-l@freelists.org
    Subject: GATHER_STATS_JOB

    Hello:

    We have a GATHER_STATS_JOB running daily at 3AM and analyzing tables
    with
    stale stats. Does this job analyze these tables
    one after another or does it run analyze on multiuple tables
    concurrently?
    Does anyone know?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Genegurevich_at_discover.com at Aug 14, 2009 at 3:01 pm
    Thank you, Tom

    thank you

    Gene Gurevich

    "Mercadante,
    Thomas F (LABOR)",
    us>
    cc
    08/14/2009 09:02
    AM Subject
    RE: GATHER_STATS_JOB

    Gene,

    It runs sequentially.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    genegurevich_at_discover.com
    Sent: Friday, August 14, 2009 9:55 AM
    To: oracle-l@freelists.org
    Subject: GATHER_STATS_JOB

    Hello:

    We have a GATHER_STATS_JOB running daily at 3AM and analyzing tables
    with
    stale stats. Does this job analyze these tables
    one after another or does it run analyze on multiuple tables
    concurrently?
    Does anyone know?

    thank you

    Gene Gurevich

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

    Please consider the environment before printing this email.

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 31, '09 at 5:17p
activeAug 14, '09 at 3:01p
posts16
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase