Hi Folks,
I am about to setup ZDM (zero downtime migration) to migration our prod database from on premise to OCI. Part of the pre-reqs is that you have to explicitly set STEAMS_POOL_SIZE.
Currently on prem we are using AMM and have the following parameters set:
memory_max_target big integer 160G
memory_target big integer 160G
sga_max_size big integer 160G
sga_target big integer 0
streams_pool_size big integer 0
I run the following to set the POOLS:
alter system set streams_pool_size=3G scope=memory sid='xxxx';
I then get the following error:
gp011:SYS>alter system set streams_pool_size=3G scope=memory sid='xxxx';
alter system set streams_pool_size=3G scope=memory sid='xxxx'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
So I think we are using up all the memory.
So I try to reset the MEMORY_TARGET and take away 3GB so that I can give it to the STREAMS
gp013:SYS>alter system set memory_target=157G scope=memory sid='gp013';
alter system set memory_target=157G scope=memory sid='gp013'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least
162816M
Im not sure what to do here, this all worked fine on our test env with the same settings, obviously its not been used as much as prod.
If i flush the buffer cache will it free up some memory and then ill be able to give back to STREAMS?
Or will need to bounce the DB and then allocate to streams?
Not sure how to get around this issue.