# Oracle Physical/Logical Structures

1.  Internal database structures - a very brief overview of all Oracle
    internal structures:

    a.  Tables, columns, constraints, datatypes

    b.  Partitions & subpartitions

        1.  Breaking up larger tables into smaller ones based on
            particular value

        2.  Helps performance because optimizer will know where to look
            for particular entries

        3.  Large partitions can be further subpartitioned.

        4.  Index partitions -- same concept

            a.  Local: partitions match the table partitions

            b.  Global: partitions don't match the table partitions.

    c.  Indexes, clusters, & hash clusters

    d.  Views

    e.  Sequences

    f.  Procedures, functions, packages, and triggers.

    g.  Synonyms

    h.  Users, Privileges and roles

    i.  Database links

    j.  Rollback segments

    k.  Snapshots and materialized views.

        1.  Snapshot provides data replication from a remote database
            based on a query. Can be scheduled, read-only or updateable.

        2.  Materialized view provides the same functionality for local
            databases.

2.  Internal memory structures - Helps define System Requirements

    a.  System global area: Primarily composed of:

        1.  Data block buffers: Holds the data. This is the *cache
            hit/miss* thing you keep hearing about.

        2.  Redo log buffer: Redo log buffer: Tracks changes to the
            database.

        3.  Shared sql pool: Important parts of which:

            a.  Dictionary cache: Holds the dictionary. If the cache is
                too small, data dictionary will be queried, resulting in
                a recursive call.

            b.  Library cache (sql & pl/sql parsed statements)

3.  Background processes: - Helps define System Requirements

    a.  smon: System monitor

        1.  Responsible for database recovery

        2.  Coalesces contiguous extents (pctincrease = 0 in tables)

    b.  pmon: User process monitor -- cleans up failed user processes.

    c.  dbwr: Database writer -- manages the data block buffer cache.

    d.  lgwr: Log writer -- writes the redo log buffer cache to the redo
        logs.

    e.  chkp: Checkpoints:

        1.  Causes dbwr to write all dirty data blocks to disk

        2.  Update the data file headers & control file with the
            timestamp of the checkpoint.

    f.  arch: Archives redo logs before lgwr overwrites them.

4.  External structures: -Helps define Hardware Requirements

    a.  Files: Tablespace files are considered both internal, since
        they're used for tablespaces, and external, since they're OS
        dependent.

    b.  Redo logs:

        1.  A log of everything that happens on the database.

        2.  Used in the event of a database crash

    c.  Control files

        1.  Contains the entire architecture of the database. Very
            important

        2.  Maintained on separate disks (hopefully)

    d.  Trace files -- all background processes create trace files; used
        for indepth troubleshooting.

    e.  Alert log: Alert log keeps track of major database events

Space   is the biggest lesson learned\
Dev - Int - Prd \--\> important to have standards and do testing\
Hardware - duplicate machines to run instances on\
Disks -  LOTS of disks   -  Plan for current capacity and anticipated
growth\
Test -  and Test\
Follow Standards\
Configuration, configuration, configuration of hardware\
Tools for Daily Monitoring built

**Requirements**

-   Database Liaison position, especially an active liason with some\
    authority has been helpful The disk hardware options must be known.

-   The performance goals for the system must be identified.

-   The disk mirroring architecture must be known.

-   Disks must be dedicated to the database.

-   SPACE - **In fact, I venture to say, it has consumed more time than
    any other\
    issue.**

-   **Plan for current capacity and anticipated growth**

-   Requirements Definition and Implementation for each application

-   Identical Machines for testing -- follow dev -\> int -\> prd
    methodology

-   Integration instance - this separation allows changes to be made to
    any of these areas without effecting the others, and allows testing
    of all the applications together \-- Consider the importance of your
    testing approach again follow dev -\> int -\> prd methodology

-   Documentation on all schemas

-   Testing

-   Upgrade Plan to ensure Oracle plays nice with all applications

-   Simulation for sizing

Configuration, configuration, configuration

A very significant amount of our time was spent on configuration, or of
the various pieces of software infrastructure that were required to get
the whole circus marching up the street in the same direction.

Oracle: Daily tasks\
We have built in house tools to help monitor our the database and use
the OEM tool for monitoring as well as creating charts for space and cpu
usage. These tools are also used for decision making regarding tuning of
the database.

Designer tool

Following standards cvs, cutting scripts

Ddl in repositories, store all code

Work with application owners

## Recovery /backup

Tape

Disks

Robot/Library -- depending on size of database