Embedded Files
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.
Page updated
Google Sites
Report abuse