OraFAQ Forum: Server Administration » Need help on Initialization Parameters

Home » RDBMS Server » Server Administration » Need help on Initialization Parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help on Initialization Parameters [message #151505]

Thu, 15 December 2005 03:37

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Hi,

How to check which parameter is modifiable using scope=spfile/memory/both.I guess the issys_modifiable parameter in v$parameter is applicable only for scope=memory .Also, which are the main parameters that can be changed dynamically with scope=Memory/Both using spfile that were not sys modifiable
with pfile..??

Regards,
reena

Report message to a moderator

Re: Need help on Initialization Parameters [message #151522 is a reply to message #151505]

Thu, 15 December 2005 06:12

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

 SQL> select distinct issys_modifiable from v$parameter;

ISSYS_MOD
---------
DEFERRED
FALSE
IMMEDIATE

If the ISSYS_MODIFIABLE value is set to FALSE for a parameter,it means that the parameter cannot change its value in the lifetime of the instance; the database needs to be restarted for changes to take effect. A parameter set to IMMEDATE value means that it is dynamic and can be set to change the present active instance as well as future database restarts. A parameter set to DEFERRED is also dynamic, but changes only affect subsequent sessions, currently active sessions will not be affected and retain the old parameter value.

Mean to say :
scope
IMMEDIATE -- memory/spfile/both
FALSE -- spfile only

Hope its clear now.

Report message to a moderator

Re: Need help on Initialization Parameters [message #151539 is a reply to message #151505]

Thu, 15 December 2005 07:29

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Hi Tarun,

But there are certain parameters with issys_modifiable=false in v$parameter which i am not able to change even by using scope=spfile in alter system(Though alter session is allowed).e.g nls_date_format...

Please check the below output:

1* alter system set nls_date_format='dd/mm/yyyy' scope=spfile
SQL> /
alter system set nls_date_format='dd/mm/yyyy' scope=spfile
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

That's where i got confused....!!!Please justify.

Regards,
reena

Report message to a moderator

Re: Need help on Initialization Parameters [message #151544 is a reply to message #151539]

Thu, 15 December 2005 08:19

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

How did you start the database?
using spfile or pfile?
See session.

sys@9i > select sysdate from dual;

SYSDATE
----------
15/12/2005

-- database is using spfile

sys@9i > show parameter SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora

sys@9i > select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name like 'nls_date_format';

NAME                 ISSES ISSYS_MOD
-------------------- ----- ---------
nls_date_format      TRUE  FALSE

sys@9i > alter system set nls_date_format='dd-Mon-yyyy' scope=spfile;

System altered.

--- NO changes. Until you do the restart.

sys@9i > select sysdate from dual;

SYSDATE
----------
15/12/2005

sys@9i > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

-- again using spfile, which is default in 9i and above.

sys@9i > startup
ORACLE instance started.

Total System Global Area  202868968 bytes
Fixed Size                   731368 bytes
Variable Size             167772160 bytes
Database Buffers           33554432 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.

---  your changes are here.

sys@9i > select sysdate from dual;

SYSDATE
-----------
15-Dec-2005


sys@9i > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

--- Startup using pfile.
--  Do the routine stuff.
--
sys@9i > startup open pfile='$ORACLE_HOME/dbs/initmutation.ora'
ORACLE instance started.

Total System Global Area  504859288 bytes
Fixed Size                   731800 bytes
Variable Size             167772160 bytes
Database Buffers          335544320 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.
sys@9i > show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

sys@9i >  alter system set nls_date_format='dd-Mon-yyyy' scope=spfile;
 alter system set nls_date_format='dd-Mon-yyyy' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup


sys@9i > alter system set nls_date_format='dd-Mon-yyyy';
alter system set nls_date_format='dd-Mon-yyyy'
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

And next time, please format your code.

Report message to a moderator

Re: Need help on Initialization Parameters [message #151554 is a reply to message #151544]

Thu, 15 December 2005 09:25

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Hi,

I am using spfile during startup.Then why am i getting such message??

Regards,
reena

Report message to a moderator

Re: Need help on Initialization Parameters [message #151558 is a reply to message #151554]

Thu, 15 December 2005 09:36

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>I am using spfile during startup?
Sure?
Post what you did.
along with version and OS information.

Report message to a moderator

Re: Need help on Initialization Parameters [message #151743 is a reply to message #151558]

Sun, 18 December 2005 22:14

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Hi,

Please check the atached spool for the sequence of code.
Database is 9.2. OS is Windows XP(Professional).

Regards,
reena

Attachment: test5.lst
(Size: 1.16KB, Downloaded 1506 times)

Report message to a moderator

Re: Need help on Initialization Parameters [message #151866 is a reply to message #151743]

Mon, 19 December 2005 08:55

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

For 9.2.0.1 this is documented behaviour.

Report message to a moderator

Previous Topic:	Tablespace file size maximum stuck at 2G
Next Topic:	Need Help in creating Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 13:54:05 CST 2025