[code language=”sql”]

#!/bin/ksh
############################################################################
# Script Name..: 90_config_db_sga.ksh
# Description..: Modifies MRCTST01 parameters:
# remote_listener
# cpu_count
# resource_manager_cpu_allocation
# db_recovery_file_dest_size
# sga_max_size
# sga_target_size
# processes
# Author…….: Michael Culp
# Date………: 03/21/2013
# Version……:
# Modified By..:
# Date Modified:
# Comments…..:
# Schema owner.:
# alter session set current
# Login User…:
# Run Order….:
# Dependent on.:
# Script type..:
#
############################################################################

sqlplus -s "/ as sysdba" <<EOF
set echo on
set feedback on
set linesize 500

spool logs/90_config_db_sga.log

set timing on;

— Gather existing parameters

show parameter sga_max_size
show parameter sga_target

create pfile=’/tmp/dpodst01_before_sga_change’ from spfile;

— Register service
— alter system register;

— Modify Remote Listener

— alter system set remote_listener='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.124)(PORT=49147))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.125)(PORT=49147))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.40.190.126)(PORT=49147)))’ scope=spfile sid=’*’;

— Cage Instance

— alter system set cpu_count=2 scope=spfile sid=’*’;
— alter system set resource_manager_cpu_allocation=2 scope=spfile sid=’*’;

— Set FRA

— alter system set db_recovery_file_dest_size=50G scope=spfile sid=’*’;

— Set SGA

alter system set sga_max_size=180G scope=spfile sid=’*’;
alter system set sga_target=96G scope=spfile sid=’*’;

create pfile=’/tmp/dpodst01_after_sga_change’ from spfile;

— Set Processes

— alter system set processes=1000 scope=spfile sid=’*’;

— Set Parallel_Execution_Message_Size

— alter system set parallel_execution_message_size=32768 scope=spfile sid=’*’;

— show parameter remote_listener
— show parameter cpu_count
— show parameter resource_manager_cpu_allocation
— show parameter db_recovery_file_dest_size
— show parameter sga_max_size
— show parameter sga_target
— show parameter process

— Alter existing default tablespace sizes as per project requirement

spool off

EOF

# bounce DB after changing SGA_MAX_SIZE only no bounce needed if just changing
# SGA_TARGET
srvctl stop database -d MRCTST01
srvctl start database -d MRCTST01
srvctl status database -d MRCTST01
srvctl status service -d MRCTST01

[/code]