Docstoc

Oracle Architecture-Oracle Day 1

Document Sample
Oracle Architecture-Oracle Day 1 Powered By Docstoc
					Oracle - J2EE (LC)

www.Fullinterview.com
Oracle Architecture
      Oracle Day 1
                    Objectives
   To Recall RDBMS concepts
   To introduce ORACLE and its products
   To introduce Oracle Client/Server Architecture
   To explain Oracle Physical structure-Data Files, Control Files,
     Redo Files
   To explain Oracle Logical Structure- Tablespaces,
     Segments, Extents, Blocks
   To introduce Schema objects-Tables, Sequences,
     Synonyms, Views, Indexes and Clusters
   To explain Oracle Memory Structures and Background
     Processes, Data Dictionary
RDBMS - Overview
•   Why RDBMS?
•   Keys
•   SQL
•   Embedded SQL
•   Transaction processing
      What is Oracle?

Oracle is a Relational Database
Management System.

• It is a management system which
  uses the Relational Data Model.
• In the Relational Data Model, data is
  seen by the users in form of tables.
        Oracle Products

Oracle broadly sells products in
  two categories

• Database Servers
• Application Development
  Tools
          Database Servers
• Oracle 10g Database
• Oracle 9i Database
• Oracle 8i Database
Application Development
          Tools

  • Oracle Developer (Includes Forms,
    Reports, Graphics etc.)
  • Oracle Designer
  • Oracle Discoverer
  • PL/SQL (Technology)
      Oracle Database Server
Oracle Database server:
                                 Oracle Server
   – Is a database
     management system
     that provides an open,
     comprehensive,
     integrated approach to
     information
     management
   – Consists of
      • An Oracle Instance and
      • An Oracle Database
Oracle Database Server Architecture
                                              Instance
    User
   process                Shared Pool           SGA

                             Library                              Redo Log
                                               Database
                             Cache                                 Buffer
                                              Buffer Cache
    Server               Data Dictionary
   process                   Cache                                Large Pool
         PGA
                    PMON SMON DBWR LGWR                         CKPT    Others



                                                                           Archived
                                 Datafiles   Control files   Redo Log
             Parameter                                         files       Log files
                file

             Password
                file
                                                         Database
           Oracle Database

Oracle Database:
   – Is a collection of data that is treated as a unit
   – Consists of three file types
                   Oracle Database

              Datafiles   Control
                           files     Redo
                                    Log files
                  Oracle Instance
  Oracle Instance:

         • Is a means to access an Oracle database
                  Instance
         • Always open one and only one
  Shared Pool       SGA

     Library database            Redo Log
                    Database
                                           Memory structures
         • Consists
     Cache                        Buffer
                   Buffer of memory structures and
                          Cache
 Data Dictionary
     Cache background processes Large Pool


PMON SMON DBWR LGWR         CKPT   Others      Background
                                                Processes
         Establishing a Connection
          and Creating a Session
Connecting to an Oracle Instance:
  – Establishing a user connection
  – Creating a session
                     Server
     Connection     process
     established              Session created
        User                   Oracle Server
      process




    Database user
         Physical Structure

The physical structure includes three types
  of files:
• Control files, Data files, Redo log files.
                Oracle Database
Other key files: Control
  Parameter Datafiles                     Archived
                      files    Redo
• Parameter file
     file                     Log files   Log files
  Password
• Password file
     file

• Archived redo log file
        Memory Structure

• Oracle’s memory structure consists of two
  memory areas known as:
  – System Global Area (SGA): Allocated at
    instance startup, and is a fundamental
    component of an Oracle Instance
  – Program Global Area (PGA): Allocated
    when the server process is started
           System Global Area
The SGA consists of several memory structures:
      •   Shared Pool
      •   Database Buffer Cache
      •   Redo Log Buffer
      •   Other structures (for example, lock and latch
          management, statistical data)
There are additional memory structures that can
  be configured within
