[20191219]shared_pool_size设置跃变.txt

--//别人的系统,增加processes数量,重启出现类似错误:
ORA-00371: not enough shared pool memory, should be atleast 1087664611 bytes

--//自己在测试环境演示看看:

1.环境:
--//为了重复测试建立pfle:
SYS@book> create pfile='/tmp/@.ora' from spfile ;
File created.

2.检查/tmp/book.ora内容:
$ cat /tmp/book.ora
book.__db_cache_size=314572800
book.__java_pool_size=37748736
book.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
book.__shared_pool_size=268435456
*._cursor_bind_capture_area_size=2001
*._dbms_sql_security_level=384
*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/admin/book/adump'
*.audit_sys_operations=TRUE
*.audit_syslog_level='Local0.info'
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.4.0'
*.control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl'#Restore Controlfile
*.db_block_size=8192
#*.db_cache_size=427819008
*.db_cache_size=432013312
*.db_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.db_name='book'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=64424509440
*.db_unique_name='book'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=book,bookXDB)'
*.fal_client='book'
*.fal_server='bookdg'
*.filesystemio_options='NONE'
*.java_pool_size=12582912
*.job_queue_processes=200
*.large_pool_size=12582912
*.log_archive_config='DG_CONFIG=(book,bookdg)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/book/ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=book'
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
*.log_archive_dest_state_2='DEFER'
*.log_archive_max_processes=2
*.log_checkpoints_to_alert=TRUE
*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
*.max_shared_servers=1
#*.memory_max_target=0
#*.memory_target=0
*.open_cursors=300
*.open_links_per_instance=1
*.pga_aggregate_target=4294967296
#*.processes=50
*.processes=2241
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='BOOK','BOOKSHARE'
*.sga_max_size=600M
*.sga_target=0M
#*.shared_pool_size=180355072
*.shared_pool_size=456M
#*.shared_pool_size=1087664611
*.standby_file_management='auto'
*.streams_pool_size=0
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='only'

--//注意*.processes=2241.

3.测试:
SYS@book> startup pfile='/tmp/@.ora';
ORACLE instance started.

Total System Global Area  947920896 bytes
Fixed Size                  2258760 bytes
Variable Size             503318712 bytes
Database Buffers          432013312 bytes
Redo Buffers               10330112 bytes
Database mounted.
Database opened.

--//可以发现启动正常!!
SYS@book> select * from v$sgainfo;
NAME                                          BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size                              2258760 No
Redo Buffers                               10330112 No
Buffer Cache Size                         432013312 Yes
Shared Pool Size                          478150656 Yes
Large Pool Size                            12582912 Yes
Java Pool Size                             12582912 Yes
Streams Pool Size                                 0 Yes
Shared IO Pool Size                               0 Yes
Granule Size                                4194304 No
Maximum SGA Size                          947920896 No
Startup overhead in Shared Pool           453423256 No
Free SGA Memory Available                         0
12 rows selected.
--//Shared Pool Size=478150656 ,478150656 /1024/1024 = 456M
--//如果我修改*.processes=2242.

SYS@book> startup pfile='/tmp/@.ora';
ORA-00371: not enough shared pool memory, should be atleast 1087664611 bytes
--//真心不知道1087664611如何计算的.后面竟然还是奇数.

$ oerr ora 00371
00371, 00000, "not enough shared pool memory, should be atleast %s bytes"
// *Cause:  Init.ora parameter shared_pool_size is too small
// *Action: Increase the parameter value

--//1087664611/1024/1024 = 1037.27M.
--//仅仅增加1个processes,Shared Pool Size大小就从456M需求跃变到1038M.

4.实际上这个问题解决很简单设置:
--//修改参数配置:
*.shared_pool_size=1087664611

SYS@book> startup pfile='/tmp/@.ora';
ORACLE instance started.
Total System Global Area 1603485696 bytes
Fixed Size                  2253624 bytes
Variable Size            1157631176 bytes
Database Buffers          436207616 bytes
Redo Buffers                7393280 bytes
Database mounted.
Database opened.

SYS@book> select * from v$sgainfo;
NAME                                          BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size                              2253624 No
Redo Buffers                                7393280 No
Buffer Cache Size                         436207616 Yes
Shared Pool Size                         1090519040 Yes
Large Pool Size                            33554432 Yes
Java Pool Size                             33554432 Yes
Streams Pool Size                                 0 Yes
Shared IO Pool Size                               0 Yes
Granule Size                               16777216 No
Maximum SGA Size                         1603485696 No
Startup overhead in Shared Pool           507309824 No
Free SGA Memory Available                         0
12 rows selected.
--//1090519040/1024/1024 = 1040

$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 450887680  oracle    640        16777216   23
0x00000000 450920449  oracle    640        1593835520 23
0xe8a8ec10 450953218  oracle    640        2097152    23

SYS@book> show parameter processes
NAME                      TYPE    VALUE
------------------------- ------- ---------
aq_tm_processes           integer 0
db_writer_processes       integer 3
gcs_server_processes      integer 0
global_txn_processes      integer 1
job_queue_processes       integer 200
log_archive_max_processes integer 2
processes                 integer 2242

5.总结:
--//实际上这个问题解决很简单,可以做1个假设,假设dba修改processes参数,由于各种原因没有及时重启数据库,等到下次重启时也许问
--//题出现,这个时候留给dba的解决问题的时间窗口可能很短.如果在这个问题纠缠,会很不好理解为什么出现shared_pool_size设置这种
--//跃变.