Analysis of Oracle parameters and parameter files in seven categories (1)

Anyone who has studied Oracle knows that Oracle parameters are very important for database initialization. Because Oracle initialization parameters can impose database constraints and resource restrictions, the following describes how to set Oracle parameters and parameter files.

There are three types of initialization parameters:
Derivation Parameters
Operating System dependency Parameters
Variable parameters

1. Derivation of Oracle parameters (derived parameters)

 
 

  
  
select name,value from v$parameter where name in (‘processes’,’sessions’);   
 
 

The processes parameter represents the number of concurrent oracle connections. It is very important to set the processes parameter properly. If the number of processes exceeds the maximum number, dba cannot log on to the database.

We recommend that you change the value of this parameter to 500 when creating a database. The default value of this parameter is 150.

 
 

  
  
alter system set processes=500 scope=spfile;   
 
 

View modified values after restart

 
 

  
  
select name,value from v$parameter where name=’processes’;   
  
  
select * from v$sgastat where name=’processes’;   
 
 

2. Operating System Dependent Parameters

The valid values or value ranges of some parameters are limited by the operating system, such as the db_cache_size parameter, which sets the buffer cache memory size used by oracle.
The maximum value is limited by the physical memory. This type of parameter is usually called the operating system dependency parameter.

3. variable Oracle Parameters

Variable parameters include the most adjustable parameters that may affect system performance. Some variable parameters are set with restrictions, such as open_cursors, and some are set with capacity, such
Db_cache_size

Obtain initialization parameters
Show parameter sga

Use SQL _trace to trace the current session

 
 

  
  
alter session set sql_trace=true;   
  
  
show parameter sga;   
  
  
alter session set sql_trace=false;  
 
 

4. Oracle parameter file
Initialize parameter files (initialization parameter files) PFILE, text file, which can be manually modified
Server parameter files (server parameter files) SPFILE, binary file, cannot be modified manually

 
 

  
  
cd /var/oracle11g/app/dbs   
  
  
file init.ora   
  
  
file spfilehugwww.ora   
 
 

In the SPFILE file, you can modify the parameters in the command line. In sqlplus, modify the parameters through alter system.

 
 

  
  
select sid,name,value from v$spparameter where value is not null;   
  
  
show spparameter;   
  
  
show spparameter memory_target;   
 
 

Calling spfile through pfile and overwriting the parameter settings in spfile with the configured parameters are a method to solve the Parameter Setting Errors in spfile.

5. Modify parameters

You can use alter system or import and export the spfile content.
The new scope option has three optional values:
Memory only changes the current instance and becomes invalid after the database is restarted.
Spfile only changes the spfile settings, does not change the current instance, takes effect after the database is restarted
Both changes the instance and spfile at the same time. The current change takes effect immediately and remains valid after restart.
In the rac environment, you can specify sid = <Instance name>