the SGA:
      • Large Pool
            Shared Pool
– Used to store:
    • Most recently executed SQL statements
    • Most recently used data definitions
– It consists of two key performance-related
  memory structures:
    • Library Cache     Shared Pool
                          Library
    • Data Dictionary Cache
                          Cache
                           Data
– Sized by the parameterDictionary
                          Cache
  SHARED_POOL_SIZE
           Library Cache

– Stores information about the most recently
  used SQL and PL/SQL statements
– Enables the sharing of commonly used
  statements
– Is managed by a least recently used (LRU)
  algorithm
– Consists of two structures:
   • Shared SQL area
   • Shared PL/SQL area
– Size determined by the Shared Pool sizing
Data Dictionary Cache


– A collection of the most recently used
  definitions in the database
– Includes information about database
  files, tables, indexes, columns, users,
  privileges, and other database objects
– During the parse phase, the server
  process looks at the data dictionary for
  information to resolve object names
  and validate access
– Caching data dictionary information
  into memory improves response time
 Database Buffer Cache


– Stores copies of data blocks that have been
  retrieved from the datafiles
– Enables great performance gains when you
  obtain and update data
– Managed through the LRU algorithm
– DB_BLOCK_SIZE determines the block
      Database Buffer
          Cache
  size
      Redo Log Buffer

– Records all changes made to the database
  data blocks
– Primary purpose is recovery
– Changes recorded within are called redo
  entries
– Redo Redo Logcontain information to
        entries
          Buffer
  reconstruct or redo changes
– Size defined by LOG_BUFFER
        Large Pool


– An optional area of memory in the
  SGA
– Relieves the burden placed on the
  Shared Pool
– Used for:
   • Session memory (UGA) for the
     Shared Server
   • I/O server processes
   • Backup and restore operations or
     RMAN
    Program Global Area
– Memory reserved for each
                                        PGA
  user process connecting to Server
  an Oracle database          process
– Allocated when a process is
  created                       User
– Deallocated when the        process
  process is terminated
– Used by only one process
           Process Structure
Oracle takes advantage of various types of
  processes:
   – User process: Started at the time a database
     user requests connection to the Oracle
     server
   – Server process: Connects to the Oracle
     Instance and is started when a user
     establishes a session
   – Background processes: Started when an
     Oracle Instance is started
           User Process
– A program that requests interaction with
  the Oracle server
– Must first establish a connection
– Does not interact directly withServer
                                  the
  Oracle server User            process
                  process
                            Connection
                            established


         Database user
                Server Process
   – A program that directly interacts with
      the Oracle server
                Server
   – Fulfills calls generated and returns
   Connection  process
   established          Session created
      results
    User                  Oracle server
   – Can be Dedicated or Shared Server
  process




Database user
      Background Processes
• Maintains and enforces relationships between
  physical and memory structures
   – Mandatory background processes:
      • DBWR            PMON
            CKPT
      • LGWR            SMON
   – Optional background processes:
      • ARCn                    RECO
Database Writer (DBWn)
  Instance
    SGA
   Database
    Buffer
                            • DBWn writes
    Cache                     when:
                              – Checkpoint
 DBWn                           occurs
                              – Dirty buffers
                    Redo
                                reach threshold
Datafiles Control
           files
                    Log
                    files     – There are no
                                free buffers
        Database              – Timeout occurs
                              – Tablespace
                                OFFLINE
               Log Writer (LGWR)
               Instance                • LGWR writes:
                 SGA
                            Redo Log
                                          – At commit
                             Buffer       – When one-third
                                            full
             DBWn LGWR
                                          – When there is 1
                                            MB of redo
                                          – Every three
                    Redo
Datafiles Control   Log                     seconds
           files    files
                                          – Before DBWn
                                            writes
        Database
            System Monitor (SMON)
                Instance
                  SGA

                               • Responsibilities:
                                 – Instance
                                   recovery
      SMON
                                    • Rolls
                                      forward
                       Redo
