Change parameters in Oracle

Sooner or later every Oracle dba need to change some configuration parameter... First thing i do is to check if database is running with spfile or init file. If database is running with spfile when you execute (if your database is 10g or higher your database probably has spfile)

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                     /orcl/dbs/spfileorcl.ora
SQL>

spfile entry will give you the path where spfile is located, like above, if not, value column will be null..

1-Modify WITH INIT:

show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

initORCL.ora file:

db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = '/u01/app/oracle/oradata/ORCL/control.dbf'
compatible ='11.2.0'

In case, you database is working with init file, is allways needed to restart database to change any parameter. init file is located on $ORACLE_HOME/dbs/ and his format is init${ORACLE_SID}.ora, for example in case ORACLE_SID=orcl it will be initORCL.ora

[oracle@ODIGettingStarted dbs]$ echo $ORACLE_SID
orcl
[oracle@ODIGettingStarted dbs]$ ls -ltr initORCL.ora
-rwxrwxrwx. 1 oracle oracle 2824 Dec 20  2013 initORCL.ora

In this case first step to check database parameter and shutdown database (in this case we will go to modify db_writer_processes parameter)

SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     1

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

second step is to manually modify initORCL.ora file with "vi" editor, don´t worry about you want to modify a parameter is not in initORCL.ora file (this means this parameter is default) simply add a new line at the end of the file:

db_writer_processes=2

Is a good practice to make a copy of this file before modify it:

And finally, you can start database and check:

[oracle@ODIGettingStarted dbs]$ sqlplus " / as sysdba "

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 05:59:38 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             616563592 bytes
Database Buffers          444596224 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes                  integer     2
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ODIGettingStarted dbs]$ ps -ef | grep dbw
oracle    3591     1  0 05:59 ?        00:00:00 ora_dbw0_orcl
oracle    3593     1  0 05:59 ?        00:00:00 ora_dbw1_orcl

You can see that two oracle writer processes (dbw) are running on this machine

2-Modify WITH SPFILE:
If your database is running with spfile there are some parameters you can change without database restart:

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /orcl/dbs/spfileorcl.ora

You can check if an specific parameter can be modified dynamically with this query (modify DB_WRITER_PROCESSES with parameter you what):

set linesize 190
select INST_ID, NAME, TYPE,  ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from gv$parameter 
where upper(name) like 'DB_WRITER_PROCESSES';

ISSES_MODIFIABLE Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
ISINSTANCE_MODIFIABLE For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.


If parameter CAN NOT be dynamically changed, you can do it by:

SQL> set linesize 190
col VALUE for a40
col NAME for a30
select INST_ID, NAME, VALUE, TYPE,  ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from 
gv$parameter where upper(name) like 'MEMORY_MAX_TARGET';SQL> SQL> SQL>

   INST_ID NAME                           VALUE                                          TYPE ISSES ISSYS_MOD ISINS
---------- ------------------------------ ---------------------------------------- ---------- ----- --------- -----
         1 memory_max_target              1577058304                                        6 FALSE FALSE     FALSE

SQL> ALTER system SET MEMORY_MAX_TARGET=1500M SCOPE=spfile sid='*';

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2253584 bytes
Variable Size            1107299568 bytes
Database Buffers          452984832 bytes
Redo Buffers                7471104 bytes
Database mounted.
Database opened.
SQL>  show parameter MEMORY_MAX_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 1504M
SQL> ALTER system SET memory_target=1300M SCOPE=BOTH sid='*';

System altered.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1312M

SCOPE=BOTH --> Changes parameter in memory and in spfile
sid='*' --> Changes parameter is all instances of a RAC

If parameter can be dynamically changed, you can do it by:

SQL>  set linesize 190
select INST_ID, NAME, TYPE,  ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from gv$parameter where upper(name) like 'MEMORY_TARGET';SQL>

   INST_ID NAME                                                                                   TYPE ISSES ISSYS_MOD ISINS
---------- -------------------------------------------------------------------------------- ---------- ----- --------- -----
         1 memory_target                                                                             6 FALSE IMMEDIATE TRUE


SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1G

SQL> ALTER system SET memory_target=1300M SCOPE=BOTH sid='*';

System altered.

SQL> show parameter memory_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 1312M