Oracle parameter modification Summary

V $ parameter

The modification of Oracle parameters is complex. Some parameters can be modified at the session level, while others must be modified at the system level. Some parameters take effect immediately after modification (no restart is required ), some parameters must be restarted to take effect. How do we know this information? You can query the two columns in the dynamic view v $ parameter (as shown below:

1. ISSES_MODIFIABLE

Indicates whether the parameter can be modified at the session level (Alter session set) (True or False)

2. ISSYS_MODIFIABLE

Indicates whether the parameter can be modified at the system level (Alter system set). The following three values are available:

1) IMMEDIATE-effective immediately

2) DEFERRED-the next session takes effect

3) False-it must be restarted to take effect (Scope = spfile must be specified)

Let's take a look at the following example:

SYS @ TEST16> select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v $ parameter where name in ('workarea _ size_policy ', 'audit _ file_dest', 'sga _ target ', 'sga _ max_size ');

NAME ISSES_MODIFIABL ISSYS_MODIFIABL
--------------------------------------------------------------------------------------------------------------
Sga_max_size FALSE
Sga_target FALSE IMMEDIATE
Audit_file_dest FALSE DEFERRED
Workarea_size_policy TRUE IMMEDIATE

Only workarea_size_policy can be modified at the session level, and the other three can only be modified at the system level.

The modified sga_target takes effect immediately. The next session takes effect after the modified audit_file_dest takes effect. The modified sga_max_size takes effect only after being restarted (spfile ).

Alter session/SYSTEM SET

The syntax of alter session set is as follows:

Alter session set parameter_name = parameter_value;

As mentioned above, not all parameters can be modified at the session level. Only isses_modifiable is set to true.

The syntax of alter system set is as follows:

 

Where:

Comment (optional): additional description can be added during modification;

Deferred (optional): If the issys_modifiable of v $ parameter is deferred, the deferred option must be added to the modification, indicating that the next session will take effect.

Scope (default: both): There are three values:

-- Memory indicates that it is only modified in the memory and will expire after the instance is restarted;

-- Spfile indicates that it is modified only in spfile and takes effect only after the spfile is re-read after restart;

-- Both indicates modifying both memory and spfile (recommended)

Sid (default: *): This option applies to RAC. The default value is *, indicating that all RAC instances are modified at the same time. If you do not want to modify all of them, use Sid to specify the Oracle instance.

Example

Today, developers complain that Oracle databases often fail to be connected and report the following error:

ORA-12519, TNS: no appropriate service handler found this error is because the Oracle parameter processes settings are too small and need to be adjusted, processes current value is:

SYS @ TEST16> show parameter processes;
NAME TYPE VALUE
-----------------------------------------------------------------------------
Processes integer 100

According to the query v $ parameter (as shown below), this parameter is system-level and must be restarted to take effect:

Select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v $ parameter where name = 'process ';
NAME ISSES_MODIFIABL ISSYS_MODIFIABL
--------------------------------------------------------------------------------------------------------------
Processes FALSE

Use alter system set to modify:

SYS @ TEST16> alter system set processes = 1500 comment = 'change from 100 to 100' scope = spfile;

System altered.

Note: scope = spfile must be added here; otherwise, the following error will be reported because issys_modifiable = false

ORA-02095: specified initialization parameter cannot be modified

Recommended reading:

Basic Oracle tutorial-copying a database through RMAN

Reference for RMAN backup policy formulation

RMAN backup learning notes

Oracle Database Backup encryption RMAN Encryption