Datafiles    Control   Log            changes in
              files    files
                                      redo logs
                                    • Opens
            Database
                                      database for
                                      user access
                                    • Rolls back
       Process Monitor (PMON)
         Instance   • Cleans up after failed
           SGA
                      processes by:
                       – Rolling back the
                         transaction
                       – Releasing locks
PMON
                       – Releasing other
                         resources
                       – Restarting dead
        PGA area
                         dispatchers
  Checkpoint (CKPT)
  Instance                  • Responsible for:
    SGA
                              – Signaling DBWn
                                at checkpoints
                              – Updating datafile
                                headers with
 DBWn LGWR CKPT
                                checkpoint
                                information
Datafiles Control
                    Redo
                    Log
                              – Updating control
           files    files       files with
                                checkpoint
        Database                information
        Archiver (ARCn)
 – Optional background process
 – Automatically archives online redo logs
    when ARCHIVELOG mode is set
                  Redo  ARCn
          Control
 – Preserves the record of all changes made
Datafiles
           files
                  Log
                  files      Archived Redo
                                Log files
    to the database
           Control File
– A small binary file
– Defines current state of physical database
– Maintains integrity of database
– Required:
   • At MOUNT state during database startup
                             Database
   • To operate the database
                              Control
– Linked to a single database files
– Loss may require recovery
– Sized initially by
  CREATE DATABASE
     Control File Contents

• A control file contains the following
  entries:
   – Database name and identifier
   – Time stamp of database creation
   – Tablespace names
   – Names and locations of datafiles and
     redo log files
   – Current redo log file sequence number
   – Checkpoint information
   – Begin and end of undo segments
         Redo Log Files
Redo log files have the following
  characteristics:
– Record all changes made to data
– Provide a recovery mechanism
– Can be organized into groups
                          Redo
– At least two groups required
                           Log
                           files
     Structure of Redo Log Files

Group 1     Group 2   Group 3
                                Disk 1

Member      Member    Member




Member      Member    Member    Disk 2
How Redo Log Files Work
– Redo log files are used in a cyclic fashion.
– When a redo log file is full, LGWR will
  move to the next log group.
   • Called a log switch
   • Checkpoint operation also occurs
   • Information written to the control file
 Archived Redo Log Files
– Filled online redo log files can be archived.
– There are two advantages in running the
  database in ARCHIVELOG mode and
  archiving redo log files:
   • Recovery: A database backup together
     with online and archived redo log files
     can guarantee recovery of all committed
     transactions.
   • Backup: This can be performed while the
     database is open.
– By default, database is created in
  NOARCHIVELOG mode.
Archived Redo Log Files

– Accomplished automatically by ARCn
– Accomplished manually through SQL
  statements
– When successfully archived:
   • An entry in the control file is made
   • Records: archive log name, log
     sequence number, and high and low
     system change number (SCN)
   • Filled redo log file cannot be reused
     until:
        Logical Structure
– Dictates how the physical space of a
  database is used
– Hierarchy Tablespace
             consisting of tablespaces,
  segments, extents, and blocks
              Datafile
                       Segment
     Segment




        Extent     Blocks
    Tablespaces and Datafiles
• Oracle stores data logically in tablespaces and
  physically in datafiles.
   – Tablespaces:
      • Can belong to only one database at a
        time
      • Consist of one or more datafiles
                         Database

      • Are further divided into logical units of
                           Tablespace
        storage
   – Datafiles:               Datafiles

      • Can belong to only one
        tablespace and one database
    Types of Tablespaces
– SYSTEM tablespace
   • Created with the database
   • Contains the data dictionary
   • Contains the SYSTEM undo segment
– Non-SYSTEM tablespace
   • Separate segments
   • Eases space administration
   • Controls amount of space allocated to a
     user
          Creating Tablespaces
