Parameters which require DB bounce.
2 min readIn v$parameter we can find one column ie. ISSYS_MODIFIABLE.
This column contains three phases: 1) Immediate 2) Deferred 3) False
1. Immediate : We can change the parameter in fly database ie Dynamic.(only need to change the value no need to bounce) 2. DEFERRED : We can change the parameter in fly database but this will effect after restart the database only.(here we need to edit using spfile and bounce the database) 3. False : (NO IT IS NOT MODIFABLE) WITH ALTER SYSTEM COMMAND CHANGE IT IN PFILE
SQL> Select NAME,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where NAME like '%parallel_max_serve%';
NAME VALUE ISDEFAULT ISSES ISSYS_MOD
-------------------- ------------------------------ --------- ----- ---------
parallel_max_servers 20 TRUE FALSE IMMEDIATE
SYS @ SHA:SQL > alter system set parallel_max_servers=8 scope=both;
System altered.
SQL> Select NAME,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where NAME like '%parallel_max_serve%';
NAME VALUE ISDEFAULT ISSES ISSYS_MOD
-------------------- ------------------------------ --------- ----- ---------
parallel_max_servers 8 TRUE FALSE IMMEDIATE
Above result got changes in Up and running condition.
SQL> select NAME,ISSYS_MODIFIABLE from v$parameter where NAME='shared_pool_size';
NAME ISSYS_MOD
-------------------- ---------
shared_pool_size IMMEDIATE
SQL> show parameter %shared_pool_size%
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> alter system set shared_pool_size=16M;
System altered.
SQL> show parameter %shared_pool_size%
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 16M
Here see about DEFERRED
SQL> select name, ISSYS_MODIFIABLE from v$parameter where ISSYS_MODIFIABLE='DEFERRED';
NAME ISSYS_MOD
------------------------------ ---------
backup_tape_io_slaves DEFERRED
recyclebin DEFERRED
session_cached_cursors DEFERRED
private_temp_table_prefix DEFERRED
audit_file_dest DEFERRED
object_cache_optimal_size DEFERRED
object_cache_max_size_percent DEFERRED
sort_area_size DEFERRED
sort_area_retained_size DEFERRED
client_statistics_level DEFERRED
olap_page_pool_size DEFERRED
11 rows selected.
SQL> show parameter %audit_file_dest%;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/prod/adump
SQL> alter system set audit_file_dest='/u01/adump' scope=spfile;
System altered.
SQL> show parameter %audit_file_dest%;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/oracle/admin/prod/adump
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 322240512 bytes
Fixed Size 1299652 bytes
Variable Size 197135164 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.
QL> show parameter %audit_file_dest%;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/adump
