Oracle Startup and shutdown principles-parameter file management

2. Modifying parameters

Parameters inside the SPFile and pfile files:

orcl.__db_cache_size=373293056
orcl.__java_pool_size=33554432
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=192937984
Orcl.__streams_pool_size=0
*.audit_file_dest= '/u01/oracle/admin/orcl/adump '
*.background_dump_dest= '/u01/oracle/admin/orcl/bdump '
*.compatible= ' 10.2.0.1.0 '
*.control_files= '/u01/oracle/oradata/orcl/control01.ctl ', '/u01/oracle/oradata/orcl/control02.ctl ', '/u01/oracle /oradata/orcl/control03.ctl '
*.core_dump_dest= '/u01/oracle/admin/orcl/cdump '
*.db_block_size=8192
*.db_domain= "
*.db_file_multiblock_read_count=16
*.db_name= ' ORCL '
*.db_recovery_file_dest= '/u01/oracle/flash_recovery_area '
*.db_recovery_file_dest_size=2147483648
*.dispatchers= ' (protocol=tcp) (SERVICE=ORCLXDB) '
*.job_queue_processes=10
*.local_listener= ' ORCL '
*.open_cursors=300
*.pga_aggregate_target=201326592
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.sga_target=605028352
*.undo_management= ' AUTO '
*.undo_tablespace= ' UNDOTBS1 '
*.user_dump_dest= '/u01/oracle/admin/orcl/udump '

2.1 Modifying parameters with commands

Sql> Show parameter processes; --Maximum number of connections allowed for the database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Processes integer 150

Sql> alter system set PROCESSES=250 Scope=spfile; --Modify the maximum number of connections allowed for the database

System altered.

Scope Explanation:
SPFile: Writes the value of this parameter to the SPFile file, and the next time the instance is restarted

After restarting
Sql> Show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Aq_tm_processes integer 0
Db_writer_processes Integer 1
Gcs_server_processes integer 0
Job_queue_processes Integer 10
Log_archive_max_processes Integer 2
Processes integer 250

Memory: The current instance is in effect and is restored when restarted, but the parameter modified in this way must be a dynamic parameter

Sql> alter system set PROCESSES=300 scope=memory;
Alter system set PROCESSES=300 Scope=memory
*
ERROR at line 1:
Ora-02095:specified initialization parameter cannot be modified--This parameter is a static parameter, so it cannot be used in memory mode

Both: combination of the above two methods

Sql> alter system set PROCESSES=300 Scope=both;
Alter system set PROCESSES=300 Scope=both
*
ERROR at line 1:
Ora-02095:specified initialization parameter cannot be modified

Description processes is a static parameter that must be restarted for the instance to take effect


Examples of dynamic parameters:

Sql> Show parameter Cpu_count;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Cpu_count Integer 1

Sql> alter system set cpu_count=2 scope=memory; --Dynamic parameters

System altered.

Sql> Show parameter Cpu_count;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Cpu_count Integer 2

Sql> alter system set cpu_count=2 Scope=both;

System altered.

How to know if a parameter is static or dynamic

In view V$parameter, isinstance_modifiable, if this value is False, indicates that the parameter is a static parameter, and True indicates that the parameter is a dynamic parameter

To modify parameters at session level:
Sql> Show Parameter Db_file_mu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_file_multiblock_read_count Integer 16


Sql> alter session set DB_FILE_MULTIBLOCK_READ_COUNT=32;

Session altered.

Sql> Show Parameter Db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_file_multiblock_read_count Integer 32


2.2 By modifying the Pfile file--reboot required

(1) Reverse SPFile file

SQL > Create Pfile from SPFile;

(2) Open the Initorcl.ora file, modify the parameters

processes=300

(3) Reverse the new Pfile file to SPFile file

SQL > Shutdown Immediate--note that you need the database to be turned off to reverse the solution

SQL > Create SPFile from Pfile;

SQL > Startup

SQL > Show parameter Processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Processes integer 300



3. Oracle parameter file Hierarchical search principle

A. Find SPFILEORCL File
B. Find SPFile File
C. Find Pfile File

Operation:
(1) Rename the parameter file and open the database
[Email protected] dbs]$ MV Spfileorcl.ora spfileorcl.ora-
[Email protected] dbs]$ MV Initorcl.ora initorcl.ora-
SQL > Startup
Sql> Startup
Ora-01078:failure in processing system parameters
Lrm-00109:could not open parameter file '/u01/oracle/product/10.2.0/db_1/dbs/initorcl.ora '

Oracle Startup and shutdown principles-parameter file management