参数文件路径:
$ORACLE_HOME/dbs (/u01/app/oracle/product/11.2.0/db_1/dbs)
spfile[ORACLE_SID].ora二进制文件(保存实例的参数)
init.ora为系统提供的参数文件模板,如spfile文件损坏或丢失可用此模板编辑恢复启库;
[oracle@itkaifang ~]$ cd $ORACLE_HOME/dbs [oracle@itkaifang dbs]$ ls hc_DBUA0.dat lkITKAIFAN spfileitkaifang.ora hc_itkaifang.dat lkITKAIFANG init.ora orapwitkaifang [oracle@itkaifang dbs]$
将spfile二进制参数文件生成pfile可编辑参数文件:
1.生成一个可编辑的参数文件:在SQL*Plus中执行 create pfile from spfile;
2.使用vi编辑器打开生成的pfile文件 init[ORACLE_SID].ora 查看并修改具体参数(路径与spfile相同);
3.重新启动数据库,加载参数文件,生效参数,指定pfile参数文件启动 startup nomount pfile='指定路径';
4.查看参数值是否生效:show parameter 参数。
itkaifang@SYS> create pfile from spfile; File created. itkaifang@SYS> exit Disconnected from Oracle Database 11g Enterprise Edition Relea se 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@itkaifang ~]$ cd $ORACLE_HOME/dbs [oracle@itkaifang dbs]$ ls hc_DBUA0.dat init.ora orapwitkaifang hc_itkaifang.dat lkITKAIFAN spfileitkaifang.ora inititkaifang.ora lkITKAIFANG [oracle@itkaifang dbs]$ vi inititkaifang.ora itkaifang.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentitkaifang.__pga_aggregate_target=335544320 itkaifang.__sga_target=499122176 itkaifang.__shared_io_pool_size=0 itkaifang.__shared_pool_size=205520896 itkaifang.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/itkaifang/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/itkaifang/control01.ctl',' /u01/app/oracle/oradata/itkaifang/control02.ctl'*.db_block_size=8192 *.db_domain='' *.db_name='itkaifan' *.db_unique_name='itkaifang' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=itkaifangXDB)' *.memory_target=833617920 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' itkaifang@SYS> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. itkaifang@SYS> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inititkaifang.ora' ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2217912 bytes Variable Size 549455944 bytes Database Buffers 276824064 bytes Redo Buffers 2433024 bytes itkaifang@SYS> alter database mount; Database altered. itkaifang@SYS> alter database open; Database altered. itkaifang@SYS> show parameter pga NAME TYPE VALUE ----------------------- ------------ ----------- pga_aggregate_target big integer 0 itkaifang@SYS> select * from v$sgastat where pool='shared pool ' and name='free memory'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool free memory 87333160 itkaifang@SYS>
SQL*Plus中以命令修改参数:
三种修改模式:
memory -->在内存更改,立即生效,但重启数据库后修改的参数会重做为原值;
spfile -->会把修改的参数保存至参数文件spfile,但需重启数据库生效修改的参数值;
both -->memory+spfile两种模式的集合,不指定模式的情况下默认。
生效范围:
alter system set 参数名=值 scope=指定模式; --->系统全局生效
alter session set 参数名=值 scope=指定模式; --->当前会话生效
(processes,sessions,max-->修改时需指定模式scope=指定模式)
重点参数管理:
在oracle11g中内存是默认自动管理的配置如下(memory_target=sga_target + pga_aggregate_target):
memory_max_target=796M(此处值以设置不同而不同,给出内存对象的最大值)
memory_target=796M(此处值以设置不同而不同,796M为我配置的当前库自动分配的值)
sga_target=0
pga_aggregate_target=0
目标内存手动管理:
memory_target=0
sga_target=指定值(M)
pga_aggregate_target=指定值(M)
itkaifang@SYS> select status from v$instance; STATUS ------------ OPEN itkaifang@SYS> show parameter memory_target NAME TYPE VALUE ---------------------- ----------- ------------- memory_target big integer 796M itkaifang@SYS> show parameter sga NAME TYPE VALUE ---------------------- ----------- ------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 796M sga_target big integer 0 itkaifang@SYS> show parameter pga NAME TYPE VALUE ---------------------- ----------- ------------- pga_aggregate_target big integer 0 itkaifang@SYS> show parameter memory_max_target NAME TYPE VALUE ---------------------- ----------- ------------- memory_max_target big integer 796M itkaifang@SYS>
将memory_target值修改为0并生成pfile,再以pfile启动至nomount状态,并成功启库;
itkaifang@SYS> alter system set memory_target=0 scope=spfile; System altered. itkaifang@SYS> create pfile from spfile; File created. itkaifang@SYS> shutdown immediate itkaifang@SYS> startup nomount pfile='/u01/app/oracle/product/ 11.2.0/db_1/dbs/inititkaifang.ora' ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes itkaifang@SYS> alter database mount; Database altered. itkaifang@SYS> alter database open; Database altered. itkaifang@SYS>
查看修改后内存参数;
itkaifang@SYS> show parameter memory_target NAME TYPE VALUE ------------------------------------ ----------- ------------- memory_target big integer 0 itkaifang@SYS> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 208M sga_target big integer 0 itkaifang@SYS> show parameter pga NAME TYPE VALUE ------------------------------------ ----------- ------------- pga_aggregate_target big integer 42362470 itkaifang@SYS>
查看当前数据库并发会话最大个数,并更改最大会话数为300;
centos@SYS> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 centos@SYS> centos@SYS> alter system set processes=300 scope=spfile; System altered. centos@SYS>
undo块在内存中保留的时间,可修改为90分钟避免出现ORA-01555错误;
centos@SYS> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 centos@SYS> alter system set undo_retention=5400; System altered. centos@SYS> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 5400 undo_tablespace string UNDOTBS1 centos@SYS>
开启审计;
centos@SYS> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB centos@SYS> alter system set audit_trail=os scope=spfile; System altered. centos@SYS>
重启数据库验证会话数、审计是否生效;
centos@SYS> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. centos@SYS> startup ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 587205752 bytes Database Buffers 239075328 bytes Redo Buffers 2392064 bytes Database mounted. Database opened. centos@SYS> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 300 centos@SYS> show parameter audit_trail NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string OS centos@SYS>