||Oracle Tips by Burleson
Scheduling Oracle SGA Reconfiguration
One of the most common techniques for
reconfiguring an Oracle instance is to use a shell script. To
illustrate a simple example, consider a database that runs in online
transaction-processing mode during the day and data warehouse mode at
night. For this type of database, the Oracle DBA can schedule a job to
reconfigure the instance to the most appropriate configuration for the
type of processing that is being done.
Oracle professionals generally use two tools
for scheduling a dynamic reconfiguration. The most common approach is
to use a UNIX cron job in order to schedule a periodic
reconfiguration, while some other Oracle professionals prefer to use
the Oracle dbms_job utility. Both of these approaches allow the
Oracle professional to schedule a reconfiguration.
In the example, below we see a UNIX script
that can be used to reconfigure Oracle for decision support
processing. One must note the important changes to the configuration
in the shared_pool, db_cache_size, and
pga_aggregate_target in order to accommodate data warehouse
Here is a script to change Oracle into DSS-mode
each evening at 6:00 PM
# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2
$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size
alter system set shared_pool_size
alter system set pga_aggregate_target
As we can see, writing scripts to re-configure
the SGA is easy. Next, let’s look at how to use buffer trend reports
from Oracle STATSPACK to predict those times when the data buffers
need additional RAM.
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Burleson Consulting shares their personal arsenal of
Oracle data dictionary scripts in this comprehensive download. With
decades of experience using Oracle monitoring scripts and Oracle tuning
scripts, BC experts share their secrets for navigating the Oracle data
Packed with 681 ready-to-use Oracle scripts, this is
the definitive collection for every senior Oracle DBA.
It would take man-years to develop these scripts
from scratch, making this download the best value in the Oracle
priced this collection of 681 scripts at $79.95.
You can download them immediately at this link: