Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Concepts - initialization parameters init.ora

Oracle Tips by Burleson Consulting

The Oracle initialization parameter file

When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

Here is a sample init.ora file:

db_cache_size = 176000M

db_2k_cache_size = 2048M

db_16k_cache_size = 99000M

db_keep_cache_size = 600000M

db_recycle_cache_size = 64000M

shared_pool_size = 14000M

In this section we will look at the database parameter file in more detail. First we will look at the two different types of parameter files you can use, PFILE?s and SPFILE?s, followed by a look at the parameters that are maintained in the database parameter file. Finally, we will look at how to manage the initialization parameter file.

Oracle init.ora Parameters

The initialization parameters are a very important part of the Oracle database. Oracle reads the initialization parameter values from either a PFILE or SPFILE as the database is starting. The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and the location of existing datafiles.

A parameter has a name and a value. In this example, we have a parameter named db_block_size. This parameter tells oracle how big each individual block in the database is (we discussed blocks in an earlier chapter in this book). In this case, each block is 8192 bytes, or 8k, in size.

db_cache_size=8192

The next parameter you see is the background_dump_dest parameter. This parameter defines the location of Oracle trace files (log files) that are created by the Oracle background processes (we talked about the Oracle processes earlier in this book) and the important alert log where database messages reside. In this case, all files written by Oracle background processes will be in the /u01/oracle/admin/mydb/bdump directory.

background_dump_dest=/u01/oracle/admin/mydb/bdump

You can find all the Oracle Database parameters at Oracle?s web site, tahiti.oracle.com. Look in the reference guide and you will find them all listed, except for undocumented hidden parameters.  These parameters, while changeable, should only be modified with the aid of an experienced DBA or Oracle Technical Support (called MOSC).

Some parameters are dynamic and they can be changed while the database is running. For example, you can decrease the database buffer cache in many cases while the database instance is running with the alter system command:

alter system set db_recovery_file_dest_size=10g;

In this example, we dynamically changed the parameter db_recovery_file_dest to a value of 10 gigabytes (10g). The database will maintain this parameter until it is rebooted, unless a SPFILE is used. We will talk about SPFILE?s shortly.

Initialization File

Although not normally discussed as a database file a database will not startup without a valid initialization parameter file. The file is only read at startup and contains the information required to set up the SGA, control file locations, trace and dump file locations and setup parameters for multi-threaded server, parallel query and Oracle Parallel Server. I suggest keeping a copy in a secure place incase yours ever becomes corrupted, deleted or damaged. The Oracle Reference, which is included in the Oracle8 and Oracle8i documentation set, provides descriptions of all initialization parameters. There are also undocumented initialization parameters that allow specialized behaviors to be configured. Both the documented and undocumented initialization parameters are covered in my book ?ORACLE8i Administration and Management? from Wiley and Sons.

It should be obvious that the most important file regarding database setup and operation is probably the INIT<sid>.ORA, or initialization file. This file contains the assignments for the database initialization parameters.

For Oracle7, version 7.3, there are 154 initialization parameters, for Oracle8, version 8.0.5, there are 184. In Oracle8i there are 194. Table 11 is a list of the Oracle8i INIT.ORA parameters, their default values, and descriptions. Note that on your platform there may be more or less than this number of parameters.

NAME

VALUE

DESCRIPTION

O7_DICTIONARY_ACCESSIBILITY

TRUE

V7 Dictionary accessibility  Support (Migration only)

ALWAYS_ANTI_JOIN

NESTED_LOOPS

Always use this anti-join when possible

ALWAYS_SEMI_JOIN

standard

Always use this semi-join when possible

AQ_TM_PROCESSES

0

Number of AQ Time Managers to start

AUDIT_FILE_DEST

(PD)

Destination for audit files

AUDIT_TRAIL

NONE

Enable system auditing

BACKGROUND_CORE_DUMP

PARTIAL

Sets whether SGA is dumped with core file dump,   PARTIAL means don't dump SGA.

BACKGROUND_DUMP_DEST

(PD)

Detached process dump directory

BACKUP_TAPE_IO_SLAVES

FALSE

BACKUP Tape I/O slaves

BITMAP_MERGE_AREA_SIZE

1048576

Maximum memory allow for BITMAP MERGE

BLANK_TRIMMING

FALSE

Blank trimming semantics parameter

BUFFER_POOL_KEEP

0

Number of database blocks/latches in KEEP buffer pool             

BUFFER_POOL_RECYCLE

0

Number of database blocks/latches in recycle buffer pool

COMMIT_POINT_STRENGTH

1

Bias this node has toward not preparing in a two-phase commit

COMPATIBLE

8.1.0

 Database will be compatible with this software version

CONTROL_FILE_RECORD_KEEP_TIME

7

Control file record keep time in days

