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