Scribd Logo
Upload

Welcome to Scribd!

  • 73 views

    Dbam 02

    Uploaded by

    eashaarsal
    This document provides an overview of the logical and physical structures of an Oracle database. It describes the logical components including tables, indexes, and users. Physically, the database consists of datafiles, redo logs, control files, and other files. It also explains the memory structures including the system global area (SGA) and process structures like server processes and background processes that manage the database.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Dbam 02

    Uploaded by

    eashaarsal
    73 views13 pages
    This document provides an overview of the logical and physical structures of an Oracle database. It describes the logical components including tables, indexes, and users. Physically, the database consists of datafiles, redo logs, control files, and other files. It also explains the memory structures including the system global area (SGA) and process structures like server processes and background processes that manage the database.

    Original Description:

    Database management system

    Original Title

    DBAM-02

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides an overview of the logical and physical structures of an Oracle database. It describes the logical components including tables, indexes, and users. Physically, the database consists of datafiles, redo logs, control files, and other files. It also explains the memory structures including the system global area (SGA) and process structures like server processes and background processes that manage the database.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    73 views13 pages

    Dbam 02

    Uploaded by

    eashaarsal
    This document provides an overview of the logical and physical structures of an Oracle database. It describes the logical components including tables, indexes, and users. Physically, the database consists of datafiles, redo logs, control files, and other files. It also explains the memory structures including the system global area (SGA) and process structures like server processes and background processes that manage the database.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 13

    13/02/16

    Database
    Administration & Management

    Lecture 02

    Oracle Database Structure

    • Logical Database Structure

    • Physical Database Structure


    13/02/16

    Oracle Logical Database Structure


    • Table
    • Business Rules
    • Integrity
    • Indexes
    • Views
    • Users
    • Schemas
    • Profiles
    • Procedures/Functions
    • Triggers

    Oracle Physical Storage Structure


    • Datafile.
    • Redo Log Files.
    • Archived Log Files.
    • Control Files.
    • Initialization Parameter Files.
    • Alert and Trace Log Files.
    • Back-up Files.
    • Oracle Managed Files.
    • Password Files.
    13/02/16

    Oracle Physical Storage Structure


    • Datafile:

    • Every oracle database must contain at least one datafile

    • One datafile corresponds to one physical operating system file on disk.

    • Each datafile is a member of one and only one tablespace.

    Oracle Physical Storage Structure


    • Redo Log Files:
    • Any missing entries in database buffer cache that are not yet written to
    the datafiles due to server/power failure can be redo upon next restart.

    • An entry is written to current redo log file upon add/remove/change of


    data in a table/index/object.

    • Each oracle database must have at least two redo log files.

    • Oracle reuses them in circular fashion; when one is filled the CURRENT
    log file is marked as ACTIVE, if it is needed for instant recovery.

    • Other marked at INACTIVE.


    13/02/16

    Oracle Physical Storage Structure


    • Archived Log Files:
    • Oracle database can work in two modes.
    • Nonarchivelog mode
    • Archivelog mode
    • Redo log files don’t work in nonarchivelog mode.

    • Archivelog mode sends filled redo log files to specified destinations that
    can be used later to reconstruct database if database media failure
    occurs.

    • Control Files:
    • Maintains metadata of database.
    • Every oracle database has at least one control file.

    Oracle Physical Storage Structure

    • Initialization Parameter Files:

    • Open/execute when a database instance starts for initialization startup.

    • Either a text-based file : Init<SID>.ora (known as init.ora or PFILE).

    • or server parameter file : spfile<SID>.ora (known as spfile.ora or SPFILE).


    13/02/16

    Oracle Physical Storage Structure


    • Alert and Trace Log Files:

    • Contains both routine messages and error conditions.


    • Contains any alter database or alter system commands.
    • Contains operations involving tablespace and datafiles.
    • Errors i.e. tablespace running out of space, corrupted redo logs.

    • Trace files are created for individual user sessions or connections to


    database.
    • i.e. user privilege problem, running out of space

    Oracle Physical Storage Structure


    • Backup Files:
    • Cold backups: Operating system copy commands (full copies of
    datafiles, redo log files, control files, archived files etc.)

    • Oracle Recovery Manager (RMAN) : full or incremental backups.

    • Recovery manager’s backups are smaller as it does not back up unused


    blocks.

    • Oracle Managed Files (OMF):


    • To automate creation and removal of datafiles.
    13/02/16

    Oracle Physical Storage Structure


    • Password Files:
    • Used to authenticate oracle system administrators i.e. SYSDBA and
    SYSOPER privileges (to create database. Startup or shutdown database).

    Memory Structures
    13/02/16

    • Instance:
    • A set of memory structures that manage database files.

    • Database is a set of physical files on disk.

    • Consists of memory and process structure; as a mean to access an


    Oracle database; always opens one and only one database.

    • Instance can exist without database and database can exist without
    instance.

    • Instance manages its associated data and serves database users.

    Oracle Memory Structures


    • Server’s physical memory is used in an Oracle instance to
    execute:
    • Session information.
    • Processes involved with database.
    • SQL statements etc.

    • The shared area allocated for an oracle instance is called


    SYSTEM GLOBAL AREA (SGA).

    • The non shared memory private to each server process is


    Program Global Area (PGA).
    13/02/16

    System Global Area (SGA)


    • A group of shared memory structures for an Oracle instance;
    shared by users of database instance.
    • It is either hard-coded in Oracle software or reads values from
    initialization parameter file.
    • Most of its parameters have dynamic size unless the parameter
    SGA_MAX_SIZE is not specified.
    • SGA is allocated in units of GRANULES.
    • A granule can either be 4MB or 16MB. (if the total SGA is less
    than or equal to 128MB, then a granule is 4MB, otherwise
    16MB).

    System Global Area (SGA)

    • Shared Pool.

    • Large Pool.

    • Java Pool.
    13/02/16

    System Global Area (SGA)


    • Shared Pool:
    • Shared pool is sized by SHARED_POOL_SIZE initialization parameter;
    otherwise is dynamic parameter.

    • Contains two major subcaches:


    • Library cache.
    • Data dictionary cache.

    System Global Area (SGA)


    • Shared Pool:
    • Library Cache:
    • Holds information about SQL and PL/SQL statements that run
    against database.

    • Shared by all users, thus many users can share same SQL statement.

    • Also contains execution plan and syntax-tree (also known as parse


    tree) or SQL statement to improve execution time without reloading
    SQL statements each time.
    13/02/16

    System Global Area (SGA)


    • Shared Pool:
    • Data Dictionary Cache:
    • Is a collection of database tables, owned by SYS and SYSTEM
    schemas, that contains metadata about the database, its structures,
    privileges and roles of database users.

    • Large Pool:
    • Is an optional area of SGA.
    • Having large blocks of memory.
    • A dynamic parameter with initialization parameter LARGE_POOL_SIZE.
    • Used for transactions that interact with more than one database; for
    processing parallel queries, RMAN parallel backups and restore
    operations.

    Processes
    13/02/16

    Processes
    Processes execute series of steps, or perform a specific task in
    Oracle.

    • User Processes.

    • Oracle Processes.

    Processes
    • User Process:

    • It must first establish a connection.

    • Cannot interact directly with Oracle server.

    • Is created to execute the program code of an application program.

    • It links the user’s connection or client application to the Oracle instance.


    13/02/16

    Processes
    • Oracle Process:
    • Invoked by any other process to perform specific functions on behalf of
    invoking process.
    • Two subtypes; server process and background processes

    • Server Process:
    • Directly interacts with Oracle server.
    • Can be dedicated or shared server.
    • Server processes communicate with user processes acting as a “relay” between the
    user process and SGA information.

    Processes
    Server
    process
    Connection
    established Session created
    User Oracle server
    process

    Database user

    • Oracle Processes:
    • Background Processes:
    • Perform designated data management and processing functions for
    Oracle instance.
    13/02/16

    Processes
    • Background Processes:
    • Process Monitor (PMON) performs cleanup duties (cleaning up cache,
    releases locks etc.)
    • System Monitor (SMON) provides instant recovery during startup.
    • Database Writer (DBWR) writes data from data cache to physical disk
    files.
    • Log Writer (LGWR) managing the redo log buffer.
    • Recover (RECO) automatically resolves failures involving distributed
    transactions.
    • Archiver (ARCn) copies redo log files to destination directories if
    ARCHIVELOG mode is enabled.
    • Checkpoint (CKPT) helps to reduce the amount of time required for
    instant recovery.

    Overview of Primary Components


    Instance
    User
    process Shared pool SGA
    Library
    cache Database Redo log
    Server Data Dict. buffer cache buffer cache
    process cache
    PGA
    PMON SMON DBWR LGWR CKPT Others

    Data Control Redo log


    Parameter files files files Archived
    file log files

    Password
    file Database

    You might also like