DBM 420

1
DBM 420

• Oracle Internals
• Workshop Two
• Richard Perry

2
Agenda

• Oracle Instance Start Up
• Oracle Memory Structure
• Oracle Processes
• Creating an Oracle Database
• Data Dictionary
• Control Files
• Database Architecture - Tablespaces
• Wrap Up

3
Oracle Instance Start Up

• Memory Allocated
• Background Processes Start
• Disks Mounted
• Database Opened
• Memory, Process, and Disk information are all
  contained in init.ora and control files.

4
Oracle Memory Structure

• System Global Area
• Make as large as possible while fitting in real
  memory.
• Database Buffer Cache (DB_BLOCK_BUFFERS)
• Most recently used blocks of data (modified and
  unmodified)
• Write List
• Dirty Buffers - data modified but not yet written
  to disk

5
Oracle Memory Structure (cont.)

• Least Recently Used List
• Free Buffers - dont contain any useful data,
  available for use
• Pinned Buffers - currently being accessed
• Dirty buffers - not moved to Write List
• Redo Log Buffer (LOG_BUFFER)
• Log of changes to the database
• Written to the online redo log file
• Used if Database recovery is necessary

6
Memory (cont.)

• Shared Pool (SHARED_POOL_SIZE)
• Library Cache
• Shared SQL Area
• process every SQL statement submitted to the
  database
• Contains parse tree and execution plan
• Private SQL Area
• PL/SQL Procedures and Packages
• Control Structures
• Locks
• Library cache handles
• Dictionary Cache

7
Memory (cont.)

• Control Structures
• Reusable Runtime Memory
• Large Pool
• Optional
• Used for Backup and Restore operations and I/O
  server operations
• Program Global Area
• Stack Space
• Session Information

8
Oracle Processes

• User Processes
• Created to execute the application code or an
  Oracle tool.
• Server Processes
• Dedicated Server (11)
• Multi-threaded (1M)
• Handles connection requests from User Processes

9
Oracle Processes (cont.)

• Background Processes
• DBWn - Database Writer
• writes modified blocks from the database buffer
  cache to the datafiles.
• LGWR - Log Writer
• writes redo log entries to disk from the redo log
  buffer
• CKPT - Checkpoint
• Writes all modified database buffers to datafiles
  at specific times. CKPT signals the DBWn
  processes.

10
Oracle Processes (cont.)

• SMON - System Monitor
• Performs crash recovery when a failed instance
  starts up. Also coalesces free extents within
  tablespaces.
• PMON - Process Monitor
• Performs recovery when a user process fails.
• ARCn - Archiver
• Copies online redo logs to archival storage when
  they are full or a log switch occurs.

11
Oracle Processes (cont.)

• RECO - Recoverer
• Used to resolve distributed transactions that are
  pending due to a network or system failure.
• Dnnn - Dispatchers
• Optionally used with multi-threaded server
  configurations. Routes requests from user
  processes to server processes and back.
• LCK0 - Lock
• Used for inter-instance locking in the Oracle
  Parallel Server.

12
Introduction to Database Creation

• What is the importance of planning the creation
  of a database before it is created?
• What do you plan for before creating a database?

• Why is it important to maintain multiple copies
  of the control file on separate disk storage?
• What is the role of the redo logs in maintain
  application transaction integrity?

13
Creating an Oracle Database

• Back up any existing databases.
• Create parameter files.
• Edit new parameter files.
• Check the instance identifier for your system.
• Use Oracle Database Creation Wizard.
• Start the instance.
• Create the database.
• Back up the database.

14
Procedures of Creating an Oracle 8i Database

• Software Hardware Architecture
• Database Planning and Creation
• Operating System Environment Considerations
• Parameter Files and Instances
• The Create Database Command

15
Data Dictionary

• Read Only Reference for Information about the
  Database
• Data Dictionary Structure
• USER
• ALL
• DBA
• Dynamic Performance Views (V_)

16
Control Files

• Redo Log
• Protects altered database data in memory that has
  not been written to a datafile.
• Online Redo Log
• Archived Redo Log
• Online Redo Switches
• Two or more files used in a cyclical manner.
• Uses a log sequence number.

17
Control Files (cont.)

• Log Switches and Checkpoints
• Archiving Redo Log Files
• Redo logs can be archived before being reused.
• Maintain Redo Log Groups
• Planning Online Redo Logs
• How fast do logs fill?
• How quickly can logs be archived?
• Troubleshooting LGWR Errors

18
Database Architecture - Tablespaces

• Tablespace is a logical unit of storage for a
  database.
• DB may have one or more tablespaces.
• Each tablespace has one or more datafiles, the
  physical unit of storage.
• Each tablespace is divided into segments, the
  logical unit of storage.
• Each segment is divided in extents.

19
Tablespaces (cont.)

• Creating Tablespaces
• Resizing Tablespaces
• Changing Tablespace Properties
• Read-only Tablespaces
• Dropping Tablespaces
• Tablespace Information

20
Wrap Up

• Questions?
• Project Status
• Next Week