How to add a comment when changing a parameter?

Posted on January 7, 2012 by Alex Lima

In “ALTER SYSTEM” one can include a comment which gets recorded in the spfile and also in v$parameter as shown below. It can be used to record why parameter was changed.

Example:
SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ';

SQL> select value, update_comment from v$parameter where name = 'open_cursors';

VALUE
——————————————————————————–
UPDATE_COMMENT
——————————————————————————–
400

$ strings spfileTESTDB.ORA | grep open_cursors
*.open_cursors=400#07-Jan-2012 Changed AJ needed for application XYZ

How to delete/reset a parameter in spfile using ALTER SYSTEM?

Posted on August 26, 2009 by Alex Lima

Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile.

Example:
— shows the parameter is in the spfile
$ pwd
/u01/oracle/product/10.2.0/db_1/dbs
$ strings spfileTEST.ora | grep open
*.open_cursors=100

— Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it applies to all instances if it is a cluster
SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

# check spfile after running reset command grep no longer finds the parameter in the spfile
$ strings spfileTEST.ora | grep open
$ echo $?
1

Note: If the parameter is not found in the spfile oracle will return “ORA-32010: cannot find entry to delete in SPFILE”, and if the scope is set to both oracle will return “ORA-32009: cannot reset the memory value for instance * from instance TESTDB”

Format for parameter LOG_ARCHIVE_FORMAT?

Posted on January 18, 2009 by Alex Lima

This parameter controls the format of the archive log file name. This parameter can’t be changed on the fly therefore requires a restart of the instance. This parameter can be changed with scope=spfile if spfile is used.

If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and in the alert log the following message would be reported “ORA-00294: invalid archivelog format specifier..” or you will see an error message when you try to archive the current redo log by running “ALTER SYSTEM ARCHIVE LOG CURRENT;”, so if you change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

Format options available on 9i:

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it’s own archivelog

Format options available on 10g

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it’s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

In 10g, %s, %t, %r are required to be present in the parameter, if it doesn’t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r. Using this format makes it the archive log filename unique for that instance.

The following article has information how to start database in archivelog mode

How to make trace files visible to all users?

Posted on September 13, 2008 by Alex Lima

To make trace files visible to all users, set the parameter _TRACE_FILES_PUBLIC=TRUE, this is undocumented parameter. This parameter requires a restart of instance to take into effect.

SQL> alter system set “_TRACE_FILES_PUBLIC”=true scope=spfile;

System altered.

Shows the trace file is accessible by all users, you may have change permissions on the user_dump_dest folder
-rw-r–r– 1 oracle oinstall 1433 Feb 21 21:07 test_ora_26729.trc

View the following link to find if parameter can be modified without restart or requires a restart.
https://oraclespin.wordpress.com/2008/05/14/internal-parameters/

How to tell if the database was started with spfile or pfile?

Posted on June 22, 2008 by Alex Lima

There are couple of ways of finding if the database was started with spfile or pfile.

One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.

— belows show database was started by spfile
SQL> show parameter spfile;

NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora

Another way to find would be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.

SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup

How to identify parameters that has been modified since instance started?

Posted on June 19, 2008 by Alex Lima

To identify the parameter one can query v$parameter view to find the parameters that were modified. The column ISMODIFIED is FALSE by default when the instance starts up, when the value is changed it is SYSTEM_MOD indicating the value is changed at the SYSTEM level and if it’s MODIFIED then it’s changed at session level.

-- Before parameter change displaying value of ISMODIFIED
SQL> select name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         301   FALSE

SQL> alter system set open_cursors=300;

System altered.

SQL> select name, value, ismodified from v$parameter where name = 'open_cursors';

NAME                 VALUE ISMODIFIED
-------------------- ----- ----------
open_cursors         300   SYSTEM_MOD

An Oracle Spin by Alex Lima

Sharing My Database Experience

Category Archives: Parameters