Managing Database Instances (part 4)

Salah Quddomi
8 Mar, 2023

Managing Initialization-Parameter Files

Oracle uses initialization-parameter files to store information about initialization parameters used when an Oracle instance starts. Oracle reads the parameter file to obtain information about how the Oracle instance should be sized and configured upon startup.

The parameter file can be 

  1. a plaintext file, commonly referred to as a pfile, 
  2. a binary parameter file, commonly referred to as an spfile. 

You can use either type of file to configure instance and database options;  however, there are some important differences between the two types of configuration files :

Pfiles vs. Spfiles  :

Pfile :
  1. Text file that can be edited using a text editor. 
  2. When changes are made to the pfile, the instance must be shut down and restarted before it takes effect.
  3. Is called initinstance_name.ora.
  4. Oracle instance reads only from pfile.
  5. Can be created from an spfile using the create pfile from spfile command.
create pfile from spfile;
Spfile :
  1. Binary file that cannot be edited directly.
  2. Parameter changes made to the database using ALTER SYSTEM are updated in the spfile.
  3. Is called spfileinstance_name.ora.
  4. Oracle instance reads and writes to the spfile.
  5. Can be created from a pfile using the create spfile from pfile command.
create spfile from pfile;

Basic Initialization Parameters :
The basic initialization parameters. A “Yes”  indicates that the parameter is static and cannot be modified dynamically without a database restart.RII
  1. CLUSTER_DATABASE "Yes" Tells the instance whether it is part of a clustered environment.
  2. COMPATIBLE "Yes" Specifies the release level and feature set you want to be active in the instance.
  3. CONTROL_FILES "Yes" Designates the physical location of the database control files.
  4. DB_BLOCK_SIZE "Yes" Specifies the default database block size. The database block size specified at database creation cannot be changed.
  5. DB_CREATE_FILE_DEST "No" Specifies the directory location where database data files will be created if the Oracle-Managed Files feature is used.
  6. DB_CREATE_ONLINE_LOG_DEST_n "No" Specifies the location(s) where the database redo log files will be created if the Oracle-Managed Files feature is used.
  7. DB_DOMAIN "YES" Specifies the logical location of the database on the network.
  8. DB_NAME "YES" Specifies the name of the database that is mounted by the instance.
  9. DB_RECOVERY_FILE_DEST "NO" Specifies the location where recovery files will be written if the flash recovery feature is used.
  10. DB_RECOVERY_FILE_DEST_SIZE "NO" Specifies the amount of disk space available for storing flash recovery files.
  11. DB_UNIQUE_NAME "YES" Specifies a globally unique name for the database within the enterprise.
  12. INSTANCE_NUMBER "YES" Identifies the instance in a Real Application Clusters (RAC) environment.
  13. LDAP_DIRECTORY_SYSAUTH "YES" Enables or disables Oracle Internet Directory–based authentication for SYSDBA and SYSOPER connections to the database.
  14. LOG_ARCHIVE_DEST_n "NO" Specifies as many as nine locations where archived redo log files are to be written.
  15. LOG_ARCHIVE_DEST_STATE_n "NO" Indicates how the specified locations should be used for log archiving.
  16. NLS_LANGUAGE "YES" Specifies the default language of the database.
  17. NLS_TERRITORY "YES" Specifies the default region or territory of the database.
  18. OPEN_CURSORS "NO" Sets the maximum number of cursors that an individual session can have open at one time.
  19. PGA_AGGREGATE_TARGET "NO" Establishes the overall amount of memory that all PGA processes are allowed to consume.
  20. PROCESSES "YES" Specifies the maximum number of operating-system processes that can connect to the instance.
  21. REMOTE_LISTENER "NO" Specifies a network name that points to the address or list of addresses of remote Oracle Net listeners.
  22. REMOTE_LOGIN_PASSWORDFILE "YES" Determines whether the instance uses a password file and what type.
  23. SESSIONS "YES" Determines the maximum number of sessions that can connect to the database.
  24. SGA_TARGET "NO" Establishes the maximum size of the SGA, within which space is automatically allocated to each SGA component when Automatic Memory Management is used.
  25. SHARED_SERVERS "NO" Specifies the number of shared server processes to start when the instance is started. 
  26. STAR_TRANSFORMATION_ENABLED "NO" Determines whether the optimizer will consider star transformations when queries are executed. 
  27. UNDO_TABLESPACE "NO" Specifies which tablespace stores undo segments if the Automatic Undo Management option is used. 
