Wednesday, December 4, 2013

Modify Hidden Parameter in Oracle Database


Lets see the steps to Modify Hidden Parameter in Oracle Database

*********************************************** Step By Step Procedure **************************************

Click Here to know how to check Hidden Parameter @ database

#########################################
# 1) check Present Value of the parameter
#########################################

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name like '%real%';

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
_realfree_heap_pagesize_hint        65536 FALSE     FALSE FALSE

=====================================================================================================================

#########################################
# 2) Modify the parameter in Spfile
#########################################

alter system set "_realfree_heap_pagesize_hint"=262144 scope=spfile sid='*';

Note :

a) As this is RAC environment, we are using sid='*'
b) For Hidden Parameter we are using double quotes (" "), this is not needed for normal parameters

=====================================================================================================================

#########################################
# 3) Shutdown / Startup Database
#########################################

srvctl stop database -d oralin

srvctl start database -d oralin -n hostu01

srvctl status database -d oralin

=====================================================================================================================

#########################################
# 4) Check the modified Value
#########################################

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name like '%real%';

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
_realfree_heap_pagesize_hint        262144                    FALSE     FALSE FALSE

=====================================================================================================================

1 comment:

Jason Brown said...

I referenced your post in my blog article: https://jasonbrownsite.wordpress.com/ Article "ORA-04030: out of process memory when trying to allocate 1432 bytes." I used your site to confirm my belief that changes needed to be done at both the OS Level and database level. The support article suggested either or but I didn't notice results until I did both. Thanks!!