How to add a comment when changing a parameter?

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

Advertisements

How to find parameters that will take into effect for new sessions?

Using the following query one can find the list of parameters that will take info effect for new sessions if the value of the parameter is changed.
SQL> SELECT name FROM v$parameter WHERE issys_modifiable = ‘DEFERRED’;

The parameter has be changed using the deferred option:
SQL> alter system set sort_area_size=65538 deferred;

System altered.

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

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?

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