restart database after increase the db_cache_size? [message #564351] |
Thu, 23 August 2012 00:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/76b43e6b0d209509b136fa13ddd45f4e?s=64&d=mm&r=g) |
shmily2012
Messages: 2 Registered: August 2012
|
Junior Member |
|
|
Hi All,
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1152M
sga_target big integer 0
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
in scenario above, the database do not using ASMM, and spfile
If I wan to increase db_cache_size parameter, do i need to rebounce instance?
|
|
|
Re: restart database after increase the db_cache_size? [message #564354 is a reply to message #564351] |
Thu, 23 August 2012 01:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
When working with Oracle, a test that takes a few moments can save hours of speculation:orcl> sho parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 512M
sga_target big integer 0
orcl> sho parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
db_cache_size big integer 180M
orcl> alter system set db_cache_size=150m;
System altered.
orcl> sho parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
db_cache_size big integer 152M
orcl> alter system set db_cache_size=250m;
alter system set db_cache_size=250m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
orcl> alter system set db_cache_size=200m;
System altered.
orcl> alter system set db_cache_size=250m scope=spfile;
System altered.
orcl> it depends on various factors, so experiment and then confirm the detail by reading the docs, which explain how the various parameters interact.
|
|
|
|
|
|
|
Re: restart database after increase the db_cache_size? [message #564528 is a reply to message #564527] |
Fri, 24 August 2012 14:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
In 10g I increase the sga_max_size and restart all instances prior to the following:
-- 10g Parameters before changes
INSTANC VALUE NAME
------- ---------------------------------------- ---------------------
NDOCP1 15367929856 sga_max_size
NDOCP1 12297699328 sga_target
NDOCP1 2063597568 db_keep_cache_size
NDOCP1 2063597568 db_recycle_cache_size
ENDOCP1 > @esys NDOCP1
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_2G_caches_pfile.ora' FROM SPFILE;
File created.
SYS AS SYSDBA> @esys NDOCP2
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_2G_caches_pfile.ora' FROM SPFILE;
File created.
SYS AS SYSDBA> @E NDOCP2
Connected.
ENDOCP2 > alter system set sga_target=14g scope=memory sid='NDOCP2';
System altered.
ENDOCP2 > alter system set db_recycle_cache_size=3g scope=memory sid='NDOCP2';
System altered.
ENDOCP2 > alter system set db_keep_cache_size=3g scope=memory sid='NDOCP2';
System altered.
ENDOCP2 > @E NDOCP1
Connected.
ENDOCP1 > alter system set sga_target=14g scope=memory sid='NDOCP1';
System altered.
ENDOCP1 > alter system set db_keep_cache_size=3g scope=memory sid='NDOCP1';
System altered.
ENDOCP1 > alter system set db_recycle_cache_size=3g scope=memory sid='NDOCP1';
System altered.
ENDOCP1 > alter system set sga_target=14g scope=spfile sid='*';
System altered.
ENDOCP1 > alter system set db_keep_cache_size=3g scope=spfile sid='*';
System altered.
ENDOCP1 > alter system set db_recycle_cache_size=3g scope=spfile sid='*';
System altered.
ENDOCP1 > @esys NDOCP1
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_3G_caches_pfile.ora' FROM SPFILE;
File created.
SYS AS SYSDBA> @esys NDOCP2
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_3G_caches_pfile.ora' FROM SPFILE;
File created.
SYS AS SYSDBA> @vp
Enter value for substr_of_name: sga
old 3: where upper(name) like upper('%&substr_of_name%')
new 3: where upper(name) like upper('%sga%')
INSTANC VALUE NAME
------- ---------------------------------------- -------------------------------------
NDOCP2 15032385536 sga_max_size
NDOCP2 FALSE pre_page_sga
NDOCP2 FALSE lock_sga
NDOCP2 15032385536 sga_target
|
|
|