Oracle Database Certification

OCA Associate (1Z0-061 and 1Z0-052)
OCP Professional
OCM Master
RAC Real Application Clusters
Database Control process
Oracle Enterprise Manager Grid Control
OCI (Oracle Call Interface)


Data Definition Language (DDL)
Data Control Language (DCL)
Transaction Control Language (TCL)

database = physical files on disk
instance = background processes
shared memory segments = system global area, or SGA . Contains: database buffer cache, log buffer, shared pool.
nonshareable memory = program global area, or PGA, private to the session
database buffer cache : to execute SQL.
log buffer (circular buffer) : contains change vectors before they are written to redo log
log writer background process, the LGWR, writes log buffer to disk (commit).
data dictionary cache = tables descriptions etc
library cache = executed parsed code
PL/SQL Area = part of the shared pool, in data dictionary
SQL Query and PL/SQL Function Result Cache
to retrieve tables definitions:
SELECT table_name FROM user_tables;
change vector, redo log
data guard: physical standby and logical standby
is part of RAC DB?
select parallel from v$instance;
has standby DB?
select protection_level from v$database;

Streams configured?
select * from dba_streams_administrator;
display memory settings for different areas (shared pool, large pool, java pool):
select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE from v$sga_dynamic_components;
display PGA memory allocation:
select name,value from v$pgastat;

Notable processes:
System Monitor (SMON), Process Monitor (PMON), Database Writer (DBWn), Log Writer (LGWR), Checkpoint Process (CKPT), Manageability Monitor (MMON), Memory Manager (MMAN), Archiver (ARCn), Recoverer (RECO)
SMON mounts a DB
DBWn writes Database Buffer Cache to DB files
LGWR writes Log Buffers to disk
CKPT flushes dirty buffers
MMON monitors performance and statistics, launches the ADDM Automatic Database Diagnostic Monitor
MMAN Memory Manager
ARCn Archiver
to show all processes: select * from v$process;
Files:
controlfile (pointers to DB files, integrity checksums ),
the online redo log files,
and the datafiles

datafiles consist in blocks, segments, extents (=group of blocks)
SYSTEM and SYSAUX are 2 default tablespaces, they contain the METADATA (data dictionary)

USER_TABLES ALL_TABLES DBA_TABLES
dba_extents :
select DISTINCT TABLESPACE_NAME from dba_extents; -> SYSAUX UNDOTBS1 USERS SYSTEM
v$datafile : select * from v$datafile; gives you all the DBF files who make the DB
select * from dba_tablespaces; TABLESPACE_NAME are SYSTEM SYSAUX UNDOTBS1 TEMP USERS DEV_IAS_TEMP DEV_STB