oracle 11g 修改内存示例

增加Oracle内存示例：

环境：集群环境Oracle 11.2.0.0.4 

 

脚本示例如下

操作系统修改shm 为3072M ，sqlplus中备份spfile生成pfile，修改pfile中mem参数，使用pfile启动，创建spfile，完毕。

 

[root@rac2 ~]# df -h | grep shm
tmpfs                    1.9G  459M  1.5G  25% /dev/shm
[root@rac2 ~]# grep /etc/fstab
^C
[root@rac2 ~]# grep shm /etc/fstab
[root@rac2 ~]# vi /etc/fstab 
[root@rac2 ~]# grep shm /etc/fstab
tmpfs /dev/shm                    tmpfs    defaults,size=3072m        0 0
[root@rac2 ~]# mount -o remount tmpfs
[root@rac2 ~]# df -h | grep shm
tmpfs                    3.0G  459M  2.6G  15% /dev/shm
[root@rac2 ~]# 

　　fstab文件如下

[root@rac2 ~]# cat /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Tue Sep 14 23:19:16 2021
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/centos-root /                       xfs     defaults        0 0
UUID=e123d649-cfa2-45f6-b49c-eb994a56c914 /boot                   xfs     defaults        0 0
/dev/mapper/centos-swap swap                    swap    defaults        0 0
tmpfs /dev/shm                    tmpfs    defaults,size=3072m        0 0
[root@rac2 ~]# 

　　

sqlplus 中操作如下：

 

[oracle@rac2 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 17 00:23:13 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 1520M
sga_target			     big integer 0
SQL> create pfile="001.ora" from spfile;
create pfile="001.ora" from spfile
             *
ERROR at line 1:
ORA-02236: invalid file name


SQL> create pfile='001.ora' from spfile;

File created.

SQL> ho vi /u01/app/oracle/product/11.2.0/db_1/dbs/001.ora

SQL> startup force pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/001.ora';
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2254952 bytes
Variable Size		 1275070360 bytes
Database Buffers	  855638016 bytes
Redo Buffers		    4923392 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 FALSE
sga_max_size			     big integer 2G
sga_target			     big integer 0
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/001.ora';

File created.

SQL> startup force 
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size		    2254952 bytes
Variable Size		 1275070360 bytes
Database Buffers	  855638016 bytes
Redo Buffers		    4923392 bytes
Database mounted.
Database opened.
SQL> 

　　修改完毕。

　　001.ora 文件参考

[root@rac2 ~]# cat /u01/app/oracle/product/11.2.0/db_1/dbs/001.ora
rac1.__db_cache_size=587202560
rac2.__db_cache_size=587202560
rac1.__java_pool_size=16777216
rac2.__java_pool_size=16777216
rac1.__large_pool_size=33554432
rac2.__large_pool_size=33554432
rac1.__pga_aggregate_target=637534208
rac2.__pga_aggregate_target=637534208
rac1.__sga_target=956301312
rac2.__sga_target=956301312
rac1.__shared_io_pool_size=0
rac2.__shared_io_pool_size=0
rac1.__shared_pool_size=301989888
rac2.__shared_pool_size=301989888
rac1.__streams_pool_size=0
rac2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/rac/controlfile/current.260.1083455781'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='rac'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac2.instance_number=2
rac1.instance_number=1
#*.memory_target=1588592640
*.memory_target=2048m
*.open_cursors=300
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
rac2.thread=2
rac1.thread=1
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'
[root@rac2 ~]#