Introduction to Oracle - PowerPoint PPT Presentation

1 / 27
About This Presentation

Introduction to Oracle


Introduction to Oracle Physical Structure Logical Structure SGA / PGA Background Processes Backup Methods Computer Science Database CS01 Administrative Tasks – PowerPoint PPT presentation

Number of Views:308
Avg rating:3.0/5.0
Slides: 28
Provided by: MarkG201


Transcript and Presenter's Notes

Title: Introduction to Oracle

Introduction to Oracle
  • Physical Structure
  • Logical Structure
  • SGA / PGA
  • Background Processes
  • Backup Methods
  • Computer Science Database CS01
  • Administrative Tasks

(No Transcript)
Physical Structures
  • Datafiles (.dbf)
  • The datafiles contain all the database data. The
    data of logical database structures, such as
    tables and indexes, is physically stored in the
    datafiles allocated for a database.
  • Control Files (.ctl)
  • Every Oracle database has a control file. A
    control file contains entries that specify the
    physical structure of the database such as
    Database name and the Names and locations of
    datafiles and redo log files.
  • Redo Log Files (.log)
  • The primary function of the redo log is to record
    all changes made to data. If a failure prevents
    modified data from being permanently written to
    the datafiles, then the changes can be obtained
    from the redo log, so work is never lost.

Physical Structures (contd)
  • Archive Log Files (.log)
  • Oracle automatically archives log files when the
    database is in ARCHIVELOG mode. This prevents
    oracle from overwriting the redo log files before
    they have been safely archived to another
  • Parameter Files (initSID.ora)
  • Parameter files contain a list of configuration
    parameters for that instance and database.
  • Alert and Trace Log Files (.trc)
  • Each server and background process can write to
    an associated trace file. When an internal error
    is detected by a process, it dumps information
    about the error to its trace file. The alert log
    of a database is a chronological log of messages
    and errors.

Logical Structures
  • Tablespaces
  • A database is divided into logical storage units
    called tablespaces, which group related logical
    structures together. One or more datafiles are
    explicitly created for each tablespace to
    physically store the data of all logical
    structures in a tablespace.
  • Oracle Data Blocks
  • At the finest level of granularity, Oracle
    database data is stored in data blocks. One data
    block corresponds to a specific number of bytes
    of physical database space on disk. The standard
    block size is specified by the DB_BLOCK_SIZE
    initialization parameter.

Logical Structures (contd)
  • Extents
  • The next level of logical database space is an
    extent. An extent is a specific number of
    contiguous data blocks, obtained in a single
    allocation, used to store a specific type of
  • Segments
  • Above extents, the level of logical database
    storage is a segment. A segment is a set of
    extents allocated for a certain logical
    structure. The different types of segments are
  • Data segment stores table data
  • Index segment stores index data
  • Temporary segment temporary space used during
    SQL execution
  • Rollback Segment stores undo information

Logical Structures (contd)
  • Schema Overview
  • A schema is a collection of database objects. A
    schema is owned by a database user and has the
    same name as that user. Schema objects are the
    logical structures that directly refer to the
    database's data. Schema objects include
    structures like tables, views, and indexes.

(No Transcript)
Oracle Instance
  • An Oracle database server consists of an Oracle
    database and an Oracle instance. Every time a
    database is started, a system global area (SGA)
    is allocated and Oracle background processes are
    started. The combination of the background
    processes and memory buffers is called an Oracle

System Global Area (SGA)
  • The System Global Area (SGA) is a shared memory
    region that contains data and control information
    for one Oracle instance. Users currently
    connected to an Oracle database share the data in
    the SGA. The SGA contains the following memory
  • Database Buffer Cache
  • Database buffers store the most recently used
    blocks of data. The set of database buffers in an
    instance is the database buffer cache. The buffer
    cache contains modified as well as unmodified
    blocks. Because the most recently (and often, the
    most frequently) used data is kept in memory,
    less disk I/O is necessary, and performance is

System Global Area (contd)
  • Redo Log Buffer of the SGA
  • The redo log buffer stores redo entriesa log of
    changes made to the database. The redo entries
    stored in the redo log buffers are written to an
    online redo log, which is used if database
    recovery is necessary. The size of the redo log
    is static.
  • Shared Pool of the SGA
  • The shared pool contains shared memory
    constructs, such as shared SQL areas. A shared
    SQL area is required to process every unique SQL
    statement submitted to a database. A shared SQL
    area contains information such as the parse tree
    and execution plan for the corresponding

Program Global Area (PGA)
  • PGA is a memory buffer that contains data and
    control information for a server process. A
    server process is a process that services a
    clients requests. A PGA is created by oracle
    when a server process is started. The information
    in a PGA depends on the oracle configuration. The
    PGA area is a non-shared area of memory created
    by oracle when a server process is started. The
    basic difference between SGA and PGA is that PGA
    cannot be shared between multiple processes in
    the sense that it is used only for requirements
    of a particular process whereas the SGA is used
    for the whole instance and it is shared.