The V$PARAMETER view shows the parameters that are available to use. 
Review the DESCRIPTION column to understand the purpose. The ISBASIC
column with value TRUE identifies the basic parameters.

SELECT name, description FROM v$parameter WHERE isbasic = 'TRUE';

Example of the contents of a typical Oracle Database 12c pfile after the database was created using DBCA:
*.audit_file_dest='/u01/app/oracle/admin/c12ndb1/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u02/oradata/c12ndb1/control01.ctl','/u01/app/oracle/fast_recovery_area/c12ndb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='c12ndb1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=11g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=c12ndb1XDB)'
*.local_listener='LISTENER_C12NDB1'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=4000m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

The sizes of the shared pool, database buffer cache, large pool, and Java pool are not individually specified.  Instead, Oracle’s Automatic Memory Management features allow you to simply set one configuration parameter—MEMORY_TARGET— to establish the total amount of memory allocated to the SGA and PGA.

On production databases, 
if your Oracle license is based on the number of named users,
you can enforce the license compliance by setting the LICENSE_MAX_USERS parameter. 
The default for this parameter is 0, which means you can create any number of users in the database and the license compliance is not enforced.

Certain parameters in the spfile begin with a double underscore. 
These are dynamic areas managed by Oracle automatic features and are used as starting sizes when you restart the database.

$ strings spfileC12DB1.ora | head -10
C12DB1.__data_transfer_cache_size=0
C12DB1.__db_cache_size=788529152
C12DB1.__java_pool_size=16777216
C12DB1.__large_pool_size=16777216
C12DB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
C12DB1.__pga_aggregate_target=788529152
C12DB1.__sga_target=2348810240
C12DB1.__shared_io_pool_size=117440512
C12DB1.__shared_pool_size=1375731712
C12DB1.__streams_pool_size=0

Locating the Default Parameter File

To find the pfile and spfile parameter files is $ORACLE_HOME/dbs on Unix systems and %ORACLE_HOME%\database on Windows systems.
Oracle uses a search hierarchy when a startup command is issued without specifying either a pfile or an spfile.
Oracle looks for files with the following names in the default directory to start the instance:
  1. spfile$ORACLE_SID.ora
  2. spfile.ora
  3. init$ORACLE_SID.ora
Oracle first looks for a parameter file called spfile$ORACLE_SID.ora. 
If it doesn’t find that, it searches for spfile.ora. 
Finally, it searches for a traditional text pfile with the default name of init$ORACLE_SID.ora.
If the parameter files do not exist in the default location or you want to use a different parameter file to start your database,  you can specify a parameter file to use when you issue a startup command to start the Oracle database.

Modifying Initialization-Parameter Values

There are a few options to change the initialization-parameter value, based on the type of parameter file used. Here they are :
  • If PFILE is used, edit the pfile using an OS editor and make any appropriate changes.
  • If SPFILE is used, connect to the instance and make the changes using the ALTER SYSTEM SET parameter_name = value statement.
  • Use EM Database Express to make changes.

V$PARAMETER

