Imp Oracle INIT parameters

To get the details of the initialization parameters

V$SYSTEM_PARAMETER2 : Will show the parameter for the whole system

V$PARAMETER2 : Will show the parameter for this session

V$PARAMETER_VALID_VALUES : Will show all the possible valued values for that parameter

V$OBSOLETE_PARAMETER : Obsolete parameter which are included in the initialization file.

Changing init parameters

SPFILE :

When you have started your database with spfile you could modify the initialization

parameters using "ALTER SYSTEM " command, however you need to be aware

of the argument "scope" with the command

First Check the various states for the initialization parameters

select disctint issys_modifiable from v$parameter

ISSYS_MOD

------------

DEFFERED

FALSE

IMMEDIATE

Above

DEFFERED : Bounce is required for the modification to take effect

FALSE : Means the parameter cannot be modified

IMMEDIATE : Parameter could be changed instantly

Various scope i.e

ALTER SYSTEM <parameter>=<value> scope=spfile

means it makes modification in spfile however changes come in affect at next startup

ALTER SYSTEM <parameter>=<value> scope=memory

means changes take effect instantly and are there till the instance is on and are gone

when it is restarted

For parameter whose ISSYS_MOD=false, you cannot modify the parameter online,

you have to bounce the database and make changes to pfile,

So the inference is that

if the parameter shows issys_modifiable=IMMEDIATE, you could use scope=both to get

it done immediately and last forever

==

O7_DICTIONARY_ACCESSIBILITY : Values TRUE | FALSE

Controls access to objects in SYS schema, if set to True enables access.

If set to FALSE it restricts access, so when you give select any right to a user

he/she is not able to acces sys objects.

Also when set to false and you need to access the sys objects, you have to explicity

grant the "SELECT_CATALOG_ROLE"

==

ARCHIVE_LAG_TARGET : Forces a log switch after the specified amount of time elapses.

==

DB_BLOCK_CHECKING : ALTER SYSTEM set DB_BLOCK_CHECKING = OFF | LOW | MEDIUM | FULL

OFF : No Block Checking is performed for blocks in the user tablespaces, block

checking for SYSTEM tablespace blocks is always turned on

LOW : Block header checks are performed after block content changes in memory

MEDIUM : All LOW checks are performed,not the index-organized table blocks

FULL : All LOW checks are performed,including the index blocks

( FULL=TRUE and,FALSE=OFF , For backward compatibility )

==

DB_BLOCK_CHECKSUM ALTER SYSTEM set DB_BLOCK_CHECKSUM = OFF | TYPICAL | FULL

Checksum is stored for every block and is verified everytime block is read.

When set to full catches in-memory corruptions and stops them from making it to the disk

For backward Compatibility

True=Typical

False=off

=

DB_CACHE_ADVICE : ON | OFF Gives us the estimation for different cache sizes, through V$DB_CACHE_ADVICE

==

DB_CACHE_SIZE = integer [K | M | G]

ASMM : Automatic Shared Memory Management

To Enable ASMM set SGA_TARGET should not be equal to zero

and STATISTICS_LEVEL should be set to TYPICAL | ALL

Below mentioned are the auto-tuned parameters ASSM dynamically changes the sizes of these pools.

db_cache_size

shared_pool_size

large_pool_size

java_pool_size

SGA _TARGET : It specifies the total amount of SGA memory is available to the auto-tuned parameters.Oracle distributes the memory

among the components , shared pool, java pool, large pool and buffer cache.

If SGA_TARGET is not set, then the default is either 48 MB or 4MB * number of CPUs * granule size, whichever is greater.

============================================

PGA_AGGREGATE_TARGET

This distributes memory across the following parameters.

SORT_AREA_SIZE

HASH_AREA_SIZE

BITMAP_MERGE_AREA_SIZE

CREATE_BITMAP_AREA_SIZE

When the PGA_AGGREGATE_TARGET is not specified,

WORKAREA_SIZE_POLICY initialization parameter if not included in the init file default to its value of

AUTO which means enabled. The value of the PGA_AGGREGATE_TARGET defaults to 10M or 20% of SGA

whichever is higher.

DB_FILE_MULTIBLOCK_READ_COUNT : Specifies maximum number of blocks read in one I/O Operation during a sequential scan.

Since 10g Oracle recommends not setting the db_file_multiblock_read_count, oracle computes on its own.