CONTROL_FILES

(PD)

Control file names list

CORE_DUMP_DEST

(PD)

Destination for core dump files.

CPU_COUNT

(PD)

Number of cpu's for this instance

CREATE_BITMAP_AREA_SIZE

8388608

Size of create bitmap buffer for bitmap index

CURSOR_SPACE_FOR_TIME

FALSE

Use more memory in order to get faster execution

DB_BLOCK_BUFFERS

8000

Number of database blocks  cached in memory

DB_BLOCK_CHECKING

TRUE

 Data and index block checking overrides events 10210 and 10211

DB_BLOCK_CHECKSUM

FALSE

Store checksum in db blocks and check during reads

DB_BLOCK_LRU_LATCHES

1|CPU_COUNT/2

Number of lru latches

DB_BLOCK_MAX_DIRTY_TARGET

DB_BLOCK_BUFFERS

Upper bound on modified buffers/recovery reads

DB_BLOCK_SIZE

(PD)

Size of database block in bytes

DB_DOMAIN

WORLD

Directory part of global database name stored with CREATE DATABASE

DB_FILE_DIRECT_IO_COUNT

64

Sequential I/O block count

DB_FILE_MULTIBLOCK_READ_COUNT

8

Db blocks read for each IO

DB_FILE_NAME_CONVERT

NULL

 Datafile name convert pattern and string for standby/clone database

DB_FILES

MAXDATAFILES

Max allowable # db files

DB_NAME

(PD)

Database name specified in CREATE DATABASE

DB_WRITER_PROCESSES

1

Number of background database writer processes to start

DBLINK_ENCRYPT_LOGIN

FALSE

Enforce password for distributed login always be encrypted

DBWR_IO_SLAVES

0

Number of DBWR I/O slaves

DELAYED_LOGGING_BLOCK_CLEANOUTS

TRUE

Turns delayed block cleanout on or off

DISK_ASYNCH_IO

TRUE

Use asynch I/O for random access devices

DISTRIBUTED_TRANSACTIONS

(PD)

Max. number of concurrent distributed transactions

DML_LOCKS

4*Trans.

Dml locks - one for each table modified in a transaction

ENQUEUE_RESOURCES

Derived

Resources for enqueues

EVENT

NULL

Debug event control

FIXED_DATE

NULL

Fixed SYSDATE value

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY

FALSE

Freeze database during instance recovery (OPS)

GC_DEFER_TIME

10

How long to defer down converts for hot buffers (DFS)(OPS)

GC_FILES_TO_LOCKS

NULL

Mapping between file numbers and lock buckets  (DFS)(OPS)

GC_RELEASABLE_LOCKS

0

Number of releasable locks (DFS)(OPS)

GC_ROLLBACK_LOCKS

20

Locks for the rollback segments (DFS)(OPS)

GLOBAL_NAMES

TRUE

Enforce that database links have same name as remote database

HASH_AREA_SIZE

2*SORT

Size of in-memory hash work area

HASH_JOIN_ENABLED

TRUE

Enable/disable hash join

HASH_MULTIBLOCK_IO_COUNT

1

Number of blocks hash join will read/write at once

HI_SHARED_MEMORY_ADDRESS

0

SGA starting address (high order 32-bits on 64-bit platforms)

HS_AUTOREGISTER

TRUE

Enable automatic server DD updates in HS agent self-registration

IFILE

NULL

Include file in init.ora

INSTANCE_GROUPS

NULL

List of instance group names

INSTANCE_NAME

NULL

Instance name supported by the instance

 

 

 

INSTANCE_NUMBER

0

Instance number

JAVA_POOL_SIZE

10000K

Size in bytes of the Java pool

JOB_QUEUE_INTERVAL

60

Wakeup interval in seconds for job queue processes

JOB_QUEUE_KEEP_CONNECTIONS

FALSE

Keep network connections between execution of jobs

JOB_QUEUE_PROCESSES

0

Number of job queue  processes to start

LARGE_POOL_SIZE

0

Size in bytes of the large allocation pool (auto set at 600k)

LICENSE_MAX_SESSIONS

0

Maximum number of non-system user sessions allowed

LICENSE_MAX_USERS

0

Maximum number of named users that can be created in the database

LICENSE_SESSIONS_WARNING

0

Warning level for number of non-system user sessions

LM_LOCKS

12000

Number of locks configured for the lock manager (OPS)

LM_PROCS

64

Number of client processes configured for the lock manager (OPS)

LM_RESS

6000

Number of resources configured for the lock manager (OPS)                                      

LOCAL_LISTENER

NULL

Local listener

LOCK_NAME_SPACE

NULL

Lock name space used for generating lock names for standby/clone database

LOCK_SGA

FALSE

Lock entire SGA in physical memory

 

 

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.