There are 2 types of parameter file’s
using parameter file oracle bring database to no mount stage !!
export ORACLE_SID=dbname
sqlplus / as sysdba
sql> startup nomount;
Oracle will read the parameter file in ORACLE_HOME/dbs location ###
pfile is textfile
spfile is binary file ( server parameter file )
if spfile don’t exist, then oracle uses pfile to bring database to nomount stage ##
##if spfile exists , then it will take spfile and bring database to nomount stage ###
###
pfile initdatabase.ora
spfile spfiledatabase.ora
cd $ORACLE_HOME/dbs
in oracle_home/dbs location : oracle saves all parameter files in dbs location##
[admind@tnc63 dbs]$ pwd
/u01/app/admind/product/12.1.0/dbhome_1/dbs
[admind@tnc63 dbs]$ ls -ltr
-rw-r--r-- 1 admind oinstall 1083 Apr 15 20:39 inittalent9.ora
-rw-r----- 1 admind oinstall 3584 Apr 21 21:23 spfiletalent9.ora
[admind@tnc63 dbs]$ cat inittalent9.ora
talent9.__data_transfer_cache_size=0
talent9.__db_cache_size=301989888
talent9.__java_pool_size=4194304
talent9.__large_pool_size=8388608
talent9.__oracle_base='/u01/app/admind'#ORACLE_BASE set from environment
talent9.__pga_aggregate_target=213909504
talent9.__sga_target=520093696
talent9.__shared_io_pool_size=8388608
talent9.__shared_pool_size=188743680
talent9.__streams_pool_size=0
*.audit_file_dest='/u01/app/admind/admin/talent9/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/admind/oradata/TALENT9/controlfile/o1_mf_cg6q60gm_.ctl','/u01/app/admind/fast_recovery_area/TALENT9/controlfile/o1_mf_cg6q60jr_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/admind/oradata'
*.db_domain=''
*.db_name='talent9'
*.db_recovery_file_dest='/u01/app/admind/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/admind'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=talent9XDB)'
*.memory_target=697m
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_retention=1500
*.undo_tablespace='UNDOTBS1'
sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=‘undo_retention’;
NAME, VALUE , ISSYS_MOD
---------------------------------------------------
undo_retention 1500 IMMEDIATE
Immediate / True : Then the parameter is dynamic
False : Then the parameter is static
sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=’cursor_sharing’;
sql> select name,value,ISSYS_MODIFIABLE from v$parameter where name=’open_cursors’;
NAME, VALUE , ISSYS_MOD
---------------------------------------------------
open_cursors 300 IMMEDIATE
If it is a dynamic parameter #
sql> alter system set open_cursors=500 scope=both;
scope=both
# Value is updated to spfile
# Value is updated in memory
It is static parameter
# scope=spfile # value is updated only in the spfile ####
sql> alter system set parameter_name=value scope=spfile;
In order to update in memory we need to bounce the database #
sql> shut immediate;
sql> startup;
Assignment # change following parameters
processes=600 to 900
open_cursors=500
undo_retention=1800
db_recovery_file_dest_size=5000m;
--Useful scripts #
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN name FORMAT A30
COLUMN value FORMAT A70
COLUMN ses_mod FORMAT a10
COLUMN sys_mod FORMAT a10
COLUMN ins_mod FORMAT a10
SELECT p.name,
p.type,
p.value,
p.isses_modifiable as SES_MOD,
p.issys_modifiable as SYS_MOD,
p.isinstance_modifiable as INS_MOD
FROM v$parameter p
ORDER BY p.name
/
---Nikhil Tatineni--
---Oracle In memory--
