Honey, I shrunk the database!

For my current assignment I need to get 3 SOA/B2B environments running. I'm going to try-out the multi-hop functionality, where the middle B2B environment should work as a dispatcher. The idea is that in Dev, Test and Pre Prod environments the dev environment can send messages to the remote trading partner's test environment, through the in-between-environment. To the remote trading partner, that in-between-environment should act as the local test environment, but then should be able to dispatch the message to the actual dev, test or pre-prod environment.

I envision  a solution, where this in-between B2B environment should act as this dispatching B2B hop. So I need to have 3 VMs running with their own database (although I could have them share one database), and Fusion Middleware domain.

The Vagrant project that I wrote about earlier this week, creates a database and then provisions all the FMW installations and a domain. That database is a 12cR1 database (that I could upgrade) that is installed default. In my setup it takes about 1.8GB of memory. My laptop is 16GB, so to have 2 VMs running on it, and let Windows have some memory too, I want to have a VM of at most 6,5 GB.
I need to run an AdminServer and a SOAServer, that I gave 1GB and GB respectively. And since they're not Docker containers, they both run an Oracle Linux 7 OS too.

So, one of the main steps is to downsize the database to "very small".

My starting point is an article I wrote years ago about shrinking an 11g database to XE proportions.
As described in that article I created an pfile as follows:

create pfile from spfile;

This creates an initorcl.ora in the $ORACLE_HOME/dbs folder.

I copied that file to initorcl.ora.small and editted it:

orcl.__data_transfer_cache_size=0
#orcl.__db_cache_size=1291845632
orcl.__db_cache_size=222298112
#orcl.__java_pool_size=16777216
orcl.__java_pool_size=10M
#orcl.__large_pool_size=33554432
orcl.__large_pool_size=4194304
orcl.__oracle_base='/app/oracle'#ORACLE_BASE set from environment
#orcl.__pga_aggregate_target=620756992
orcl.__pga_aggregate_target=70M
#orcl.__sga_target=1828716544
orcl.__sga_target=210M
#orcl.__shared_io_pool_size=83886080
orcl.__shared_io_pool_size=0
#orcl.__shared_pool_size=385875968
orcl.__shared_pool_size=100M
orcl.__streams_pool_size=0
*.audit_file_dest='/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/app/oracle/oradata/orcl/control01.ctl','/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=578m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
#*.sga_target=1734m
*.sga_target=350M
*.undo_tablespace='UNDOTBS1'

The lines that I changed are copied with the original values commented out. So I downsized the db_cache_size, java_pool, large_pool and pga_aggregate_target. Also the sga_target, shared_io_pool(have it auto-managed) and shared_pool. I needed to set the sga_target to at least 350M, to get it started.
SOASuite needs at least  300 processes and open_cursors.

Now the script, checks if the database running. It is actually a copy of the startDB.sh script also in my Vagrant project.

If it is running, it shutdowns the database. It then creates a pfile for backup. If the database isn't running, it only creates the pfile.

Then it I copied that file to initorcl.ora.small and creates a spfile from it. And then it starts the database again.

#!/bin/bash
SCRIPTPATH=$(dirname $0)
#
. $SCRIPTPATH/../../install_env.sh
. $SCRIPTPATH/db12c_env.sh
#
db_num=`ps -ef|grep pmon |grep -v grep |awk 'END{print NR}'`

if [ $db_num -gt 0 ]
then 
  echo "Database Already RUNNING."
  $ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
shutdown immediate;
prompt create new initorcl.ora.
create pfile from spfile;
exit;
EOF
  #
  # With use of a plugable database the following line needs to be added after the startup command
  # startup pluggable database pdborcl; 
  #
  sleep 10
  echo "Database Services Successfully Stopped. "
else
  echo "Database Not yet RUNNING."
  $ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
prompt create new initorcl.ora.
create pfile from spfile;
exit;
EOF
  sleep 10
fi
#
echo Copy initorcl.ora.small to $ORACLE_HOME/dbs/initorcl.ora, with backup to $ORACLE_HOME/dbs/initorcl.ora.org
mv $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorcl.ora.org
cp $SCRIPTPATH/initorcl.ora.small $ORACLE_HOME/dbs/initorcl.ora
#
echo "Starting Oracle Database again..."
$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF
create spfile from pfile;
startup;
exit;
EOF

The scripts can be found here.

Oh, by the way: I must state here that I'm not a DBA. I'm not sure if those settings make sense all together. (Should have someone review it). So you should not rely on them for a serious environment. Not even a development one. My motto is that a development environment is a developer's production environment. For me this is to be able to try something out. And to show the mechanism to you.