LINUX RAC修改配置LOCK_SGA

来源:岁月联盟 编辑:exp 时间:2012-03-02
通过修改lock_sga和pre_page_sga参数可以保证SGA不被换出到虚拟内存,进而可以提高SGA的使用效率。通过这个小文儿给大家展示一下这两个参数的修改过程,不要太乐观,修改过程是存在“小坎坷”的。
当lock_sga参数设置为TRUE时(默认值是FALSE),可以保证整个SGA被锁定在物理内存中,这样可以防止SGA被换出到虚拟内存。只要设置lock_sga为“TRUE”便可保证SGA被锁定在物理内存中,这里之所以顺便将pre_page_sga参数也设置为“TRUE”,是因为这样可以保证在启动数据库时把整个SGA读入到物理内存中,以便提高系统的效率(虽然会增加系统的启动时间)。
 
 env:linux oracle 10.2.0.4
 
 
 
Node2
 
Linux操作系统对每一个任务在物理内存中能够锁住的最大值做了限制!需要手工进行调整。
 
 
 
 
 
[root@ldbrac2 mysql]# su - oracle
 
[oracle@ldbrac2 ~]$ sqlplus / as sysdba
 
 
 
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:27:24 2012
 
 
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
 
 
 
Connected to:
 
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Production
 
With the Partitioning, Real Application Clusters,OLAP and Data Mining options
 
 
 
SQL> show parameter sga
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
lock_sga                             boolean     FALSE
 
pre_page_sga                         boolean     FALSE
 
sga_max_size                         big integer 880M
 
sga_target                           big integer 880M
 
