Oracle Tips 1 (Oracle Architectural Components)

Oracle Server consists of an Oracle instance and an Oracle database

instance: memory structure, background process

database: datafile, log file, control file

shutdown immediate

startup nomount

alter database mount;

alter database open;

show sga;

select * from v$bgprocess

select * from v$logfile

select * from v$controlfile

show parameter shared

SGA is dynamic and sized using SGA_MAX_SIZE

show parameter sga;

 alter system set db_cache_size=640m;

Shared pool is used to store the most recently executed SQL statements and the most recently used datadefinitions.

Database Writer(DBWn) writes when: CheckPoint, Dirty buffers threshold reached, No free buffers, Timeout, RAC ping request, Tablespace read only, Table DROP OR TRUNCATE, Tablespace BEGIN BACKUP.

Log Writer(LGWR) writes when: write commit, one-third full, there is 1 MB of redo, every 3 secods, before DBWn writes.

System Monitor(SMON) Responsibilities: instance recovery, Coalesces free space ever 3 sec, Deallocates temporary segments.

Process Monitor(PMON) cleans up after failed processes by: rolling back the transaction, releasing locks, releasing other resource, restarts dead dispatchers.

Checkpoint(CKPT) Responsible for: Singanlling DBWn at checkpoints, Updating datafier headers with checkpoint information, Updating control file with  checkpoint information.

Archiever(ARCn) Optional background process, Automatically carchieves online redo logs when ARCHIVELOG  mode is set, Preserves the record of all change made to the database.