oracle 10g installation in OEL 5

                                                  - - - dotted lines states oracle server process.

oracle instance -- logical. -- sga,pga,server process,bg process(smon, pmon, dbwr, lgwr, checkpoint).
oracle database -- physical. - datafiles, control files, online redo log files.


user process (User Process Interface) : client sending a request from one end. it starts as we use putty to connect to server machine. connecting to server as sqlplus prompt command.
ex: sqlplus sys/sys as sysdba

server process (Oracle Process Interface) : connecting to db using command sqlplus / as sysdba..
2 types of server process in oracle,
1. shared server(dispatcher): multiple user using single resources for sending request and receving reply from the database as using single server process.(till 8i)
2. Dedicated server(listener) : each and every user uses dedicate server process as independent of other user for sharing.(from 9i) (from 10g default ).

passwd file : this will maintain the user password and login details and
parameter(pfile) : this will state the parameter required for db creation.

datafile is used to store data in the format of binary as tables. (sys & sysaux tables).
control file is used to store data about the database which will control entire database. (1 - 8)
logfile is used in recovery process as it takes out backup of each and every transaction logs.
groups (2 - 32)
members (1 - 8)

components in share pool : shared_pool_size= 16mb
1. library cache : used to validate the given user query request.
  sql area : it undergoes validation stage and query processing.
     1. syntax & semantic check
     2. hash value generation.
     3. parsing. - hard & soft parsing. uses LRU & MRU algorithms.
     4. execution plans - by default will generate 2000 execution plans.
            parameter used to set to generate execution plans,
    optimizer_max_permutations   --- by default 2000 plans will b generated.

   plsql area :
    1. same as sql area 1.
    2. compilation code. taking hash value. and rest are same.

2. data dictionary : meta data, where it contains data about the oracle standard database information which will be used for validating a user input.

Pin SQL statements in memory using DBMS_SHARED_POOL:
1. Issue the SQL statement
2 packages need to be run,
dbmspool.sql
prvtpool.plb
For example:
connect sys user and perform as below.

SQL>select * from SCOTT.emp;

2. Get the SQL address from v$SQLAREA
SQL> select address, hash_value, sql_text
from v$sqlarea
where sql_text like '%SCOTT.emp%';

ADDRESS HASH_VALUE SQL_TEXT
________ ____________ ______________
836187c0 1131378091 select * from SCOTT.emp

3. Pin the SQL statement using address and hash_value.
SQL> exec dbms_shared_pool.keep('836187c0 1131378091','select * from SCOTT.emp');

4. To check if it was pinned properly
SQL> select name, type, kept
from v$db_object_cache
where kept = 'YES';



3. RLBC : log_buffer=

   Redo log buffer cache, used for database recovery process. it will maintain transaction logs and other logs of user actions as a binary file.

DBBC :
 parts : default, keep, recycle.
 buffers : free, pinned & dirty.
 db_cache_size=  " to resize default in dbbc"
 db_keep_cache_size= " to set size for keep part in dbbc"
 db_recycle_cache_size = " to set size for recycle part in dbbc"

table pinning in dbbc :
To Pin a Table:
Alter table <tab-name> storage (buffer_pool default); -- to pin table in default. (unpinning)
alter table <tab-name> storage (buffer_pool keep);

checking whether its been pinned.
sql> select TABLE_NAME,BUFFER_POOL from dba_tables where owner='SCOTT';


java pool :
if getting java related query..
java_pool_size = " to set size for java pool "

large pool :
          used to reduce burden of share pool.
types of burden its reduces are,
Handling huge amount of incoming request which is more than size of share pool.
session information maintaining,
rman backup info maintaining,
segment info.
 large_pool_size=0 (by default)
 to enable large pool to reduce share pool burden, this paremeter need to be turned on,
parallel_automatic_tunning = T/F

streams pool :
from 10g it has started. it is called capture and allocation.
for ex: a table containing 15 columns of data.. where we need only 10 cols. stream pool will capture that  column img and put in stream pool buffer and allocate memory size for it. den process to dbbc.


Automatic memory management(from 10g):
SGA_target = fixed and completely used size.
SGA_MAX_SIZE = its an expandable size.
 it shud be equal to sga_target or more than tat.
from 11g,
Memory_target= will automatically assign for pga & sga.
memory_max_target= same as its expand size.


background processes :
 till now as in 11g , we have 250 + bg process running.
9i - 60+ bg process,
10g - 150+ bg process,
11g - 250+ bg process.
v$parameter - to see what are the parameters available.
v$bgprocess - to see wat are the bg process available in db level.

