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)
1-Modify WITH INIT:
And finally, you can start database and check:
2-Modify WITH SPFILE:
If your database is running with spfile there are some parameters you can change without database restart:
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:
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> 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 stringinitORCL.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.oraIn 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=2Is 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_orclYou 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
Comments
Post a Comment