Tuning Oracle DB Parameters - Steps to follow

First we can check the existing parmaters with following commands:

$show sga

$show parameter shared_pool;

$show parameter sga_max_size;

$show parameter db_cache_size;

Then take backup for pfile and spfile (initSID.ora and spfileSID.ora)

/oracle/SID/102_64/dbs

$create pfile from spfile

then edit pfile with the parameters that are recommended

start database with changed pfile

$startup pfile='/oracle/SID/102_64/dbs/initSID.ora'

check the parameters

if they are correct then

$create spfile from pfile

then shutdown and restart the database

$shutdown immediate
$startup

//Issue faced
while restarting database listener not found

$tnsping SID

gave error for resolving name

resolution

setenv TNS_ADMIN /oracle/SID/102_64/network/admin

later on added the same in .dbenv.csh and .dbenv_avggstqh.csh to make the same permanent.


//we can dynamically set the parameters in spfile online but this is not recommended as Oracle needs to adjust the parameters as well.

$alter system set shared_pool_size=400M scope=spfile;