Oracle Database Parameters

• Introduction: Oracle database parameters are settings that define how the database operates, influencing aspects like memory allocation, resource limits, security, and performance. These parameters are typically stored in a parameter file, which is a text file (PFILE) or a binary file (SPFILE).

• We can use parameters to:
• Set limits for the entire database.
• Set limits on database resources.
• Set user or process limits.
• Control the database's behaviour and performance.

• Parameter Files:
• PFILE (Parameter File- init[SID].ora): A text file containing initialization parameters.
• SPFILE (Server Parameter File- spfile[SID].ora): A binary file that stores initialization parameters.

• Scope: SCOPE parameter in ALTER SYSTEM statements determines where and when parameter changes take effect. Below are the available options to be used.
• MEMORY: It will change the parameter value only in the current instance's memory, and the change will be lost post restart of DB instance.
• SPFILE: It will change the parameter value in the Server Parameter File (SPFILE). DB restart is required to take change effects. 
• BOTH:  It will update the parameter value in both the current instance's memory and the SPFILE. The change will reflect instantly, but only applicable for parameters that can be modified dynamically. 

• Dynamic vs. Static Parameters: Some parameters are dynamic that means those can be changed while the database is running while others are static that means those required Database restart to reflect. 

• Modifying parameters: Dynamic parameters can be modified while the database is running using the ALTER SYSTEM or ALTER SESSION statement, while others require a database restart.
• ALTER SYSETM: Whenever a parameter is modified using this statement, the Oracle Database records the statement that modifies the parameter in the alert log.
• ALTER SESSION: This statement changes the value of the specified parameter for the duration of the session that invokes the statement. The value of the parameter does not change for other sessions in the instance.
  
• How to identify dynamic parameter:  For this we need to query the v$parameter view and refer the value of ISSYS_MODIFIABLE column. If the value is IMMEDIATE, then the parameter can be modified dynamically otherwise not.

    Example:

SQL>set lines 333 pages 999
SQL>col name for a30
SQL>col value for a10
SQL>col ISSYS_MODIFIABLE for a30
SQL>select NAME, VALUE, ISSYS_MODIFIABLE
from V$PARAMETER where           NAME='archive_lag_target';

NAME                           VALUE     ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
archive_lag_target             1200      IMMEDIATE

SQL> select NAME, VALUE, ISSYS_MODIFIABLE from V$PARAMETER where NAME='processes';

NAME                           VALUE     ISSYS_MODIFIABLE
------------------------------ ---------- ------------------------------
processes                      300       FALSE

 As per above output archive_lag_target is dynamic & processes is static.

List of Dynamic/Static Parameter

• Sample commands for changing Parameter:

At session level:
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';

At system level:

Dynamic changes:
alter system set archive_lag_target =900;
alter system set archive_lag_target =900 scope= memory sid=’*’;
alter system set archive_lag_target =900 scope= both sid=’*’;

SID is used in RAC which helps to set parameter on al nodes.

                        Static changes:
                        alter system set processes=1000 scope=spfile;
                        alter system set processes=1000 scope=spfile sid=’*’;

 

 

Thanks for visiting!!