The V$PARAMETER view shows information about the initialization parameters that are currently in effect.  This view has several useful columns : 
  1. NAME This specifies the name of the initialization parameter.
  2. VALUE This specifies the current value of the parameter.
  3. DISPLAY_VALUE This specifies the current value in a more user-friendly format.
  4. DESCRIPTION This gives a short description about the parameter.
  5. ISBASIC TRUE indicates that the parameter is categorized as a basic parameter.
  6. ISDEFAULT FALSE indicates that the parameter was specified in the pfile or spfile during instance startup.
  7. ISMODIFIED FALSE indicates that the parameter has not been modified since the instance started.
  8. ISSES_MODIFIABLE TRUE indicates that the parameter can be modified using an ALTER SESSION statement.
  9. ISSYS_MODIFIABLE FALSE indicates that the parameter cannot be modified using an ALTER SYSTEM statement. Such parameters can be changed only using the SCOPE=SPFILE clause.

V$SPPARAMETER

The V$SPPARAMETER view shows the contents of the spfile used to start the instance. 
A TRUE value for the ISSPECIFIED column shows whether the parameter was specified in the spfile.
If a pfile was used to start the instance, all the rows will have FALSE for the ISSPECIFIED column. 
Sometimes, querying the V$SPPARAMETER can produce readable output for parameters that take multiple values.

You can use the ALTER SYSTEM statement to change the value of a parameter system-wide or in the spfile, or both.  you use the SCOPE clause to define where you want to change the parameter value: 
  1. MEMORY 
  2. SPFILE
  3. BOTH 
A value of DEFERRED or IMMEDIATE in the ISSYS_MODIFIABLE column shows that the parameter
can be dynamically changed using ALTER SYSTEM. 
The DEFERRED value indicates that the change you make does not take effect until a new session is started;  the existing sessions will use the current value. 

IMMEDIATE indicates that as soon as you change the value of the parameter,
it is available to all sessions in the instance.  A session is a job or task that Oracle manages.

Some parameters values can be set only at instance startup; they are not modifiable when the instance is running.  Such parameter changes can be made with the SCOPE=SPFILE clause. 

Usually when you make a parameter change, you want it to take effect immediately in memory as well as persist the change across database shutdowns. 
You can use the SCOPE=BOTH clause, which is the default, for this purpose. 
So if you omit the SCOPE clause, Oracle will make changes to the memory and to the spfile. 
If a pfile is used to start the instance, the change will be in memory only for the current running instance.


You can use the SQL*Plus command SHOW PARAMETER to view the current value of an initialization parameter.  You can specify the full parameter name or part of the name. 










## To view all undocumented parameters in the database (logged in as SYSDBA) 
select ksppinm parameter, ksppdesc description
from x$ksppi
where substr(ksppinm,1,1) = '_' ;


##To apply your changes to the spfile only or to the spfile and the currently running instance.
alter system set "<parameter>"=<value> scope=spfile sid='*'.

## To Reset to remove the parameter from SPFile and have it default.
alter system reset "<parameter>" scope=spfile sid='*'.


## To shows the difference in the result from the V$PARAMETER and V$SPPARAMETER views:

SELECT name, value 
FROM v$parameter
WHERE name LIKE 'control%' AND isdefault = 'FALSE';

SELECT name, value
FROM v$spparameter
WHERE name LIKE 'control%' AND isspecified = 'TRUE';

## To change the default date-display format for the session only

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

## To change a parameter value for the current instance but do not want the
change to persist across database shutdowns, you can specify SCOPE=MEMORY, as in the following

ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=MEMORY;

## Oracle will make the change only to the spfile, which takes effect after you restart the database:

Example : 
ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL;
ORA-02095: specified initialization parameter cannot be modified

ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL SCOPE=SPFILE;
System altered.

## Oracle will make changes to the memory and to the spfile. 
If a pfile is used to start the instance, the change will be in memory only for the current running instance.
ALTER SYSTEM SET SGA_TARGET=500M SCOPE=BOTH;

## to view all the parameters related to undo, you can do the following. The first one
(SHOW PARAMETER) shows parameters as in memory, and the second one (SHOW SPPARAMETER) shows parameters defined in the SPFile.

SHOW PARAMETER undo
SHOW SPPARAMETER undo

Previous Post
No Comment
Add Comment
comment url