How to delete parameter from 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”

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

How to make trace files visible to all users?

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.

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

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

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?

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

Internal parameters

– view oracle internal parameters, note this can be run by SYS user
– name, value, default value, session modifiable, system modifiable, description
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '\_%' escape '\'
order by name
/

– If SYSMOD = Immediate (The Parameter can be changed with ALTER SYSTEM without restart of th e instance)
– If SYSMOD = DEFERRED (The Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions)
– If SYSMOD = FALSE (The parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances)