streams_pool_size

[Roshan]

Oracle DB 12.1.0.2
RAC 2 nodes
Solaris 11.4

Hi Erman,

I have set the streams_pool_size to 3G on first node. On the second node I am getting error below despite sufficient memory exists on the Server.

on the second node I am getting

SQL> alter system set streams_pool_size=3G sid='inst12' scope=both;

alter system set streams_pool_size=3G sid='inst12' scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

allow_group_access_to_sga            boolean     FALSE

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     TRUE

sga_max_size                         big integer 11200M <============== 11.2G allocated for SGA

sga_target                           big integer 11200M

unified_audit_sga_queue_size         integer     1048576

 Kindly advise why I am not able to set it to 3G on second node.

Thanks.

[Roshan]

Pfile from first node:

inst12.__data_transfer_cache_size=0
inst11.__data_transfer_cache_size=0
inst12.__db_cache_size=4798283776
inst11.__db_cache_size=5737807872
inst12.__java_pool_size=33554432
inst11.__java_pool_size=33554432
inst11.__large_pool_size=268435456
inst12.__large_pool_size=268435456
inst11.__oracle_base='/u01/app/ora12c'#ORACLE_BASE set from environment
inst12.__oracle_base='/u01/app/ora12c'#ORACLE_BASE set from environment
inst12.__pga_aggregate_target=3925868544
inst11.__pga_aggregate_target=3925868544
inst12.__sga_target=11744051200
inst11.__sga_target=11744051200
inst12.__shared_io_pool_size=536870912
inst11.__shared_io_pool_size=536870912
inst12.__shared_pool_size=6039797760
inst11.__shared_pool_size=3892314112
inst11.__streams_pool_size=1241513984
inst12.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/ora12c/admin/inst1/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/inst1/CONTROLFILE/current.349.1018631791','+RECO/inst1/CONTROLFILE/current.1610.1018631793'
*.db_block_size=16384
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='inst1'
*.db_recovery_file_dest_size=1099511627776
*.db_recovery_file_dest='+RECO'
*.diagnostic_dest='/u01/app/ora12c'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=inst1XDB)'
*.enable_goldengate_replication=TRUE
inst12.instance_number=2
inst11.instance_number=1
*.log_archive_config='DG_CONFIG=(cbs,cbs_stby,cbs_dr,inst1)'
*.log_archive_dest_1='LOCATION=+RECO VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
*.log_archive_dest_4='LOCATION=+RECO/foreign  VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=3726m
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.sga_target=11179m
inst12.thread=2
inst11.thread=1
inst12.undo_tablespace='UNDOTBS2'
inst11.undo_tablespace='UNDOTBS1'

Extrat from pfile on second node:

inst12.__data_transfer_cache_size=0

inst11.__data_transfer_cache_size=0

inst12.__db_cache_size=4798283776

inst11.__db_cache_size=3590324224

inst12.__java_pool_size=33554432

inst11.__java_pool_size=33554432

inst11.__large_pool_size=268435456

inst12.__large_pool_size=268435456

inst11.__oracle_base='/u01/app/ora12c'#ORACLE_BASE set from environment

inst12.__oracle_base='/u01/app/ora12c'#ORACLE_BASE set from environment

inst12.__pga_aggregate_target=3925868544

inst11.__pga_aggregate_target=3925868544

inst12.__sga_target=11744051200

inst11.__sga_target=11744051200

inst12.__shared_io_pool_size=536870912

inst11.__shared_io_pool_size=536870912

inst12.__shared_pool_size=6039797760

inst11.__shared_pool_size=4093640704

inst12.__streams_pool_size=33554432

inst11.__streams_pool_size=3187671040

*.audit_file_dest='/u01/app/ora12c/admin/dware1/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='12.1.0.2.0'

*.control_files='+DATA/DWARE1/CONTROLFILE/current.349.1018631791','+RECO/DWARE1/CONTROLFILE/current.1610.1018631793'

*.db_block_size=16384

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='dware1'

*.db_recovery_file_dest_size=1099511627776

*.db_recovery_file_dest='+RECO'

*.diagnostic_dest='/u01/app/ora12c'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dware1XDB)'

*.enable_goldengate_replication=TRUE

inst12.instance_number=2

inst11.instance_number=1

*.log_archive_config='DG_CONFIG=(cbs,cbs_stby,cbs_dr,dware1)'

*.log_archive_dest_1='LOCATION=+RECO VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'

*.log_archive_dest_4='LOCATION=+RECO/foreign  VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=3726m

*.processes=3000

*.remote_login_passwordfile='exclusive'

*.sga_target=11179m

inst11.streams_pool_size=3187671040

inst12.thread=2

inst11.thread=1

inst12.undo_tablespace='UNDOTBS2'

inst11.undo_tablespace='UNDOTBS1'

[ErmanArslansOracleBlog]

It seems there is not enough room in your sga for letting the streams pools grow..
It seems your db buffer cache and shared pool in node 2 is bigger than node 1.
Basically, you should make some room in the SGA for extending the size of streams pool in node 2.

You may decrease the size of other pools(actually they are managed automatically in ASMM, but still if you set any values for them, then you may consider decreasing those values)
 
or

You can increase the sga_max_size.. (if your db is RAC, you should modify this parameter for all the instances -- database-wide setting)