[20191219]shared_pool_size设置跃变.txt

[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设置这种 --//跃变.