Changing Oracle initsid.ora ,listener.ora parameter

[Former Member]

Hi,

We would like to enable the trace by changing the init.ora ,listener.ora parameter.

and would like to confirm whether restart of R/3 system is necessary or not, if we make changes at database level.

Regds,

Satyanarayana N.

[stefan_koehler · Accepted solution]

Hello Satyanarayana,

>> We would like to enable the trace by changing the init.ora ,listener.ora parameter.

Which tracing? SQL Tracing or SQL*Net Tracing?

>> and would like to confirm whether restart of R/3 system is necessary or not, if we make changes at database level.

It depends on the parameter, if the init.ora parameter can be changed on the fly ... use this query:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#sthref3816

> SQL> SELECT * FROM V$PARAMETER WHERE ISSYS_MODIFIABLE = '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.

It depends on the listener.ora parameters, if you want to enable the listener or the client trace. But you can restart the listener while running a SAP system, because the established sessions/connections will persist.

Regards

Stefan

[Former Member · Accepted solution]

Hi,

Thanks for your quick response...

We are going to edit the parameter manully and then save the parameter....

In this case do we need to restart the system or not..

Regds,

Satyanarayana N.

[stefan_koehler · Accepted solution]

Hello,

>> We are going to edit the parameter manully and then save the parameter....

>> In this case do we need to restart the system or not..

If you mean the init.ora paramter editing by an editor like vi or notepad... then yes, you have to restart the database , but why do you want to do that? If you have a spfile, you should not edit the file with notepad/vi.

If you mean the SQL*Net listener/client parameter .. then yes you have to restart the listener.

Regards

Stefan

[Former Member]

thanks

[Former Member]

Hi,

There are two ways to update the parameter in the system.

1. Update PFILE through the notepad/vi editor and copy the SPFILE from PFILE

i. Make the changes in init<sid>.ora

ii. in sql prompt issue the below command

"copy spfile from pfile;"

iii. need to restart the server to take effect.

2 . You can update directly to the SPFILE.

1. You can issue the command in SQL prompt with alter command

Example

SQL> alter system set utl_file_dir='/tmp' scope=both;

If you say the scope as "both", no need to restart the server, it will effect immediately.

if you say the scope as "spfile" you need to restart the server.

if you say the scope as "memory" no need to restart the server. But the change will effect till the resatrt. If you restart the server the changed paramater value come to original value.

If you want to issue the command with scope as "both", then this perticular paramter "ISSYS_MODIFIABLE" should be IMMEDIATE

You can check in sql prompt.

example

SQL> select name, value, issys_modifiable from v$parameter where name = 'utl_file_dir';

Hope your doubt cleared.

Rgds,

JP.

[Former Member]

Hi

Yes you must restart the both R3 System & Oracle Database

With regards

N.Rameshkarthik

[Former Member]

Hi

Yes you must restart the both R3 System & Oracle Database

With regards

N.Rameshkarthik