=

DML lock : DML_LOCKS specifies the maximum number of DML locks

Should be greater than the Total of all dml locks on all tables used by the users;

If your database supports a lot of parallel DML, you may need to increase the value of this parameter.

=

FILESYSTEMIO_OPTIONS , Specifies the I/O Operations

==

SERVICE_NAMES : Specifies the names by which client would connect to the oracle database.

The Service name must be registered with oracle,

=

REMOTE_LOGIN_PASSWORDFILE = Specifies oracle whether to check for password file

None : ignores password file

shared : Password file is shared

Exclusive : Password file can be used by only one database , and contains name other than SYS

====

OPTIMIZER_FEATURES_ENABLE : optimizer version number corresponds to the oracle version.

if you db has been upgraded form 8 to 10, then you have two version of optimizer features to use from

10.1.0 or 8.1.7

====

optimizer_index_cost_adj : changes the way the optimizer utilizes,

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal

+++++++++++++

07_dictionary_accessibility : TRUE|FALSE.

If set to true, objecs in sys schema is allowed.

If set to false, access to sys schema is restricted even if we give select any table privilege

archive_lag_target : 0 To disable, Other values [60,7200] Seconds. Forces log switch after specified time elapses.

asm_diskgroups : List of disk groups to be mounted by ASM instance.

asm_power_limit : The maximum power on an Automatic Storage Management instance for disk rebalancing

audit_trail : none - disables database auditing

os - OS' log file

db - enters in SYS.AUD$ Table

xml - enter in xml file on os

audit_sys_operation : True/False, Enable or disables audit of SYS,SYSDBA,SYSOPER uses.

audit_file_dest : When Audit_trail set to OS, file_dest provides the location on OS to write audit logs

background_core_dump : partial = Does not include SGA in core dump

full = It does include SGA

background_dump_dest and user_dump_dest are depricated in 11g.

diagnostic_dest : This replaces the above background_dump_dest and user_dump_dest locations.

Also it is called Automatic Diagnostic Repository (ADR) Home.

The following files are located under the ADR home directory:

Trace files - located in subdirectory <adr-home>/trace

Alert logs - located in subdirectory <adr-home>/alert.

In addition, the alert.log file is now in XML format,

which conforms to the Oracle ARB logging standard.

Core files - located in the subdirectory <adr-home>/cdumd

Incident files - the occurrence of each serious error (for example, ORA-600, ORA-1578, ORA-7445)

causes an incident to be created. Each incident is assigned an ID and dumping for each incident

(error stack, call stack, block dumps, and so on) is stored in its own file, separated from

process trace files. Incident dump files are located in <adr-home>/incident/<incdir#>.

You can find the incident dump file location inside the process trace file.

bitmap_merge_area_size : PGA_AGGREGATE_TARGET paramter is recommented to be use, as it automatically

sizes the SQL Working areas, including the BITMAP_MERGE_AREA_SIZE,

=>Amount of memory to use for merging bitmaps from range of index scan.

blank_trimming

control_file_record_keep_time : number of days before a reusable record in the control file can be reused

If this parameter is set to 0, then reusable sections never expand, and records are reused as needed

DB_ULTRA_SAFE sets the default values for other parameters that control protection levels.

Values:

OFF

When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.

DATA_ONLY

DB_BLOCK_CHECKING will be set to MEDIUM.

DB_LOST_WRITE_PROTECT will be set to TYPICAL.

DB_BLOCK_CHECKSUM will be set to FULL.

DATA_AND_INDEX

DB_BLOCK_CHECKING will be set to FULL.

DB_LOST_WRITE_PROTECT will be set to TYPICAL.

DB_BLOCK_CHECKSUM will be set to FULL.

workarea_size_policy : AUTO:MANUAL : - Relates to the Memory-intensive operators are sized automatically

If Manual, we have to specify the values of *_AREA_SIZE

utl_file_dir : UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O.

All users can read or write to all files specified by the parameter.

Memory Management.

max_memory_target

memory_target

sga_max_size

sga_target

pga_aggregate_target

Memory Target specifies the Memory Utilized by Oracle.

The database tunes (reducing or enlarging) memory for SGA and PGA as required by oracle, based on system requirement.

memory_max_target specifies the max memory that could be available for oracle (i.e to be utilized by Memory target)

If you omit max_memory_target, oracle automatically sets it to memory_target.