Hello experts, i am facing problem. i am not able increase size, not SGA_MAX_SIZE
Oracle vesion=11gR2
OS version= Red hat Linux 5.5
[root@VCC-WEB-1 ~]# free -g
total used free shared buffers cached
Mem: 31 15 16 0 0 20
[root@VCC-WEB-1 ~]# cat /etc/sysctl.conf
fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 10737418240
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
When i was change sga size, then it was not change sga. it take same size again. as i share my log my production server.
[oracle@VCC-WEB-1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 10:33:46 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2336M
sga_target big integer 0
SQL> alter system set sga_max_size=4G scope=spfile;
SQL> shu immediate
SQL> startup
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2336M
sga_target big integer 0
Alert logfile.
Wed Nov 21 00:10:00 2012
Instance shutdown complete
Wed Nov 21 00:11:40 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in client-side pfile /u01/app/oracle/product/11.2.0/dbs/initorcl.ora on machine VCC-WEB-1
System parameters with non-default values:
processes = 150
sga_max_size = 2336M
memory_target = 12864M
control_files = "/u01/app/oracle/oradata/orcl/control01.ctl"
control_files = "/u01/app/oracle/flash_recovery_area/orcl/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 3852M
undo_tablespace = "UNDO"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
local_listener = "(address=(protocol=tcp)(host=localhost)(port=1521))"
audit_file_dest = "/u01/app/oracle/admin/orcl/adump"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Wed Nov 21 00:11:41 2012
PMON started with pid=2, OS id=31773
Wed Nov 21 00:11:41 2012
VKTM started with pid=3, OS id=31775 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Nov 21 00:11:41 2012
GEN0 started with pid=4, OS id=31779
Wed Nov 21 00:11:41 2012
DIAG started with pid=5, OS id=31781
Wed Nov 21 00:11:41 2012
DBRM started with pid=6, OS id=31783
Wed Nov 21 00:11:41 2012
PSP0 started with pid=7, OS id=31785
Wed Nov 21 00:11:41 2012
DIA0 started with pid=8, OS id=31787
Wed Nov 21 00:11:41 2012
MMAN started with pid=9, OS id=31789
Wed Nov 21 00:11:41 2012
DBW0 started with pid=10, OS id=31791
Wed Nov 21 00:11:41 2012
LGWR started with pid=11, OS id=31793
Wed Nov 21 00:11:41 2012
CKPT started with pid=12, OS id=31795
Wed Nov 21 00:11:41 2012
SMON started with pid=13, OS id=31797
Wed Nov 21 00:11:41 2012
RECO started with pid=14, OS id=31799
Wed Nov 21 00:11:41 2012
MMON started with pid=15, OS id=31801
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Nov 21 00:11:41 2012
MMNL started with pid=16, OS id=31803
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Wed Nov 21 00:11:42 2012
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1327708734
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Wed Nov 21 00:11:46 2012
ALTER DATABASE OPEN
Thread 1 opened at log sequence 227
Current log# 7 seq# 227 mem# 0: /oradata1/oradata/redo07.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 13.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Nov 21 00:11:48 2012
QMNC started with pid=20, OS id=32399
Completed: ALTER DATABASE OPEN
Wed Nov 21 00:11:49 2012
db_recovery_file_dest_size of 3852 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
Wed Nov 21 00:11:51 2012
CJQ0 started with pid=21, OS id=32448
Setting Resource Manager plan SCHEDULER[0x3004]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Nov 21 00:11:54 2012
Starting background process VKRM
Wed Nov 21 00:11:54 2012
VKRM started with pid=23, OS id=32730
Wed Nov 21 00:12:16 2012
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process QMNC
Wed Nov 21 00:12:18 2012
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 6
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Wed Nov 21 00:12:23 2012
SMON: disabling tx recovery
SMON: disabling cache recovery
I was it throw it pfile also but face same issue.