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