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 150One 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.oraThis 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-13Backup 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 orcl1In 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 200If, 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 DatabaseSQL> 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 584MMemory_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 0memory_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 0sga_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 500Msga_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 SIMILARIf you know any other parameter which plays an important role for Database Please share in comments part.