smon - system monitor (used to fix system level process issues)
used in recovery, fragmentation of memory space etc.
  1. instance recovery
          roll forward the transaction logs from log files to rlbc.
          opens the database and release the uncommitted.
          roll back the uncommitted transaction and committed transaction will get reexecuted in dbbc.
  2. coalesce the adjusent free space
           it will arrange the dispattern or scattered mode of filling buffers into linear order of blocks and the buffers as free and used buffers.
  3. deallocate the temporary memory segment.
               In pga, temporary memory gets used for sorting purpose. after a user uses tat area, the memory needs to be released for next user process. this will be done by this process..

pmon - process monitor (to fix issue on process level)
 1. roll back the uncommitted transactions. (use of normal rollback command issued by user). also savepoint
    operation is handled by pmon.
 2. releasing the locks.
         table level locks. & row level locks.
  two different user accessing same row at same time to modify value will result in row level lock for a user. this lock is released by pmon.
  similarly 2 user accessing same table at same time for manipulation will result in table level lock and this lock is released by pmon.
 3. releasing the deadlocks.
         this is a user level lock as the user itself goes on a lock. only dba can unlock a user using ticketing system raised from user.



Dbwr :
       this background process will write dirty buffer from dbbc to datafiles. dbwr writes on when it satisfying any one of the conditions as listed below,

 1. checkpointt occurence
 2. when no free buffers available in dbbc.
 3. when reaches to threshold limit. (30% size of dbbc get filled)
 4. timeout occurs.
 5. Rac ping request.
 6. tablespace drop or offline.
 7. tablespace begin & end backup.

 in 10g & 11g, we have 20 dbwr process, by default v have only 1 dbwr process running for a db (dbw0-dbw9 & dbwa-dbwj). to check how many dbwr process are there
in a running db,
  sql> show parameter db_writer_processes;


lgwr :
   This background process will write the log files from rlbc to redo log files. scenarios its writes on,
1. when commit is issued,
2. 1/3rd of rlbc is getting filled.
3. if 1mb is getting filled.
4. every 3 sec it writes.
5. before dbwr writes lgwr will write the log files to redo log.


Check point:
    this is background process that signals or issue a event to the (dbwr) database for the database consistency. when this happens, dbwr writes data from dbbc to datafiles.

1. Also it updates the unique number called scn(system change number) to datafile,control file & logfile header.
 scn -- every 3 nanosec scn number will be generated in database level. at 3rd sec, the current scn number will be captured and that scn number will be updated in the header files. also at the time of db shutdown, update the scn number in the header file.
   we have 3 types of checkpoints,
 1. full chkpt, -- during shutdown, an overall chkpt will occur. (shut normal, shut immediate, shut transactional)
 2. incremental chkpt, -- forcing a checkpt to occur for database consistency. (can be done by switching log files)
    to force a checkpt, use the parameter,
   alter system fast_start_mttr_target=<number>;
 3. partial chkpt. --for a partial work checkpt to occur. occurs on tablespace backup time.

Other bg processes :
Archiver process (arcn) : this will writes archive log files from redo log file to archive log. for this, archive log & automatic archiving has to be enabled(archive mode). oracle instance can have upto 10 arcn process(ARC0 - ARC9)
 to see how many ARCn in the database,
 sql> show parameter log_archive_max_processes
 to modify ARCn,
 Sql> alter system set log_archive_max_processes=3;

Recoverer process (reco) : this process used to fix the issues involving in connection btw distributed database. if connection from one db is failed to another
in a distributed system, reco tries to connect back to another database with an increase in wait time before it attempts for a new connection. this process is
present only the transactions are a distributed one. (mostly suits on RAC)

job queue process: this process is used for batch processing as this process run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs as PL/SQL statements or procedures on an Oracle instance.

queue monitor processing (QMNn):The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes. These processes, like the job queue processes, are different from other Oracle background processes in that process failure does not cause the instance to fail.


PGA (Program Global Area):

PGA - Program Global Area.. used for sorting purpose.
components in PGA:
1. Private Sql Area
   1.1 persistent - will maintain bind variables information + cursor information. (cursor open & closed info)
A bind variable is a place-holder variable in a SQL statement that must be replaced with a valid value (or address of a value) before the statement can successfully

execute. At runtime the program will provide a value for :EMPNO_BIND_VAR before executing the statement.
"DELETE FROM employees WHERE empno = :EMPNO_BIND_VAR"

   1.2 Runtime Area - will maintain runtime information.
2. SQL Work Area  - for sorting, index creation,..etc.
   2.1 sort area
   2.2 hash area - used for partition.
   2.3 bit map merge area - used on index concept.
   2.4 create bitmap - used on index concept
3. Session memory. -- will maintain abt runtime information for a session.



parameter to set pga_aggregate_target=<size>
from 9i, memory management concept comes, before that all in parameters need to be given. parameters are,
SORT_AREA_SIZE=size
HASH_AREA_SIZE=size
BITMAP_MERGE_AREA_SIZE=size
CREATE_BITMAP_AREA_SIZE =size
after 9i,
PGA_AGGREGATE_TARGET = size
WORKAREA_SIZE_POLICY = auto/manual