Oracle Background Processes
  • An Oracle database uses memory structures and
    processes to manage and access the database. All
    memory structures exist in the main memory of the
    computers that constitute the database system.
    Processes are jobs that work in the memory of
    these computers.
  • Oracle creates a set of background processes for
    each instance. The background processes
    consolidate functions that would otherwise be
    handled by multiple Oracle programs running for
    each user process. They asynchronously perform
    I/O and monitor other Oracle processes to provide
    increased parallelism for better performance and
  • The most common background processes are
  • System Monitor SMON
  • This database background process performs
    instance recovery at the start of the database.
    SMON also cleans up temporary segments that are
    no longer in use and recovers dead transactions
    skipped during crash and instance recovery
    because of file-read or offline errors. It
    coalesces i.e. combines contiguous free extents
    into larger free extents.

Background Processes (contd)
  • Process Monitor - PMON
  • This database background process cleans up failed
    user processes. PMON is responsible for releasing
    the lock i.e. cleaning up the cache and freeing
    resources that the process was using. Its effect
    can be seen when a process holding a lock is
  • Database Writer - DBWR
  • This background process is responsible for
    managing the contents of the data block buffer
    cache and dictionary cache. DBWR performs batch
    writes of changed block. Since Oracle uses
    write-ahead logging, DBWR does not need to write
    blocks when a transaction commits. In the most
    common case, DBWR writes only when more data
    needs to be read into the system global area and
    too few database buffers are free. The least
    recently used data is written to the datafiles
  • Although there is only one SMON and one PMON
    process running per database instance, one can
    have multiple DBWR processes running at the same
    time. Note the number of DBWR processes running
    is set via the DB_WRITER_PROCESSES.

Background Processes (contd)
  • Log Writer - LGWR
  • This background process manages the writing of
    the contents of the redo log buffer to the online
    redo log files. LGWR writes the log entries in
    batch form. The Redo log buffers entries always
    contain the most up-to-date status of the
  • Archiver - ARCH
  • The Archiver process reads the redo log files
    once Oracle has filled them and writes a copy of
    the used redo log files to the specified archive
    log destination(s). Actually, for most databases,
    ARCH has no effect on the overall system
    performance. On some large database sites,
    however, archiving can have an impact on system

Background Processes (contd)
  • Checkpoint - CKPT
  • All modified information in database buffer in
    the SGA is written to the datafiles by a database
    write process (DBWR). This event indicates a
    checkpoint. The checkpoint process is responsible
    for signaling DBWR at checkpoints and updating
    all of the datafiles and control files of the
  • Recover - RECO
  • The recover process automatically cleans up
    failed or suspended distributed transactions.
  • Job Queue Processes
  • Job queue processes are used for batch
    processing. They run user jobs. They can be
    viewed as a scheduler service that can be used to
    schedule jobs as PL/SQL statements or procedures
    on an Oracle instance. Given a start date and an
    interval, the job queue processes try to run the
    job at the next occurrence of the interval.

(No Transcript)
Computer Science Database
  • Server Information
  • Sun e4500
  • 8GB Ram
  • 8 x 400mhz CPU
  • 32GB Disk for Oracle
  • 4mm DAT DDS3 Tape Backup

Computer Science Database
  • Instance Name CS01 (vdatabase)
  • Instance Version
  • Tablespaces (dba_tablespaces)
  • SYSTEM holds all system tables
  • INDEX01 user indexes
  • USERS01 user tables
  • USERS02 user tables (faculty)
  • RBS rollback segments

(No Transcript)
Backup Methods
  • Cold Backup (aka Consistent Backups)
  • The only way to make a consistent whole database
    backup is to shut down the database with the
    make the backup while the database is closed.
  • Advantage No recovery is required after
    datafiles are restored quicker restore
  • Disadvantage No access to database during
    backup time (depends on size/system speed)

Backup Methods (contd)
  • Hot Backup (aka Inconsistent Backups)
  • If the database must be up and running 24 hours a
    day, seven days a week, then you have no choice
    but to perform inconsistent backups of the whole
    database. A backup of online datafiles is called
    an online backup. This requires that you run your
    database in ARCHIVELOG mode.
  • Advantage Database remains open during backup
  • Disadvantage Large databases may have
    performance impact during backup, recovery takes
    longer and is slightly more complex

Backup Methods (contd)
  • Logical backup (Export)
  • Logical backups are exports of schema objects,
    like tables and stored procedures, into a binary
    file. Oracle utilities are used to move Oracle
    schema objects in and out of Oracle.
  • Not recommended for backup of a whole database,
    but useful for backing up individual objects or
    schemas or moving data into another database

Administrative Tasks
  • Daily Checks
  • Check database availability
  • Check logs / trace files
  • Check free space / resources
  • Check for invalid objects
  • Check for broken jobs
  • Verify backup

Administrative Tasks (contd)
  • Weekly Tasks
  • Collect statistics (database job)
  • Archive / delete log files
  • Run performance reports (statspack)

Administrative Tasks (contd)
  • Others
  • Applying patches
  • Database upgrades
  • New Database installations
  • Creating user accounts

More Information
  • Oracle 10g Release 2 Database Documentation
  • http//
  • Oracle Database / SQL Help
  • http//
  • http//
  • FREE Oracle Software Downloads
  • http//
  • Oracle Database 10g Express Edition
  • Oracle SQL Developer
  • This Document
  • Computer Science Homepage -gt On-Line help
Write a Comment
User Comments (0)