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.