List of 10 Must Know Oracle Database Parameters for Database Administrator -

List of 10 Must Know Oracle Database Parameters for Database Administrator

Database Parameters are environment variables for a database which can change database behavior according to value of the parameter. These are input values which are taken at time of database startup and continue until changed. There are few default Database Parameter value created at time of database creation and above that we can change them according to our requirements.

Since, all parameters in database resides in parameter file (spfile or init file), so we will start this discussion from spfile operations and maintenance.

Database Parameters are stored in two files.

1. init file: This is a text file which is manually editable. The location of the file is $ORACLE_HOME/dbs/init{instancename}.ora

2. Spfile: This is a binary file, which is not manually editable. This file is used at time of database startup to read database parameter values. The location and name of the file are $ORACLE_HOME/dbs/spfile{instancename}.ora. This is the default file used for database startup, any change into this file is persistent across instance shutdown and startup.

Below is the screen shot of spfile having database named "orcl" with database version Oracle 11.2

[oracle@database ~]$ cat /etc/oracle/oracle/dbs/spfileorcl.ora 
orcl.__db_cache_size=146800640
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/etc/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=213909504
orcl.__sga_target=398458880
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=230686720
orcl.__streams_pool_size=4194304
*.audit_file_dest='/etc/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cell_offload_plan_display='ALWAYS'
*.compatible='11.2.0.0.0'
*.control_files='/etc/oracle/oradata/orcl/control01.ctl','/etc/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='database.com'
*.db_name='orcl'
*.db_recovery_file_dest='/etc/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/etc/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.memory_target=612368384
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

To display value of a particular parameter use:

sql> show parameter {parameter_name}
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     10
log_archive_max_processes            integer     4
processes                            integer     150

One command to display all parameters

sql> select * from v$parameter;

How to change Database Parameter:

sql> alter system set {parameter_name}={value} scope=both;

This command will change parameter value in spfile and memory and the changes will take effect immediately. Remote DBA can also check database is using pfile or spifle for current instance.

How to Take Parameter file backup:

There are two ways to take database Paramter file (spfile) backup.

a. Create init file from spfile: Make a text file from binary file (spfile) as a backup of current spfile.

SQL> create pfile from spfile;
File created.
[oracle@database ~]$ cd $ORACLE_HOME/dbs
[oracle@database dbs]$ ls init*
init.ora  initorcl.ora

This will make init{instance_name}.ora file at $ORACLE_HOME/dbs. Database Administrator can also give a different location just by giving full path and name of the file.

SQL> create pfile = '/home/oracle/initorcl.ora' from spfile; 
File created.
SQL> !
[oracle@database dbs]$ ls /home/oracle/init*
/home/oracle/initorcl.ora

b. Parameter file (spfile) backup using RMAN: Remote DBA can also take spfile backup using RMAN commands given below.

[oracle@database]$ export ORACLE_SID=orcl
[oracle@database ~]$ rman target sys/sys

Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 17 22:12:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1264572123)

RMAN> backup spfile;
Starting backup at 17-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-MAY-13
channel ORA_DISK_1: finished piece 1 at 17-MAY-13
piece handle=/etc/oracle/fast_recovery_area/ORCL/backupset/2013_05_17/o1_mf_nnsnf_TAG20130517T221303_8sg3gzn7_.bkp tag=TAG20130517T221303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAY-13

Backup of parameter file is placed at /etc/oracle/fast_recovery_area/ORCL/backupset/2013_05_17/o1_mf_nnsnf_TAG20130517T221303_8sg3gzn7_.bkp tag=TAG20130517T221303.
RMAN automatically takes spfile backup every time RMAN backup of database is taken.

Here is the List of 10 Must Know Oracle Database Parameters for Database Administrator

1. DB_NAME: A basic parameter which shows the name of database created into the system. The maximum length of db_name can go upto 8 characters only. This is a case insensitive parameter. DB_NAME is very important in case of Real Application Cluster environment. In RAC env. one database can have multiple instances, Suppose database name is orcl and database has there instances on three servers. In RAC env instance names would be orcl1, orcl2 and orcl3 but database name on all the instances would be same. Command to show DB_NAME value.

SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl

Database Administrator can't change this parameter using alter command, but can change this using nid command.

2. INSTANCE_NAME: In Single instance database, DB_NAME and INSTANCE_NAME are same. In case of Real Application Cluster env As explained above in DB_NAME parameter a suffix number is added to make it instance number. Below is an example of Real Application Cluster with Database name oracl and Instance name orcl1.

SQL> show parameter db_name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl

SQL> show parameter instance_name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl1

In Oracle, a database can have multiple instances, but an instance can be part of only one database. To login in a RAC environment DBA has to mention instance_name value with export ORACLE_SID env variable.

if, I took above case, to login to to database, i have to user.

# export ORACLE_SID=orcl1

Using this command, I will be loogged into oracl database oracl1 instance.

3. RECYCLEBIN: This is used to control flashback feature of database. If this is off dropped table will not go into recyclebin and on means you can get back dropped table from recyclebin. A detail article covering this.

4. PROCESSES: One of the most important Database Parameter for a Remote DBA. Used to specify maximum number of operating system process that can connect to the database at a given point of time. The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. In order to change these values Database Administrator has to change PROCESSES value.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     10
log_archive_max_processes            integer     4
processes                            integer     150

A DBA sometimes face, "ORA-00020: maximum number of processes (%s) exceeded" which is mainly because of this process limit reached.

To resolve "ORA-00020: maximum number of processes (%s) exceeded" DBA has to increase the value of processes parameter. Since, you are not able to connect to database so to apply these changes use below.

