ITRemote

#!/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