Webcenter 11g VM: downsizing the database

This week I started with Webcenter 11g, using the Oracle VirtualBox VM that can be downloaded here.
One of the tips upfront was that the NAT network adapter should be "Internal Network" or "Host Only". Because otherwise the UCM part of the hands-on would not work.

I also changed the memory settings to 3GB at first. But that caused my Windows 7 to stutter. Aparently Windows has became very memory consumptive. On my Linux (Open Suse) it would not have be too much of a problem to raise the VM to 3GB. An 8GB laptop would be nice. So I brought the settings back to a more modest 2,5 GB.

But then I encountered that the install of Oracle DB 11g was pretty basic. And that means  a memory consumption of 700GB only the lonely for the database. I remembered my earlier post to tune Oracle DB11g together with SoaSuite10g on a OEL5 VM.

It was basically about resizing the memory. What I did was to startup an XE database. There I looked at the basic memory settings. For convenience I created a plain init.ora.

For the non-dba's amongst you, you can do that by loging on as internal with:

sqlplus "/ as sysdba"

having set the ORACLE_HOME and ORACLE_SID:

ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_SID=orcl

When you logged on as internal you can create an init.ora (also called a pfile) with:

create pfile from spfile;

Then you'll find an init.ora in the $ORACLE_HOME/dbs folder.

For an Oracle XE database the most interesting settings I found were:

java_pool_size=4194304
    large_pool_size=4194304
    shared_pool_size=67108864
    open_cursors=300
    sessions=20
    pga_aggregate_target=70M
    sga_target=210M

The sga_max_size was not set.
So I changed the 11g database with these settings, created a spfile from the pfile again (create spfile from pfile) started it again.

My initorcl.ora:

#orcl.__db_cache_size=222298112
#orcl.__java_pool_size=12582912
orcl.__java_pool_size=10M
orcl.__large_pool_size=4194304
....
#orcl.__pga_aggregate_target=159383552
orcl.__pga_aggregate_target=70M
#orcl.__sga_target=478150656
orcl.__sga_target=210M
orcl.__shared_io_pool_size=0
#orcl.__shared_pool_size=234881024
orcl.__shared_pool_size=100M
orcl.__streams_pool_size=0
...
#*.memory_target=635437056
*.open_cursors=300
#*.processes=150
*.sessions=20
...
*.sga_max_size=250
...

Mark that I unset the db_cache_size and memory_target. I also replaced the processes parameter with the sessions parameter being 20. These two parameters relate to eachother, one computed from the other.

I found that I had a database of 145MB!But that's some what too small, especially the shared-poolsize being about 64M, while the sga_max_size was 145M.

I changed my sga_max_size to explicitly 250M and the large_pool_size to 100M:

SQL> alter system set sga_max_size=250M scope=spfile;
System altered.
SQL> alter system set shared_pool_size=100M scope=spfile;
System altered.

Then restarting the database resulted in a database of 250M:

Total System Global Area 263639040 bytes
Fixed Size 1299284 bytes
Variable Size 209718444 bytes
Database Buffers 50331648 bytes
Redo Buffers 2289664 bytes

That looks better to me. And having it posted again refreshes it for me.

Update: I now see that in the initorcl.ora I had a sga_max_size of 250. But that should be 250M... Maybe that caused the shared_pool_size and sga_max_size too small.