• A tablespace is created using the command:
• CREATE TABLESPACE
CREATE TABLESPACE userdata
DATAFILE '/ora/data01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
        Dropping Tablespaces
      – Cannot drop a tablespace if it:
         • Is the SYSTEM tablespace
         • Has active segments
      – INCLUDING CONTENTS drops the
DROP TABLESPACE userdata
        segments
INCLUDING CONTENTS AND DATAFILES;
      – INCLUDING CONTENTS AND
        DATAFILES deletes datafiles
      – CASCADE CONSTRAINTS drops all
        referential integrity constraints
     Obtaining Tablespace
         Information

• Obtaining tablespace and datafile
  information can be obtained by querying
  the following:
   – Tablespaces:
      • DBA_TABLESPACES
   – Datafile information:
      • DBA_DATA_FILES
     Types of Segments

– Data Segment
– Index Segment
– Rollback Segment
– Temporary Segment
                   Rollback Segment
               Old image

                              Table



                                       New image



Rollback segment




                            Update transaction
         Rollback Segments: Purpose


                                          Transaction rollback




                                              Read consistency
Transaction recovery
                       Rollback segment
        Read Consistency
            SELECT *
Table      FROM table
Types of Rollback Segments
– SYSTEM: Used for objects in the
  SYSTEM tablespace
– Non-SYSTEM: Used for objects in other
  tablespaces:

      – Private: Acquired by a single instance
      – Public: Acquired by any instance
– Deferred: Used when tablespaces are taken
  offline immediate, temporary, or for
  recovery
Extent Allocation and Deallocation


      – An extent is a chunk of space used by a
        segment within a tablespace.
      – An extent is allocated when the segment
        is:
          • Created
          • Extended
          • Altered
      – An extent is deallocated when the
        segment is:
          • Dropped
 Manually Allocating Extents

ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);
         Database Block
– Minimum unit of I/O
– Consists of one or more operating system
  blocks
– Set at database creation
– DB_BLOCK_SIZE is the block size
Database Block Contents

              Header


              Free space




              Data
     Block Space
Utilization Parameters




          PCTFREE


          PCTUSED
      Row Migration and Chaining
Before update              After update




                 Pointer
    Database and Instance Startup
• Start database [reads Parameter file before
  this]
  – allocate & create SGA, Background processes
• Mount database
  – Associate database with previously started
    instance
  – Close database
  – Find and open control file
  – Read Redo /data file names from this file and
    confirm existence
• Open database
  – Open online log files and data files
  – Automatically perform instance
Database and Instance Shutdown

   • Close database
     – Write all buffer data to disk
     – Close Online redo files and data files
     – Close database
   • Dismount database
     – Close control files
     – Dissociate database from Instance
   • Shut down instance
     – Remove SGA from memory
     – Terminate background processes
                  Database Schema
•   A schema is a named collection   Schema Objects
    of objects.                      Tables
•   A user is created, and a         Triggers
    corresponding schema is          Constraints
    created.
                                     Indexes
•   A user can be associated only
    with one schema.                 Views
•   Username and schema are          Sequences
    often used interchangeably.      Stored program units
                                     Synonyms
                                     User-defined data types
                                     Database links
     Built-In Database Objects
• Other objects created with the database:
  – Data Dictionary
  – Performance tables
  – PL/SQL packages
  – Database event triggers
           Data Dictionary
–   Central to every Oracle database
–   Describes the database and its objects
–   Contains read-only tables and views
–   Stored in the SYSTEM tablespace
–   Owned by the user SYS
                   Control Redo
             Data
–                          Log
    Maintained by files Oracle server
             files   the files Database
–   Accessed with SELECT
         Data Dictionary
             tables
Base Tables and Data Dictionary
             Views
 • The data dictionary contains two parts:
    – Base tables
       • Stores description of the database
       • Created with CREATE DATABASE
    – Data dictionary views
       • Used to simplify the base table
         information
       • Accessed through public synonyms
       • Created with the catalog.sql script
     Data Dictionary Contents