SQL>sqlplus -prelim / as sysdba
SQL> alter system set processes=200 scope=spfile;
SQL> shutdown immediate
sql> startup
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     10
log_archive_max_processes            integer     4
processes                            integer     200

If, Database Administrator is facing this error in case of ASM instance, then use to login to ASM instance.

SQL>sqlplus -prelim / as sysasm

Sometimes, database face this issue because of high number of parallel process, to restrict total number of parallel process DBA can user PARALLEL_MAX_SERVERS.

5. COMPATIBLE: This parameter in datbase allows you to use newest database releases, along with provides backward compatibility with older version. Both database and ASM instances has compatible parameter. Support you are currently running Oracle 10.2.0.4 Version and after database upgrade database version is 11.2.0.1 but if still your compatible parameter is 10.2.0.4. This means DBA can get back database to 10.2.0.4 any time without any issue into the database.

I would recommend to upgrade compatible parameter to current database version, if every thing goes fine after upgrade because some of the new features may not work with lower version compatible parameter.

A recent issue DBA face, Database was on 11.2.0.3 version and compatible parameter was 11.2.0.1 but it was hitting a bug which was fixed in 11.2.0.3. So, ideally this issue should not be there in 11.2.0.3, this was only because of compatible parameter. So, DBA change compatible parameter to 11.2.0.3 and issue was resolved.

Here you can set database compatible parameter and these are steps to set compatible parameter in ASM

6. CLUSTER_DATABASE: This parameter is basically used by DBA to set database is cluster or non cluster. If database is a RAC database then this parameter would be true other wise false.

Single Instance Database

SQL> show parameter cluster_database
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

Real Application cluster Database

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     1
cluster_interconnects                string      192.168.10.7

Default value for this CLUSTER_DATABASE is false, To migrate Single instance database to RAC database DBA has to change this parameter. One more important situation. If restoration of database is required using RMAN backup and it's cluster database in that case remote DBA has to set CLUSTER_DATABASE=false and then restore it.

One more case to discuss, if it's a RAC Database and in no archivelog mode, then to covert in archive log mode DBA has to change CLUSTER_DATABASE=false. Steps to follow for Enabling Archive Logs in a RAC Environment from Toad World.

7. MEMORY_MAX_TARGET & MEMORY_TARGET: These are new DB Parameters introduced in Oracle 11g. MEMORY_TARGET target is sum of SGA_TARGET and PGA_TARGET any they are allocated dynamically from MEMORY_TARGET.

SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 584M

SQL> show parameter memory_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_target                        big integer 584M

Memory_target can be resized even if instance is up and running, but the maximum upper limit of memory_target is memory_max_target only.

Command to change memory_target

SQL> alter system set memory_target=400M scope=both;
System altered.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 584M
memory_target                        big integer 400M
shared_memory_address                integer     0

memory_target value has changed from 584M to 400M without restarting database.

Command to change memory_max_target (needs a database restart)

SQL> alter system set memory_max_target=600M scope=spfile;
System altered.

SQL> show parameter memory_max_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 584M

SQL> shutdown immediate
SQL> startup
SQL> show parameter memory_max_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 600M

During memory_max_target DBA can face ORA-00845: MEMORY_TARGET not supported on this system.Database administrator usually resize memory paraterms to accommodate database load on the system.

8. SGA_MAX_SIZE & SGA_TARGET: Till Oracle 10g there was only concept of SGA_MAX_SIZE & SGA_TARGET. An SGA also has all other components. These parameters in database has same properties as MEMORY_MAX_TARGET & MEMORY_TARGET in case of changing parameter values. i.e. SGA_TARGET is a dynamic component while to change SGA_MAX_SIZE database restart is required.

SQL> show parameter sga_max
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 584M

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

sga_target can be resized even if instance is up and running, but the maximum upper limit of sga_target is sga_max_size only. Command to change sga_target

SQL> alter system set sga_target=500M scope=both;
System altered.

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 500M

sga_target value has changed from 584M to 400M without restarting database.

SQL> alter system set sga_max_size=600M scope=spfile;
System altered.

SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 584M

SQL> shutdown immediate
SQL> startup
SQL> show parameter memory_max_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                     big integer 600M

9. PGA_AGGREGATE_TARGET: This parameter in database specifies aggregate pga memory allocated to all server process associated with the instance. PGA area in an instance is basically used for operations like sort, group-by, hash-join, bitmap merge, and bitmap create etc. A nonzero value for this parameter is the default, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater as default value. Setting PGA_AGGREGATE_TARGET means setting WORKAREA_SIZE_POLICY parameter to AUTO and effect of this, sql work area used by operation sort, merge etc will be automatically resized as per need basis.

"ORA-4030" is the most common error because of insufficient memory. In this case eithe DBA has to resize PGA memory or need to investigate what is causing this issue.

There are so many memory related parameters like MEMORY_MAX_TARGET & MEMORY_TARGET, SGA_MAX_SIZE & SGA_TARGET and PGA_AGGREGATE_TARGET. Here are n number of possibilities for setting these parameters. All the possibilities are coverd in at one place.

10. CURSOR_SHARING: The parameter in database determines SQL statements can share the same cursor or not. This has three possible values FORCE, SIMILAR and EXACT. Default
value for this parameter is EXACT, but most of the times it's recommended to user SIMILAR to get faster query respone, However this depends on env as well.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

SQL> alter system set cursor_sharing='SIMILAR' SCOPE=both;
System altered.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      SIMILAR

If you know any other parameter which plays an important role for Database Please share in comments part.