Cloning and your SGA

Finally back from a little vacation... It started off rocky. During a normal disk resize operation (SAN attached) the storage team encountered a bug (or feature depending how you look at it) and it corrupted all of the databases on our dev server. Normally this isn't a big deal but we have some rather large projects ongoing at the moment and their environments are also on this server. So, the first weekend of my vacation was spent recovering.
----
Cloning is the act of creating an identical copy of an EBS environment. That means everything from the number of httpd servers to the size of your SGA will be the same. This can be a problem if your cloning from production, since its memory footprint is probably significantly larger than your dev/test environments can support. Whether thats because the server(s) aren't physically large enough or because multiple environments are eating into the available memory. So during the cloning process, you may want to resize the database's SGA.

Note: There are probably a few ways to change the SGA size during cloning. So if you know if another method please leave a comment because i'd be interested in knowing. For the sake of this article, lets assume the only parameter I want to change is the sga_target.

Before you clone an environment you have to prepare the source system by running adpreclone.sh on both the appsTier and the dbTier. This process gathers information about your environment and creates template files. In the case of the database tier, these templates are stored under $ORACLE_HOME/appsutil/template. Two files in particular are:

$ORACLE_HOME/appsutil/template/afinit_db920.ora -- 9i Database
$ORACLE_HOME/appsutil/template/afinit_db102.ora -- 10G Database

If you look at your log file ($ORACLE_HOME/appsutil/log/ApplyDBTier*.log) created during the database post cloning ($ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl ) you will see the below:

instantiate file:
source : /envision1/app/oradb/1020_64bit/appsutil/template/afinit_db102.ora
dest : /envision1/app/oradb/1020_64bit/dbs/initORA.ora
backup : /envision1/app/oradb/1020_64bit/dbs/initORA.ora to /envision1/app/oradb/1020_64bit/appsutil/out/ORA_myserver/0
6220730/initORA.ora

In this case I have a 10g database, so the afinit_db102.ora file is being referenced to create the new parameter file. If you view this file it looks like a normal parameter file except for the fact not all the values are specified. For example:

sga_target = %s_db_sga_target% # Required 11i setting

So one way to modify SGA parameters would be to edit the template file for your database version and hard code the value you want, ie:

sga_target = 700M

Another option would be to modify the context file that is used, in conjunction with the template file, to build the parameter file. I'm not 100% sure that the file i'm about to reference is the correct one but from my limited testing it has worked for me. During the preclone, along with the template file creation I noticed some other files are created. One such being:

$ORACLE_HOME/appsutil/clone/context/db/CTXORIG.xml

Changing the following line in this file from:

<sga_target oa_var="s_db_sga_target">800M</sga_target>

to
<sga_target oa_var="s_db_sga_target">600M</sga_target>


also produces the same result. Once adcfgclone.pl completes the SGA_TARGET in the newly created parameter file will be set to 600M.