The data dictionary provides information about:
  – Logical and physical database structures
  – Definitions and space allocations of objects
  – Integrity constraints
  – Users
  – Roles
  – Privileges
  – Auditing
How the Data Dictionary is Used
• Primary uses:
   – Oracle server uses it to find information about:
      • Users
      • Schema objects
      • Storage structures
   – Oracle server modifies it when a DDL
     statement is executed.
   – Users and DBAs use it as a read-only
     reference for information about the database.
         Data Dictionary View
              Categories
    – Three sets of static views
    – Distinguished by their scope:
DBA_xxx DBA: In all the database
         All of the objects in the schemas

        ALL: What the the current access
   ALL_xxx Objects accessible byuser canuser

        USER: In the user’s schema
      USER_xxx Objects owned by the current user
  Classification of Indexes
– Logical
   • Single column or concatenated
   • Unique or nonunique
   • Function-based
   • Domain
– Physical
   • Partitioned or nonpartitioned
   • B-tree
      – Normal or reverse key
   • Bitmap
          Comparing B-Tree and
            Bitmap Indexes
B-tree                           Bitmap
•Suitable for high-cardinality   •Suitable for low-cardinality
columns                          columns
•Updates on keys relatively      •Updates to key columns very
inexpensive                      expensive
•Inefficient for queries         •Efficient for queries
using OR predicates              using OR predicates

Useful for OLTP                  Useful for data warehousing
               Creating Indexes
CREATE INDEX empl_last_name_idx
ON empl(last_name);
Creating Indexes: Guidelines
– Balance query and DML needs
– Place in separate tablespace
– Use uniform extent sizes: Multiples of five
  blocks or MINIMUM EXTENT size for
  tablespace
– Consider NOLOGGING for large indexes
– INITRANS should generally be higher on
  indexes than on the corresponding tables.
            Dropping Indexes
     – Drop and recreate an index before bulk
       loads.
     – Drop indexes that are infrequently needed
       and build them when necessary.
DROP INDEX index1;
     – Drop and recreate invalid indexes.
  Obtaining Index Information

• Information about indexes can be obtained by
  querying the following views:
   – USER_INDEXES: Provides information on
     the indexes
   – USER_IND_COLUMNS: Provides
     information on the columns indexed
                 SEQUENCES
• Generation of Sequence numbers
• Sequence object stored in database
• Example
  – CREATE SEQUENCE SEQ_TRIAL
    INCREMENT BY 1 START WITH 1
  – insert into employees values (seq_trial.nextval,
    'Test', 1, 2);
  – use sequence.currval for current value of
    sequence
   Database Administrator Users
   Users SYS and SYSTEM are created
    automatically during database creation and
    granted the DBA role

• SYS
   – Password: change_on_install
   – Owner of the database data dictionary
• SYSTEM
   – Password: manager
   – Owner of additional internal tables and
     views used by Oracle tools
       Creating Users


CREATE USER swetalina
IDENTIFIED BY mamuni
DEFAULT TABLESPACE usr01
TEMPORARY TABLESPACE temp01
QUOTA 10M ON usr01;

Granting Privileges

GRANT <role-name> TO swetalina;
               Dropping a User
     – Use the CASCADE
DROP USER swetalina;       clause to drop all
       objects in the schema if the schema
       contains objects.


DROP USER swetalina CASCADE;



     – Users who are currently connected to the
       Oracle server cannot be dropped.
            Summary

We have discussed the following:

– Introduced ORACLE products
– Recalled RDBMS concepts
– Introduced Oracle Client Server Architecture
– Explained Oracle Physical structure-Data
  Files, Control Files, Redo Files.
– Explained Oracle Logical Structure-
  Tablespaces, Segments, Extents, Blocks
– Introduced Schema objects-Tables,
Thank You!

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:8/8/2011
language:English
pages:78