2 minutes readManaging an Oracle Instance

When Oracle engine starts an instance, it reads the initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA and creates background processes. At this point, no database is associated with these memory structures and processes.

Type of initialization file:

• Static (PFILE)
• Persistent (SPFILE)

Pfile is the Text file, We can modification with an OS editor Cammand.

SP file is the binry file. We can not modify it. Oracle server can only modify using alter system command.

Initialization parameter file content:

* Instance parameter
* Name of the database
* Memory structure of the SGA
* Name and location of control file
* Information about undo segments
* Location of udump, bdump and cdump file

Managing Initialization Files

During startup, in $ORACLE_HOME/dbs (for UNIX/Linux) Oracle will look for the correct
initialization file to use, in the following order:

• spfile$ORACLE_SID.ora
• init$ORACLE_SID.ora

# currently used SPFiles (if null, pfile was used)
select * from v$parameter where name = ‘spfile’

# create pfile
CREATE SPFILE=’/u01/oracle/dbs/test_spfile.ora’ FROM PFILE=’/u01/oracle/dbs/test_init.ora’

# use pecific spfile during startup
STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

# PFILE can indicate to use SPFILE
SPFILE = /database/startup/spfileDBA1.ora

Managing Parameters in SPFILE

— display current value of a parameter
select * from v$parameter where name = ‘spfile’

— parameter values set in SPFILE
select NAME, ISSPECIFIED from V$SPPARAMETER where name like ‘%dest%’;

— in SQL Plus
show parameter target

— set parameter value
alter system set parameter=value scope=SPFILE|MEMORY|BOTH

— delete a parameter from SPFILe
ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID=’node01′;

— Only sessions started after the statement is executed are affected
— this option is a must for the parameters whose ISSYS_MODIFIABLE column
— in V$PARAMETER is DEFERRED. You cannot use it, if the column value is FALSE

ALTER SYSTEM SET parameter_name DEFERRED;

— undocumented parameters
SELECT
a.ksppinm parameter, a.ksppdesc description,
b.ksppstvl session_value, c.ksppstvl instance_value
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE
a.indx = b.indx
AND a.indx = c.indx
AND SUBSTR (a.ksppinm,1,1) = ‘_’
ORDER BY a.ksppinm;