Shulou(Shulou.com)06/01 Report--

two。 Modify parameters

Parameters in 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 modify parameters through commands

SQL > show parameter processes;-the maximum number of connections allowed by the database

NAME TYPE VALUE

-

Processes integer 150

SQL > alter system set processes=250 scope=spfile;-- modify the maximum number of connections allowed in the database

System altered.

Scope explained:

Spfile: write the value of this parameter to the spfile file, and the next restart of the instance will take effect.

After reboot

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 takes effect and is restored when it is restarted, but the parameters modified in this way must be dynamic parameters.

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 static, so you cannot use memory.

Both: combine the above two ways

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

It indicates that processes is a static parameter, and the instance must be restarted 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 whether a parameter is static or dynamic

In the view v$parameter, ISINSTANCE_MODIFIABLE, if the value is false, the parameter is static, and TRUE indicates that the parameter is dynamic

Modify parameters at the session 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-restart is required

(1) inverse interpretation of spfile files

SQL > create pfile from spfile

(2) Open the initORCL.ora file and modify the parameters

Processes=300

(3) convert the new pfile file to the spfile file

SQL > shutdown immediate-- Note that the database needs to be closed before it can be solved.

SQL > create spfile from pfile

SQL > startup

SQL > show parameter processes

NAME TYPE VALUE

-

Processes integer 300

3. The principle of hierarchical search for oracle parameter files

a. Find the spfileORCL file

b. Find the spfile file

c. Find the pfile file

Action:

(1) rename the parameter file, and then open the database

[oracle@oracle3 dbs] $mv spfileORCL.ora spfileORCL.ora-

[oracle@oracle3 dbs] $mv initORCL.ora initORCL.ora-

SQL > startup

SQL > startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file'/ u01qqoracle.productUniverse 10.2.0Uniplication1anddbsUniplex initORCL.ora'.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.