SQL> show parameter lock_a^Hsg^H^[[D^[[D
 
SQL> show parameter lock_sga
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
lock_sga                             boolean     FALSE
 
SQL> alter system set lock_sga=truescope=spfile;
 
 
 
System altered.
 
 
 
SQL> alter system set pre_page_sga=truescope=spfile;
 
 
 
System altered.
 
 
 
SQL> shutdown immediate;
 
Database closed.
 
Database dismounted.
 
ORACLE instance shut down.
 
SQL>      
 
SQL>
 
SQL> startup;
 
ORACLE instance started.
 
 
 
Total System Global Area  922746880 bytes
 
Fixed Size                  1222624 bytes
 
Variable Size             260048928 bytes
 
Database Buffers          654311424 bytes
 
Redo Buffers                7163904 bytes
 
Database mounted.
 
Database opened.
 
SQL> show parameter lock_sga
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
lock_sga                             boolean     TRUE
 
SQL>
 
SQL> show parameter lock_sga
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
lock_sga                             boolean     TRUE
 
SQL>  showparameter lock_sga
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
lock_sga                             boolean     TRUE
 
 
 
 
 
 
 
 
 
 
 
Node1
 
 
 
 
 
[root@ldbrac1 ~]# ulimit -l unlimited
 
[root@ldbrac1 ~]# ulimit -a
 
core file size         (blocks, -c) 0
 
data seg size           (kbytes, -d) unlimited
 
file size               (blocks, -f) unlimited
 
pending signals                 (-i) 1024
 
max locked memory       (kbytes, -l) unlimited
 
max memory size         (kbytes, -m) unlimited
 
open files                      (-n) 1024
 
pipe size           (512 bytes, -p) 8
 
POSIX message queues     (bytes, -q) 819200
 
stack size              (kbytes, -s) 10240
 
cpu time               (seconds, -t) unlimited
 
max user processes              (-u) 32764
 
virtual memory          (kbytes, -v) unlimited
 
file locks                      (-x) unlimited
 
[root@ldbrac1 ~]# su - oracle
 
 
 
[oracle@ldbrac1 ~]$ sqlplus / as sysdba
 
 
 
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:29:58 2012
 
 
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
 
 
 
Connected to:
 
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Production
 
With the Partitioning, Real Application Clusters,OLAP and Data Mining options
 
 
 
SQL> alter system set lock_sga=truescope=spfile;
 
alter system set lock_sga=true scope=spfile
 
*
 
ERROR at line 1:
 
ORA-32001: write to SPFILE requested but no SPFILEspecified at startup
 
 
 
SQL> shutdown immediate;
 
 
 
 
 
[oracle@ldbrac1 ~]$ cat/u01/app/oracle/product/10.2.0/db_1/dbs/initldbrac1.ora
 
ldbrac2.__db_cache_size=666894336
 
ldbrac1.__db_cache_size=734003200
 
ldbrac1.__java_pool_size=4194304
 
ldbrac2.__java_pool_size=8388608
 
ldbrac1.__large_pool_size=4194304
 
ldbrac2.__large_pool_size=4194304
 
ldbrac2.__shared_pool_size=226492416
 
ldbrac1.__shared_pool_size=163577856
 
ldbrac2.__streams_pool_size=8388608
 
ldbrac1.__streams_pool_size=8388608
 
*.audit_file_dest='/u01/app/oracle/admin/ldbrac/adump'
 
*.background_dump_dest='/u01/app/oracle/admin/ldbrac/bdump'
 
*.cluster_database_instances=2
 
*.cluster_database=true
 
*.compatible='10.2.0.1.0'
 
*.control_file_record_keep_time=14
 
*.control_files='+DATA/ldbrac/controlfile/current.260.732154615'
 
*.core_dump_dest='/u01/app/oracle/admin/ldbrac/cdump'
 
*.db_block_size=8192
 
*.db_create_file_dest='+DATA'
 
*.db_domain='domain'
 
*.db_file_multiblock_read_count=16
 
*.db_name='ldbrac'
 
*.dispatchers='(PROTOCOL=TCP)(service=test.domain)'
 
ldbrac1.instance_number=1
 
ldbrac2.instance_number=2
 
*.job_queue_processes=10
 
*.log_archive_dest_1='location=+DATA/ldbrac/archives2mandatory'
 
*.log_archive_dest_2=''
 
*.max_shared_servers=5
 
*.open_cursors=300
 
*.pga_aggregate_target=307232768
 
*.processes=200
 
*.remote_listener='LISTENERS_LDBRAC'
 
*.remote_login_passwordfile='exclusive'
 
*.service_names='ldbrac.domain,test.domain'
 
*.sga_target=922746880
 
*.shared_servers=3
 
ldbrac2.thread=2
 
ldbrac1.thread=1
 
*.undo_management='AUTO'
 
ldbrac2.undo_tablespace='UNDOTBS2'
 
ldbrac1.undo_tablespace='UNDOTBS1'
 
*.user_dump_dest='/u01/app/oracle/admin/ldbrac/udump'
 
 
 
修改pfile加入spfile的指定
 
[oracle@ldbrac1 dbs]$ vi initldbrac1.ora
 
 
 
ldbrac2.__db_cache_size=666894336
 
ldbrac1.__db_cache_size=734003200
 
ldbrac1.__java_pool_size=4194304
 
ldbrac2.__java_pool_size=8388608
 
ldbrac1.__large_pool_size=4194304
 
ldbrac2.__large_pool_size=4194304
 
ldbrac2.__shared_pool_size=226492416
 
ldbrac1.__shared_pool_size=163577856
 
ldbrac2.__streams_pool_size=8388608
 
ldbrac1.__streams_pool_size=8388608
 
*.audit_file_dest='/u01/app/oracle/admin/ldbrac/adump'
 
*.background_dump_dest='/u01/app/oracle/admin/ldbrac/bdump'
 
*.cluster_database_instances=2
 
*.cluster_database=true
 
*.compatible='10.2.0.1.0'
 
*.control_file_record_keep_time=14
 
*.control_files='+DATA/ldbrac/controlfile/current.260.732154615'
 
*.core_dump_dest='/u01/app/oracle/admin/ldbrac/cdump'
 
*.db_block_size=8192
 
*.db_create_file_dest='+DATA'
 
*.db_domain='domain'
 
*.db_file_multiblock_read_count=16
 
*.db_name='ldbrac'
 
*.dispatchers='(PROTOCOL=TCP)(service=test.domain)'
 
ldbrac1.instance_number=1
 
ldbrac2.instance_number=2
 
*.job_queue_processes=10
 
*.log_archive_dest_1='location=+DATA/ldbrac/archives2mandatory'
 
*.log_archive_dest_2=''
 
*.max_shared_servers=5
 
*.open_cursors=300
 
*.pga_aggregate_target=307232768
 
*.processes=200
 
*.remote_listener='LISTENERS_LDBRAC'
 
*.remote_login_passwordfile='exclusive'
 
*.service_names='ldbrac.domain,test.domain'
 
*.sga_target=922746880
 
*.shared_servers=3
 
ldbrac2.thread=2
 
ldbrac1.thread=1
 
*.undo_management='AUTO'
 
ldbrac2.undo_tablespace='UNDOTBS2'
 
ldbrac1.undo_tablespace='UNDOTBS1'
 
*.user_dump_dest='/u01/app/oracle/admin/ldbrac/udump'
 
SPFILE='+DATA/ldbrac/spfileldbrac.ora'
 
 
 
 
 
[oracle@ldbrac1 dbs]$ sqlplus / as sysdba
 
 
 
SQL*Plus: Release 10.2.0.1.0 - Production on WedFeb 29 10:54:19 2012
 
 
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
 
Connected to an idle instance.
 
SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initldbrac1.ora';
 
ORACLE instance started.
 
 
 
Total System Global Area  922746880 bytes
 
Fixed Size                  1222624 bytes
 
Variable Size             180357152 bytes
 
Database Buffers          734003200 bytes
 
Redo Buffers                7163904 bytes
 
Database mounted.
 
SQL> show parameter lock
 
 
 
NAME                                 TYPE        VALUE
 
------------------------------------ -----------------------------------------
 
db_block_buffers                     integer     0
 
db_block_checking                    string      FALSE
 
db_block_checksum                    string      TRUE
 
db_block_size                        integer     8192
 
db_file_multiblock_read_count        integer     16
 
ddl_wait_for_locks                   boolean     FALSE
 
distributed_lock_timeout             integer     60
 
dml_locks                            integer     988
 
gc_files_to_locks                    string
 
lock_name_space                      string
 
lock_sga                             boolean     TRUE
 
SQL>
 
SQL> alter database open;
 
 
 
Database altered.
 
 
 
SQL>
 
 [oracle@ldbrac1 ~]$ ipcs -m|grep ora
0x00fa5a34 131073     oracle    640        94371840   16                     
0xb1260140 262146     oracle    640        924844032  41                locked
 
 
 
 
.lock_sga和pre_page_sga参数在Oracle10gR2官方文档中的描述,供参考。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams097.htm#REFRN10084
LOCK_SGA
Property Description
Parameter type Boolean
Default value false
Modifiable No
Range of values true | false
Basic No
LOCK_SGAlocks the entire SGA into physical memory. It is usually advisable tolock the SGA into real (physical) memory, especially if the use of virtualmemory would include storing some of the SGA using disk space. This parameteris ignored on platforms that do not support it.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams168.htm#REFRN10174
PRE_PAGE_SGA
Property Description
Parameter type Boolean
Default value false
Modifiable No
Range of values true | false
PRE_PAGE_SGAdetermines whether Oracle reads the entire SGA into memory atinstance startup. Operating system page table entries are then prebuilt foreach page of the SGA. This setting can increase the amount of time necessaryfor instance startup, but it is likely to decrease the amount of time necessaryfor Oracle to reach its full performance capacity after startup.
Note:
This setting does not prevent your operating system from paging or swapping theSGA after it is initially read into memory.
PRE_PAGE_SGAcan increase the process startup duration, because every processthat starts must access every page in the SGA. The cost of this strategy isfixed; however, you might simply determine that 20,000 pages must be touchedevery time a process starts. This approach can be useful with someapplications, but not with all applications. Overhead can be significant ifyour system frequently creates and destroys processes by, for example,continually logging on and logging off.
The advantage thatPRE_PAGE_SGAcan afford depends on page size. For example, ifthe SGA is 80 MB in size and the page size is 4 KB, then 20,000 pages must betouched to refresh the SGA (80,000/4 = 20,000).
If the system permits you to set a 4 MB page size, then only 20 pages must betouched to refresh the SGA (80,000/4,000 = 20). The page size is operatingsystem-specific and generally cannot be changed. Some operating systems,however, have a special implementation for shared memory whereby you can changethe page size.
.小结
通过修改lock_sga和pre_page_sga参数值为“TRUE”可以有效的将整个SGA锁定在物理内存中,这样可以有效的提高系统的性能,推荐酌情进行调整。
注意:不同的操作系统对这lock_sga参数的支持情况是不同的,如果操作系统不支持这种锁定,lock_sga参数将被忽略。Windows不支持lock sga。
 
 
 
AIX
 
Metalink上给出修改参考三步骤:
      1.$ /usr/sbin/vmo -r -ov_pinshm=1                                                
      2.$ /usr/sbin/vmo -r -omaxpin%=percent_of_real_memory                                                               
      3.Set LOCK_SGA parameter to TRUE in the init.ora