Introduction to Oracle

1
Introduction to Oracle

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

2
(No Transcript)
3
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.

4
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
  location.
• 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.

5
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.

6
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
  information.
• 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

7
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.

8
(No Transcript)
9
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
  instance.

10
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
  structures
• 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
  improved.

11
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
  statement.

12
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.

13
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
  reliability.
• 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.

14
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
  killed.
• 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
  first.
• 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.

15
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
  database.
• 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
  performance.

16
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
  database.
• 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.

17
(No Transcript)
18
Computer Science Database

• Server Information
• Sun e4500
• 8GB Ram
• 8 x 400mhz CPU
• 32GB Disk for Oracle
• 4mm DAT DDS3 Tape Backup

19
Computer Science Database

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

20
(No Transcript)
21
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
  NORMAL, IMMEDIATE, or TRANSACTIONAL options and
  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)

22
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

23
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

24
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

25
Administrative Tasks (contd)

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

26
Administrative Tasks (contd)

• Others
• Applying patches
• Database upgrades
• New Database installations
• Creating user accounts

27
More Information

• Oracle 10g Release 2 Database Documentation
• http//www.oracle.com/pls/db102/
• Oracle Database / SQL Help
• http//asktom.oracle.com/
• http//www.oracle.com/technology//index.html
• FREE Oracle Software Downloads
• http//www.oracle.com/technology/software/index.ht
  ml
• Oracle Database 10g Express Edition
• Oracle SQL Developer
• This Document
• Computer Science Homepage -gt On-Line help