[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設定這種
--//躍變.