Oracle Database Administration/Architecture

This lesson introduces Oracle database architecture.

Objectives and Skills

Objectives and skills for the database architecture portion of Oracle Database Administration I certification include:^[1]

Exploring the Oracle Database Architecture
- Explain the Memory Structures
- Describe the Process Structures
- Overview of Storage Structures

Readings

Oracle: Database 2-Day DBA Managing the Oracle Instance

Multimedia

Activities

Complete the tutorial Oracle: Managing the Oracle Instance.
Identify instance configuration.
1. Review Oracle: Introduction to Oracle Database Architecture.
2. Using either SQL*PLUS or SQL Developer, connect to Oracle Database as either SYSTEM or SYS AS SYSDBA.
3. Use the following query to identify clustered (YES) or stand-alone mode:
  SELECT PARALLEL FROM V$INSTANCE;
4. Use the following query to identify data protection level:
  SELECT PROTECTION_LEVEL FROM V$DATABASE;
5. Use the following query to identify Streams configuration:
  SELECT * FROM DBA_STREAMS_ADMINISTRATOR;
Identify memory structures.
1. Review Oracle: Memory Architecture.
2. Use the following query to identify System Global Area (SGA) memory structures and usage:
  SELECT COMPONENT, CURRENT_SIZE, MIN_SIZE, MAX_SIZE FROM V$SGA_DYNAMIC_COMPONENTS;
3. Use the following query to identify Program Global Area (PGA) memory usage:
  SELECT * FROM V$PGASTAT;
Identify process structures.
1. Review Oracle: Process Architecture.
2. Use the following query to identify current sessions:
  SELECT PROGRAM FROM V$SESSION;
3. Use the following query to identify current processes:
  SELECT PROGRAM FROM V$PROCESS;
Identify storage structures.
1. Review Oracle: Tablespaces, Datafiles, and Control Files.
2. Use the following query to identify user extents:
  SELECT * FROM DBA_EXTENTS WHERE TABLESPACE_NAME = 'USERS';
3. Use the following query to identify data files:
  SELECT * FROM V$DATAFILE;
4. Use the following query to identify table spaces:
  SELECT * FROM DBA_TABLESPACES;
5. Use the following query to identify log files:
  SELECT * FROM V$LOGFILE
6. Use the following query to identify the control file:
  SELECT * FROM V$CONTROLFILE;
7. Use the following query to identify log information:
  SELECT * FROM V$LOG