I have oracle 10.2.0.3 database running on Linux x86 using Oracle Enterprise Linux 32 bits.
I wanted to use a 64bits "os"/database since I have a 64 bits machine. But since Oracle does not support "hsodbc" in 64 bits yet. I am stuck using Oracle 32bits. See metalink 261726.1. Yeah that sucks. I have been told this should be fix in 11g. Not to sure of that, I will see that on july 7th.
Anyways, I have a 16 gig RAM machine, and 32 bits Oracle Database is pretty limited regarding having a large SGA. So I enabled the database to use all that ram using Metalink note :317141.1: How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages.
But to use all that RAM on a 32 bits database, no more automatic SGA feature, I have setup all memory parameters manually.
pga_aggregate_target=2147483648
java_pool_size=524288000
large_pool_size=524288000
shared_pool_reserved_size=104857600
shared_pool_size=838860800
use_indirect_data_buffers=true
db_block_buffers=1100000
Everything went well from there until i used the data pump.
Here is my error:
Export: Release 10.2.0.3.0 - Production on Monday, 04 June, 2007 11:41:51
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user BILLING
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20070604114151" and "KUPC$S_1_20070604114151" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1580
ORA-00832: no streams pool created and cannot automatically create one
Notice the last line :ORA-00832: no streams pool created and cannot automatically create one
So no problem i said to myself, since that streams_pool can be modified online.
But not in my case:
sys@CRN_PROD> alter system set streams_pool_size = 10M scope = both ;
alter system set streams_pool_size = 10M scope = both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
I think this is because of my memory hack to use Very Large Memomy with ramfs.
If i run the top command here is my output:
top - 11:11:01 up 23:48, 3 users, load average: 1.63, 1.85, 2.42
Tasks: 281 total, 2 running, 279 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.5% us, 3.1% sy, 0.0% ni, 72.4% id, 17.0% wa, 0.1% hi, 0.0% si
Mem: 16633368k total, 16587716k used, 45652k free, 18596k buffers
Swap: 2048276k total, 181192k used, 1867084k free, 13307608k cached
So i scheduled a maintenance window, i shutdown the database, and configured a small 30 MEG streams_pool_size.
And my data pump is working now.
Oracle says the following in the documentation, http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams211.htm#REFRN10243
"If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool."
So my question is this: why the data pump is not using the shared_pool_size instead of the streams_pool when the streams_pool is not configured?