LINUX RAC修改配置LOCK_SGA

通过修改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